PL/SQL ExcelDocumentType

最新推荐文章于 2024-08-25 20:48:51 发布

whz110

最新推荐文章于 2024-08-25 20:48:51 发布

阅读量194

点赞数

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.csdn.net/whz110/article/details/84464263

版权

Oracle 专栏收录该内容

119 篇文章 1 订阅

订阅专栏

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)

CREATE OR REPLACE PROCEDURE excelObjectTestWeb
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:

excelObjectDemo

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:

employeeReport

from: http://www.jasonsdevelopercorner.com/?page_id=8

whz110

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL ExcelDocumentType

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 spreadsheet...
复制链接

扫一扫

专栏目录