今天写一下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.
