Uploading XLSX files is a very common requirement these days. Application Express at this time only supports uploading CSV files out of the box. The community provides quite a few blog postings and also plug-ins; most of these store the data into a collection, which makes it hard to parse an XLSX outside of an APEX context, e.g. in the background using a scheduler job.
This blog posting shows a method to parse XLSX files with only SQL and PL/SQL. The actual work will be done by a table function, thus no collections and no APEX session is required. Since a table function returns rows and columns, one can easily insert the results into a table, apply some transformation logic, push it into the background with a scheduler job or just display.
The XLSX format is based on XML: an XSLX file is actually a ZIP file (you can easily verify that by renaming to .zip and opening it) containing several XML files. To parse the data, we first have to extract the contents, look up the right XML files and parse these. For the latter task, the XMLTABLE SQL function comes in very handy.
The following example code creates an XLSX_PARSER package. It uses the APEX_ZIP package to unpack the XLSX file and the XMLTABLE SQL function in order to parse the XML. The PARSE function is implemented as a table function which returns the first 50 columns ("A" to "AX"). If you need more columns, you can easily extend the code accordingly; the upper limit is 1000 columns.
The package provides the following functions:
FUNCTION GET_WORKSHEETS RETURNS APEX_T_VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_CONTENT BLOB IN DEFAULT P_XLSX_NAME VARCHAR2 IN DEFAULT FUNCTION PARSE RETURNS XLSX_TAB_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_NAME VARCHAR2 IN DEFAULT P_XLSX_CONTENT BLOB IN DEFAULT P_WORKSHEET_NAME VARCHAR2 IN DEFAULT P_MAX_ROWS NUMBER IN DEFAULT
You can pass either the XLSX content as a BLOB or the name of an uploaded file. In the latter case, the package will look up the file name in the APEX_APPLICATION_TEMP_FILES table. A typical call sequence is as follows:
Let's try this out. Create an APEX application with an empty page, then add the following elements to that page.
Your page should now look as follows:
You can already upload a file, but nothing will happen. Next, we'll want to show a select list to pick one of the worksheets contained in the XLSX file.
select column_value d, column_value r from table( xlsx_parser.get_worksheets( p_xlsx_name => :PX_XLSX_FILE ) )
Now run your page again. After you have uploaded an XSLX file, your page should look as follows:
So we can now pick a worksheet. So far, so good. Time to actually do the job and extract data from the XLSX file. For now, we just want to display the data as a classic report. So create a Classic Report region and use the following SQL Query.
select * from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) );
Add the PX_WORKSHEET item to the Classic Reports Page Items to Submit attribute. Then add a Dynamic Action in order to refresh the report when a worksheet has been chosen in the Select List item.
You then should be able to do the following steps:
And that's it ... the nice thing is that there are no limits to process the worksheet data ...
create table worksheet_data as select col02 as first_name, col03 as last_name, col05 as country from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) );
insert into worksheet_data( select cast( col02 as varchar2(200) ) as first_name, cast( col03 as varchar2(200) ) as last_name, case col04 when 'Female' then 'F' when 'Male' then 'M' end as gender, cast( col05 as varchar2(200) ) as country, to_number( to_char( sysdate, 'YYYY') ) - to_number( col06 ) as birth_year from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ) where line# != 1)
Try the sample code out - once the table function returns data, there are no limits for further processing any more. You can do with the data whatever you want: from just displaying over loading into a table to executing complex processing in the background: Everything is possible.