abap2xlsx读取excel数据

需求背景：

因为excel有合并的单元格，用传统函数： ALSM_EXCEL_TO_INTERNAL_TABLE只能读取的合并单元格第一行的数据。所以用abap2xlsx方案。zcl_excel_worksheet的属性mt_merged_cells，获取合并单元格的信息。

效果

excel数据

拆分到abap内表结果

代码

FORM frm_upload_qc .
  DATA lv_file_name TYPE ibipparms-path.

  DATA: lt_data TYPE TABLE OF zss_yspj_qc,
        ls_data TYPE zss_yspj_qc,
        ls_qc   TYPE ty_qc.
  DATA: worksheet      TYPE REF TO zcl_excel_worksheet,
        highest_column TYPE zexcel_cell_column,
        highest_row    TYPE int4,
        column         TYPE zexcel_cell_column VALUE 1,
        col_str        TYPE zexcel_cell_column_alpha,
        row            TYPE int4               VALUE 1,
        value          TYPE zexcel_cell_value,
        converted_date TYPE d.
  DATA: excel           TYPE REF TO zcl_excel,
        lo_excel_writer TYPE REF TO zif_excel_writer,
        reader          TYPE REF TO zif_excel_reader.
  DATA merged_fg TYPE char1.
  DATA row_from TYPE i.
  DATA lv_index TYPE n LENGTH 5.
  DATA lv_dat TYPE dats.
  DATA lv_file_name_temp TYPE string.
  DATA lv_rc TYPE i.
  DATA stripped_name  TYPE string.
  DATA file_path  TYPE string.
  DATA lv_workdir        TYPE string.
  DATA lv_file_separator TYPE c.

  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
    IMPORTING
      file_name     = lv_file_name.

  FIELD-SYMBOLS: <fs> TYPE any.

  IF lv_file_name IS INITIAL.
    gv_error = abap_true.
    MESSAGE s001(00) WITH '未选择导入文件' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.

* 解决abap2xlsx读取不了打开的excel文件问题,拷贝文件到工作区，然后读取临时文件，最后删除临时文件
  cl_gui_frontend_services=>get_sapgui_workdir( CHANGING sapworkdir = lv_workdir ).
  cl_gui_frontend_services=>get_file_separator( CHANGING file_separator = lv_file_separator ).

  CALL FUNCTION 'TRINT_SPLIT_FILE_AND_PATH'
    EXPORTING
      full_name     = lv_file_name
    IMPORTING
      stripped_name = stripped_name
      file_path     = file_path
    EXCEPTIONS
      x_error       = 1
      OTHERS        = 2.

* 临时文件
  lv_file_name_temp =  lv_workdir &&  lv_file_separator && stripped_name.

  cl_gui_frontend_services=>file_copy(
    EXPORTING
      source               = CONV string( lv_file_name )
      destination          = lv_file_name_temp
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      wrong_parameter      = 3
      disk_full            = 4
      access_denied        = 5
      file_not_found       = 6
      destination_exists   = 7
      unknown_error        = 8
      path_not_found       = 9
      disk_write_protect   = 10
      drive_not_ready      = 11
      not_supported_by_gui = 12
      OTHERS               = 13
  ).
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
      WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 DISPLAY LIKE 'E'.
    gv_error = abap_true.
    RETURN.
  ENDIF.

  TRY.
      row = 3.    "读取excel的开始数据行
      CREATE OBJECT reader TYPE zcl_excel_reader_2007.
      excel = reader->load_file( lv_file_name_temp  ).
      worksheet = excel->get_active_worksheet( ).
      highest_column = worksheet->get_highest_column( ).
      highest_row    = worksheet->get_highest_row( ).
      WHILE row <= highest_row.
        WHILE column <= highest_column.
          col_str = zcl_excel_common=>convert_column2alpha( column ).
* 合并单元各处理
          CLEAR: merged_fg,row_from,lv_dat.
          LOOP AT worksheet->mt_merged_cells INTO DATA(ls_merged) WHERE row_from LE row
                                                                   AND row_to GE row
                                                                   AND col_from LE column
                                                                   AND col_to GE column.
            row_from = ls_merged-row_from.
          ENDLOOP.
          IF sy-subrc = 0.
            merged_fg = abap_true.  "合并的行标记
          ENDIF.

          IF merged_fg = abap_true.  "如果是合并单元格，用该合并区第一行的值
            worksheet->get_cell(
             EXPORTING
               ip_column = col_str
               ip_row    = row_from
             IMPORTING
               ep_value = value
           ).
          ELSE.
            worksheet->get_cell(
              EXPORTING
                ip_column = col_str
                ip_row    = row
              IMPORTING
                ep_value = value
            ).
          ENDIF.
          ASSIGN COMPONENT column OF STRUCTURE ls_data TO <fs>.
          IF sy-subrc = 0.
            CASE  col_str.
              WHEN 'F'OR 'G'OR  'M' OR 'N' OR 'Q'.    "日期列
                TRY.
* EXCEL日期类型,用zcl_excel_common=>excel_string_to_date转换
                    zcl_excel_common=>excel_string_to_date(
                      EXPORTING
                        ip_value = value
                      RECEIVING
                        ep_value = lv_dat
                    ).
                    <fs> = lv_dat.
                  CATCH zcx_excel INTO DATA(lo_cx1).
* 非EXCEL日期类型,先保存原本的string类型值,用后面的form:conv_extdat_to_intdat转换
                    <fs> = value.
                ENDTRY.
              WHEN OTHERS.
                <fs> = value.
            ENDCASE.
          ENDIF.
          column = column + 1.
        ENDWHILE.
        APPEND ls_data TO lt_data.
        CLEAR: ls_data.
        column = 1.
        row = row + 1.
      ENDWHILE.


    CATCH zcx_excel INTO DATA(ex).    " Exceptions for ABAP2XLSX
      MESSAGE  ex->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
      gv_error = abap_true.
      RETURN.
  ENDTRY.

  cl_gui_frontend_services=>file_delete(
    EXPORTING
      filename             = lv_file_name_temp
    CHANGING
      rc                   = lv_rc
    EXCEPTIONS
      file_delete_failed   = 1
      cntl_error           = 2
      error_no_gui         = 3
      file_not_found       = 4
      access_denied        = 5
      unknown_error        = 6
      not_supported_by_gui = 7
      wrong_parameter      = 8
      OTHERS               = 9
  ).

  IF lt_data[] IS INITIAL.
    MESSAGE s001(00) WITH '导入数据为空' DISPLAY LIKE 'E'.
    gv_error = abap_true.
    RETURN.
  ENDIF.

  LOOP AT lt_data INTO ls_data.
    lv_index = lv_index + 1.

    MOVE-CORRESPONDING ls_data TO ls_qc.
    ls_qc-zguid = sy-datum && sy-uzeit && lv_index.
    ls_qc-kunnr = |{ ls_qc-kunnr ALPHA = IN }|.

    PERFORM conv_extdat_to_intdat
      USING ls_data-cp_date
      CHANGING ls_qc-cp_date.

    PERFORM conv_extdat_to_intdat
      USING ls_data-dq_date
      CHANGING ls_qc-dq_date.

    PERFORM conv_extdat_to_intdat
      USING ls_data-cjwt_date
      CHANGING ls_qc-cjwt_date.

    PERFORM conv_extdat_to_intdat
     USING ls_data-zzsq_date
     CHANGING ls_qc-zzsq_date.

    PERFORM conv_extdat_to_intdat
      USING ls_data-zzsq_date_bs
      CHANGING ls_qc-zzsq_date_bs.

    ls_qc-create_date = sy-datum.
    ls_qc-create_time = sy-uzeit.
    ls_qc-create_user = sy-uname.
    ls_qc-gjahr = p_gjahr.
    ls_qc-monat = p_mon.

    APPEND ls_qc TO gt_qc.
    CLEAR: ls_data,ls_qc.

  ENDLOOP.
ENDFORM.

FORM conv_extdat_to_intdat  USING    p_extdat
                            CHANGING p_intdat.

  DATA: lv_extdat TYPE string,
        lv_len    TYPE i.

  lv_extdat = p_extdat.
  REPLACE ALL OCCURRENCES OF '/' IN lv_extdat WITH ''.
  REPLACE ALL OCCURRENCES OF '-' IN lv_extdat WITH ''.
  CONDENSE lv_extdat NO-GAPS.
  lv_len =  strlen( lv_extdat ).
  IF lv_len = 6.
    p_intdat = lv_extdat+0(4) && '0' && lv_extdat+4(1) && '0' && lv_extdat+5(1).
  ELSE.
    p_intdat = lv_extdat.
  ENDIF.

ENDFORM.