当前位置:首页 > 服务端 > 2020.02.28 【ABAP随笔】- EXCEL批导程式3

2020.02.28 【ABAP随笔】- EXCEL批导程式3

怎么找到这个类的呢,是因为Wang Jerry 的一篇文章。感谢sap研究院大佬让我更透彻的了解了一下XLSX其实就是多个XML文件组合成的。

文章链接如下,请大家自行关注查看:

https://mp.weixin.qq.com/s/29v98Z4mgFboZjBTLsiWbw

2020.02.28 【ABAP随笔】- EXCEL批导程式3 _ JavaClub全栈架构师技术笔记

关键      DATA error_text TYPE string.

      lv_filename = p_file.
      CHECK lv_filename IS NOT INITIAL.
      CALL METHOD 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 ).

 
   
"下列代码实现了Excel 单个sheet页的导入 代码可以直接使用
METHOD import_document_from_frontend. *create a xlsx handler DATA(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( )."创建了句柄 *open xlsx into xstring TRY. DATA(xstring_excel) = cl_openxml_helper=>load_local_file( pi_filename ). "获取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. *extract data from sheet ( pi_sheetname) TRY. DATA(firstsheet) = xlsxdocument->get_sheet_by_name( iv_sheet_name = pi_sheetname ). 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 . ENDTRY. IF error_text IS NOT INITIAL. MESSAGE e001(00) RAISING file_open_error WITH error_text. ENDIF. *extract data from first sheet IF firstsheet IS INITIAL . CLEAR error_text. TRY. firstsheet = xlsxdocument->get_sheet_by_id( iv_sheet_id = 1 ). CATCH cx_openxml_format INTO openxml_format. error_text = openxml_format->get_text( ) . error_text = |Error occurs when extract data from first 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 first sheet: | && error_text . CATCH cx_dynamic_check INTO dynamic_check. error_text = |Error occurs when extract data from first sheet: | && dynamic_check->get_text( ) . ENDTRY. ENDIF. 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(<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 "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(<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. ENDMETHOD.

结合前2天的随笔,一共给出3种excel导入的方法,掌握应该是第一种最容易掌握,当然第一种缺点也最大。

比较一下三种方法在效率上的区别:导入100条数据测试

1.ALSM_EXCEL_TO_INTERNAL方法:用时7秒

2020.02.28 【ABAP随笔】- EXCEL批导程式3 _ JavaClub全栈架构师技术笔记

 

 2.客制OLE-突破9999行的限制 用时6秒 (其实都是ole 和1 差不多也是正常)

2020.02.28 【ABAP随笔】- EXCEL批导程式3 _ JavaClub全栈架构师技术笔记

 

 3.CL_EHFND_XLSX类 用时2秒

2020.02.28 【ABAP随笔】- EXCEL批导程式3 _ JavaClub全栈架构师技术笔记

 

 由以上可得,多花心思研究下XML方式导入excel,以后就可以不用OLE,又快又舒服。

2020.02.28 【ABAP随笔】- EXCEL批导程式3 _ JavaClub全栈架构师技术笔记

"获取excel

作者:TAB_Zhu
来源链接:https://www.cnblogs.com/jxzhu/p/12375210.html

版权声明:
1、Java侠(https://www.javaxia.com)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。





本文链接:https://www.javaxia.com/server/124915.html

分享给朋友:

“2020.02.28 【ABAP随笔】- EXCEL批导程式3” 的相关文章