感谢@汪子熙 ,@
两位大佬的文章
我这里写了一个Demo,实现了本地上传Excel并解析为内表,支持通过sheet页名称,sheet页顺序号导入单sheet页。为了方便sheet页名称的指定,这里选择屏幕不直接使用类型,而是使用了lower case的数据元素。这样默认的进入SAP后,不会变成,这样会无法完成上传的。
个人感觉这个方法应该封装成函数,这样返回的内表可以是动态的,或者修改类,返回的数据是解析到的众单元格数据(类似的返回表)。
基本解析步骤是先根据上传路径,将xlsx格式的Excel文档,上传成格式的字符串,然后使用这个接口将字符串解析成xml文档,然后指定sheet页,获取到所有单元格的值,最后解析到内表当中。
对类修改后,可以轻松实现后台解析excel的功能。
*&---------------------------------------------------------------------*
*& Report ZZXLSX
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zzxlsx.
CLASS zxlsx DEFINITION.
PUBLIC SECTION.
DATA error_text TYPE string.
METHODS import_document_from_frontend
IMPORTING pi_filename TYPE /iwbep/sb_odata_ty_char_400sm
pi_sheetid TYPE i OPTIONAL
pi_sheetname TYPE /iwbep/sb_odata_ty_char_400sm OPTIONAL
check_structure TYPE char1"检查内表结构和文档是否一致
start_row TYPE i"正文从第几行开始
RETURNING VALUE(pt_tab) TYPE zstpp_fm_003_itab
EXCEPTIONS file_open_error.
ENDCLASS.
CLASS zxlsx IMPLEMENTATION.
"下列代码实现了Excel 单个sheet页的导入 代码可以直接使用
METHOD import_document_from_frontend.
*create a xlsx handler
DATA(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( )."创建了句柄
*open xlsx into xstring
TRY.
DATA im_file_name TYPE string.
im_file_name = pi_filename.
DATA(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.
*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 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.
DATA(cellvalue) = firstsheet->get_cell_content(
EXPORTING
iv_row = 1
iv_column = column ).
APPEND INITIAL LINE TO columnfromfile ASSIGNING FIELD-SYMBOL().
-column = column.
-columnname = cellvalue.
IF line_exists( tablecomponents[ name = cellvalue ] ).
DELETE tablecomponents WHERE name = cellvalue.
ELSE.
error_text = error_text && |,{ cellvalue }|.
ENDIF.
column = column + 1.
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) = firstsheet->get_last_row_number( ).
DATA(currentrow) = start_row.
*get data of excel
CASE check_structure.
WHEN abap_on.
"按照字段对应获取数据
WHILE currentrow <= rowcount.
APPEND INITIAL LINE TO pt_tab ASSIGNING FIELD-SYMBOL().
LOOP AT columnfromfile REFERENCE INTO DATA(currentcolumn).
cellvalue = firstsheet->get_cell_content( EXPORTING iv_row = currentrow
iv_column = currentcolumn->*-column ).
ASSIGN COMPONENT currentcolumn->*-columnname OF STRUCTURE TO FIELD-SYMBOL().
= cellvalue.
ENDLOOP.
currentrow = currentrow + 1.
ENDWHILE.
WHEN OTHERS.
"按照顺序获取数据
CLEAR column.
WHILE currentrow <= rowcount.
APPEND INITIAL LINE TO pt_tab ASSIGNING .
DO columncount TIMES.
column = column + 1.
cellvalue = firstsheet->get_cell_content(
EXPORTING iv_row = currentrow
iv_column = column ).
ASSIGN COMPONENT column OF STRUCTURE TO .
= cellvalue.
ENDDO.
CLEAR column.
currentrow = currentrow + 1.
ENDWHILE.
ENDCASE.
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.
START-OF-SELECTION.
* sheet = 'Sheet1'.
DATA lr TYPE REF TO zxlsx.
DATA itab TYPE zstpp_fm_003_itab.
CREATE OBJECT lr.
itab = lr->import_document_from_frontend(
EXPORTING pi_filename = file
pi_sheetname = sheet
pi_sheetid = id
check_structure = check
start_row = row ).
excel文档实例:
发表回复