그냥 스프레드시트 받으면 되는데 왜 굳이 버튼을 만들어 달라는걸까
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.