上午分享了如何上传excel,下午修改了下代码,实现下载功能!
基本原理:大部分项目都是预设模板,然后填格子。所以我在上午代码的基础上只做了少量的修改。单元格的充填方式仍然是一个格子一个格子的充填,速度其实不行,但直接生成xml,摆脱了office的依赖。
测试5*50000的数据,花费2分钟11秒,还行(错误的实例:只有50000个单元格有数据)。3分41秒,25万单元格。
有没有大佬知道还能怎么优化进一步提速?
*&---------------------------------------------------------------------*
*& Report ZZXLSX
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zzxlsx2.
TYPES: BEGIN OF typ_alv,
col001(50),
col002(50),
col003(50),
col004(50),
col005(50),
END OF typ_alv,
typ_t_alv TYPE TABLE OF typ_alv WITH DEFAULT KEY..
CLASS zxlsx DEFINITION.
PUBLIC SECTION.
DATA: error_text TYPE string,
xstring_excel TYPE xstring.
METHODS import_document_from_frontend
IMPORTING
pi_filename TYPE /iwbep/sb_odata_ty_char_400sm
EXCEPTIONS
file_open_error.
METHODS export_document_from_server
IMPORTING
pi_filename TYPE /iwbep/sb_odata_ty_char_400sm
EXCEPTIONS
file_open_error.
METHODS deserialize_document
IMPORTING
pi_sheetid TYPE i OPTIONAL
pi_sheetname TYPE /iwbep/sb_odata_ty_char_400sm OPTIONAL
check_structure TYPE char1
start_row TYPE i
pt_tab TYPE typ_t_alv
EXCEPTIONS
file_open_error.
ENDCLASS.
CLASS zxlsx IMPLEMENTATION.
METHOD import_document_from_frontend.
CLEAR xstring_excel.
*open xlsx into xstring
TRY.
DATA im_file_name TYPE string.
im_file_name = pi_filename.
xstring_excel = cl_openxml_helper=>load_local_file( im_file_name ). "获取excel路径 为 xstring格式
CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
error_text = openxml_not_found->get_text( ).
ENDTRY.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
ENDMETHOD.
METHOD export_document_from_server.
*save xlsx by xstring
TRY.
DATA im_file_name TYPE string.
im_file_name = pi_filename.
cl_openxml_helper=>store_local_file( im_file_name = im_file_name
im_data = xstring_excel ). "获取excel路径 为 xstring格式
CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
error_text = openxml_not_found->get_text( ).
CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed).
error_text = openxml_not_allowed->get_text( ).
ENDTRY.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
CLEAR xstring_excel.
ENDMETHOD.
"下列代码实现了Excel 单个sheet页的导出 代码可以直接使用
"注意,这个程序基于模板下载数据
METHOD deserialize_document.
*create a xlsx handler
DATA(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( )."创建了句柄
*load the xlsx
TRY.
DATA(xlsxdocument) = xlsxhandler->load_doc( iv_file_data = xstring_excel ). "加载excel
CATCH cx_openxml_format INTO DATA(openxml_format).
error_text = openxml_format->get_text( ).
CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed).
error_text = openxml_not_allowed->get_text( ).
CATCH cx_dynamic_check INTO DATA(dynamic_check).
error_text = dynamic_check->get_text( ).
ENDTRY.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
*获取指定sheet名称或者id或者第一个sheet页数据
TRY.
IF pi_sheetid IS NOT INITIAL.
DATA(firstsheet) = xlsxdocument->get_sheet_by_id( pi_sheetid ).
ELSEIF pi_sheetname IS NOT INITIAL.
DATA iv_sheet_name TYPE string.
iv_sheet_name = pi_sheetname.
firstsheet = xlsxdocument->get_sheet_by_name( iv_sheet_name ).
ELSE.
firstsheet = xlsxdocument->get_sheet_by_id( 1 ).
ENDIF.
CATCH cx_openxml_format INTO openxml_format.
error_text = openxml_format->get_text( ).
error_text = |Error occurs when extract data from specific sheet: | && error_text.
CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
error_text = openxml_not_found->get_text( ).
error_text = |Error occurs when extract data from specific sheet: | && error_text .
CATCH cx_dynamic_check INTO dynamic_check.
error_text = |Error occurs when extract data from specific sheet: | && dynamic_check->get_text( ) .
ENDTRY.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
"return if no sheet in xlsx file
CHECK NOT firstsheet IS INITIAL.
"check file structure, first line of excel file
DATA(columncount) = firstsheet->get_last_column_number_in_row( 1 ). "获取第1行列数 (一共几列)
DATA column TYPE i VALUE 1.
"data tablecomponents type cl_abap_structdescr=>component_table.
*get the components of structure
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 ). "获取内表的 components
DATA(tablecomponents) = tablestructure->get_components( ).
"get the content of excel.
TYPES: BEGIN OF columninfo,
column TYPE i,
columnname TYPE string,
END OF columninfo.
TYPES columnsinfo TYPE STANDARD TABLE OF columninfo WITH EMPTY KEY.
DATA columnfromfile TYPE columnsinfo.
IF check_structure = abap_on.
* get the title row compare with tab structure if need
DO columncount TIMES.
column = sy-index.
DATA(cellvalue) = firstsheet->get_cell_content(
EXPORTING
iv_row = 1
iv_column = column ).
APPEND INITIAL LINE TO columnfromfile ASSIGNING FIELD-SYMBOL(<columnfromfile>).
<columnfromfile>-column = column.
<columnfromfile>-columnname = cellvalue.
IF line_exists( tablecomponents[ name = cellvalue ] ).
DELETE tablecomponents WHERE name = cellvalue.
ELSE.
error_text = error_text && |,{ cellvalue }|.
ENDIF.
ENDDO.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
ENDIF.
* get the title row compare with tab structure if need
"last row
DATA(rowcount) = lines( pt_tab[] ).
DATA(currentrow) = start_row.
*get data of excel
CASE check_structure.
WHEN abap_on.
"按照字段对应获取数据
LOOP AT pt_tab ASSIGNING FIELD-SYMBOL(<currentrow>).
LOOP AT columnfromfile REFERENCE INTO DATA(currentcolumn).
ASSIGN COMPONENT currentcolumn->*-columnname OF STRUCTURE <currentrow> TO FIELD-SYMBOL(<cellvalue>).
firstsheet->set_cell_content( EXPORTING iv_row = currentrow
iv_column = currentcolumn->column
iv_value = <cellvalue> ).
UNASSIGN <cellvalue>.
ENDLOOP.
currentrow = currentrow + 1.
ENDLOOP.
WHEN OTHERS.
"按照顺序获取数据
CLEAR column.
LOOP AT pt_tab ASSIGNING <currentrow>.
DO columncount TIMES.
column = column + 1.
ASSIGN COMPONENT column OF STRUCTURE <currentrow> TO <cellvalue>.
firstsheet->set_cell_content( EXPORTING iv_row = currentrow
iv_column = column
iv_value = <cellvalue> ).
UNASSIGN <cellvalue>.
ENDDO.
CLEAR column.
currentrow = currentrow + 1.
ENDLOOP.
ENDCASE.
TRY.
xstring_excel = xlsxdocument->save( ).
CATCH cx_openxml_format INTO openxml_format.
error_text = openxml_format->get_text( ).
error_text = |Error occurs when save data from specific sheet: | && error_text.
CATCH cx_openxml_not_found INTO openxml_not_found.
error_text = openxml_not_found->get_text( ).
error_text = |Error occurs when save data from specific sheet: | && error_text .
CATCH cx_openxml_not_allowed INTO openxml_not_allowed.
error_text = openxml_not_allowed->get_text( ).
error_text = |Error occurs when save data from specific sheet: | && error_text .
ENDTRY.
IF error_text IS NOT INITIAL.
MESSAGE e001(00) RAISING file_open_error WITH error_text.
ENDIF.
ENDMETHOD.
ENDCLASS.
PARAMETERS: file TYPE /iwbep/sb_odata_ty_char_400sm,
sheet TYPE /iwbep/sb_odata_ty_char_400sm NO-DISPLAY,
id TYPE i NO-DISPLAY,
check TYPE char1 AS CHECKBOX,
row TYPE i.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR file.
DATA: file_table TYPE filetable,
rc TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
file_filter = 'Excel(*.xlsx)'
CHANGING
file_table = file_table
rc = rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
READ TABLE file_table ASSIGNING FIELD-SYMBOL(<fs_file>) INDEX 1.
IF sy-subrc = 0.
file = <fs_file>.
ENDIF.
START-OF-SELECTION.
* sheet = 'Sheet1'.
DATA lr TYPE REF TO zxlsx.
DATA itab TYPE typ_t_alv.
itab = VALUE #( ( col001 = 1 )
( col002 = 2 )
( col003 = 3 )
( col004 = 4 )
( col005 = 5 ) ).
CREATE OBJECT lr.
lr->import_document_from_frontend( file ).
lr->deserialize_document(
EXPORTING
pi_sheetname = sheet
pi_sheetid = id
check_structure = check
start_row = row
pt_tab = itab ).
lr->export_document_from_server( file ).
cl_demo_output=>display( itab ).