SAP 导入EXCEL

最新推荐文章于 2024-09-19 15:31:59 发布

二十三年冬至

最新推荐文章于 2024-09-19 15:31:59 发布

阅读量574

点赞数 11

文章标签： excel 服务器 java

本文链接：https://blog.youkuaiyun.com/qq_44624746/article/details/135346226

版权

"调用方法  
CALL METHOD zcl_common_method=>import_excel
    EXPORTING
      iv_filename   = lv_filename
      iv_start_row  = '4' "开始获取的行号
    IMPORTING
      ev_error_text = error_text
    CHANGING
      ct_excel      = lt_tab
    EXCEPTIONS
      file_open_error = 1
      OTHERS          = 2.
"下面是方法
METHOD import_excel.
  "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.

*单个sheet页面导入
*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( iv_filename ). "获取excel路径 为 xstring格式
    CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
      ev_error_text = openxml_not_found->get_text( ).
  ENDTRY.
  IF ev_error_text IS NOT INITIAL.
    RETURN.
  ENDIF.

*load the xlsx
  TRY.
      DATA(xlsxdocument) = xlsxhandler->load_doc( iv_file_data = xstring_excel ). "加载excel
    CATCH cx_openxml_format cx_openxml_not_allowed cx_dynamic_check INTO DATA(openxml_format).
      ev_error_text = openxml_format->get_text( ).
  ENDTRY.
  IF ev_error_text IS NOT INITIAL.
    RETURN.
  ENDIF.

*获取页码数据
  DATA:lv_id TYPE i VALUE 1.
  DATA(lt_sheets) = xlsxdocument->get_sheets( )."获取sheet页面
  READ TABLE lt_sheets INTO DATA(ls_sheets) INDEX 1.
  lv_id = ls_sheets-sheet_id.
  TRY.
      DATA(firstsheet) = xlsxdocument->get_sheet_by_id( iv_sheet_id = lv_id ).
    CATCH cx_openxml_format cx_openxml_not_found cx_dynamic_check INTO openxml_format.
      ev_error_text = openxml_format->get_text( ) .
      ev_error_text = |获取第一个sheet页失败: | && ev_error_text .
  ENDTRY.

  IF ev_error_text IS NOT INITIAL.
    RETURN.
  ENDIF.

  "return if no sheet in xlsx file
  CHECK NOT firstsheet IS INITIAL.

  DATA(columncount) = firstsheet->get_last_column_number_in_row( 1 ). "获取第1行列数 （一共几列）
  DATA column TYPE i VALUE 1.

*获取数据结构
  DATA:ls_tab_ref TYPE REF TO data.
  CREATE DATA ls_tab_ref LIKE LINE OF ct_excel.

  DATA tablestructure TYPE REF TO cl_abap_structdescr.
  tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( ls_tab_ref ). "获取内表的 components
  DATA(tablecomponents) = tablestructure->get_components( ).

  DATA(rowcount) = firstsheet->get_last_row_number( ).
  DATA(currentrow) = iv_start_row.
*获取数据到内表
  CLEAR column.
  WHILE currentrow <= rowcount.
    APPEND INITIAL LINE TO ct_excel ASSIGNING FIELD-SYMBOL(<currentrow>).
    DO columncount TIMES.
      column = column + 1.
      DATA(cellvalue) = firstsheet->get_cell_content(
        EXPORTING
          iv_row    = currentrow
          iv_column = column ).
      ASSIGN COMPONENT column OF STRUCTURE <currentrow> TO FIELD-SYMBOL(<cellvalue>).
      <cellvalue> = cellvalue.
    ENDDO.
    CLEAR column.
    currentrow = currentrow + 1.
  ENDWHILE.

ENDMETHOD.