Jason Bennett's Developer Corner

Home

My Articles

Recommended Books

Contact Me

A Little About Jason Bennett ...

I've had an interest in publishing technical articles and HELPFUL code for a few years. I am (by trade and hobby) a developer who specializes in Oracle technologies and web based architectures. I have been an employee of both TUSC and Oracle Corporation. My intent here is to share my ideas and coding experiences with the developer community as a whole. As with all developers some of my ideas are great and some of them are .... well you know. Anyway, I hope you find something here that will aid in your endeavor, or spark a new idea.
I am more than happy to assist with technical issues and will even write a little code if need be. If you find something on the site that is really useful and you'd like to make a contribution (absolutely up to you and absolutely not required), just click the "Make a Donation" button on the left!
Good luck and good coding !

	Thursday, October 26, 2006

Create Excel Spreadsheets with PL/SQL: ExcelDocumentType

[Updated 04/14/2009 to allow for custom tag attributes on cell, row, and column definition. Document columns calculated differently, allowing for flexible

layout. Jason Bennett]

This little project started about a year ago (give or take), after the need arose to generate Excel formatted reports containing data from our primary Oracle database instance (at work). In the past, I had done this by manually running a query in TOAD and saving the results as an Excel spreadsheet. This works great for those little adhoc requests we all get from time to time from our customers. However, the manual method can quickly get out of hand and distract us from our "real" work ... developing applications. So, being the ever resourceful developer, I decided that it would be cool to automate the process. My search for pre-existing, free, and easy to use tools to generate Excel spreadsheets directly from the database turned up very little. The search did turn up with some very useful information on Excel XML. Armed with this new knowledge, I set out to create a PL/SQL based utility that would easily allow the user to generate formatted Excel spreadsheets from their data. I ended up creating an Oracle user defined object type called ExcelDocumentType.

The ExcelDocumentType generates an Excel XML document as its end product. Excel XML documents are automatically recognized by IE and the Windows OS as Excel documents, and are treated as such when opened (double click ...). The documents generated by the object work very well with MS Office 2003 and Open Office 2.0 (However, you have to open the file thru Open Office Calc explicitly ... it will not open it automatically). The object gives the user the ability to create documents with the following features:

Creation of multiple Worksheets
Create and apply user defined styles
Apply formulas to cells
Create custom print headers
Define rows, columns, and cells

The object provides three methods of document retrieval:

The document can be retrieved as a CLOB.
The document can be delivered through mod_plsql to a web browser.
The document can be retrieved in a PL/SQL table via user defined type called ExcelDocumentLine.

Object Member Functions and Procedures

The ExcelDocumentType makes use of a global temporary table called ExcelDocumentStore . The global temporary tables acts as storage for each document segment as the document is being constructed. A previous version used VARRAYs, but that method had some serious performance issues for very large documents. The object contains the following member functions and procedures:

ExcelDocumentType - Constructor (Function).
documentOpen - Open a document for writing.
documentClose - Close document for writing.
worksheetOpen - Opens a new worksheet, and takes a parameter for naming the worksheet.
worksheetClose - Closes the worksheet.
worksheetHeaderOpen - Open the header segment (of a worksheet) for writing. Takes no parameters.
worksheetHeaderValues - Sets the worksheet header values (may be used one or more times if constructing a header with LEFT, RIGHT, and CENTER positioned values):
- Header Text
- Header location (LEFT,RIGHT,CENTER)
- Header format string
- Header font size
worksheetHeaderClose - Closes worksheet header segment.
stylesOpen - Open the style definition segment.
createStyle - Create user defined styles. Each style has to be named so that it may be referenced later. Styles items are limited to the following parameters:
- Style name or label ('My Style')
- Font (Font name in Upper lower format: 'Times New Roman')
- Font Family ('Roman')
- Font Size
- Bold (Y or N)
- Italic (Y or N)
- Underline (underline type, such as 'Single')
- Text Color ('Blue', 'Red', etc ...)
- Cell Color ('Blue','Red', etc ...)
- Cell Texture ('Solid' or other valid Excel texture types)
- Vertical Alignment ('Center','Left','Right','Top', etc ...)
- Horizontal Alignment ('Center','Left','Right','Top', etc ...)
- Text Wrap (Y or N)
- Number Format (Standard Excel number format strings)
- Custom XML - add custom (but compliant) XML for additional style elements.
defaultStyle - use the default Excel document style.
stylesClose - Close styles segment
defineColumn - Defines column and takes index and width as parameters.
rowOpen - Starts a new row segment. Takes an optional style parameter to set row color, font, etc.
rowClose - Close row segment.
addCell - Add a cell segment to a row segment. Cell reside in a specific column within a specific row. This procedure takes the following types of parameters:
- Column Index - as defined with defineColumn procedure
- Data - The data that will occupy the cell.
- Data Type - String, Date, Number, etc (Excel standard)
- Style - Apply user defined style by label name.
- Formula - Valid Excel cell formula string.
displayDocument - generates the entire Excel XML document for display thru a web browser or other application that can accept a document via HTTP (thru mod_plsql).
getDocument - Function that generates a CLOB version of the Excel XML document.
getDocumentData - Function that returns a Collection of type ExcelDocumentLine. Each bucket in the collection will contain a line from the XML document. The collection type is a PL/SQL table.

The following code sample demonstrates how to use the ExcelDocument Object:

CREATE OR REPLACE PROCEDURE excelObjectTest

demoDocument ExcelDocumentType;

-- An array containing each line of the document.

-- Very useful when writing the document to a file

-- on the file system

documentArray ExcelDocumentLine := ExcelDocumentLine();

-- The document can be retrieved as a CLOB and stored in a table.

clobDocument CLOB;

-- File handle used in writing the document to file

v_file UTL_FILE.FILE_TYPE;

BEGIN

demoDocument := ExcelDocumentType();

-- Open the document
demoDocument.documentOpen;

-- Define Styles

demoDocument.stylesOpen;

-- Include Default Style
demoDocument.defaultStyle;

-- Add Custom Styles

     /* Style for Column Header Row */
     demoDocument.createStyle(p_style_id =>'ColumnHeader',
                               p_font     =>'Times New Roman',
                               p_ffamily =>'Roman',
                               p_fsize    =>'10',
                               p_bold     =>'Y',
                               p_underline =>'Single',
                               p_align_horizontal=>'Center',
                               p_align_vertical=>'Bottom');

    /* Styles for alternating row colors. */
    demoDocument.createStyle(p_style_id=>'NumberStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid',
                               p_number_format => '###,###,###.00',
                               p_align_horizontal => 'Right');

    demoDocument.createStyle(p_style_id=>'TextStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid');

    /* Style for numbers */
    demoDocument.createStyle(p_style_id => 'NumberStyle',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'ColumnSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Blue');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'RowSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Red');

-- Close Styles
demoDocument.stylesClose;

-- Open Worksheet
demoDocument.worksheetOpen('Weekly Earnings');

     -- Define Columns
     demoDocument.defineColumn(p_index=>'1',p_width=>30); -- Emp Name
     demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
     demoDocument.defineColumn(p_index=>'3',p_width=>16);
     demoDocument.defineColumn(p_index=>'4',p_width=>16);
     demoDocument.defineColumn(p_index=>'5',p_width=>16);
     demoDocument.defineColumn(p_index=>'6',p_width=>16);
     demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column

-- Define Header Row
demoDocument.rowOpen;

   --Define Header Row Data Cells
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');

demoDocument.rowClose;

   /*------------------------------------*/
   /* Sheet Data would normally be       */
   /* data driven via cursor loops       */
   /* or other means.                    */
   /* The purpose here is to demonstrate */
   /* the features of the utility.       */
   /*------------------------------------*/

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'50000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'14000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 2
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Joe Smith');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Jones');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'300');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'350');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'2000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'159');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Chris P.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'45000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Summary Row 5
   demoDocument.rowOpen;
   demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.rowClose;

-- Close the Worksheet
demoDocument.worksheetClose;

-- Open New Worksheet
demoDocument.worksheetOpen('Weekly Earnings 2');

-- Define Header Row
demoDocument.rowOpen;

demoDocument.rowClose;

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'80000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'75000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'94000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Smith');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'77000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'850');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'359');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell', p_data=>'Jeff F.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'99000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

-- Close the Worksheet
demoDocument.worksheetClose;

-- Close the document.
demoDocument.documentClose;

-- Get CLOB Version

clobDocument := demoDocument.getDocument;

-- Display the document to browser.
demoDocument.displayDocument;

-- Write document to a file
-- Assuming UTL file setting are setup in your DB Instance.
--
-- documentArray := demoDocument.getDocumentData;

-- Use command CREATE DIRECTORY FOO as ''
-- to create a directory for the file.

--v_file := UTL_FILE.fopen('FOO','ExcelObjectTest.xml','W',4000);

   --FOR x IN 1 .. documentArray.COUNT LOOP

    -- UTL_FILE.put_line(v_file,documentArray(x));

-- END LOOP;

-- UTL_FILE.fclose(v_file);

EXCEPTION
WHEN OTHERS THEN
      /* For displaying web based error.*/
      htp.p(sqlerrm);
      /* For displaying command line error */
      dbms_output.put_line(sqlerrm);

END;

The following document was generated by the code above (opens a new window):

ExcelObjectTest.xml

The Code ...

The following link will allow you to download a zip file containing the ExcelDocumentObject and the demonstration code above. The object has been tested in both Oracle9i and Oracle 10g.

ExcelDocumentType Code

As with all of my code, please feel free to improve it (and pass along the improvement) and distribute it.

10:52:21 PM