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

关于我之前的问题返回显式游标的SQL语句 https://stackoverflow.com/questions/41281665/return-the-sql-statement-of-an-explicit-cursor, 我能够生成 Excel(.xls)文件使用DBMS_SQL and UTL_FILE并通过一个SYS_REFCURSOR（代码在上一个问题中）。 然而，我遇到的一个挑战是生成的文件超过 25 MB。 我将通过电子邮件将此信息传输给企业用户，这肯定会填满他们的邮箱大小。 当我手动提取时xlsx using SQL Developer它仅生成大约 4 MB 的数据。

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

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

我还评论过类似的帖子，例如在Oracle中使用UTL_FILE包写入ExcelSheet https://stackoverflow.com/questions/8414050/writing-in-excelsheet-using-utl-file-package-in-oracle, 但正如答案所说，它需要使用Java。所以它不适用于我。 另一个帖子，从 Oracle 数据库创建 Excel 电子表格 https://stackoverflow.com/questions/6017863/create-an-excel-spreadsheet-from-a-oracle-database，也正在使用xls。所以它也不适用。

有什么想法吗？

甲骨文版本：

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

我见过一个叫做as_xlsx安东·谢弗，使用 PL/SQL 创建 Excel 文件 https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/它解决了我的问题。 我还对其进行了一些修改以放入工作表名称并允许SYS_REFCURSOR作为参数而不是VARCHAR2按照我上一篇文章的要求（返回显式游标的SQL语句 https://stackoverflow.com/questions/41281665/return-the-sql-statement-of-an-explicit-cursor).

我在程序重载的包规范中添加了此内容：

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

这是我的并发请求中创建文件的示例块：

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;

希望这对某人有帮助！ :)