PL/SQL ExcelDocumentType
Latest release date: 01/15/2012
(Download the code here:Code)
(For complete ExcelDoctypeUtils PL/SQL API documentation , see the following document (opens in new window):ExcelDocTypeUtils API Guide)
The ExcelDocumentType is an Oracle User Defined Type (UDT) that allows a developer to generate fully functional Microsoft Excel Spreadsheets using PL/SQL directly from the database. The spreadsheets will be encoded as Microsoft Excel 2003 XML Spreadsheets. Microsoft Excel will recognize the files with both a .xml or .xls extension (Excel 2007 will ask user for permission to open the file if the .xls extension is used …). The ExcelDocumentType supports the following Excel features:
- Creation of multiple worksheets
- Custom Header and Footer
- Creation of custom Excel styles (color, font, number formats, text rotation, etc.)
- Creation and application of formulas
- Worksheet titles (expanded cell)
- Worksheet naming (sheet tabs)
- Creation and application of worksheet level conditional formatting.
- Creation of hyper-linked cells (Sheet to sheet, external links, or email links).
- Custom cell attributes (in case you want to apply one that isn’t already provided).
- Freeze Column Header Row
The ExcelDocumentType provides three direct methods of document retrieval/delivery:
- The document can retrieved as a CLOB.
- The document can be streamed directly to a web browser through a PL/SQL DAD (if delivering content to the browser using PL/SQL).
- The document can be retrieved in a PL/SQL table via a user defined type called ExcelDocumentLine
The code packaged with the ExcelDocumentType includes a PL/SQL Package (API) called ExcelDocTypeUtils that makes the creation of a spreadsheet very simple. The following examples demonstrate how to create a spreadsheet using the ExcelDocumentType directly and then using the ExcelDocTypeUtils package.
Example 1. (Without the ExcelDocTypeUtils PL/SQL package … the long way)
ISdemoDocument ExcelDocumentType;
documentArray ExcelDocumentLine := ExcelDocumentLine();
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 Sheet Conditional Formatting values
demoDocument.worksheetCondFormatOpen(p_range=>’R2C2:R4C7′);
demoDocument.createCondFormat(p_qualifier=>’Between’,
p_value=>’0,2000 ‘,
p_format_style=>’color:green’);
demoDocument.createCondFormat(p_qualifier=>’Between’,
p_value=>’2001,10000000′,
p_format_style=>’color:red’);
demoDocument.worksheetCondFormatClose;
– 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 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=>’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 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 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;
– 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 to browser.
demoDocument.displayDocument;
EXCEPTION
WHEN OTHERS THEN
/* For displaying web based error.*/
htp.p(sqlerrm);
END;
/
Here is a link to the spreadsheet generated by the code (The file is an XML file with a .xls extension):
ExcelObjectTest.xls
Here is a screen shot of the resulting spreadsheet:
Example 2. (Using the ExcelDocTypeUtils PL/SQL package … the easy way):
* This example covers a few features:
* – Multiple worksheets with multipe queries
* – Creating Styles and applying them to columns
* – Worksheet Title (spanning multiple cells)
* – Conditional Formating for a range of cells in a worksheet
* – Sending finished report to a web browser (call it thru a PL/SQL DAD …)
*/CREATE OR REPLACE PROCEDURE employeeReport AS
v_sql_salary VARCHAR2(200) := ‘SELECT last_name,first_name,salary FROM hr.employees ORDER BY last_name,first_name’;
v_sql_contact VARCHAR2(200) := ‘SELECT last_name,first_name,phone_number,email FROM hr.employees ORDER BY last_name,first_name’;
v_sql_hiredate VARCHAR2(200) := ‘SELECT last_name,first_name,to_char(hire_date,”MM/DD/YYYY”) hire_date FROM hr.employees ORDER BY last_name,first_name’;
excelReport ExcelDocumentType := ExcelDocumentType();
documentArray ExcelDocumentLine := ExcelDocumentLine();
v_worksheet_rec ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;
v_worksheet_array ExcelDocTypeUtils.WORKSHEET_TABLE := ExcelDocTypeUtils.WORKSHEET_TABLE();
v_sheet_title ExcelDocTypeUtils.T_SHEET_TITLE := NULL;
– Objects for Defining Document Styles (Optional)
v_style_def ExcelDocTypeUtils.T_STYLE_DEF := NULL;
v_style_array ExcelDocTypeUtils.STYLE_LIST := ExcelDocTypeUtils.STYLE_LIST();
– Object for Defining Conditional Formating (Optional)
v_condition_rec ExcelDocTypeUtils.T_CONDITION := NULL;
v_condition_array ExcelDocTypeUtils.CONDITIONS_TABLE := ExcelDocTypeUtils.CONDITIONS_TABLE();
– Conditions are applied to a range of cells … there can be more than grouping of format conditions per worksheet.
v_conditional_format_rec ExcelDocTypeUtils.T_CONDITIONAL_FORMATS;
v_conditional_format_array ExcelDocTypeUtils.CONDITIONAL_FORMATS_TABLE := ExcelDocTypeUtils.CONDITIONAL_FORMATS_TABLE();
BEGIN
– Define Styles (Optional)
v_style_def.p_style_id := ‘LastnameStyle’;
v_style_def.p_text_color := ‘Red’;
ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);
v_style_def := NULL;
v_style_def.p_style_id := ‘SheetTitleStyle’;
v_style_def.p_align_horizontal := ‘Center’;
v_style_def.p_bold := ‘Y’;
v_style_def.p_text_color := ‘Green’;
ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);
v_style_def := NULL;
v_style_def.p_style_id := ‘FirstnameStyle’;
v_style_def.p_text_color := ‘Blue’;
ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);
– Style that includes custom borders around numbers
v_style_def := NULL;
v_style_def.p_style_id := ‘NumberStyle’;
v_style_def.p_number_format := ‘$###,###,###.00′;
v_style_def.p_align_horizontal := ‘Right’;
v_style_def.p_custom_xml := ‘<Borders>’||
‘<Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”3″/>’||
‘<Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”3″/>’||
‘<Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”3″/>’||
‘<Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”3″/>’||
‘</Borders>’;
ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);
– Define Sheet Title
v_sheet_title.title := ‘Employee Salary Report’;
– Must Less than or Equal to the max number of columns on the worksheet.
v_sheet_title.cell_span := ’3′;
v_sheet_title.style := ‘SheetTitleStyle’;
v_worksheet_rec.title := v_sheet_title;
– Add conditional formating for Salary Ranges … color code salary amounts
– across three different ranges.
v_condition_rec.qualifier := ‘Between’;
v_condition_rec.value := ’0,5000′;
v_condition_rec.format_style := ‘color:red’;
ExcelDocTypeUtils.addConditionType(v_condition_array,v_condition_rec);
v_condition_rec.qualifier := ‘Between’;
v_condition_rec.value := ’5001,10000′;
v_condition_rec.format_style := ‘color:blue’;
ExcelDocTypeUtils.addConditionType(v_condition_array,v_condition_rec);
v_condition_rec.qualifier := ‘Between’;
v_condition_rec.value := ’10001,1000000′;
v_condition_rec.format_style := ‘color:green’;
ExcelDocTypeUtils.addConditionType(v_condition_array,v_condition_rec);
– Format range for Column 3 starting at row 2 and going to row 65000 …
v_conditional_format_rec.range := ‘R2C3:R65000C3′;
v_conditional_format_rec.conditions := v_condition_array;
ExcelDocTypeUtils.addConditionalFormatType(v_conditional_format_array,v_conditional_format_rec);
v_worksheet_rec.worksheet_cond_formats := v_conditional_format_array;
– Salary
v_worksheet_rec.query := v_sql_salary;
v_worksheet_rec.worksheet_name := ‘Salaries’;
v_worksheet_rec.col_count := 3;
v_worksheet_rec.col_width_list := ’25,20,15′;
v_worksheet_rec.col_header_list := ‘Lastname,Firstname,Salary’;
v_worksheet_rec.col_datatype_list := ‘String,String,Number’;
v_worksheet_rec.col_style_list := ‘LastnameStyle,FirstnameStyle,NumberStyle’;
ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);
v_worksheet_rec := NULL;
– Contact
v_worksheet_rec.query := v_sql_contact;
v_worksheet_rec.worksheet_name := ‘Contact_Info’;
v_worksheet_rec.col_count := 4;
v_worksheet_rec.col_width_list := ’25,25,25,25′;
v_worksheet_rec.col_header_list := ‘Lastname,Firstname,Phone,Email’;
v_worksheet_rec.col_style_list := ‘LastnameStyle,FirstnameStyle,,’;
ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);
v_worksheet_rec := NULL;
– Hiredate
v_worksheet_rec.query := v_sql_hiredate;
v_worksheet_rec.worksheet_name := ‘Hiredate’;
v_worksheet_rec.col_count := 3;
v_worksheet_rec.col_width_list := ’25,20,20′;
v_worksheet_rec.col_header_list := ‘Lastname,Firstname,Hiredate’;
v_worksheet_rec.col_style_list := ‘LastnameStyle,FirstnameStyle,,’;
ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);
excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array,v_style_array);
excelReport.displayDocument;
END;
/
Here is a link to the spreadsheet generated by the code (The file is an XML file with a .xls extension):
employeeReport.xls
Here is a screen shot of the resulting spreadsheet:
