Friday 21 August 2020

EXCEL like tables in Oracle APEX

Oracle APEX and Handsontable integration

  Excel or Google Sheets like tables in APEX application

This time something different about yet another cool little integration into your APEX apps. 
There is a "no thank you" feeling spread around our dev community when you get asked by people that are prone to using Excel like people less skilled in IT, people in finance or project management to help them build something that looks like Excel or Google Sheets. 
We might say: "Yes definitely you are at the right place and I have the tool for the job" - but let's face it we are all thinking: "Oh boy there will be a huge learning curve" getting them to use APEX screens and to love DB/relational things compared to what they are used to. 
On the other side they have this 'cool' little and very often old spreadsheets doing some fancy logic to process their data. 
So how it normally works they give you some crazy impossible deadline, you go back into your cocoon you build up your APEX muscles and get a perfect demo out in absolutely no time.  They test it like everything you did they even like APEX features like reporting, charting, development speed and above all that they are finally storing they data in a proper secure manner which is in a database. All happy there, project grows and then their pleads to have Excel like things starts to grow. 
If for nothing there is two obvious reasons - the speed to enter/change their data especially on a large scales and "mouseless" interaction they got so used to when working in their sheets.
  • Copy and paste data in and out
  • Undo/Redo action
  • Alt +..... Shift +, CTL + shortcuts
  • Formulas
  • Formatting 
  • ....
We would all have our favorites shortcut even us who are not using it on daily bases, right!? I know, I know... you are already saying where am I going with this mixing apples and pears. Different tools different needs etc....
Correct APEX is a web app and we can do so much already with its existing features like Interactive reports, Grids, Cards, Forms, Template options and so on..... but then again making your end users happy is the main reason why we are here. This is probably one of mine sweet spots of APEX its ability to integrate almost anything we need into it. 
What if we can bring that worksheet like feeling into our apps with little or no trouble. Requirements are nothing big: use the mouse, being able to copy data in and out maybe use a right click on occasion, sort data, quickly enter data and save it to the database. 
By this I do not mean - Interactive grids (sorry John :) ). I do not know what is it with IGs, but people seem to wither to hate them or love them. Usually the one on the wrong side of this are people 'spoiled' by habit of using spreadsheets. Without further going into this discussion in this post we will step outside of IGs for a moment. 
To cut the story short here..... there are tons of available JavaScripts spreadsheet libraries available that we can try implementing. In this case we will see Handsontable JS in action. 
Reason why is simple  - it is easy to integrate, well documented, tables seem very intuitive to use and they really feel like Excel. Drawback is there is a license fee associated with using it commercially. Since we are only using it for demo here no issue with me trying it out.   
Let's create our first demo. 
Create a page, create a region with a static ID = dept_region set Blank with Attributes for its template. Add two buttons for latter SAVE and RUN. Something along these lines for now.
If you open click Demo link we get all we need to integrate it into APEX. 
You can immediately see how it all works. Under the code section there is HTML/JS we need to get over to APEX.
First copy library links into your APEX page settings.


Now copy all JavaScript code under Function and global Variable declaration and change one line of code
var hotElement = document.querySelector('#dept_region');
Save and run. This part was easy wasn't it. ;)