Create Excel Spreadsheets with PL/SQL: ExcelDocumentType



Create Excel Spreadsheets with PL/SQL: ExcelDocumentType

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 executed (double click …). The documents generated by the object work very well with Office 2003. 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 two 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.

• worksheetHeaderFooterOpen – 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):

o Header String

o Header font size

o Use constants for Excel Specific header/footer values:

▪ WHF_FORMAT_LEFT – Left side header/footer

▪ WHF_FORMAT_CENTER – Center of header/footer

▪ WHF_FORMAT_RIGHRT – Right side header/footer

▪ WHF_FORMAT_PAGE – Insert current page number

▪ WHF_FORMAT_PAGES – Insert value for total number of pages

▪ WHF_FORMAT_DATE – Insert current date

▪ WHF_FORMAT_TIME – Insert current value for time

▪ WHF_FORMAT_FILEPATH – Display file path

▪ WHF_FORMAT_FILE – Display filename

▪ WHF_FORMAT_TAB – Insert name of current worksheet (“tab”)

▪ WHF_FORMAT_FONT – Font size place holder

• worksheetFooterValues – Sets the worksheet footer values (may be used one or more times if constructing a header with LEFT, RIGHT, and CENTER):

o Footer String

o Footer font size

o Use constants for Excel Specific header/footer values:

▪ WHF_FORMAT_LEFT – Left side header/footer

▪ WHF_FORMAT_CENTER – Center of header/footer

▪ WHF_FORMAT_RIGHRT – Right side header/footer

▪ WHF_FORMAT_PAGE – Insert current page number

▪ WHF_FORMAT_PAGES – Insert value for total number of pages

▪ WHF_FORMAT_DATE – Insert current date

▪ WHF_FORMAT_TIME – Insert current value for time

▪ WHF_FORMAT_FILEPATH – Display file path

▪ WHF_FORMAT_FILE – Display filename

▪ WHF_FORMAT_TAB – Insert name of current worksheet (“tab”)

▪ WHF_FORMAT_FONT – Font size place holder

• worksheetHeaderFooterClose – 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:

o Style name or label (‘My Style’)

o Font (Font name in Upper lower format: ‘Times New Roman’)

o Font Family (‘Roman’)

o Font Size

o Bold (Y or N)

o Italic (Y or N)

o Underline (underline type, such as ‘Single’)

o Text Color (‘Blue’, ‘Red’, etc …)

o Cell Color (‘Blue’,’Red’, etc …)

o Cell Texture (‘Solid’ or other valid Excel texture types)

o Vertical Alignment (‘Center’,’Left’,’Right’,’Top’, etc …)

o Horizontal Alignment (‘Center’,’Left’,’Right’,’Top’, etc …)

o Text Wrap (Y or N)

o Number Format (Standard Excel number format strings)

o 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:

o Column Index – as defined with defineColumn procedure

o Data – The data that will occupy the cell.

o Data Type – String, Date, Number, etc (Excel standard)

o Style – Apply user defined style by label name.

o 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

IS

demoDocument ExcelDocumentType;

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;

-- Close the document.

demoDocument.documentClose;

-- Display the document.

demoDocument.displayDocument;

EXCEPTION

WHEN OTHERS THEN

htp.p(sqlerrm);

END;

/

Specific Examples …

How to create customer headers and footers:

demoDocument.worksheetOpen('MyWorkSheet');

-- Open Header/Footer section

demoDocument.worksheetHeaderFooterOpen;

-- Add page number “1 of 10” to the left side of the header

demoDocument.worksheetHeaderValues(p_headerstring=>demoDocument.WHF_FORMAT_LEFT||

demoDocument.WHF_FORMAT_PAGE||

' of '||demoDocument.WHF_FORMAT_PAGES);

-- Add some text for a title to the center portion of the header with a font size of 12

demoDocument.worksheetHeaderValues(p_headerstring=>demoDocument.WHF_FORMAT_CENTER||

demoDocument.WHF_FORMAT_FONT||'My Title',

p_fontsize=>'12');

-- Add the date and time to the left side of the header

demoDocument.worksheetHeaderValues(p_headerstring=>demoDocument.WHF_FORMAT_RIGHT||

demoDocument.WHF_FORMAT_DATE||' '||

demoDocument.WHF_FORMAT_TIME);

-- Add the current filename to the right side of the footer.

demoDocument.worksheetFooterValues(p_footerstring=>demoDocument.WHF_FORMAT_RIGHT||

demoDocument.WHF_FORMAT_FILE);

-- Close the header and footer section.

demoDocument.worksheetHeaderFooterClose;

How to send your Excel XML document to a file:

If you are sending the document to file from within a PL/SQL package, I recommend setting up a directory mapping using the following command:

CREATE DIRECTORY MYDIRECTORY as ' ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download