[개발Tip] WebGUI에서 Excel 업로드(CL_FDT_XL_SPREADSHEET/ABAP2XLSX)

WebGUI에서 Excel 업로드

CL_FDT_XL_SPREADSHEET

ABAP2XLSX

 

 

ABAP에서 엑셀 업로드(Excel to Internal Table)를 할 경우 대부분의 경우 ALSM_EXCEL_TO_INTERNAL_TABLE 펑션을 이용합니다.

단, 해당 펑션은 Excel OLE 기능을 이용하다 보니 WebGUI에서는 작동하지 않습니다.

 

이 경우 GUI_UPLOAD를 이용하여 PC의 엑셀 파일을 SAP에 업로드 하여 Excel(XML) Object를 만든 후에 Internal Table로 변환할 수 있습니다.

CL_FDT_XL_SPREADSHEET 클래스를 이용하거나, ABAP2XLSX를 이용하여 기능을 구현할 수 있으며 현장 상황에 따라 아래 링크처럼 Class(또는 펑션)로 만들어서 활용하면 됩니다.

2023.11.22 - [ABAP/소스코드] - [Class] Excel 업로드/다운로드 통합 Class - ZCL_EXCEL_UPDOWN

 

# 소스코드 예시

REPORT YSAND_BOX.


  PARAMETERS: R_FDTXL RADIOBUTTON GROUP RB1,
              R_AB2XL RADIOBUTTON GROUP RB1.

INITIALIZATION.
  %_R_FDTXL_%_APP_%-TEXT = 'CL_FDT_XL_SPREADSHEET'.
  %_R_AB2XL_%_APP_%-TEXT = 'ABAP2XLSX'.

START-OF-SELECTION.
  "파일 선택
  DATA: LT_FILE_TABLE TYPE FILETABLE,
        LV_RC TYPE I,
        LT_DATA_TAB    TYPE SOLIX_TAB.

  CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG(
    exporting
      FILE_FILTER             =  SWITCH #( SY-LANGU WHEN '3' THEN 'Excel 통합문서 (*.xlsx)|*.xlsx' ELSE 'Excel Workbook (*.xlsx)|*.xlsx' )
    changing
      FILE_TABLE              = LT_FILE_TABLE
      RC                      = LV_RC
    exceptions
      FILE_OPEN_DIALOG_FAILED = 1
      CNTL_ERROR              = 2
      ERROR_NO_GUI            = 3
      NOT_SUPPORTED_BY_GUI    = 4
      OTHERS                  = 5
  ).
  CHECK LT_FILE_TABLE IS NOT INITIAL.

  "파일 업로드
  CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD(
    exporting
      FILENAME                = CONV #( LT_FILE_TABLE[ 1 ]-FILENAME )
      FILETYPE                = 'BIN'    " File Type (ASCII, Binary)
    importing
      FILELENGTH              = DATA(LV_LENGTH)
    changing
      DATA_TAB                = LT_DATA_TAB
    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.
    EXIT.
  endif.

  DATA(LV_XSTRING) = CL_BCS_CONVERT=>SOLIX_TO_XSTRING( LT_DATA_TAB ).
  FIELD-SYMBOLS <LFT_XLS> TYPE STANDARD TABLE.
  TYPES: BEGIN OF T_XLS,
        COL1 TYPE STRING,
        COL2 TYPE STRING,
        COL3 TYPE STRING,
        END OF T_XLS.
      DATA: LT_XLS TYPE STANDARD TABLE OF T_XLS,
            LS_XLS LIKE LINE OF LT_XLS.

  "Excel to Internal Table
  CASE ABAP_TRUE.
    WHEN R_FDTXL.   "CL_FDT_XL_SPREADSHEET 클래스 사용
* CL_FDT_XL_SPREADSHEET --------------------------------------------------------------------*
      TRY.
        DATA(LO_EXCEL_REF) = NEW CL_FDT_XL_SPREADSHEET(
                                  DOCUMENT_NAME = CONV #( LT_FILE_TABLE[ 1 ]-FILENAME )
                                  XDOCUMENT     = LV_XSTRING ) .
      CATCH CX_FDT_EXCEL_CORE.
        EXIT.
      ENDTRY .

      "GET LIST OF WORKSHEETS
      LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES(
        IMPORTING
          WORKSHEET_NAMES = DATA(LT_WORKSHEETS) ).

      "Internal table 변환
      IF NOT LT_WORKSHEETS IS INITIAL.
        DATA(LO_DATA_REF) = LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET(
                                                 WORKSHEET_NAME = LT_WORKSHEETS[ 1 ] ).
        "NOW YOU HAVE EXCEL WORK SHEET DATA IN DYANMIC INTERNAL TABLE
        ASSIGN LO_DATA_REF->* TO <LFT_XLS>.

        DATA(LT_DFIES) = CL_SALV_DDIC=>GET_BY_DATA( <LFT_XLS> ).
        DATA LV_COL_NUM TYPE I.

        LOOP AT <LFT_XLS> ASSIGNING FIELD-SYMBOL(<LFS_XLS>).
          CLEAR LS_XLS.
          LOOP AT LT_DFIES INTO DATA(LS_DFIES).
            ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_XLS> TO FIELD-SYMBOL(<LFV_XLS>).

            PERFORM CONVERT_COLUMN_TO_I USING LS_DFIES-FIELDNAME CHANGING LV_COL_NUM.
            ASSIGN COMPONENT LV_COL_NUM OF STRUCTURE LS_XLS TO FIELD-SYMBOL(<LFV_ITAB>).

            IF <LFV_XLS> IS ASSIGNED AND <LFV_ITAB> IS ASSIGNED.
              <LFV_ITAB> = <LFV_XLS>.
            ENDIF.
          ENDLOOP.
          APPEND LS_XLS TO LT_XLS.
        ENDLOOP.
      ENDIF.
* CL_FDT_XL_SPREADSHEET --------------------------------------------------------------------*

    WHEN R_AB2XL.   "ABAP2XLSX 클래스 사용
* ABAP2XLSX --------------------------------------------------------------------*
      TRY.
        NEW ZCL_EXCEL_READER_2007( )->ZIF_EXCEL_READER~LOAD( LV_XSTRING )->GET_ACTIVE_WORKSHEET( )->GET_TABLE(
*          exporting
*            IV_SKIPPED_ROWS           =
*            IV_SKIPPED_COLS           =
*            IV_MAX_COL                =
*            IV_MAX_ROW                =
*            IV_SKIP_BOTTOM_EMPTY_ROWS = ABAP_FALSE
          importing
            ET_TABLE                  = LT_XLS
        ).
      catch ZCX_EXCEL.
        EXIT.
      ENDTRY.
* ABAP2XLSX --------------------------------------------------------------------*

  ENDCASE.


  "Internal Table 내용 확인
  CL_DEMO_OUTPUT=>DISPLAY( LT_XLS ).



FORM CONVERT_COLUMN_TO_I USING P_INPUT
                      CHANGING P_COL_NUMBER TYPE I.
  data:
      input       type string,
      cc          type c value '',
      strl        type i,
      cnt         type i value 0,
      ascii_val   type i,
      ascii_val_a type i.

  DATA col_number TYPE I.

  MOVE P_INPUT TO INPUT.

  ascii_val_a = cl_abap_conv_out_ce=>uccp( 'A' ).
  translate input to upper case.
  strl = strlen( input ) - 1.

  while strl >= 0.
    cc = input+cnt(1).
    ascii_val = cl_abap_conv_out_ce=>uccp( cc ).
    if cc >= 'A' and cc <= 'Z'.
      col_number = col_number + ( ascii_val - ascii_val_a + 1 ) * 26 ** strl .
      cnt = cnt + 1.
      strl = strl - 1 .
    else.
      col_number = 0.
      exit.
    endif.
  endwhile.

  MOVE col_number TO P_COL_NUMBER.
ENDFORM.