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 !

	Friday, January 02, 2009

A Simple Method for Generating Excel Reports from Oracle using the ExcelDocumentType

A couple of years ago, I created a user defined type called the ExcelDocumentType that allows a developer to create a custom Excel (XML) document using PL/SQL. The user
defined type has been well received, but a few folks have commented on how tedious the coding can become when creating multiple reports (with a similar tabular layout). I came to the same conclusion recently, and have created a method that greatly simplifies the process. The method makes use of a couple of new user defined types and a PL/SQL package (all provided for your use). It makes creating a multi worksheet document (with a different query for each worksheet) a breeze and with very little coding involved.

(DOWNLOAD THE CODE FROM :The Code )

The Basics

As mentioned above, this new method for generating ExcelDocumentType reports uses a few Oracle user defined types and a small utility package called ExcelDocUtils. Before I get into the details of the PL/SQL package and the supporting user defined types, let's look at a code sample that will generate an Excel document containing three worksheets.

For this example I used the EMPLOYEES table from the Oracle HR demo schema:

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();

v_worksheet_rec ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;

v_worksheet_array ExcelDocTypeUtils.WORKSHEET_TABLE :=
ExcelDocTypeUtils.WORKSHEET_TABLE();

BEGIN

-- 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_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

-- 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,20,20,25';
v_worksheet_rec.col_header_list := 'Lastname,Firstname,Phone,Email';

v_worksheet_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

-- Contact
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_array.EXTEND;
v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array);

excelReport.displayDocument;

END;
/

The code above generates the following Excel document:

So, what are we looking at here? First, a drastic reduction in the amount of code it takes to generate a basic Excel report. Second, we see some interesting data structures that allow us to accomplish that feat. The code above centers around three structures and a new PL/SQL package called ExcelDocTypeUtils:

(1) A record type called T_WORKSHEET_DATA that holds: the query string that generates the worksheet data; the worksheet name; the number of columns
displayed in the sheet; a list containing the column width for each displayed column; a list of column headers. This record is defined in the ExcelDocTypeUtils package.

(2) A collection object of T_WORKSHEET_DATA records called WORKSHEET_TABLE. Each item in the collection represents one worksheet in the Excel document.

(3) The ExcelDocumentType is that third structure. The WORKSHEET_TABLE collection is passed to a function in the ExcelDocTypeUtils package that returns a
fully populated ExcelDocumentType object.

(4) The ExcelDocTypeUtils package contains a single public utility function called createExcelDocument. This function creates and returns an ExcelDocumentType
object based upon the WORKSHEET_TABLE input parameter. The package makes use of another user defined type called AnonymousFunction. The
AnonymousFunction object (user defined type) allows for the dynamic generation and execution of PL/SQL functions at runtime (sort of like Lambda in Lisp).
Since the user can pass any query (with varying numbers of return values in the result set or select clause), we need to be able to generate a function that can
execute any refcursor (OPEN-FOR FETCH-INTO) and have the ability to create variable numbers "FETCH INTO" recipients.

The Code

I have provided a link in this blog entry that will allow you to download the code and all of the required objects. Download it and give it a try. I have been using at it at my place
of employment to generate scheduled reports for various departments and individuals. So far so good!

For those who just want to take a look at the code, here is the code for the ExcelDocTypeUtils package:

CREATE OR REPLACE TYPE T_ROW AS TABLE OF VARCHAR2(500);
/
CREATE OR REPLACE TYPE T_ROW_NUMBER AS TABLE OF NUMBER(3);
/
CREATE OR REPLACE TYPE RESULT_TABLE AS TABLE OF T_ROW;
/

CREATE OR REPLACE PACKAGE ExcelDocTypeUtils AS

TYPE t_refcursor IS REF CURSOR;

/* This record contains all of the components
required to create an Excel Report worksheet. */
TYPE T_WORKSHEET_DATA IS RECORD(
query VARCHAR2(4000),
worksheet_name VARCHAR2(20),
col_count NUMBER(3),
col_width_list VARCHAR2(500),
col_header_list VARCHAR2(2000)
);

/* An Array of T_WORKSHEET_DATA allows us to create an excel document
with multiple worksheets based on
different queries. */
TYPE WORKSHEET_TABLE IS TABLE OF T_WORKSHEET_DATA;

FUNCTION createExcelDocument(p_worksheet_data WORKSHEET_TABLE) RETURN ExcelDocumentType;

END;
/
sho err;

/*******************************************************************************/
/******************************** PACKAGE BODY ******************************/
/*******************************************************************************/

CREATE OR REPLACE PACKAGE BODY ExcelDocTypeUtils AS

/*

Function that returns the element at the
requested position in a delimited string.

*/
FUNCTION getStringElement(p_string VARCHAR2,
p_element NUMBER,
p_delimiter VARCHAR2 := ',',
p_level NUMBER := 0) RETURN VARCHAR2
IS

v_string VARCHAR2(2000) := NULL;
v_element VARCHAR2(2000) := NULL;
v_next VARCHAR2(2000) := NULL;

v_level NUMBER(4) := 0;

BEGIN

v_level := p_level + 1;

v_element := substr(p_string||p_delimiter,1,
instr(p_string||p_delimiter,p_delimiter)-1);

-- need to look ahead to make sure we handle the null elements.
v_next := substr(p_string||p_delimiter,
instr(p_string||p_delimiter,p_delimiter),length(p_delimiter));

IF ((v_level >= p_element) OR
(v_element IS NULL AND v_next != p_delimiter)) THEN

RETURN v_element;

ELSE

v_string := substr(p_string||p_delimiter,
instr(p_string||p_delimiter,p_delimiter)+1,length(p_string));

RETURN getStringElement(v_string,p_element,p_delimiter,v_level);

END IF;

END;

/*==============================================================================*/
/*
This function executes the given query and returns the data
in a RESULT_TABLE Collection object.

*/

FUNCTION buildDataSet(p_query_string VARCHAR2 := NULL,
p_col_count NUMBER := 0) RETURN RESULT_TABLE
IS

r_table RESULT_TABLE := RESULT_TABLE();

v_row_symbol VARCHAR2(20) := 'v_row';
v_row_fetch VARCHAR2(1000) := NULL;
v_row_extend NUMBER(3) := p_col_count;

v_anydata SYS.ANYDATA := NULL;
v_func_result PLS_INTEGER := 0;

v_query VARCHAR2(16000) := p_query_string;

funcResultTable AnonymousFunction := AnonymousFunction();

v_result_func VARCHAR2(32000) := 'FUNCTION getRowSet RETURN RESULT_TABLE '||
'IS '||
' TYPE t_refcursor IS REF CURSOR; '||
' v_table RESULT_TABLE := result_table(); '||
' v_row T_ROW := T_ROW(); '||
' v_query VARCHAR2(4000) := ''''; '||
' v_refcur t_refcursor; '||
'BEGIN '||
' OPEN v_refcur FOR v_query; '||
' LOOP '||
' v_row.extend(); '||
' FETCH v_refcur INTO ;'||
' EXIT WHEN v_refcur%NOTFOUND; '||
' v_table.EXTEND; '||
' v_table(v_table.COUNT) := v_row; '||
' v_row.DELETE; '||
' END LOOP; '||
' RETURN v_table; '||
'END; ';

BEGIN

FOR x IN 1 .. v_row_extend LOOP

v_row_fetch := v_row_fetch||v_row_symbol||'('||x||'),';

END LOOP;
v_row_fetch := RTRIM(v_row_fetch,',');

v_result_func := REPLACE(v_result_func,'',REPLACE(v_query,'''',''''''));

v_result_func := REPLACE(v_result_func,'',to_char(v_row_extend));

v_result_func := REPLACE(v_result_func,'',v_row_fetch);

funcResultTable.defineFunction(p_function_name=>'getRowSet',
p_function_text=>v_result_func,
p_function_type=>DBMS_TYPES.TYPECODE_TABLE);

v_anydata := funcResultTable.executeFunction;
v_func_result := v_anydata.getCollection(r_table);

RETURN r_table;

END;

/*=========================================================================*/
/*

This functiom constructs and returns an ExcelDocumentType
based upon the parameters passed in
by the WORKSHEET_TABLE type parameter.
*/

FUNCTION createExcelDocument(p_worksheet_data WORKSHEET_TABLE) RETURN ExcelDocumentType
IS

resultDocument ExcelDocumentType;
v_row T_ROW := T_ROW();
v_results RESULT_TABLE := RESULT_TABLE();

v_default_col_width NUMBER(3) := 30;
v_col_width NUMBER(3) := 0;

BEGIN

BEGIN

COMMIT;

EXCEPTION
WHEN OTHERS THEN NULL;
END;

resultDocument := ExcelDocumentType();

-- Open Document
resultDocument.documentOpen;

-- Define Customs Styles
resultDocument.stylesOpen;

resultDocument.defaultStyle;

/* Style for Column Header Row */
resultDocument.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');

resultDocument.stylesClose;

FOR ws_index IN 1 .. p_worksheet_data.COUNT LOOP
-- Open Worksheets

resultDocument.worksheetOpen(p_worksheet_data(ws_index).worksheet_name);

FOR colnum IN 1 .. p_worksheet_data(ws_index).col_count LOOP

v_col_width := NVL(TO_NUMBER(getStringElement(p_worksheet_data(ws_index).col_width_list,colnum))
,v_default_col_width);

resultDocument.defineColumn(p_index=>TO_CHAR(colnum),
p_width=>v_col_width);
END LOOP;

-- Heading Row
resultDocument.rowOpen;

FOR colnum IN 1 .. p_worksheet_data(ws_index).col_count LOOP
resultDocument.addCell(p_style=>'ColumnHeader',
p_data=>getStringElement(p_worksheet_data(ws_index).col_header_list,colnum));
END LOOP;

resultDocument.rowClose;

v_results := buildDataSet(p_worksheet_data(ws_index).query,
p_worksheet_data(ws_index).col_count);

FOR r_index IN 1 .. v_results.COUNT LOOP

resultDocument.rowOpen;

v_row := v_results(r_index);

FOR c_index IN 1 .. v_row.COUNT LOOP

resultDocument.addCell(p_data=>v_row(c_index));

END LOOP;

v_row.DELETE;

resultDocument.rowClose;

END LOOP;

v_results.DELETE;

resultDocument.worksheetClose;

END LOOP;
resultDocument.documentClose;

RETURN resultDocument;

END;

/*=============*/
/* END PACKAGE */
/*=============*/
END;
/

Wrapping It Up
As with all of the code I post in my blog entries, please feel to use it and modify it as you see fit. Please feel free to contact me with any questions! I hope this makes generating Excel reports a little more easy and lot less tedious.

11:15:04 AM