brunch

You can make anything
by writing

C.S.Lewis

by 동네아밥 May 03. 2022

현재 화면에 보이는 ITAB 엑셀 다운로드하기

그냥 스프레드시트 받으면 되는데 왜 굳이 버튼을 만들어 달라는걸까



  data: lt_fcat  type lvc_t_fcat,
         lv_datlo type char10,
         lv_timlo type char10,
         lv_fname type string.
 
   data: lo_data type ref to data.
   field-symbols: <lt_data> type standard table.
 
   go_grid->get_frontend_fieldcatalog( importing et_fieldcatalog = lt_fcat ).
   delete lt_fcat where no_out is not initial
                     or tech   is not initial.
 
   check lt_fcat is not initial.
 
   cl_alv_table_create=>create_dynamic_table(
     exporting
       it_fieldcatalog = lt_fcat
     importing
       ep_table        = lo_data
   ).
 
   assign lo_data->* to <lt_data>.
   <lt_data> = corresponding #( gt_data ).
 
   write sy-datlo to lv_datlo using edit mask '____.__.__'.
   write sy-timlo to lv_timlo using edit mask '__.__.__' .
   concatenate lv_datlo lv_timlo sy-title into lv_fname separated by space.
 
   call method zcl_util=>excel_download_itab
     exporting
       file_name               = lv_fname
       data_tab                = <lt_data>
       fieldnames              = lt_fcat
     exceptions
       no_data                 = 1
       download_problem        = 2
       file_not_exist          = 3
       ole_object_method_error = 4
       others                  = 5.


아래는 메서드 내용


***PARAMETERS***

FILE_NAMETYPE STRING OPTIONAL

WIN_TILTYPE STRING OPTIONAL

CELLBORDERTYPE CHAR01  DEFAULT 'X'

FONTSIZETYPE I  DEFAULT 10

HEADER_COLORTYPE CHAR01  DEFAULT 'X'

DATA_TABTYPE STANDARD TABLE

FIELDNAMESTYPE LVC_T_FCAT OPTIONAL

HEADER_TABTYPE TY_T_ZSCAGT0060 OPTIONAL

NO_DATA

DOWNLOAD_PROBLEM

FILE_NOT_EXIST

OLE_OBJECT_METHOD_ERROR

*******************


    INCLUDE EXCEL__C.
 
     DATA: OBJ_EX_APP      TYPE OLE2_OBJECT,
           OBJ_EX_WBOOK    TYPE OLE2_OBJECT,
           OLE_BOOKS       TYPE OLE2_OBJECT,
           OBJ_CELLS       TYPE OLE2_OBJECT,
           OBJ_CELL1       TYPE OLE2_OBJECT,
           OBJ_CELL2       TYPE OLE2_OBJECT,
           OBJ_CELL_RANGE  TYPE OLE2_OBJECT,
           OBJ_FONT        TYPE OLE2_OBJECT,
           OBJ_BORDERS     TYPE OLE2_OBJECT,
           OBJ_CELL_COLOR1 TYPE OLE2_OBJECT,
           OBJ_CELL_COLOR2 TYPE OLE2_OBJECT,
           OBJ_CELLS_COLOR TYPE OLE2_OBJECT,
           OBJ_INTERIOR    TYPE OLE2_OBJECT.
 
     DATA: COL_SELECT      TYPE CHAR01,
           COL_SELECT_MASK TYPE CHAR255.
 
     DATA: LV_FIL_FLT           TYPE STRING,
           LV_FILENAME          TYPE STRING,
           LV_PATH              TYPE STRING,
           LV_FULLPATH          TYPE STRING,
           LV_DEFAULT_FILE_NAME TYPE STRING,
           LV_WIN_TIL           TYPE STRING,
           LV_FNAME             TYPE STRING,
           LV_FIELD             TYPE STRING,
           LV_CFIELD            TYPE STRING,
           LV_QFIELD            TYPE STRING,
           LV_END_ROW           TYPE I,
           LV_END_COL           TYPE I,
           LV_END_HEADER_ROW    TYPE I,
           LV_INDEX             TYPE I,
           LV_TABIX             TYPE SY-TABIX.
 
     DATA: LT_TAB    TYPE REF TO DATA,
           LR_CREF   TYPE REF TO DATA,
           LT_FCAT   TYPE LVC_T_FCAT,
           LS_FCAT   TYPE LVC_S_FCAT,
           LS_FNAME  LIKE LINE OF FIELDNAMES,
           LT_HEADER TYPE TY_T_ZSCAGT0060,
           LS_HEADER LIKE LINE OF LT_HEADER.
 
     DATA: FNAME  TYPE STRING,
           RESULT TYPE ABAP_BOOL.
 
     FIELD-SYMBOLS: <EXCEL_TAB>   TYPE STANDARD TABLE,
                    <EXCEL_STR>   TYPE ANY,
                    <FS_DATA>     TYPE ANY,
                    <FS_FIELD>    TYPE ANY,
                    <FS_RFIELD>   TYPE ANY,
                    <FS_DATA_TAB> TYPE ANY.
 
     "---음수부호 앞으로 이동-----------------------------------"
     DEFINE SIGN_FRONT.
       CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
         CHANGING
           VALUE = &1.
     END-OF-DEFINITION.
     "---음수부호 앞으로 이동-----------------------------------"
 
     CLEAR: LT_FCAT, LT_FCAT[], LS_FCAT, LT_TAB, LR_CREF.
     UNASSIGN: <EXCEL_TAB>, <EXCEL_STR>, <FS_DATA>, <FS_FIELD>.
 
     " 데이타가 없을경우 에러처리.
     IF DATA_TAB[] IS INITIAL.
       RAISE NO_DATA.
     ENDIF.
 
     " 파일 저장 Dialog의 Title.
     IF WIN_TIL IS INITIAL.
       LV_WIN_TIL = 'Save as'.
     ELSE.
       LV_WIN_TIL = WIN_TIL.
     ENDIF.
 
     " 파일 확장자 - EXCEL.
 *    LV_FIL_FLT = 'Excel File(*.XLS)|*.XLS|*.XLSX|'.
     LV_FIL_FLT = 'EXCEL FILES (*.XLS)|*.XLS|EXCEL FILES (*.XLSX)|*.XLSX|'.
 
     " 파일 저장 Dialog.
     CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
       EXPORTING
         WINDOW_TITLE      = LV_WIN_TIL
         FILE_FILTER       = LV_FIL_FLT
         DEFAULT_FILE_NAME = FILE_NAME
       CHANGING
         FILENAME          = LV_FILENAME
         PATH              = LV_PATH
         FULLPATH          = LV_FULLPATH.
 
     CHECK LV_FILENAME IS NOT INITIAL.
 
     SET LOCALE LANGUAGE SY-LANGU.
     TRANSLATE LV_FULLPATH TO UPPER CASE.
 
     " 엑셀의 출력 Table 설정.
     LOOP AT FIELDNAMES INTO LS_FNAME.
       CHECK LS_FNAME-NO_OUT IS INITIAL AND
             LS_FNAME-TECH   IS INITIAL.
 
       LS_FCAT-FIELDNAME = LS_FNAME-FIELDNAME.
       LS_FCAT-COL_POS   = LS_FNAME-COL_POS.
 
       " LongText Filed는 String으로 처리.
       " FieldCatalog에서 Type을 String으로 정의.
       IF LS_FNAME-DATATYPE = 'STRG'.
         LS_FCAT-DATATYPE = 'STRG'.
       ELSE.
         LS_FCAT-DATATYPE = 'CHAR'.
         LS_FCAT-INTLEN   = 100.
       ENDIF.
 
       APPEND LS_FCAT TO LT_FCAT. CLEAR LS_FCAT.
     ENDLOOP.
 
     " 실제 엑셀 출력할 FIELD-CATALOG로 동적 INTERNAL TABLE 만들기
     CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
       EXPORTING
         IT_FIELDCATALOG = LT_FCAT[]
       IMPORTING
         EP_TABLE        = LT_TAB.
 
     ASSIGN LT_TAB->* TO <EXCEL_TAB>.
 
     CREATE DATA LR_CREF LIKE LINE OF <EXCEL_TAB>.
     ASSIGN LR_CREF->* TO <EXCEL_STR>.
 
     " 동적 인터널 테이블에 데이타 추가.
     ASSIGN ('DATA_TAB') TO <FS_DATA_TAB>.
 
     LOOP AT DATA_TAB ASSIGNING <FS_DATA_TAB>.
       LV_TABIX = SY-TABIX.
 
       LOOP AT FIELDNAMES INTO LS_FNAME.
         CHECK LS_FNAME-NO_OUT IS INITIAL AND
               LS_FNAME-TECH   IS INITIAL.
 
         " 엑셀 헤더(title) 데이타를 넘기지 않은경우 fieldcatalog의 reptext 또는 coltxt 사용.
         IF HEADER_TAB IS INITIAL AND LV_TABIX = 1.
           CLEAR LS_HEADER.
           LS_HEADER-ROW  = 1.
           LS_HEADER-COL  = LS_FNAME-COL_POS.
 
           " coltext값 있으면 헤더로 적용, 없으면 reptext값을 헤더로 적용.
           IF LS_FNAME-COLTEXT IS NOT INITIAL.
             LS_HEADER-VALUE = LS_FNAME-COLTEXT.
           ELSE.
             LS_HEADER-VALUE = LS_FNAME-REPTEXT.
           ENDIF.
 
           APPEND LS_HEADER TO LT_HEADER.
         ENDIF.
 
         CONCATENATE '<FS_DATA_TAB>-' LS_FNAME-FIELDNAME INTO LV_FIELD.
         ASSIGN (LV_FIELD) TO <FS_FIELD>.
 
         ASSIGN COMPONENT LS_FNAME-FIELDNAME OF STRUCTURE <EXCEL_STR>
             TO <FS_DATA>.
 
         IF <FS_FIELD> IS INITIAL OR <FS_FIELD> = SPACE.
           <FS_DATA> = SPACE.
           CONTINUE.
         ENDIF.
 
         <FS_DATA> = <FS_FIELD>.
 
         "---CONVEXIT 적용---"
         IF LS_FNAME-CONVEXIT IS NOT INITIAL.
           CONCATENATE 'CONVERSION_EXIT_' LS_FNAME-CONVEXIT '_OUTPUT'
                  INTO LV_FNAME.
 
           CALL FUNCTION LV_FNAME
             EXPORTING
               INPUT     = <FS_FIELD>
             IMPORTING
               OUTPUT    = <FS_DATA>
             EXCEPTIONS
               NOT_FOUND = 1
               OTHERS    = 2.
         ENDIF.
 
         "금액필드인 경우
         IF LS_FNAME-CFIELDNAME IS NOT INITIAL.
           ASSIGN COMPONENT LS_FNAME-CFIELDNAME OF STRUCTURE <FS_DATA_TAB>
               TO <FS_RFIELD>.
 
           WRITE <FS_FIELD> TO <FS_DATA> CURRENCY <FS_RFIELD>
                                         LEFT-JUSTIFIED.
           "음수부호 앞으로 이동
           SIGN_FRONT <FS_DATA>.
         ENDIF.
 
         "수량필드인 경우
         IF LS_FNAME-QFIELDNAME IS NOT INITIAL.
           ASSIGN COMPONENT LS_FNAME-QFIELDNAME OF STRUCTURE <FS_DATA_TAB>
               TO <FS_RFIELD>.
 
           WRITE <FS_FIELD> TO <FS_DATA> UNIT <FS_RFIELD>
                                         LEFT-JUSTIFIED.
           "음수부호 앞으로 이동
           SIGN_FRONT <FS_DATA>.
         ENDIF.
 
         " Date, Time필드인 경우 Format.
         IF LS_FNAME-INTTYPE = 'D' OR LS_FNAME-INTTYPE = 'T'.
           WRITE <FS_FIELD> TO <FS_DATA> LEFT-JUSTIFIED NO-GAP.
         ENDIF.
       ENDLOOP.
 
       APPEND <EXCEL_STR> TO <EXCEL_TAB>. CLEAR <EXCEL_STR>.
     ENDLOOP.
 
     " 엑셀 출력 테이블에 헤더(Title) 데이타.------------------------
     IF HEADER_TAB IS NOT INITIAL.
       LT_HEADER = HEADER_TAB.  " 테이블 헤더값이 있는 경우 입력값 사용.
     ENDIF.
 
     SORT LT_HEADER BY ROW COL.
 
     LOOP AT LT_HEADER INTO LS_HEADER.
       LV_INDEX += 1.
       READ TABLE LT_FCAT INTO LS_FCAT INDEX LV_INDEX.
       ASSIGN COMPONENT LS_FCAT-FIELDNAME OF STRUCTURE <EXCEL_STR>
           TO <FS_DATA>.
 
       <FS_DATA> = LS_HEADER-VALUE.
 
 *      AT LAST.
 *        INSERT <excel_str> INTO <excel_tab> INDEX 1.
 *        CLEAR <excel_str>.
 *      ENDAT.
       AT END OF ROW.
         INSERT <EXCEL_STR> INTO <EXCEL_TAB> INDEX LS_HEADER-ROW.
         CLEAR : <EXCEL_STR>, LV_INDEX.
         LV_END_HEADER_ROW += 1.
       ENDAT.
     ENDLOOP.
     " 엑셀 출력 테이블에 헤더(Title) 데이타.------------------------
 
     DESCRIBE TABLE FIELDNAMES LINES LV_END_COL.
 
     IF LV_END_COL > 0.
 *       Es werden nur die Spalten an MS Excel #bertragen, f#r
 *       welche #berschriften vorhanden sind
       DO LV_END_COL TIMES.
         CONCATENATE COL_SELECT_MASK 'X' INTO COL_SELECT_MASK.
       ENDDO.
 
       COL_SELECT = 'X'.
     ENDIF.
 
     CALL METHOD TRANSLATE_DATA
       CHANGING
         DATATAB = <EXCEL_TAB>.
 
     CALL FUNCTION 'GUI_DOWNLOAD'
       EXPORTING
         FILENAME                = LV_FULLPATH
 *       filetype                = 'ASC'
         WRITE_FIELD_SEPARATOR   = 'X'
         TRUNC_TRAILING_BLANKS   = 'X'
         COL_SELECT              = COL_SELECT
         COL_SELECT_MASK         = COL_SELECT_MASK
         CODEPAGE                = '8500'
       TABLES
         DATA_TAB                = <EXCEL_TAB>
       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 <> 0.
       RAISE DOWNLOAD_PROBLEM.
     ENDIF.
 
 *    Statusinformation
     CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
       EXPORTING
         TEXT   = 'MS Excel configuration...'
       EXCEPTIONS
         OTHERS = 0.
 
     " 파일 유무 체크===========================================
     FNAME = LV_FULLPATH.
 
     CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_EXIST
       EXPORTING
         FILE   = FNAME
       RECEIVING
         RESULT = RESULT
       EXCEPTIONS
         OTHERS = 0.
 
     CALL METHOD CL_GUI_CFW=>FLUSH.
     IF RESULT = ABAP_FALSE.
       RAISE FILE_NOT_EXIST.
     ENDIF.
     " 파일 유무 체크===========================================
 
 *   Excel 파일 open하여 리스트 설정(헤더, 폰트, 테두리...)--------------------------
 *   start excel
     CREATE OBJECT OBJ_EX_APP 'Excel.Application'.
 
     IF SY-SUBRC NE 0.
       FREE OBJECT OBJ_EX_APP.
 
       CALL METHOD DELETE_FILE( LV_FULLPATH ).
       RAISE OLE_OBJECT_METHOD_ERROR.
     ENDIF.
 
     SET PROPERTY OF OBJ_EX_APP 'Visible' = 0. " 엑셀창 Visible False.
 
     CALL METHOD OF OBJ_EX_APP 'Workbooks' = OLE_BOOKS.
 
 *    open data file
     CALL METHOD OF OLE_BOOKS 'Open' = OBJ_EX_WBOOK
       EXPORTING #1 = LV_FULLPATH.
 
     IF SY-SUBRC NE 0.
       FREE OBJECT: OLE_BOOKS, OBJ_EX_WBOOK, OBJ_EX_APP.
 
       CALL METHOD DELETE_FILE( LV_FULLPATH ).
       RAISE OLE_OBJECT_METHOD_ERROR.
     ENDIF.
 
     " 엑셀파일의 헤더 설정.
     " 시작 Cell.
     CALL METHOD OF OBJ_EX_APP 'Cells' = OBJ_CELL1
       EXPORTING
       #1 = 1
       #2 = 1.
 
     " 종료 Cell.
     CALL METHOD OF OBJ_EX_APP 'Cells' = OBJ_CELL2
       EXPORTING
 *      #1 = 1
       #1 = LV_END_HEADER_ROW
       #2 = LV_END_COL.
 
     " Cell 범위설정.
     CALL METHOD OF OBJ_EX_APP 'Range' = OBJ_CELLS
       EXPORTING
       #1 = OBJ_CELL1
       #2 = OBJ_CELL2.
 
 *    CALL METHOD OF obj_cells 'Select'.        " Cell 선택.
 *    CALL METHOD OF obj_cells 'Merge' .        " Cell Merge.
 
     " 헤더(title)의 색상 설정.
     IF HEADER_COLOR = ABAP_TRUE.
       CALL METHOD OF OBJ_CELLS 'Interior' = OBJ_INTERIOR.
       SET PROPERTY OF OBJ_INTERIOR 'ColorIndex' = 43.
     ENDIF.
 
     " 헤더 칼럼 전체에 대한 Alignment 및 Bold 설정. -------------------
     " Alignment 설정.
     SET PROPERTY OF OBJ_CELLS 'HorizontalAlignment' = XLCENTER.
     SET PROPERTY OF OBJ_CELLS 'VerticalAlignment'   = XLCENTER.
 
     " Bold.
     GET PROPERTY OF OBJ_CELLS 'Font' = OBJ_FONT.
     SET PROPERTY OF OBJ_FONT  'Bold' = 1.
     " 헤더 칼럼 정렬 및 폰트 Bold 설정. ------------------------------
 
     " 엑셀파일의 폰트 및 테두리 설정------------------------------------
     FREE OBJECT : OBJ_CELL1, OBJ_CELL2, OBJ_CELLS.
 
     LV_END_ROW = LINES( <EXCEL_TAB> ).
 
     " 시작 Cell.
     CALL METHOD OF OBJ_EX_APP 'Cells' = OBJ_CELL1
       EXPORTING
       #1 = 1
       #2 = 1.
 
     " 종료 Cell.
     CALL METHOD OF OBJ_EX_APP 'Cells' = OBJ_CELL2
       EXPORTING
       #1 = LV_END_ROW
       #2 = LV_END_COL.
 
     " Cell 범위설정.
     CALL METHOD OF OBJ_EX_APP 'Range' = OBJ_CELLS
       EXPORTING
       #1 = OBJ_CELL1
       #2 = OBJ_CELL2.
 
     " Font Size.
     GET PROPERTY OF OBJ_CELLS 'Font' = OBJ_FONT.
     SET PROPERTY OF OBJ_FONT 'Size' = FONTSIZE.
 
     " 테두리 설정.
     IF CELLBORDER = ABAP_TRUE.
       GET PROPERTY OF OBJ_CELLS 'Borders' = OBJ_BORDERS.
       SET PROPERTY OF OBJ_BORDERS 'LineStyle' = '1'.
       SET PROPERTY OF OBJ_BORDERS 'Weight' = 2.
     ENDIF.
     " 엑셀파일의 폰트 및 테두리 설정------------------------------------
 
     SET PROPERTY OF OBJ_EX_APP 'DisplayAlerts' = 0. " Alert 창 False.
 
     " 수정된 엑셀파일 저장.
     CALL METHOD OF OBJ_EX_WBOOK 'SaveAs'
       EXPORTING
         #1 = LV_FULLPATH            "filename
         #2 = 1.                     "fileFormat
 
     CALL FUNCTION 'FLUSH'.
 
     SET PROPERTY OF OBJ_EX_APP 'DisplayAlerts' = 1. " Alert 창 True.
     SET PROPERTY OF OBJ_EX_APP 'Visible' = 1.       " 엑셀창 Visible True.
 
 *    release OLE objects of the data table
     FREE OBJECT: OBJ_EX_WBOOK, OBJ_EX_APP, OBJ_CELL1, OBJ_CELL2, OBJ_CELLS,
                  OBJ_BORDERS, OBJ_FONT, OBJ_CELL_COLOR1, OBJ_CELL_COLOR2,
                  OBJ_CELLS_COLOR, OBJ_INTERIOR.
 
 *   force flush
     CALL FUNCTION 'FLUSH'
       EXCEPTIONS
         OTHERS = 0.

작가의 이전글 SAPGUI에서 HANA SQL 사용하기
브런치는 최신 브라우저에 최적화 되어있습니다. IE chrome safari