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.
'ABAP > 개발Tip' 카테고리의 다른 글
[개발Tip] ALV의 필드 정렬과 셀 정렬을 다르게 하고 싶을때 (0) | 2024.10.24 |
---|---|
[개발Tip] ASCII 기준 문자열 자르기 (0) | 2024.10.24 |
[개발Tip] WebGUI에서 Excel 템플릿의 셀 값 변경 (CL_FDT_XL_SPREADSHEET/ABAP2XLSX) (1) | 2023.12.07 |
[개발Tip] SAP DMS 사용 예시 (문서생성,첨부,다운,삭제,버전업) (0) | 2023.11.28 |
[개발Tip] ALV 레이아웃 필드(LVC_S_LAYO) 설명 (0) | 2023.11.17 |
댓글