使用PL / SQL创建Excel文件（.xlsx） [英] Create an Excel File (.xlsx) using PL/SQL
本文介绍了使用PL / SQL创建Excel文件（.xlsx）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
关于我之前的问题返回显式的SQL语句光标，
i可以使用 DBMS_SQL 生成Excel （。xls）文件 UTL_FILE 并传递一个 SYS_REFCURSOR （代码在上一个问题）。
然而，我遇到的一个挑战是生成的文件超过25 MB。
我将通过电子邮件发送给企业用户，它肯定会填满他们的邮箱大小。
当我使用 SQL Developer 手动提取 xlsx 时，只会生成大约4 MB的数据。 / p>
要解决这个问题，可以通过 PL / SQL 来执行以下操作吗？
- 使用更高版本的Excel生成文件（
.xlsx）来压缩大小 - 在发送
之前压缩 .xls 我也在这里审查过类似的帖子，例如写入ExcelSheet在Oracle中使用UTL_FILE包，
但是如答案所述，它需要使用Java。所以它不适用于我。
另一篇文章，从Oracle数据库创建Excel电子表格，也使用 xls 。所以它不适用。
任何想法？
Oracle版本：
Oracle数据库11g企业版版本11.2.0.4.0 - 64位生产
PL / SQL版本11.2.0.4.0 - 生产
CORE 11.2.0.4.0生产
TNS for Solaris：版本11.2.0.4.0 - 生产
NLSRTL版本11.2.0.4.0 - 生产
解决方案
我已经看到一个名为 as_xlsx Scheffer，使用PL / SQL创建Excel文件，并解决了我的问题。
我还修改了一下放入工作表名称，并允许 SYS_REFCURSOR 作为参数，而不是一个 VARCHAR2 根据我上一篇文章的要求（返回显式的SQL语句光标）。
我在程序重载的程序包规范中添加了这个：
procedure query2sheet
（p_cur IN OUT SYS_REFCURSOR
，p_column_headers boolean：= true
，p_directory varchar2：= null
，p_filename varchar2：= null
，p_sheet pls_integer：= null
，p_sheetname varchar2：= null
）;
我在程序重载的程序包体系中添加了这个（注意：行注释是我修改的行） ：
procedure query2sheet
（p_cur IN OUT SYS_REFCURSOR
，p_column_headers boolean：= true
，p_directory varchar2：= null
，p_filename varchar2：= null
，p_sheet pls_integer：= null
，p_sheetname varchar2：= null
）
是
t_sheet pls_integer;
t_c integer;
t_col_cnt整数;
t_desc_tab2 dbms_sql.desc_tab2;
t_desc_tab dbms_sql.desc_tab;
d_tab dbms_sql.date_table;
n_tab dbms_sql.number_table;
v_tab dbms_sql.varchar2_table;
t_bulk_size pls_integer：= 200;
t_r integer;
t_cur_row pls_integer;
t_d number;
begin
- 如果p_sheetname不为空，则更改
，然后
new_sheet（p_sheetname）;
else
new_sheet;
end if;
- 更改结束
--t_c：= dbms_sql.open_cursor;
--dbms_sql.parse（t_c，p_sql，dbms_sql.native）;
t_d：= DBMS_SQL.TO_CURSOR_NUMBER（p_cur）;
--dbms_sql.describe_columns2（t_c，t_col_cnt，t_desc_tab）;
dbms_sql.describe_columns（t_d，t_col_cnt，t_desc_tab）;
for c in 1 .. t_col_cnt
loop
如果p_column_headers
然后
单元格（c，1，t_desc_tab（c）.col_name，p_sheet = > t_sheet）;
end if;
--dbms_output.put_line（t_desc_tab（c）.col_name ||''|| t_desc_tab（c）.col_type）;
case
当t_desc_tab（c）.col_type in（2，100，101）
then
--dbms_sql.define_array（t_c，c，n_tab，t_bulk_size，1）;
dbms_sql.define_array（t_d，c，n_tab，t_bulk_size，1）;
当t_desc_tab（c）.col_type in（12，178，179，180，181，231）
then
--dbms_sql.define_array（t_c，c，d_tab，t_bulk_size，1） ;
dbms_sql.define_array（t_d，c，d_tab，t_bulk_size，1）;
当t_desc_tab（c）.col_type in（1，8，9，96，112）
then
--dbms_sql.define_array（t_c，c，v_tab，t_bulk_size，1）;
dbms_sql.define_array（t_d，c，v_tab，t_bulk_size，1）;
else
null;
最终案例;
结束循环;
-
t_cur_row：=当p_column_headers然后2 else 1结束时的情况;
t_sheet：= nvl（p_sheet，workbook.sheets.count（））;
-
--t_r：= dbms_sql.execute（t_c）;
loop
--t_r：= dbms_sql.fetch_rows（t_c）;
t_r：= dbms_sql.fetch_rows（t_d）;
如果t_r> 0
then
for c in 1 .. t_col_cnt
loop
case
当t_desc_tab（c）.col_type在（2，100，101）
那么
--dbms_sql.column_value（t_c，c，n_tab）;
dbms_sql.column_value（t_d，c，n_tab）;
for i in 0 .. t_r - 1
loop
如果n_tab（i + n_tab.first（））不为空
然后
单元格（c，t_cur_row + i，n_tab（i + n_tab.first（）），p_sheet => t_sheet）;
end if;
结束循环;
n_tab.delete;
当t_desc_tab（c）.col_type在（12，178，179，180，181，231）
then
--dbms_sql.column_value（t_c，c，d_tab）;
dbms_sql.column_value（t_d，c，d_tab）;
for i in 0 .. t_r - 1
loop
如果d_tab（i + d_tab.first（））不为空
然后
单元格（c，t_cur_row + i，d_tab（i + d_tab.first（）），p_sheet => t_sheet）;
end if;
结束循环;
d_tab.delete; （b，c，c，v_tab）中的
dbms_sql.column_value（t_d，c，v_tab）;
for i in 0 .. t_r - 1
loop
如果v_tab（i + v_tab.first（））不为空
然后
单元格（c，t_cur_row + i，v_tab（i + v_tab.first（）），p_sheet => t_sheet）;
end if;
结束循环;
v_tab.delete;
else
null;
最终案例;
结束循环;
end if;
退出t_r！= t_bulk_size;
t_cur_row：= t_cur_row + t_r;
结束循环;
--dbms_sql.close_cursor（t_c）;
dbms_sql.close_cursor（t_d）;
if（p_directory不为空，p_filename不为null）
然后
save（p_directory，p_filename）;
end if;
异常
当别人
然后
--if dbms_sql.is_open（t_c）
如果dbms_sql.is_open（t_d）
然后
- -dbms_sql.close_cursor（t_c）;
dbms_sql.close_cursor（t_d）;
end if;
end query2sheet;
这是创建文件的并发请求中的一个示例块：
过程EMP_ROSTER_REPORT（p_empno per_all_people_f.employee_number％type
，p_bg_id per_business_groups.business_group_id％type
，p_email_add per_all_people_f.email_address％type）
是
l_fh UTL_FILE.FILE_TYPE;
l_directory VARCHAR2（30）：='EXT_TAB_DATA';
l_filename VARCHAR2（100）;
emp_cur SYS_REFCURSOR;
l_message varchar2（100）;
g_stage varchar2（100）;
g_zipped_blob blob;
光标p_payroll_cur是
选择payroll_id
，payroll_name
，business_group_id
从pay_all_payrolls_f
其中business_group_id = p_bg_id;
BEGIN
--------------------------------- -
g_stage：='设置文件名';
-----------------------------------
l_filename：= 'EMPLOYEE_ROSTER_REPORT_'|| TO_CHAR（SYSDATE，'DD-MON-YYYY-HHMISS'）;
------------------------------------------
g_stage：='分配Emp SysRefCursor';
------------------------------------------
在p_payroll_cur循环
OPEN emp_cur FOR
SELECT'超长查询这里与参数`
from table_a
其中payroll_id = i.payroll_id;
------------------------------------------ ----------------
g_stage：='打开员工光标并写入文件';
---------------------------------------------- ------------
as_xlsx.query2sheet（p_cur => emp_cur - 使用Sys_RefCursor而不是动态SQL（Varchar2）
，p_sheetname => i .payroll_name）; - 这是我们分配表名称
as_xlsx.freeze_pane（1,1）的地方; - 冻结Excel Sheet
结束循环中最顶层和最右边的窗格;
------------------------------
g_stage：='创建文件' ;
------------------------------
as_xlsx.save（l_directory，l_filename | | .xlsx等）;
END EMP_ROSTER_REPORT;
希望这有助于某人！ ：）
In relation to my previous question Return the SQL Statement of an Explicit Cursor,
i was able to generate an Excel (.xls) file using DBMS_SQL and UTL_FILE and passing a SYS_REFCURSOR (code is in the previous question).
However, one challenge i'm encountering is the generated file is over 25 MB.
I will be transmitting this over Email to Business Users and it would surely fill up their mailbox size.
When I do manual extracts to xlsx using SQL Developer it just generates around 4 MB of data.
To Address this, would it be possible through PL/SQL to do the following?
- Generate a file using a later version of Excel (
.xlsx) to compress the size - Compress the
.xlsfile before transmitting
I've also reviewed similar posts here in SO such as Writing in ExcelSheet using UTL_FILE package in Oracle,
but as the answer stated, it needs to use Java. So its not applicable to me.
Another post, Create an Excel Spreadsheet from a Oracle Database, is also using xls. So its not applicable as well.
Any thoughts?
Oracle Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
解决方案
I've seen a package called as_xlsx by Anton Scheffer, Create an Excel-file with PL/SQL and it addressed my problem.
I also modified it a bit to put in Worksheet Names and to allow SYS_REFCURSOR as a Parameter instead of a VARCHAR2 as required in my Previous Post (Return the SQL Statement of an Explicit Cursor).
I added this in the Package Specification for Procedure Overloading:
procedure query2sheet
( p_cur IN OUT SYS_REFCURSOR
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
, p_sheetname varchar2 := null
);
I added this in the Package Body for Procedure Overloading (note: the line comments were lines I modified):
procedure query2sheet
( p_cur IN OUT SYS_REFCURSOR
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
, p_sheetname varchar2 := null
)
is
t_sheet pls_integer;
t_c integer;
t_col_cnt integer;
t_desc_tab2 dbms_sql.desc_tab2;
t_desc_tab dbms_sql.desc_tab;
d_tab dbms_sql.date_table;
n_tab dbms_sql.number_table;
v_tab dbms_sql.varchar2_table;
t_bulk_size pls_integer := 200;
t_r integer;
t_cur_row pls_integer;
t_d number;
begin
-- Changed
if p_sheetname is not null then
new_sheet(p_sheetname);
else
new_sheet;
end if;
-- End of Change
--t_c := dbms_sql.open_cursor;
--dbms_sql.parse( t_c, p_sql, dbms_sql.native );
t_d := DBMS_SQL.TO_CURSOR_NUMBER(p_cur);
--dbms_sql.describe_columns2( t_c, t_col_cnt, t_desc_tab );
dbms_sql.describe_columns( t_d, t_col_cnt, t_desc_tab );
for c in 1 .. t_col_cnt
loop
if p_column_headers
then
cell( c, 1, t_desc_tab( c ).col_name, p_sheet => t_sheet );
end if;
--dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
case
when t_desc_tab( c ).col_type in ( 2, 100, 101 )
then
--dbms_sql.define_array( t_c, c, n_tab, t_bulk_size, 1 );
dbms_sql.define_array( t_d, c, n_tab, t_bulk_size, 1 );
when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
then
--dbms_sql.define_array( t_c, c, d_tab, t_bulk_size, 1 );
dbms_sql.define_array( t_d, c, d_tab, t_bulk_size, 1 );
when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
then
--dbms_sql.define_array( t_c, c, v_tab, t_bulk_size, 1 );
dbms_sql.define_array( t_d, c, v_tab, t_bulk_size, 1 );
else
null;
end case;
end loop;
--
t_cur_row := case when p_column_headers then 2 else 1 end;
t_sheet := nvl( p_sheet, workbook.sheets.count() );
--
--t_r := dbms_sql.execute( t_c );
loop
--t_r := dbms_sql.fetch_rows( t_c );
t_r := dbms_sql.fetch_rows( t_d );
if t_r > 0
then
for c in 1 .. t_col_cnt
loop
case
when t_desc_tab( c ).col_type in ( 2, 100, 101 )
then
--dbms_sql.column_value( t_c, c, n_tab );
dbms_sql.column_value( t_d, c, n_tab );
for i in 0 .. t_r - 1
loop
if n_tab( i + n_tab.first() ) is not null
then
cell( c, t_cur_row + i, n_tab( i + n_tab.first() ), p_sheet => t_sheet );
end if;
end loop;
n_tab.delete;
when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
then
--dbms_sql.column_value( t_c, c, d_tab );
dbms_sql.column_value( t_d, c, d_tab );
for i in 0 .. t_r - 1
loop
if d_tab( i + d_tab.first() ) is not null
then
cell( c, t_cur_row + i, d_tab( i + d_tab.first() ), p_sheet => t_sheet );
end if;
end loop;
d_tab.delete;
when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
then
--dbms_sql.column_value( t_c, c, v_tab );
dbms_sql.column_value( t_d, c, v_tab );
for i in 0 .. t_r - 1
loop
if v_tab( i + v_tab.first() ) is not null
then
cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet );
end if;
end loop;
v_tab.delete;
else
null;
end case;
end loop;
end if;
exit when t_r != t_bulk_size;
t_cur_row := t_cur_row + t_r;
end loop;
--dbms_sql.close_cursor( t_c );
dbms_sql.close_cursor( t_d );
if ( p_directory is not null and p_filename is not null )
then
save( p_directory, p_filename );
end if;
exception
when others
then
--if dbms_sql.is_open( t_c )
if dbms_sql.is_open( t_d )
then
--dbms_sql.close_cursor( t_c );
dbms_sql.close_cursor( t_d );
end if;
end query2sheet;
This is a Sample Block in my Concurrent Request that Creates the File:
Procedure EMP_ROSTER_REPORT (p_empno per_all_people_f.employee_number%type
, p_bg_id per_business_groups.business_group_id%type
, p_email_add per_all_people_f.email_address%type)
is
l_fh UTL_FILE.FILE_TYPE;
l_directory VARCHAR2(30) := 'EXT_TAB_DATA';
l_filename VARCHAR2(100);
emp_cur SYS_REFCURSOR;
l_message varchar2(100);
g_stage varchar2(100);
g_zipped_blob blob;
cursor p_payroll_cur is
select payroll_id
, payroll_name
, business_group_id
from pay_all_payrolls_f
where business_group_id = p_bg_id;
BEGIN
-----------------------------------
g_stage := 'setting the filename';
-----------------------------------
l_filename := 'EMPLOYEE_ROSTER_REPORT_'||TO_CHAR(SYSDATE, 'DD-MON-YYYY-HHMISS');
------------------------------------------
g_stage := 'Assigning Emp SysRefCursor';
------------------------------------------
for i in p_payroll_cur loop
OPEN emp_cur FOR
SELECT 'extra long query here with parameters`
from table_a
where payroll_id = i.payroll_id;
----------------------------------------------------------
g_stage := 'open Employee Cursor and write into the File';
----------------------------------------------------------
as_xlsx.query2sheet( p_cur => emp_cur -- Uses Sys_RefCursor Instead of Dynamic SQL (Varchar2)
, p_sheetname => i.payroll_name); -- This is where we assign the Sheet Names
as_xlsx.freeze_pane( 1,1 ); -- Freeze the topmost and rightmost pane in the Excel Sheet
end loop;
------------------------------
g_stage := 'Create the File';
------------------------------
as_xlsx.save( l_directory , l_filename||'.xlsx');
END EMP_ROSTER_REPORT;
Hope this helps someone! :)
这篇关于使用PL / SQL创建Excel文件（.xlsx）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
