abap 导入Excel

*&---------------------------------------------------------------------*
*& Report YCLY_VL_POST1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Y_EXCEL_UPLOAD.


DATA:LO_DATA TYPE REF TO DATA.
DATA: L_RC TYPE I.

START-OF-SELECTION.

  PERFORM SUB_UPLOAD_DATA CHANGING LO_DATA L_RC.
  BREAK-POINT.


*&---------------------------------------------------------------------*
*& Form SUB_UPLOAD_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM SUB_UPLOAD_DATA   CHANGING CO_DATA TYPE REF TO DATA
                                C_RC TYPE I.
  DATA :
    L_RC            TYPE I,
    L_FILE          TYPE STRING,
    LT_FILETABLE    TYPE FILETABLE,

    L_FILENAME      TYPE STRING,
    LT_DATA         TYPE SOLIX_TAB,
    L_HEADERXSTRING TYPE XSTRING,
    L_FILELENGTH    TYPE I.

  C_RC  = 0.
  REFRESH LT_FILETABLE.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
    EXPORTING
      WINDOW_TITLE            = '选择文件'
      DEFAULT_EXTENSION       = ',*.xlsx,xlsx,*.xls,xls'
    CHANGING
      FILE_TABLE              = LT_FILETABLE
      RC                      = L_RC
    EXCEPTIONS
      FILE_OPEN_DIALOG_FAILED = 1
      CNTL_ERROR              = 2
      ERROR_NO_GUI            = 3
      NOT_SUPPORTED_BY_GUI    = 4
      OTHERS                  = 5.
  IF SY-SUBRC <> 0.
    C_RC  = SY-SUBRC.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    RETURN.
  ENDIF..

  READ TABLE LT_FILETABLE INTO DATA(LS_FILETABLE) INDEX 1.
  IF SY-SUBRC <>  0.
    C_RC  = SY-SUBRC.
    RETURN.
  ENDIF.

  L_FILENAME = LS_FILETABLE.

  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD
    EXPORTING
      FILENAME                = L_FILENAME
      FILETYPE                = 'BIN'
    IMPORTING
      FILELENGTH              = L_FILELENGTH
      HEADER                  = L_HEADERXSTRING
    CHANGING
      DATA_TAB                = LT_DATA
    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
      NOT_SUPPORTED_BY_GUI    = 17
      ERROR_NO_GUI            = 18
      OTHERS                  = 19.
  IF SY-SUBRC <> 0.
*    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    C_RC = SY-SUBRC.
    MESSAGE '请关闭EXCEL后重试.' TYPE 'E'.
    RETURN .
  ENDIF.


  CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
    EXPORTING
      INPUT_LENGTH = L_FILELENGTH
    IMPORTING
      BUFFER       = L_HEADERXSTRING
    TABLES
      BINARY_TAB   = LT_DATA
    EXCEPTIONS
      FAILED       = 1
      OTHERS       = 2.

  IF SY-SUBRC IS NOT INITIAL.
    C_RC = SY-SUBRC.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
       WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    RETURN.
  ENDIF.

  TRY .
*     操作多tab页对象创建
      DATA(LO_EXCEL_REF) = NEW CL_FDT_XL_SPREADSHEET( DOCUMENT_NAME = L_FILENAME
                                                      XDOCUMENT     = L_HEADERXSTRING ).

    CATCH CX_FDT_EXCEL_CORE INTO DATA(LO_CX_FDT_EXCEL_CORE).
      C_RC = 4.
      DATA(L_MSG) = LO_CX_FDT_EXCEL_CORE->GET_TEXT( ).
      MESSAGE E368(00) WITH 'EXCEL导入失败' L_MSG.

  ENDTRY .

  IF LO_EXCEL_REF IS BOUND.
*   获取页签列表
    LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES(
      IMPORTING
        WORKSHEET_NAMES = DATA(LT_WORKSHEETS) ).

    READ TABLE LT_WORKSHEETS INTO DATA(LS_WOKSHEETNAME) INDEX 1.
    CHECK SY-SUBRC = 0.
    CO_DATA = LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET( LS_WOKSHEETNAME ).

  ENDIF.
ENDFORM.