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 HandsOnTable.com 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.

and 

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. ;)

 

Looking at it under the hood there is not much to it. We are passing in a dataObject that contains array of data we want to use. We define some settings in hotSettings which then get passed Handsontable function which initiates and builds up things for us. Feels almost what IG is doing.

 

Perfect so far, how do we tweak this and hook our own DEPT data for example. 

 

Let's create a page item and a before header process doing this fo us

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT d.dname AS "department_name",
           d.deptno AS "department_number",
           d.loc as "location"
    FROM   dept d
    ORDER BY d.dname;
  APEX_JSON.initialize_clob_output;
  APEX_JSON.write( l_cursor); 
  --  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  :P1_DEPT_DATA := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;
END;

Run the page to see the result - JSON we are storing in PX_DEPT_DATA. Now we need to pass this into Handsontable initialization. All code you need for this demo is here by the way.

 

To be able to do this we need updating our original JS to:

Again any code needed is available under that link so it should be easy copy and paste. Save and run the page. There we have it Handsontable direclty using our DEPT data. 

Give it a go see how it feels what features it offers out of the box.

To finalize the puzzle, how do we now store this data after you changed it. 

 

For the sake of demo we will split this into two parts...... First we will do Save part then Run part which will write to the database.  

 

On SAVE button add a Dynamic action 

 

JavaScript part is 

var myJSON = JSON.stringify(dataObject);
apex.item("P1_DEPT_DATA").setValue(myJSON);

PLSQL side 

 

Before we can do the third refresh action let's create a report that will be based on user entered data. Create classic report with this SQL 

SELECT deptno, dname, location
FROM   XMLTABLE(
          '/json/row'
          PASSING APEX_JSON.to_xmltype(:P1_DEPT_DATA)
          COLUMNS
             deptno NUMBER PATH '/row/department_number',
             dname  VARCHAR2(14) PATH '/row/department_name',
             location VARCHAR2(14) PATH '/row/location')
where dname is not null
order by 2;

Beauty here is we are creating a XMLTABLE from our JSON data that we can query later on.

If I update my sheet with new data and press Save button

We see that we managed to read user entered data which we now can easily use for PLSQL processing.  Only thing missing is to create a PL/SQL process 

to write your data to the database. So cool and yet so simple.

 

This is only a vanilla demo but what we get is a very quick way for people to enter data - they can copy and paste rows from their Excel sheets into here and vice versa. They can entered data same way how how they would in they worksheets but giving them freedom to use probably best from both worlds in your app. Maybe this is all you will need.

 

Of course we can now take this a step forward where Fetch and Save would be done using AJAX process but it is material for another day. If Handsontable does not cut for you it maybe look for alternative.

 

I am hoping this gives you ideas to explore this a little more deeper. This could be a great way if we need to process larger tabular forms which were always a challenge doing in APEX.

 

Leavning you with a live demo. 

 

Happy APEXing,
Lino