oracle 使用 PL/SQL 创建 Excel 文件 (.xlsx)

Create an Excel File (.xlsx) using PL/SQL

提问by Migs Isip

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_SQLand UTL_FILEand 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 xlsxusing SQL Developerit just generates around 4 MB of data.

关于我之前的问题Return the SQL Statement of an Explicit Cursor，我能够(.xls)使用DBMS_SQL和UTL_FILE并传递a SYS_REFCURSOR（代码在上一个问题中）生成一个Excel文件。但是，我遇到的一个挑战是生成的文件超过 25 MB。我将通过电子邮件将其发送给企业用户，它肯定会填满他们的邮箱大小。当我手动提取xlsx使用SQL Developer它时，它只会生成大约 4 MB 的数据。

To Address this, would it be possible through PL/SQLto do the following?

为了解决这个问题，是否可以通过PL/SQL执行以下操作？

1. Generate a file using a later version of Excel (.xlsx) to compress the size
2. Compress the .xlsfile before transmitting

1. 使用更高版本的 Excel ( .xlsx)生成文件以压缩大小
2. .xls传输前压缩文件

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.

我还在 SO 中查看了类似的帖子，例如使用 Oracle 中的 UTL_FILE 包在 ExcelSheet 中编写，但正如答案所述，它需要使用 Java。所以它不适用于我。另一篇文章，从 Oracle 数据库创建 Excel 电子表格，也在使用xls. 所以它也不适用。

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

采纳答案by Migs Isip

I've seen a package called as_xlsxby Anton Scheffer, Create an Excel-file with PL/SQLand it addressed my problem. I also modified it a bit to put in Worksheet Names and to allow SYS_REFCURSORas a Parameter instead of a VARCHAR2as required in my Previous Post (Return the SQL Statement of an Explicit Cursor).

我看过一个as_xlsx由 Anton Scheffer调用的包，用 PL/SQL 创建一个 Excel 文件，它解决了我的问题。我还稍微修改了它以放入工作表名称并允许SYS_REFCURSOR作为参数而不是VARCHAR2我上一篇文章中要求的参数（返回显式游标的 SQL 语句）。

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! :)

希望这可以帮助某人！:)

回答by Gurwinder Singh

There is nosupport for xlsor xlsxfiles in Oracle database as such.

有没有支持xls或xlsx文件Oracle数据库本身。

What you can do is create CSV (Comma Separated) file and then, compress it into a zip file using a custom java stored procedurewhich uses java.util.zip, or a PL/SQL procedure that uses UTL_COMPRESS.

你可以做的是建立CSV（逗号分隔）文件，然后将其压缩成使用自定义的zip文件Java存储过程，其用途java.util.zip，或PL / SQL程序使用UTL_COMPRESS。

回答by Kacper

If you need to produce XLSX file and send it to business users it sounds rather like a job for some services outside DB. You should prepare procedure that returns ref cursorwith conten of report and then setup some service that consume data generate PDF or XLSX and send it.

如果您需要生成 XLSX 文件并将其发送给业务用户，这听起来更像是 DB 之外的某些服务的工作。您应该准备返回ref cursor报告内容的程序，然后设置一些消费数据的服务，生成 PDF 或 XLSX 并发送它。

If you can't do it outside DB you can still use Java inside Oracle. You can create Java procedure that will create XLSX. Hereis an example of Java creating Excel file. But creating complicated Java procedure is not the best solution and probably will need you to install some jars on DB server so I would create procedure that returns ref cursorwith data and small program that creates file and send it outside DB.

如果您不能在 DB 外执行此操作，您仍然可以在 Oracle 内使用 Java。您可以创建将创建 XLSX 的 Java 过程。这是 Java 创建 Excel 文件的示例。但是创建复杂的 Java 程序并不是最好的解决方案，可能需要您在 DB 服务器上安装一些 jar，因此我将创建返回ref cursor数据的程序和创建文件并将其发送到 DB 外部的小程序。