[Function] Internal Table 엑셀 다운로드(XLSX 형식)

인터널 테이블 엑셀 다운로드 

ZCM_ITAB_FILE_DOWNLOAD_DIALOG

ZCM_ITAB_FILE_DOWNLOAD

 

Internal Table의 데이터를 SALV class를 이용하여 XLSX 형식 엑셀 파일로 다운로드 하는 Function입니다.

ALV에서 스프레드시트로 내보내기 하는 것과 동일한 형식으로 다운로드 됩니다.

 

ABAP 내에서 E-Mail이나 oData 등에서 엑셀 파일의 Binary 처리하기 위해 "Internal Table -> Excel" Function과 Dialog Function 2개로 나누어 개발했습니다.

 

# 소스코드 ZCM_ITAB_FILE_DOWNLOAD

Internal Table의 데이터를 XLSX형식 엑셀 파일로 Binary 데이터(EV_BINTAB)으로 반환

FUNCTION ZCM_ITAB_FILE_DOWNLOAD.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(IT_FIELDCAT) TYPE  LVC_T_FCAT OPTIONAL		"필드 설정(내역 등) 미 설정시 Internal Table의 필드내역
*"     VALUE(IV_XLS) DEFAULT 'X'				"XLSX 형식 다운로드, SPACE시 MHTML
*"  EXPORTING
*"     VALUE(EV_XSTRING) TYPE  XSTRING				"Xstring 반환
*"     VALUE(EV_SIZE) TYPE  I					"Xstring 길이
*"     VALUE(EV_BINTAB) TYPE  SOLIX_TAB				"Binary table 데이터
*"  CHANGING
*"     VALUE(IT_OUTTAB)						"엑셀 전환할 Internal Table
*"----------------------------------------------------------------------
  DATA: LT_DATA         TYPE REF TO DATA,
        LT_FIELDCAT     TYPE LVC_T_FCAT.
  DATA: LO_SALV_TABLE   TYPE REF TO CL_SALV_TABLE,
        LO_RESULT_DATA  TYPE REF TO CL_SALV_EX_RESULT_DATA_TABLE.
  DATA: M_FILE_TYPE     TYPE SALV_BS_CONSTANT.

  FIELD-SYMBOLS <LFS_TAB>   TYPE ANY TABLE.

  CLEAR : EV_XSTRING, EV_SIZE.

  GET REFERENCE OF IT_OUTTAB INTO LT_DATA.

  IF IT_FIELDCAT[] IS INITIAL.
    ASSIGN LT_DATA->* TO <LFS_TAB>.
    TRY .
      CL_SALV_TABLE=>FACTORY(
        EXPORTING
          LIST_DISPLAY = ABAP_FALSE
        IMPORTING
          R_SALV_TABLE = LO_SALV_TABLE
        CHANGING
          T_TABLE      = <LFS_TAB> ).
    CATCH CX_SALV_MSG.

    ENDTRY.
    IF LO_SALV_TABLE IS NOT INITIAL.
      LT_FIELDCAT = CL_SALV_CONTROLLER_METADATA=>GET_LVC_FIELDCATALOG( R_COLUMNS      = LO_SALV_TABLE->GET_COLUMNS( )
                                                                       R_AGGREGATIONS = LO_SALV_TABLE->GET_AGGREGATIONS( ) ).
    ENDIF.
  ELSE.
    LT_FIELDCAT[] = IT_FIELDCAT[].
  ENDIF.

  LO_RESULT_DATA = CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE( R_DATA         = LT_DATA
                                                               T_FIELDCATALOG = LT_FIELDCAT ).

  IF IV_XLS IS NOT INITIAL.
    M_FILE_TYPE = IF_SALV_BS_XML=>C_TYPE_XLSX.
  ELSE.
    M_FILE_TYPE = IF_SALV_BS_XML=>C_TYPE_MHTML.
  ENDIF.

  CL_SALV_BS_TT_UTIL=>IF_SALV_BS_TT_UTIL~TRANSFORM(
    EXPORTING
      XML_TYPE      = M_FILE_TYPE
      XML_VERSION   = CL_SALV_BS_A_XML_BASE=>GET_VERSION( )
      R_RESULT_DATA = LO_RESULT_DATA
      XML_FLAVOUR   = IF_SALV_BS_C_TT=>C_TT_XML_FLAVOUR_EXPORT
      GUI_TYPE      = IF_SALV_BS_XML=>C_GUI_TYPE_GUI
    IMPORTING
      XML           = EV_XSTRING ).

  IF EV_BINTAB IS REQUESTED.
    EV_SIZE = XSTRLEN( EV_XSTRING ).
    EV_BINTAB = CL_BCS_CONVERT=>XSTRING_TO_SOLIX( EV_XSTRING ).
  ENDIF.
ENDFUNCTION.

 

# 소스코드 ZCM_ITAB_FILE_DOWNLOAD_DIALOG

Internal Table을 엑셀로 다운로드 (ZCM_ITAB_FILE_DOWNLOAD 내부 호출)

FUNCTION ZCM_ITAB_FILE_DOWNLOAD_DIALOG.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(IV_DIRECTORY) OPTIONAL			"저장할 폴더
*"     VALUE(IV_FILENAME) OPTIONAL			"저장할 파일명
*"     VALUE(IT_FIELDCAT) TYPE  LVC_T_FCAT OPTIONAL	"필드 카타로드
*"     VALUE(IV_AUTO_OPEN) TYPE  C DEFAULT SPACE	"파일 다운로드 후 열기 여부
*"  EXPORTING
*"     VALUE(EV_DOWNLOAD_FILENAME)
*"     VALUE(EV_DOWNLOAD_PATH)
*"  CHANGING
*"     VALUE(IT_OUTTAB) OPTIONAL			"엑셀 다운로드할 Internal Table
*"  EXCEPTIONS
*"      DOWNLOAD_ERROR
*"      EXTENSION_ERROR
*"----------------------------------------------------------------------
  DATA: LV_EXTENSION_MASK TYPE STRING,
        LV_FILENAME       TYPE STRING.
  DATA: LT_BINTAB         TYPE SOLIX_TAB,
        LV_XSTRING        TYPE XSTRING,
        LV_STRING         TYPE STRING,
        LV_SIZE           TYPE I.
  DATA: LV_ACTION         TYPE I,                "ACTION
        LV_PATH           TYPE STRING,
        LV_FILE_PATH      TYPE STRING,
        LV_DE_FILENAME    TYPE STRING.

  CLEAR EV_DOWNLOAD_FILENAME.

  IF IV_DIRECTORY IS NOT INITIAL AND IV_FILENAME IS NOT INITIAL.
    MOVE IV_FILENAME TO LV_DE_FILENAME.
    MOVE IV_DIRECTORY TO LV_PATH.
    CONCATENATE LV_PATH LV_DE_FILENAME INTO LV_FILE_PATH.
  ELSE.
    MOVE IV_FILENAME TO LV_DE_FILENAME.

    "확장자
    CLEAR LV_EXTENSION_MASK.
    IF SY-LANGU EQ '3'.
      CONCATENATE LV_EXTENSION_MASK 'Excel 통합문서 (*.xlsx)|*.xlsx' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|Excel 97 - 2003 통합문서 (*.xls)|*.xls' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|CSV(쉼표로 분리) (*.csv)|*.csv' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|텍스트 파일 (*.txt)|*.txt' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|모든 파일 (*)|*' INTO LV_EXTENSION_MASK.
    ELSE.
      CONCATENATE LV_EXTENSION_MASK 'Excel Workbook (*.xlsx)|*.xlsx' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|Excel 97 - 2003 Workbook (*.xls)|*.xls' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|CSV(Comma Separated) (*.csv)|*.csv' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|Text file (*.txt)|*.txt' INTO LV_EXTENSION_MASK.
*      CONCATENATE LV_EXTENSION_MASK '|All files (*)|*' INTO LV_EXTENSION_MASK.
    ENDIF.

    CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
      EXPORTING
*       WINDOW_TITLE              =
*       DEFAULT_EXTENSION         =
        DEFAULT_FILE_NAME         = LV_DE_FILENAME
*       WITH_ENCODING             =
        FILE_FILTER               = LV_EXTENSION_MASK "CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
        INITIAL_DIRECTORY         = CONV #( IV_DIRECTORY )
*       PROMPT_ON_OVERWRITE       = 'X'
      CHANGING
        FILENAME                  = LV_FILENAME
        PATH                      = LV_PATH
        FULLPATH                  = LV_FILE_PATH
        USER_ACTION               = LV_ACTION
*       FILE_ENCODING             =
      EXCEPTIONS
        CNTL_ERROR                = 1
        ERROR_NO_GUI              = 2
        NOT_SUPPORTED_BY_GUI      = 3
        INVALID_DEFAULT_FILE_NAME = 4
        others                    = 5.
    IF SY-SUBRC <> 0 OR LV_ACTION = CL_GUI_FRONTEND_SERVICES=>ACTION_CANCEL.
      EXIT.
    ENDIF.
  ENDIF.

  CALL FUNCTION 'ZCM_ITAB_FILE_DOWNLOAD'
    EXPORTING
      IT_FIELDCAT = IT_FIELDCAT
    IMPORTING
      EV_XSTRING  = LV_XSTRING
      EV_SIZE     = LV_SIZE
      EV_BINTAB   = LT_BINTAB
    CHANGING
      IT_OUTTAB   = IT_OUTTAB.

  "다운로드
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
    EXPORTING
      BIN_FILESIZE            = LV_SIZE
      FILENAME                = LV_FILE_PATH
      FILETYPE                = 'BIN'
    CHANGING
      DATA_TAB                = LT_BINTAB
    EXCEPTIONS
      FILE_WRITE_ERROR        = 1
      NO_BATCH                = 2
      GUI_REFUSE_FILETRANSFER = 3
      INVALID_TYPE            = 4
      NO_AUTHORITY            = 5
      UNKNOWN_ERROR           = 6
      HEADER_NOT_ALLOWED      = 7
      SEPARATOR_NOT_ALLOWED   = 8
      FILESIZE_NOT_ALLOWED    = 9
      HEADER_TOO_LONG         = 10
      DP_ERROR_CREATE         = 11
      DP_ERROR_SEND           = 12
      DP_ERROR_WRITE          = 13
      UNKNOWN_DP_ERROR        = 14
      ACCESS_DENIED           = 15
      DP_OUT_OF_MEMORY        = 16
      DISK_FULL               = 17
      DP_TIMEOUT              = 18
      FILE_NOT_FOUND          = 19
      DATAPROVIDER_EXCEPTION  = 20
      CONTROL_FLUSH_ERROR     = 21
      OTHERS                  = 22.
  IF SY-SUBRC NE 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING DOWNLOAD_ERROR.
    EXIT.
  ENDIF.

  IF IV_AUTO_OPEN EQ 'X'.
    CALL METHOD CL_GUI_FRONTEND_SERVICES=>EXECUTE
      EXPORTING
        DOCUMENT               = LV_FILE_PATH
      EXCEPTIONS
        CNTL_ERROR             = 1
        ERROR_NO_GUI           = 2
        BAD_PARAMETER          = 3
        FILE_NOT_FOUND         = 4
        PATH_NOT_FOUND         = 5
        FILE_EXTENSION_UNKNOWN = 6
        ERROR_EXECUTE_FAILED   = 7
        SYNCHRONOUS_FAILED     = 8
        NOT_SUPPORTED_BY_GUI   = 9
        others                 = 10.
    IF SY-SUBRC <> 0.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING DOWNLOAD_ERROR.
    ENDIF.
  ENDIF.

  EV_DOWNLOAD_FILENAME = LV_FILENAME.
  EV_DOWNLOAD_PATH     = LV_PATH.
ENDFUNCTION.

 

# 사용예시

DATA: LT_T001 TYPE STANDARD TABLE OF T001,
      LT_FCAT TYPE LVC_T_FCAT.

SELECT
  *
FROM T001
INTO CORRESPONDING FIELDS OF TABLE @LT_T001.

LT_FCAT = VALUE #(
                    ( FIELDNAME = 'BUKRS' REPTEXT = '회사코코드' )
                    ( FIELDNAME = 'BUTXT' REPTEXT = '회사명명명' )
                    ).


CALL FUNCTION 'ZCM_ITAB_FILE_DOWNLOAD_DIALOG'
  EXPORTING
*   IV_DIRECTORY               =
*   IV_FILENAME                =
    IT_FIELDCAT                = LT_FCAT[]
*   IV_AUTO_OPEN               = ' '
* IMPORTING
*   EV_DOWNLOAD_FILENAME       =
*   EV_DOWNLOAD_PATH           =
  CHANGING
    IT_OUTTAB                  = LT_T001[]
  EXCEPTIONS
    DOWNLOAD_ERROR             = 1
    EXTENSION_ERROR            = 2
    OTHERS                     = 3
          .
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.