Generate a native Excel file with SQLX

8

I wanted to generate an Excel file with data coming from an Oracle database. One of the best ways to do this is to generate an Microsoft Excel XML file. Starting from Office 2003 this XML format is supported. This way you are able to generate a native Excel file in stead of a CSV file.

In a previous post I explained how SQLX can be used to generate XML. With SQLX I was able to generate the Excel XML with only one query. The input data (Excel cell data) is supplied into the query by using a table of objects. This table of objects can be instantiated and filled by any query that supplies the data that you want to have in your Excel file. The advantage of this table of objects is that the Excel data is provided by a parameter making the query generic.

....
– Object holding cell data
CREATE OR REPLACE
TYPE xls_cell AS OBJECT
( cell_col  NUMBER(8)
,cell_row  number(8)
,cell_Type varchar2(60)
,cell_value VARCHAR2(2000)
,cell_style VARCHAR2(10)
)

– Table of cells
CREATE OR REPLACE
TYPE xls_cells AS TABLE OF xls_cell ;

The following example fills the table with 3 rows and 5 columns:

function test
return  xls_cells
is
  l_cells xls_cells := xls_cells();
begin
  l_cells.extend(15);

  l_cells(1) := xls_cell(1,1,'String','test11','s23');
  l_cells(2) := xls_cell(2,1,'String','test12','s23');
  l_cells(3) := xls_cell(3,1,'String','test13','s23');
  l_cells(4) := xls_cell(4,1,'String','test14','s23');
  l_cells(5) := xls_cell(5,1,'String','test15','s23');

  l_cells(6) := xls_cell(1,2,'String','test21','s22');
  l_cells(7) := xls_cell(2,2,'String','test22','s22');
  l_cells(8) := xls_cell(3,2,'String','test23','s22');
  l_cells(9) := xls_cell(4,2,'String','test24','s22');
  l_cells(10) := xls_cell(5,2,'String','test25','s22');

  l_cells(11) := xls_cell(1,3,'String','test31','s22');
  l_cells(12) := xls_cell(2,3,'String','test32','s22');
  l_cells(13) := xls_cell(3,3,'String','test33','s22');
  l_cells(14) := xls_cell(4,3,'String','test34','s22');
  l_cells(15) := xls_cell(5,3,'String','test35','s22');

  return l_cells;
end;

The following query generates a CLOB holding the Excel XML data. The input data is provided in the p_data parameter. This parameter is of type xls_cells. The p_worksheetname is a varchar2 input parameter holding the name of the Excel sheet.

with row_counter -- Select the number of rows
as   (select distinct cell_row as cell_rows
    from table(cast(p_data as xls_cells)) -- Cast the table of object to a table, usable in a query
    )
,    col_counter -- Select the number of columns
as   (select distinct cell_col as cell_cols
    from table(cast(p_data as xls_cells))
    )
select XMLElement("Workbook", XMLAttributes('http://www.w3.org/TR/REC-html40' AS "xmlns:html"
                                            ,'urn:schemas-microsoft-com:office:spreadsheet' AS "xmlns:ss"
                                            ,'urn:schemas-microsoft-com:office:excel' AS "xmlns:x"
                                            ,'urn:schemas-microsoft-com:office:office' AS "xmlns:o"
                                            ,'urn:schemas-microsoft-com:office:spreadsheet' AS "xmlns"
                                            )
                  , XMLElement("Styles"
                    , XMLElement( "Style", XMLAttributes( 'Normal' AS "ss:Name", 'Default' AS "ss:ID") -- Generate a style
                                , XMLElement("Alignment", XMLAttributes ('Bottom' AS "ss:Vertical"))
                                , XMLElement("Borders")
                                , XMLElement("Font")
                                , XMLElement("Interior")
                                , XMLElement("NumberFormat")
                                )
                    , XMLElement( "Style", XMLAttributes( 's23' AS "ss:ID")                            -- Generate a style
                                , XMLElement("Alignment")
                                , XMLElement("Borders")
                                , XMLElement("Font", XMLAttributes ('1' AS "ss:Bold",'Swiss' AS "x:Family"))
                                , XMLElement("Interior", XMLAttributes ('Solid' AS "ss:Pattern",'#C0C0C0' AS "ss:Color"))
                                , XMLElement("NumberFormat")
                                )
                    , XMLElement( "Style", XMLAttributes( 's22' AS "ss:ID")                            -- Generate a style
                                , XMLElement("Alignment")
                                , XMLElement("Borders")
                                , XMLElement("Font")
                                , XMLElement("Interior")
                                , XMLElement("NumberFormat", XMLAttributes ('0' AS "ss:Format"))
                                )
                  )
                  , XMLElement( "Worksheet", XMLAttributes( p_worksheetname as "ss:Name")
                    , XMLElement( "Table"
                      , ( select XMLAgg( XMLElement( "Column", XMLAttributes( '20' as "ss:Width")) -- Predefine the columns
                                        )
                          from   col_counter
                        )
                      , ( select XMLAgg( XMLElement( "Row"                                         -- Generate a row
                                                , ( select XMLagg(XMLElement( "Cell", XMLAttributes( cell_style as "ss:StyleID") -- Generate a Cell
                                                                            , XMLElement("Data", XMLAttributes( cell_type as "ss:Type"), cell_value
                                                                              )
                                                                            )
                                                                  )
                                                    from   table(cast(p_data as xls_cells))
                                                    where  cell_row = row_counter.cell_rows        -- Make sure the cells are in the correct row
                                                  )
                                                )
                                    )
                          from  row_counter
                        )
                      )
                    )
                  ).getclobval()
into l_xls
from dual;

A drawback of this query is that it only generates one sheet. But this could of course by easily added to this query.

Share.

About Author

8 Comments

  1. Hi John,
    I wrote a similar package as Jason. As you said, the problem is the amount of tags needed. Usually the file will have 3 or 4 times the size of a binary .XSL file. I have also tried an approach with generating HTML files, yet dropped it because of lack of features, mostly the restriction to one worksheet (You can do more than one, but then it becomes comlicated). Even then the size was comparable to the XML version. Have you tried sylk format? A link can be found at the end of
    http://matzberger.de/oracle/spreadsheet-en.htmls.

  2. I have reviewed and tested both this code and Jason’s code. Both are excellent pieces of work.
    I have implemented Jason’s code to help me build spreadsheets through xml.
    My problems are as follows: scalibility (over 50 columns and over 1,000 rows of data) and the
    ability to generate reports with the columns being created dynamically with user input.
    This can be done, but the report generation is slow because of the amout of xml tags required to
    format the data. The files can up to 10 times the size of an equivalent .csv file.

    I am also restricted to using Cold Fusion MX7 application server.

    As anyone found any other ways to create formatted excel from Oracle?

    Thanks

  3. I have developed code along the same line, but I took the approach of creating a user defined type (or object) that allows the user to create the document in an API type format. You can create custom styles, formulas, multiple worksheets, etc. The document can be displayed directly through the browser, dumped into a CLOB, or dumped into an array for processing to a physical file. The code can be located here:

    http://radio.weblogs.com/0137094/2006/10/26.html

  4. Hi, This is great piece of code….however it does not seem to scale well when you have rows thats are over 4k. Has anyone encounted this issue or have a resolution for it.

  5. To make my tech comment readable, replace [ and ] with to the code below:

    ‘[?xml version="1.0"?][?mso-application progid="Excel.Sheet"?]‘

    and prefix this to the clob

  6. Hi,

    Tried it, cool! If you would combine this with the possibilities of e.g.
    Oracle XDB repository, I can think of a lot of places where it can be used.

    The other way around is powerfull as well: read excel data (saved as xml file, ftp-ed
    to XDB) into the relational world:
    parse the xml (dbms_xmldom, xquery) and do with it what you want.

    One little technical note: to make excel 2003 recognize the file, the xml process instructions

    should be prefixed to the result.

    Cheers, Marc