2020.03.04 【ABAP随笔】- Excel批量导出7-CL_XLSX_DOCUMENT 导出

今天写一下CL_XLSX_DOCUMENT个人觉得这个比较好用。

 

 

 

导出代码如下：

SELECT * INTO CORRESPONDING FIELDS OF TABLE @lt_zmmt001 FROM zmmt001.
      IF sy-subrc EQ 0 AND p_file IS NOT INITIAL .
        lv_filename = p_file.
        CALL METHOD zcl_document_jxzhu=>export_document_to_frontend
          EXPORTING
            pi_filename       = lv_filename
*           iv_sheetname      = 'Sheet1'
*          IMPORTING
*           error_text        =
          CHANGING
            pt_tab            = lt_zmmt001
          EXCEPTIONS
            file_export_error = 1
            OTHERS            = 2.
        IF sy-subrc <> 0.
*         Implement suitable error handling here
        ENDIF.

METHOD export_document_to_frontend.
    IF pt_tab IS INITIAL.
      MESSAGE e001(00) RAISING file_export_error WITH |Table has no entry.|.
    ENDIF.

    DATA  filecontent TYPE xstring."内容 16进制
    TRY.
        DATA(xlsx_handling) = cl_ehfnd_xlsx=>get_instance( )."xlsx 句柄
        DATA(xlsx_document) = xlsx_handling->create_doc( )."xlsx 文件
        DATA(xlsx_sheets) = xlsx_document->get_sheets( )."得到sheets
        DATA(first_xlsx_sheet) = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id )."得到sheet
        first_xlsx_sheet->change_sheet_name( iv_sheetname )."设置sheet的名称

        DATA(lv_column) = 1."设置列 = 1

        DATA lw_tab_ref    TYPE REF TO data.
        CREATE DATA lw_tab_ref LIKE LINE OF pt_tab.

        DATA tablestructure TYPE REF TO cl_abap_structdescr.
        tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( lw_tab_ref ).
*set column name 得到列名
        LOOP AT tablestructure->components REFERENCE INTO DATA(component).
          first_xlsx_sheet->set_cell_content( iv_row = 1
                                           iv_column = lv_column
                                           iv_value = component->name ).
          lv_column = lv_column + 1.
        ENDLOOP.

        DATA(lv_row) = 2.
        FIELD-SYMBOLS <tabledata> TYPE STANDARD TABLE.
        ASSIGN pt_tab TO <tabledata>.
*set data of excel 写入内表的数据
        LOOP AT <tabledata> ASSIGNING FIELD-SYMBOL(<currenttabledata>).
          lv_column = 1.
          LOOP AT tablestructure->components REFERENCE INTO component.
            ASSIGN COMPONENT component->name OF STRUCTURE <currenttabledata> TO FIELD-SYMBOL(<columnvalue>).
            first_xlsx_sheet->set_cell_content( iv_row = lv_row
                                             iv_column = lv_column
                                              iv_value = <columnvalue> ).
            lv_column = lv_column + 1.
          ENDLOOP.
          lv_row = lv_row + 1.
        ENDLOOP.
        filecontent = xlsx_document->save( ).
      CATCH cx_openxml_format  INTO DATA(openxml_format_exception).
        MESSAGE e001(00) RAISING file_export_error
          WITH 'Error occurs when constructing excel file instance.'." cx_openxml_format.
      CATCH cx_openxml_not_found INTO DATA(openxml_not_found_exception).
        MESSAGE e001(00) RAISING file_export_error
       WITH ' Error occurs when constructing excel file instance.' "CX_OPENXML_NOT_FOUND |.
.
      CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed_exception).
        MESSAGE e001(00) RAISING file_export_error
         WITH 'Error occurs when constructing excel file instance.'" CX_OPENXML_NOT_ALLOWED |.
.
    ENDTRY.

*将16进制转化为内表
    cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = filecontent
                                    IMPORTING ex_xtab    = DATA(filecontenttab) ).
*输出
    cl_gui_frontend_services=>gui_download(
      EXPORTING
        bin_filesize              = xstrlen( filecontent )
        filename                  = pi_filename
        filetype                  = 'BIN'
        confirm_overwrite         = abap_true
      IMPORTING
        filelength                = DATA(bytestransferred)
      CHANGING
        data_tab                  = filecontenttab
      EXCEPTIONS
        file_write_error          = 1
        no_batch                  = 2
        gui_refuse_filetransfer   = 3
        invalid_type              = 4
        no_authority              = 5
        unknown_error             = 6
        header_not_allowed        = 7
        separator_not_allowed     = 8
        filesize_not_allowed      = 9
        header_too_long           = 10
        dp_error_create           = 11
        dp_error_send             = 12
        dp_error_write            = 13
        unknown_dp_error          = 14
        access_denied             = 15
        dp_out_of_memory          = 16
        disk_full                 = 17
        dp_timeout                = 18
        file_not_found            = 19
        dataprovider_exception    = 20
        control_flush_error       = 21
        not_supported_by_gui      = 22
        error_no_gui              = 23
        OTHERS                    = 24
    ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ELSE.
      MESSAGE s001(00) WITH bytestransferred ' bytes transferred'.
    ENDIF.
  ENDMETHOD.

这个方法对于excel的导出比之前的方法速度都要快，

而且时可以客制输出的样式的。

METHOD export_document_to_frontend.
    IF pt_tab IS INITIAL.
      MESSAGE e001(00) RAISING file_export_error WITH |Table has no entry.|.
    ENDIF.

    DATA  filecontent TYPE xstring."内容 16进制
    TRY.
        DATA(xlsx_handling) = cl_ehfnd_xlsx=>get_instance( )."xlsx 句柄
        DATA(xlsx_document) = xlsx_handling->create_doc( )."xlsx 文件
        DATA(xlsx_sheets) = xlsx_document->get_sheets( )."得到sheets
        DATA(first_xlsx_sheet) = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id )."得到sheet
        first_xlsx_sheet->change_sheet_name( iv_sheetname )."设置sheet的名称

        DATA(lv_column) = 1."设置列 = 1

        DATA lw_tab_ref    TYPE REF TO data.
        CREATE DATA lw_tab_ref LIKE LINE OF pt_tab.

        DATA tablestructure TYPE REF TO cl_abap_structdescr.
        tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( lw_tab_ref ).
*set column name 得到列名
        LOOP AT tablestructure->components REFERENCE INTO DATA(component).
          first_xlsx_sheet->set_cell_content( iv_row = 1
                                           iv_column = lv_column
                                           iv_value = component->name ).
          lv_column = lv_column + 1.
        ENDLOOP.

        DATA(lv_row) = 2.
        FIELD-SYMBOLS <tabledata> TYPE STANDARD TABLE.
        ASSIGN pt_tab TO <tabledata>.
*set data of excel 写入内表的数据
        LOOP AT <tabledata> ASSIGNING FIELD-SYMBOL(<currenttabledata>).
          lv_column = 1.
          LOOP AT tablestructure->components REFERENCE INTO component.
            ASSIGN COMPONENT component->name OF STRUCTURE <currenttabledata> TO FIELD-SYMBOL(<columnvalue>).
            first_xlsx_sheet->set_cell_content( iv_row = lv_row
                                             iv_column = lv_column
                                              iv_value = <columnvalue> ).
            lv_column = lv_column + 1.
          ENDLOOP.
          lv_row = lv_row + 1.
        ENDLOOP.
        filecontent = xlsx_document->save( ).
      CATCH cx_openxml_format  INTO DATA(openxml_format_exception).
        MESSAGE e001(00) RAISING file_export_error
          WITH 'Error occurs when constructing excel file instance.'." cx_openxml_format.
      CATCH cx_openxml_not_found INTO DATA(openxml_not_found_exception).
        MESSAGE e001(00) RAISING file_export_error
       WITH ' Error occurs when constructing excel file instance.' "CX_OPENXML_NOT_FOUND |.
.
      CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed_exception).
        MESSAGE e001(00) RAISING file_export_error
         WITH 'Error occurs when constructing excel file instance.'" CX_OPENXML_NOT_ALLOWED |.
.
    ENDTRY.

*将16进制转化为内表
    cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = filecontent
                                    IMPORTING ex_xtab    = DATA(filecontenttab) ).
*输出
    cl_gui_frontend_services=>gui_download(
      EXPORTING
        bin_filesize              = xstrlen( filecontent )
        filename                  = pi_filename
        filetype                  = 'BIN'
        confirm_overwrite         = abap_true
      IMPORTING
        filelength                = DATA(bytestransferred)
      CHANGING
        data_tab                  = filecontenttab
      EXCEPTIONS
        file_write_error          = 1
        no_batch                  = 2
        gui_refuse_filetransfer   = 3
        invalid_type              = 4
        no_authority              = 5
        unknown_error             = 6
        header_not_allowed        = 7
        separator_not_allowed     = 8
        filesize_not_allowed      = 9
        header_too_long           = 10
        dp_error_create           = 11
        dp_error_send             = 12
        dp_error_write            = 13
        unknown_dp_error          = 14
        access_denied             = 15
        dp_out_of_memory          = 16
        disk_full                 = 17
        dp_timeout                = 18
        file_not_found            = 19
        dataprovider_exception    = 20
        control_flush_error       = 21
        not_supported_by_gui      = 22
        error_no_gui              = 23
        OTHERS                    = 24
    ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ELSE.
      MESSAGE s001(00) WITH bytestransferred ' bytes transferred'.
    ENDIF.
  ENDMETHOD.