abap2xls应用DEMO1 解决你玩EXECEL的烦恼

某日遇到需求 接口会丢整个XLS文件过来。并且文件是一个STRING内容。要求把STRING转成内表。

经过群里的推荐 最终使用ABAP2XLS 开源解决

使用ABAP2XLS 需要ABAPGIT或者 ZSAPLINK 来安装相关内容后续学习

本次代码如下

*&---------------------------------------------------------------------*
*& Report YTEST0003
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT YTEST0003.
DATA: FILENAME TYPE STRING VALUE 'C:\a.txt'.

TYPES: BEGIN OF ITAB,
         FL1 TYPE STRING,
       END OF ITAB.

DATA: DATA_TAB TYPE STANDARD TABLE OF ITAB,
      WA_TAB   LIKE LINE OF DATA_TAB.

DATA : L_RC        TYPE  I,
       L_FILETABLE TYPE FILETABLE.
DATA:LV_STRING TYPE STRING.


PARAMETERS: P_FILE LIKE RLGRAP-FILENAME DEFAULT 'C:\a.txt'.         "物料导入摸版

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
    EXPORTING
      WINDOW_TITLE            = '请选择本地文件'
*     DEFAULT_EXTENSION       =
*     DEFAULT_FILENAME        =
      FILE_FILTER             = '文本文件 (*.txt)|*.txt|全部文件 (*.*)|*.*|'
      INITIAL_DIRECTORY       = SPACE
      "MULTISELECTION          = 'X'
    CHANGING
      FILE_TABLE              = L_FILETABLE
      RC                      = L_RC
*     USER_ACTION             =
    EXCEPTIONS
      FILE_OPEN_DIALOG_FAILED = 1
      CNTL_ERROR              = 2
      ERROR_NO_GUI            = 3
      OTHERS                  = 4.
  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

START-OF-SELECTION.
*
*  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD
*    EXPORTING
*      FILENAME                = FILENAME
*      FILETYPE                = 'ASC'
*      CODEPAGE                = '8400'
*    CHANGING
*      DATA_TAB                = DATA_TAB
*    EXCEPTIONS
*      FILE_OPEN_ERROR         = 1
*      FILE_READ_ERROR         = 2
*      NO_BATCH                = 3
*      GUI_REFUSE_FILETRANSFER = 4
*      NO_AUTHORITY            = 6
*      UNKNOWN_ERROR           = 7
*      BAD_DATA_FORMAT         = 8
*      UNKNOWN_DP_ERROR        = 12
*      ACCESS_DENIED           = 13
*      OTHERS                  = 17.
*
*  LOOP AT DATA_TAB INTO WA_TAB.
*    WRITE:/ WA_TAB-FL1.
*  ENDLOOP.
*
*  BREAK-POINT.
*

*
*  LOOP AT DATA_TAB INTO DATA(LS_TAB).
*
*
*    CALL FUNCTION 'SCMS_BASE64_DECODE_STR'
*      EXPORTING
*        INPUT  = LS_TAB-FL1
*      IMPORTING
*        OUTPUT = XS.
*
*
*    BREAK-POINT.
*
*
**    CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
**      EXPORTING
**        TEXT   =  LS_TAB-FL1
**        "MIMETYPE       = 'charset=gb2312'
**      IMPORTING
**        BUFFER = XS
**      EXCEPTIONS
**        FAILED = 1
**        OTHERS = 2.
*
*    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
*      EXPORTING
*        BUFFER        = XS
*      IMPORTING
*        OUTPUT_LENGTH = BIN_FILESIZE
*      TABLES
*        BINARY_TAB    = TEMPTABLE.
*
*
*    BREAK-POINT.
*
*    CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD(
*    EXPORTING
*    BIN_FILESIZE = BIN_FILESIZE
*    FILENAME = 'D:\a.xls'
*    FILETYPE = 'BIN'
*    CHANGING
*    DATA_TAB = TEMPTABLE
*).
*  ENDLOOP.

  DATA:XS TYPE XSTRING.
  DATA BIN_FILESIZE TYPE I.
  DATA TEMPTABLE    TYPE W3MIMETABTYPE.

*  DATA:BEGIN OF TEMPTABLE occurs 0,
*    LINE TYPE x LENGTH 1024.
*  data:END OF TEMPTABLE.

  TYPES:BEGIN OF TY_S_FDATA,
          FLINE TYPE X LENGTH 1024,
        END OF TY_S_FDATA.
  DATA:LT_MIME  TYPE STANDARD TABLE OF TY_S_FDATA.


*  CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD(
*  EXPORTING
*  "BIN_FILESIZE = BIN_FILESIZE
*  FILENAME = 'D:\a.xlsx'
*  FILETYPE = 'BIN'
*  "FILETYPE                = 'DAT'
*  DAT_MODE                = 'X'
*  IMPORTING
*    FILELENGTH   = BIN_FILESIZE
*  CHANGING
*  DATA_TAB = LT_MIME
*  ).


  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      FILENAME                = 'D:\b.xlsx'
      FILETYPE                = 'BIN'
    IMPORTING
      FILELENGTH              = BIN_FILESIZE
    TABLES
      DATA_TAB                = LT_MIME
    EXCEPTIONS
      FILE_OPEN_ERROR         = 1
      FILE_READ_ERROR         = 2
      NO_BATCH                = 3
      GUI_REFUSE_FILETRANSFER = 4
      INVALID_TYPE            = 5
      NO_AUTHORITY            = 6
      UNKNOWN_ERROR           = 7
      BAD_DATA_FORMAT         = 8
      HEADER_NOT_ALLOWED      = 9
      SEPARATOR_NOT_ALLOWED   = 10
      HEADER_TOO_LONG         = 11
      UNKNOWN_DP_ERROR        = 12
      ACCESS_DENIED           = 13
      DP_OUT_OF_MEMORY        = 14
      DISK_FULL               = 15
      DP_TIMEOUT              = 16
      OTHERS                  = 17.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                        WITH SY-MSGV1
                             SY-MSGV2
                             SY-MSGV3
                             SY-MSGV4." INTO ev_messg.
    RETURN.
  ENDIF.

  CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
    EXPORTING
      INPUT_LENGTH = BIN_FILESIZE
*     FIRST_LINE   = 0
*     LAST_LINE    = 0
    IMPORTING
      BUFFER       = XS
    TABLES
      BINARY_TAB   = LT_MIME
*                EXCEPTIONS
*     FAILED       = 1
*     OTHERS       = 2
    .
  IF SY-SUBRC <> 0.
* Implement suitable error handling here
  ENDIF.

  DATA:LO_READER TYPE REF TO ZCL_EXCEL_READER_2007.
*  "excel文档类对象
  DATA:LO_EXCEL TYPE REF TO ZCL_EXCEL.
*  "excel worksheet类对象
  DATA:LO_SHEET TYPE REF TO ZCL_EXCEL_WORKSHEET.
*  "异常类
  DATA:LF_EXCEL TYPE REF TO ZCX_EXCEL.

  DATA:LV_HIGH_ROW TYPE ZEXCEL_CELL_COLUMN.
  DATA:LV_HIGH_COL TYPE ZEXCEL_CELL_COLUMN.

  BREAK-POINT.
  LO_READER = NEW ZCL_EXCEL_READER_2007( ).
  TRY.
      LO_EXCEL = LO_READER->ZIF_EXCEL_READER~LOAD( I_EXCEL2007 = XS ).
      LO_SHEET = LO_EXCEL->GET_WORKSHEET_BY_INDEX( 1 ).

      LO_SHEET->GET_HIGHEST_ROW( ).
      LV_HIGH_ROW = LO_SHEET->GET_HIGHEST_ROW( )."获取多少行。
      LV_HIGH_COL = LO_SHEET->GET_HIGHEST_COLUMN( )."获取多少列
    CATCH ZCX_EXCEL.

  ENDTRY.

DATA:lv_rowno TYPE SYST_INDEX.
DATA:lv_colno TYPE SYST_INDEX.
DATA:lt_excel TYPE STANDARD TABLE OF ZALSMEX_TABLINE.
 DO lv_high_row TIMES.

    lv_rowno = sy-index.

*    IF lv_rowno < 2.
*      CONTINUE.
*    ENDIF.

    DO lv_high_col TIMES.

      lv_colno = sy-index.

      IF lv_colno < 1.
        CONTINUE.
      ENDIF.

      READ TABLE lo_sheet->sheet_content ASSIGNING FIELD-SYMBOL(<ls_content>)
      WITH KEY cell_row    = lv_rowno
               cell_column = lv_colno.
      IF sy-subrc = 0 AND <ls_content>-cell_value IS NOT INITIAL.
        APPEND INITIAL LINE TO lt_excel ASSIGNING FIELD-SYMBOL(<ls_line>).
        <ls_line>-row   = lv_rowno + 1 - 2.
        <ls_line>-col   = lv_colno.
        <ls_line>-value = <ls_content>-cell_value.
      ENDIF.

    ENDDO.

  ENDDO.

  BREAK-POINT.