PL/SQL을 사용하여 Excel 파일(.xlsx) 만들기

PL/SQL을 사용하여 Excel 파일(.xlsx) 만들기

이전 질문 Return the SQL Statement of an Explicit Curser와 관련하여 Excel을 생성할 수 있었습니다.(.xls)파일 사용DBMS_SQL그리고.UTL_FILE그리고 A를 통과.SYS_REFCURSOR(코드는 이전 질문에 있음).하지만 제가 직면하고 있는 한 가지 문제는 생성된 파일이 25MB 이상이라는 것입니다. 이 파일을 비즈니스 사용자에게 이메일로 전송하면 메일함 크기가 확실히 채워질 것입니다.수동 추출을 수행할 때xlsx사용.SQL Developer약 4MB의 데이터만 생성합니다.

이 문제를 해결하기 위해 다음을 통해 가능할까요?PL/SQL다음을 하기 위해서요?

1. Excel 최신 버전을 사용하여 파일 생성 (.xlsx) 크기를 압축합니다
2. 압축합니다..xls전송하기 전에 파일을 저장합니다.

여기 SO에서도 비슷한 게시물을 검토한 적이 있는데 오라클에서 UTL_FILE 패키지를 이용해서 엑셀시트에 쓰기 등이 있습니다만 답변대로 자바를 사용해야 합니다.그래서 저에게는 해당되지 않습니다.다른 게시물인 Create a Excel 스프레드시트 from a Oracle Database에서도 다음을 사용하고 있습니다.xls. 그래서 그것도 적용이 안 됩니다.

무슨 생각 있어요?

Oracle 버전:

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_xlsxAnton Scheffer가 PL/SQL로 Excel-file을 작성하여 제 문제를 해결했습니다.워크시트 이름을 넣고 허용할 수 있도록 약간 수정했습니다.SYS_REFCURSOR매개 변수로서 대신에VARCHAR2이전 게시물에서 요구한 대로(명시 커서의 SQL 문을 반환합니다).

Procedure Overloading을 위한 Package Specification에 추가했습니다.

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 Overloading을 위한 Package Body(Package Body 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
)
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;

누군가에게 도움이 되길 바랍니다! :)

에 대한 지원이 없습니다.xls아니면xlsx파일을 저장할 수 있습니다.

CSV(Comma Separated) 파일을 만든 다음 를 사용하는 사용자 지정 java 저장 프로시저 또는 를 사용하는 PL/SQL 프로시저를 사용하여 zip 파일로 압축합니다.

XLSX 파일을 제작하여 비즈니스 사용자에게 전송해야 한다면 DB 외부의 일부 서비스에 대한 작업으로 들립니다.반환되는 절차를 준비해야 합니다.ref cursor보고 내용과 함께 데이터를 소비하는 서비스를 설정하여 PDF나 XLSX를 생성하여 전송합니다.

DB 외부에서 할 수 없는 경우에도 Oracle 내부에서 Java를 사용할 수 있습니다.XLSX를 생성할 Java 프로시저를 생성할 수 있습니다.여기 자바가 엑셀 파일을 만드는 예시가 있습니다.하지만 복잡한 자바 프로시저를 만드는 것은 최선의 해결책이 아니며 아마도 DB 서버에 몇 개의 jars를 설치해야 할 것이므로 반환하는 프로시저를 만들 것입니다.ref cursor파일을 만들어 DB 외부로 보내는 데이터와 작은 프로그램으로.

언급URL : https://stackoverflow.com/questions/41299024/create-an-excel-file-xlsx-using-pl-sql