01
Excel批量导入3-CL_XLSX_DOCUMENT
我是如何接触到这个类的呢,就要感谢Wang Jerry 的一篇文章。感谢sap研究院大佬让我更透彻的了解了一下XLSX其实就是多个XML文件组合成的。
链接如下:使用ABAP操作Excel的几种方法
下面看我的程序:
DATA error_text TYPE string.
lv_filename = p_file.
CHECK lv_filename ISNOTINITIAL.
CALLMETHOD zcl_document_jxzhu=>import_document_from_frontend "调用方法获取Excel数据
EXPORTING
pi_filename = lv_filename " 文件路径
* pi_sheetname = 'Sheet1' "sheet 页名称
* start_row = '2' "开始获取的行号
check_structure = abap_on "是否匹配结构去获取excel数据(X为 excel排列顺序和内表字段顺序无关,仅用结构来匹配)
IMPORTING
error_text = error_text "错误消息
CHANGING
pt_tab = lt_zmmt001
EXCEPTIONS
file_open_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
cl_demo_output=>display( lt_zmmt001 ).
*create a xlsx handler
DATA(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( ).
*open xlxs into xstring
DATA(xstring_excel) = cl_openxml_helper=>load_local_file( pi_filename ).
*load the xlsx
DATA(xlsxdocument) = xlsxhandler->load_doc( iv_file_data = xstring_excel ).
*extract data from sheet ( pi_sheetname)
DATA(firstsheet) = xlsxdocument->get_sheet_by_name( iv_sheet_name = pi_sheetname ). ”根据传入的sheet名称获取sheet
*extract data from first sheet
firstsheet = xlsxdocument->get_sheet_by_id( iv_sheet_id = 1 ).
"获取第一个sheet页
"check file structure, first line of excel file
DATA(columncount) = firstsheet->get_last_column_number_in_row( 1 ).
DATA column TYPE i VALUE 1.
"获取第一行有多少列,为下面循环赋值做准备
*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 ).
DATA(tablecomponents) = tablestructure->get_components( ).
"get the content of excel. 将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(<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.
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
*get data of excel 获取excel的内容
*有按照结构来获取数据,和按照列的顺序来获取excel数据
CASE check_structure.
WHEN abap_on.
"按照字段对应获取数据
WHILE currentrow <= rowcount.
APPEND INITIAL LINE TO pt_tab ASSIGNING
FIELD-SYMBOL(<currentrow>).
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 <currentrow> TO FIELD-SYMBOL(<cellvalue>).
<cellvalue> = cellvalue.
ENDLOOP.
currentrow = currentrow + 1.
ENDWHILE.
WHEN OTHERS.
"按照顺序获取数据
CLEAR column.
WHILE currentrow <= rowcount.
APPEND INITIAL LINE TO pt_tab ASSIGNING <currentrow>.
DO columncount TIMES.
column = column + 1.
cellvalue = firstsheet->get_cell_content(
EXPORTING iv_row = currentrow
iv_column = column ).
ASSIGN COMPONENT column
OF STRUCTURE <currentrow> TO <cellvalue>.
<cellvalue> = cellvalue.
ENDDO.
CLEAR column.
currentrow = currentrow + 1.
ENDWHILE.
ENDCASE.
结合前2天的随笔,一共给出3种excel导入的方法,掌握应该是第一种最容易掌握,当然第一种缺点也最大。
比较一下三种方法在效率上的区别:导入100条数据测试
1.ALSM_EXCEL_TO_INTERNAL方法:用时7秒
2.客制OLE-突破9999行的限制用时6秒(其实都是ole 和1 差不多也是正常)
3.CL_EHFND_XLSX类用时2秒
由以上可得,同学们可以多花心思研究下XML方式导入excel,以后就可以不用OLE,又快又舒服。