ABAP随笔-关于ECC后台server读取Excel方案的想法

*前后台处理excel call demo.abap
*&---------------------------------------------------------------------*
*& Report ZTAB_ZHU_XLSX_02
*&---------------------------------------------------------------------*
*&=====================================================================*
*& create date:  20220514
*& created by:   tab-zhu (微信公众号：tab_jxzhu)
*&---------------------------------------------------------------------*
REPORT ztab_zhu_xlsx_02.

*Constants
DATA:BEGIN OF ls_data,
       a TYPE string,
       b TYPE string,
       c TYPE string,
     END OF ls_data.
DATA lt_data LIKE STANDARD TABLE OF ls_data.

*Global data
DATA : l_len TYPE sy-tabix.
DATA : l_filename TYPE authb-filename.
DATA l_subrc TYPE sy-subrc.
DATA: l_lines TYPE i.
DATA: l_xstring TYPE xstring.
DATA: li_e_rcgrepfile_tab TYPE cpt_x255,
      li_xtab             TYPE cpt_x255.
DATA lst_rcgrepfile TYPE cps_x255.
DATA: l_file_content TYPE xstring.

DATA:BEGIN OF gt_tab OCCURS 0,
       a1 TYPE string,
       a2 TYPE string,
       a3 TYPE string,
     END OF gt_tab.
DATA e_file_size TYPE drao-orln.
DATA e_lines TYPE i.

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE TEXT-s01.
  PARAMETERS:rb_01 RADIOBUTTON GROUP gp1,
             rb_02 RADIOBUTTON GROUP gp1.

  PARAMETERS:i_file TYPE rlgrap-filename DEFAULT '/usr/sap/S4H/SYS/src/test.xlsx'.
SELECTION-SCREEN END OF BLOCK blk.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR i_file.
  i_file = cl_openxml_helper=>browse_local_file_open(
  iv_title = 'Select XLSX File'
  iv_filename = ''
  iv_extpattern = 'Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*' ).

START-OF-SELECTION.

  CASE abap_on.
    WHEN rb_02.
* assign value
      l_filename = i_file.
* check the authority for file
      CALL FUNCTION 'AUTHORITY_CHECK_DATASET'
        EXPORTING
*         PROGRAM          =
          activity         = sabc_act_read
* Authority Check allows right now only 60 Character
          filename         = l_filename(60)
        EXCEPTIONS
          no_authority     = 1
          activity_unknown = 2
          OTHERS           = 3.
      IF sy-subrc <> 0.
        RAISE no_permission.
      ENDIF.

* read the raw-file from the appl.server
      CLEAR l_subrc.
      OPEN DATASET i_file FOR INPUT IN BINARY MODE.
      l_subrc = sy-subrc.
      IF sy-subrc <> 0 OR
      l_subrc <> 0.
        RAISE open_failed.
      ENDIF.
      DO.
        CLEAR l_len.
        CLEAR lst_rcgrepfile.
        READ DATASET i_file INTO lst_rcgrepfile LENGTH l_len.
        IF sy-subrc <> 0.
          IF l_len > 0.
            e_file_size = e_file_size + l_len.
            APPEND lst_rcgrepfile TO li_e_rcgrepfile_tab.
          ENDIF.
          EXIT.
        ENDIF.
        CONCATENATE l_file_content lst_rcgrepfile
        INTO l_file_content IN BYTE MODE.
        e_file_size = e_file_size + l_len.
        APPEND lst_rcgrepfile TO li_e_rcgrepfile_tab.
      ENDDO.
      IF sy-subrc > 10.
        RAISE read_error.
      ENDIF.
      DESCRIBE TABLE li_e_rcgrepfile_tab LINES e_lines.
      CLOSE DATASET i_file.
      IF li_e_rcgrepfile_tab[] IS NOT INITIAL.
        li_xtab[] = li_e_rcgrepfile_tab[].
      ENDIF.
*Convert data to xstring
      cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = li_xtab
      im_size = l_lines
      IMPORTING ex_xstring = l_xstring ).

    WHEN rb_01.
      DATA lv_filename TYPE string.
      lv_filename = i_file.
      TRY.
          l_xstring = cl_openxml_helper=>load_local_file( lv_filename ). "获取excel路径 为 xstring格式
        CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
          RETURN.
      ENDTRY.
  ENDCASE.
  
  DATA lo_tab_excel TYPE REF TO zcl_tab_excel.
  CREATE OBJECT lo_tab_excel.
  "mo_xlsx_document
  CALL METHOD lo_tab_excel->load_doc( iv_file_data = l_xstring ).
  lo_tab_excel->initialize( ).
  DATA(lo_sheet) = lo_tab_excel->get_sheet_by_name( 'Sheet1' ).
  DATA(ls_sheet_data) = lo_tab_excel->get_sheet_data( lo_sheet ).
  "获取第1行列数 （一共几列）
  DATA(ls_last_columns) = lo_tab_excel->get_last_column_number_in_row( 1 ).
  "获取行数
  DATA(ls_last_rows) = lo_tab_excel->get_last_row_number( ).
  DATA(startrow) = 1.
  WHILE startrow <= ls_last_rows.
    CLEAR ls_data.
    DO ls_last_columns TIMES.
      DATA(currentcolumn) = sy-index.
      DATA(lv_content) = lo_tab_excel->get_cell_content(
          iv_row     = startrow
          iv_column  = currentcolumn
      ).
      ASSIGN COMPONENT currentcolumn OF STRUCTURE ls_data TO FIELD-SYMBOL(<fs_any>).
      IF sy-subrc EQ 0.
        <fs_any> = lv_content.
      ENDIF.
    ENDDO.
    APPEND ls_data TO lt_data.
    startrow = startrow + 1.
  ENDWHILE.

  cl_demo_output=>display( lt_data ).