Create xls file using PL/SQL without going through

Question

My system is developed in APEX/Oracle 11g, and I want to create an xls file directly without having to create an xml file. The system currently creates an xml file which can then be saved to xls format, but the user, who is very picky, does not like the Windows 7 warning when one tries to open the xml file (Excel warning that the format of the file does not match its extension). Is there any way to use Oracle PL/SQL from within APEX to accomplish this?

Answer 1

You can use the OraExcel package to generate real xlsx files (in binary form). With that package you can format cells and apply styles just like in Excel.

It has a simple API where you can create Excel files step by step and describe every cell how you want to look like.

When you finish your Excel file there is an option to generate xlsx file to PL/SQL BLOB variable which can be returned to APEX and downloaded.

There are no warnings that are annoying to your customers.

You can write a simple function, like the one below, to create an excel spreadsheet and return it to APEX:

CREATE OR REPLACE FUNCTION get_excel RETURN BLOB IS
   my_blob BLOB;
BEGIN

  ora_excel.new_document;
  ora_excel.add_sheet('My sheet');

  ora_excel.query_to_sheet('SELECT field1, field2, field2 FROM my_table');

  ora_excel.save_to_blob(my_blob);

  RETURN my_blob;

END;

There are more examples on: http://www.oraexcel.com/examples

Cheers

Answer 2

You can use a Java Stored Procedure

http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm

and Apache Poi

http://poi.apache.org/spreadsheet/index.html

to create actual Spreadsheets and store those in the lob field, and to return to the user

Answer 3

Morten Braten has put together a great PLSQL resource page: http://code.google.com/p/plsql-utils/

Specifically Anton Scheffer has shared his package AS_XLSX which would meet your APEX needs: http://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

Very simple to use with good examples in the package header.