ABAP 엑셀 업로드 및 다운로드 프로그램
Updated:
1. 실행화면
코드를 작성하여 실행한 결과 다음과 같은 화면 및 기능을 구현하였다.
Selection screen
1) 엑셀 양식 다운로드
엑셀양식 다운로드는 두 가지 버전으로 구현했다. SMW0를 사용하여 SAP server에 미리 저장해둔 양식을 다운받는 방법과 OLE2를 사용하여 엑셀을 열고 코드 작성을 통해 양식이 입력되게끔 하는 방식이다.
Upload 조건이라고 라디오버튼이 되어 있지만, 다운로드 시에도 이력 혹 자격증 양식이 다르게 있으니 해당 버튼을 클릭하고 다운로드 버튼을 누르도록 한다. 다운로드 양식에 대한 선택 버튼이 추가로 필요해 보인다.
2) 엑셀 데이터 업로드 및 저장
업로드 조건을 선택하고 파일을 불러온다.
해당 파일의 정보를 읽어들여 데이터 구분에 따라 저장 가능한지, 불가능하다면 왜 안되는지에 대한 오류 내역이 비고란에 뜬다.
하나의 row 데이터를 선택하고 저장을 하면 하나의 건에 대해서 저장이 되고, 정상적으로 저장되었다면 파란 신호등 불로 나타난다.
전체 데이터에 대해 저장을 하면 정상적으로 저장된 데이터는 파란 신호등, 그 외에는 빨간 신호등과 오류 내역으로 그 결과를 확인할 수 있다.
2. 코드
REPORT PROGRAM
*&---------------------------------------------------------------------*
*& Report : ZEDU19_0020
*& DESCRIPTION : Excel Upload Program
*& MODULE : EDU
*& DEVELOPER : ABAP019
*& CREATED DATE: 2020.12.17
*&---------------------------------------------------------------------*
*& MODIFIED HISTORY :
*& DEVELOPER DATE DESCRIPTION
*----------------------------------------------------------------------*
* ABAP019 2020.12.17 신규 개발
*&---------------------------------------------------------------------*
REPORT ZEDU19_0020 MESSAGE-ID ZM19.
INCLUDE ZEDU19_0020TOP.
INCLUDE ZEDU19_0020SEL.
INCLUDE ZEDU19_0020F01.
INCLUDE ZEDU19_0020I01.
INCLUDE ZEDU19_0020O01.
*&=====================================================================*
*& INITIALIZATION
*&=====================================================================*
INITIALIZATION.
PERFORM SET_FUNCTION_KEY.
*&=====================================================================*
*& AT SELECTION-SCREEN
*&=====================================================================*
AT SELECTION-SCREEN.
PERFORM ACT_FUNCTION_KEY.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
PERFORM GET_FILE_PATH.
*&=====================================================================*
*& START-OF-SELECTION
*&=====================================================================*
START-OF-SELECTION.
PERFORM CHECK_BEFORE_PROCESS.
* 파일 업로드 진행
PERFORM UPLOAD_FROM_EXCEL.
PERFORM CONVERT_TO_DISPLAY_FORMAT.
PERFORM GET_DATA.
*&=====================================================================*
*& END-OF-SELECTION
*&=====================================================================*
END-OF-SELECTION.
CALL SCREEN 100.
TOP
*&---------------------------------------------------------------------*
*& Include ZEDU19_0020TOP
*&---------------------------------------------------------------------*
* OLE
TYPE-POOLS: SOL, OLE2.
* FUNCTION CODE
TABLES: SSCRFIELDS.
DATA: G_FUNCTION_KEY TYPE SMP_DYNTXT.
* EXCEL DOWN: DIRECTORY, SMW0
DATA: GV_DIRECTORY TYPE STRING,
GV_INITIAL_DIR TYPE STRING,
GV_FILE LIKE RLGRAP-FILENAME,
OBJFILE TYPE REF TO CL_GUI_FRONTEND_SERVICES.
* EXCEL DOWN: OLE
DATA: LS_KEY LIKE WWWDATATAB.
DATA: GO_APPLICATION TYPE OLE2_OBJECT,
GO_BOOKS TYPE OLE2_OBJECT,
GO_WBOOK TYPE OLE2_OBJECT,
GO_BOOK TYPE OLE2_OBJECT,
GO_SHEETS TYPE OLE2_OBJECT,
GO_SHEET TYPE OLE2_OBJECT,
GO_CELLS TYPE OLE2_OBJECT,
GO_CELL TYPE OLE2_OBJECT,
GO_RANGE TYPE OLE2_OBJECT,
GO_FONT TYPE OLE2_OBJECT,
GO_ROW TYPE OLE2_OBJECT,
GV_PATH TYPE STRING,
GV_NUM TYPE I.
* 이력등록
DATA: BEGIN OF GS_DES,
ZSTATUS TYPE ICON-ID, //신호등
ZEDU TYPE ZEDUT_1910-ZEDU, //사원번호
ZNAME1 TYPE ZEDUT_1910-ZNAME1, //사원명
ZPROJ TYPE ZEDUT_1920-ZPROJ, //프로젝트 ID
ZNAME2 TYPE ZEDUT_1920-ZNAME2, //프로젝트명
ZMODULE TYPE ZEDUT_1950-ZMODULE, //MODULE
ZPERIOD_S TYPE ZEDUT_1950-ZPERIOD_S, //투입월
ZPERIOD_E TYPE ZEDUT_1950-ZPERIOD_E, //종료월
ZRESULT TYPE CHAR200, //비고
END OF GS_DES.
DATA: BEGIN OF GS_D_EXCEL,
ZEDU TYPE ZEDUT_1910-ZEDU,
ZPROJ TYPE ZEDUT_1920-ZPROJ,
ZMODULE TYPE ZEDUT_1950-ZMODULE,
ZPERIOD_S TYPE ZEDUT_1950-ZPERIOD_S,
ZPERIOD_E TYPE ZEDUT_1950-ZPERIOD_E,
ZRESULT TYPE CHAR200,
END OF GS_D_EXCEL.
DATA: GT_DES LIKE TABLE OF GS_DES,
GT_D_EXCEL LIKE TABLE OF GS_D_EXCEL.
* 자격증등록
DATA: BEGIN OF GS_CER,
ZSTATUS TYPE ICON-ID, //신호등
ZEDU TYPE ZEDUT_1970-ZEDU, //사원번호
ZNAME1 TYPE ZEDUT_1910-ZNAME1, //사원명
ZCERTI TYPE ZEDUT_1940-ZCERTI, //자격증 ID
ZNAME3 TYPE ZEDUT_1940-ZNAME3, //자격증명
ZRESULT TYPE CHAR200, //비고
END OF GS_CER.
DATA: BEGIN OF GS_C_EXCEL,
ZEDU TYPE ZEDUT_1970-ZEDU,
ZCERTI TYPE ZEDUT_1970-ZCERTI,
END OF GS_C_EXCEL.
DATA: GT_CER LIKE TABLE OF GS_CER,
GT_C_EXCEL LIKE TABLE OF GS_C_EXCEL.
* 엑셀 업로드
DATA: LT_INTERN TYPE TABLE OF ALSMEX_TABLINE,
LS_INTERN TYPE ALSMEX_TABLINE,
LV_TYPE,
LV_NROW TYPE I.
FIELD-SYMBOLS <FS> TYPE ANY.
* DB 데이터
DATA: GT_EMPLOYEE TYPE TABLE OF ZEDUT_1910, //사원 정보
GT_CERTI TYPE TABLE OF ZEDUT_1940, //자격증 정보
GT_DOMAIN TYPE TABLE OF DD07T. //DOMAIN VALUE
DATA: BEGIN OF GS_PROJ, //프로젝트 정보
ZPROJ TYPE ZEDUT_1920-ZPROJ,
ZNAME2 TYPE ZEDUT_1920-ZNAME2,
ZMODULE TYPE ZEDUT_1950-ZMODULE,
END OF GS_PROJ.
DATA: GT_PROJ LIKE TABLE OF GS_PROJ,
GS_EMPLOYEE LIKE LINE OF GT_EMPLOYEE,
GS_CERTI LIKE LINE OF GT_CERTI,
GS_DOMAIN LIKE LINE OF GT_DOMAIN.
DATA: GT_ZEDUT1950 TYPE TABLE OF ZEDUT_1950,
GS_ZEDUT1950 TYPE ZEDUT_1950,
GT_ZEDUT1970 TYPE TABLE OF ZEDUT_1970,
GS_ZEDUT1970 TYPE ZEDUT_1970.
* 데이터 저장
DATA: LT_ROW TYPE LVC_T_ROID,
LS_ROW TYPE LVC_S_ROID.
* 기타
DATA: OK_CODE TYPE SY-UCOMM,
GV_TITLE TYPE SY-TITLE.
* ALV 관련
DATA: GO_DOCKING TYPE REF TO CL_GUI_DOCKING_CONTAINER,
GO_GRID TYPE REF TO CL_GUI_ALV_GRID.
DATA : GT_FCAT TYPE LVC_T_FCAT,
GS_FCAT TYPE LVC_S_FCAT,
GS_LAYOUT TYPE LVC_S_LAYO.
SELECTION
*&---------------------------------------------------------------------*
*& Include ZEDU19_0020SEL
*&---------------------------------------------------------------------*
* UPLOAD 조건
SELECTION-SCREEN BEGIN OF BLOCK BL01 WITH FRAME TITLE TEXT-001.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: P_DES RADIOBUTTON GROUP RB01 USER-COMMAND P1.
SELECTION-SCREEN COMMENT 3(7) TEXT-T01 FOR FIELD P_DES.
PARAMETERS: P_CER RADIOBUTTON GROUP RB01.
SELECTION-SCREEN COMMENT 15(7) TEXT-T02 FOR FIELD P_CER.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK BL01.
SELECTION-SCREEN SKIP.
* UPLOAD 파일
SELECTION-SCREEN BEGIN OF BLOCK BL02 WITH FRAME TITLE TEXT-002.
PARAMETERS: P_FILE TYPE RLGRAP-FILENAME DEFAULT 'C:\' OBLIGATORY.
SELECTION-SCREEN END OF BLOCK BL02.
* FUNCTION KEY
SELECTION-SCREEN: FUNCTION KEY 1,
FUNCTION KEY 2.
PAI
*&---------------------------------------------------------------------*
*& Include ZEDU19_0020I01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE USER_COMMAND_0100 INPUT.
CASE OK_CODE.
WHEN 'BACK' OR 'CANC'.
LEAVE TO SCREEN 0.
WHEN 'EXIT'.
LEAVE PROGRAM.
ENDCASE.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module SAVE_DATA INPUT
*&---------------------------------------------------------------------*
MODULE SAVE_DATA INPUT.
CASE OK_CODE.
WHEN 'SAVE'.
IF P_DES = 'X'.
PERFORM SAVE_DES_DATA.
ELSEIF P_CER = 'X'.
PERFORM SAVE_CER_DATA.
ENDIF.
ENDCASE.
ENDMODULE.
PBO
*&---------------------------------------------------------------------*
*& Include ZEDU19_0020O01
*&---------------------------------------------------------------------*
MODULE STATUS_0100 OUTPUT.
SET PF-STATUS '100'.
SET TITLEBAR '100' WITH GV_TITLE.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module SET_ALV_0100 OUTPUT
*&---------------------------------------------------------------------*
MODULE SET_ALV_0100 OUTPUT.
IF GO_DOCKING IS INITIAL.
* OBJECT*INSTANCE 생성
PERFORM CREATE_OBJECT_INSTANCE.
* FIELD CATALOG
PERFORM SET_FILDCAT.
* LAYOUT
PERFORM SET_LAYOUT.
* DISPLAY ALV
PERFORM DISPLAY_ALV_0100.
ELSE.
PERFORM REFRESH_DATA.
ENDIF.
ENDMODULE.
PERFORM
*&---------------------------------------------------------------------*
*& Include ZEDU19_0020F01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form CREATE_OBJECT_INSTANCE
*&---------------------------------------------------------------------*
FORM CREATE_OBJECT_INSTANCE .
CREATE OBJECT GO_DOCKING
EXPORTING
SIDE = CL_GUI_DOCKING_CONTAINER=>DOCK_AT_LEFT
EXTENSION = 3000.
CREATE OBJECT GO_GRID
EXPORTING
I_PARENT = GO_DOCKING.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_FILDCAT
*&---------------------------------------------------------------------*
FORM SET_FILDCAT .
DEFINE _FCAT.
CLEAR: GS_FCAT.
GS_FCAT-FIELDNAME = &1.
GS_FCAT-COLTEXT = &2.
GS_FCAT-KEY = &3.
APPEND GS_FCAT TO GT_FCAT.
END-OF-DEFINITION.
CLEAR: GT_FCAT.
IF P_DES = 'X'.
_FCAT: 'ZSTATUS' '상태' 'X',
'ZEDU' '사원번호' 'X',
'ZNAME1' '사원명' '',
'ZPROJ' '프로젝트 ID' '',
'ZNAME2' '프로젝트명' '',
'ZMODULE' '모듈' '',
'ZPERIOD_S' '투입날짜' '',
'ZPERIOD_E' '종료날짜' '',
'ZRESULT' '비고' ''.
ELSEIF P_CER = 'X'.
_FCAT: 'ZSTATUS' '상태' 'X',
'ZEDU' '사원번호' 'X',
'ZNAME1' '사원명' '',
'ZCERTI' '자격증 ID' '',
'ZNAME3' '자격증명' '',
'ZRESULT' '비고' ''.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_LAYOUT
*&---------------------------------------------------------------------*
FORM SET_LAYOUT .
GS_LAYOUT-ZEBRA = 'X'.
GS_LAYOUT-CWIDTH_OPT = 'A'.
GS_LAYOUT-SEL_MODE = 'D'. //드래그 가능
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY_ALV_0100
*&---------------------------------------------------------------------*
FORM DISPLAY_ALV_0100 .
IF P_DES = 'X'.
PERFORM DISPLAY_ALV USING GT_DES.
ELSEIF P_CER = 'X'.
PERFORM DISPLAY_ALV USING GT_CER.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form REFRESH_DATA
*&---------------------------------------------------------------------*
FORM REFRESH_DATA .
CALL METHOD GO_GRID->REFRESH_TABLE_DISPLAY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_FILE_PATH
*&---------------------------------------------------------------------*
FORM GET_FILE_PATH .
* 선택된 파일의 주소를 P_FILE 입력칸에 할당
* METHOD 사용
DATA : LT_FILE TYPE FILETABLE,
LS_FILE TYPE FILE_TABLE,
LV_RC TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
CHANGING
FILE_TABLE = LT_FILE
RC = LV_RC.
READ TABLE LT_FILE INTO LS_FILE INDEX 1.
IF SY-SUBRC = 0.
P_FILE = LS_FILE.
ENDIF.
* FUNCTION 사용시: CALL FUNCTION 'F4_FILENAME'
ENDFORM.
*&---------------------------------------------------------------------*
*& Form UPLOAD_FROM_EXCEL
*&---------------------------------------------------------------------*
FORM UPLOAD_FROM_EXCEL.
IF P_DES = 'X'.
DESCRIBE FIELD GS_DES TYPE LV_TYPE COMPONENTS LV_NROW.
ELSEIF P_CER = 'X'.
DESCRIBE FIELD GS_CER TYPE LV_TYPE COMPONENTS LV_NROW.
ENDIF.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE //파일경로
I_BEGIN_COL = 1 //인식 시작 열 번호
I_BEGIN_ROW = 2 //인식 시작 행 번호
I_END_COL = LV_NROW //필드 수
I_END_ROW = 6500 //최대 행 수
TABLES
INTERN = LT_INTERN
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE I002. //데이터를 불러오는 중 오류가 발생했습니다.
LEAVE LIST-PROCESSING.
ENDIF.
IF LT_INTERN IS INITIAL.
MESSAGE E005. //데이터가 존재하지 않습니다.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CONVERT_TO_DISPLAY_FORMAT
*&---------------------------------------------------------------------*
FORM CONVERT_TO_DISPLAY_FORMAT .
IF P_DES = 'X'.
PERFORM DATA_DES_UPLOAD.
ELSEIF P_CER = 'X'.
PERFORM DATA_CER_UPLOAD.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_BEFORE_PROCESS
*&---------------------------------------------------------------------*
FORM CHECK_BEFORE_PROCESS .
* 파일 주소 확인
IF P_FILE EQ SPACE.
MESSAGE I003.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DATA_DES_UPLOAD
*&---------------------------------------------------------------------*
FORM DATA_DES_UPLOAD .
* 엑셀 데이터 넣기
LOOP AT LT_INTERN INTO LS_INTERN.
ASSIGN COMPONENT LS_INTERN-COL OF STRUCTURE GS_D_EXCEL TO <FS>.
<FS> = LS_INTERN-VALUE.
AT END OF ROW.
MOVE-CORRESPONDING GS_D_EXCEL TO GS_DES.
APPEND GS_DES TO GT_DES.
CLEAR: GS_D_EXCEL, GS_DES.
ENDAT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DATA_CER_UPLOAD
*&---------------------------------------------------------------------*
FORM DATA_CER_UPLOAD .
* 엑셀 데이터 넣기
LOOP AT LT_INTERN INTO LS_INTERN.
ASSIGN COMPONENT LS_INTERN-COL OF STRUCTURE GS_C_EXCEL TO <FS>.
<FS> = LS_INTERN-VALUE.
AT END OF ROW.
MOVE-CORRESPONDING GS_C_EXCEL TO GS_CER.
APPEND GS_CER TO GT_CER.
CLEAR: GS_C_EXCEL, GS_CER.
ENDAT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
FORM GET_DATA .
* 필요 데이터 취합
PERFORM GET_NEEDED_DATA.
* 업로드 조건에 따라 ALV 출력 데이터 취합
IF P_DES = 'X'.
PERFORM GET_DES_DATA.
ELSEIF P_CER = 'X'.
PERFORM GET_CER_DATA.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_DES_DATA
*&---------------------------------------------------------------------*
FORM GET_DES_DATA .
* 데이터 체크 및 연관 데이터 가져오기
LOOP AT GT_DES ASSIGNING FIELD-SYMBOL(<FS_DES>).
* 사원번호 존재 여부 확인 & 사원이름 채우기
PERFORM CHECK_EMPLOYEE_DES CHANGING <FS_DES>.
* 프로젝트 ID 존재 여부 확인 & 프로젝트명 채우기
PERFORM CHECK_PROJ CHANGING <FS_DES>.
* DOMAIN VALUE 확인
PERFORM CHECK_DOMAIN CHANGING <FS_DES>.
* 테이블 중복 KEY 확인
PERFORM CHECK_DES_EXIST CHANGING <FS_DES>.
* 저장가능 데이터 표시
PERFORM CHECK_AVAILABLE CHANGING <FS_DES>-ZSTATUS
<FS_DES>-ZRESULT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_CER_DATA
*&---------------------------------------------------------------------*
FORM GET_CER_DATA .
* 데이터 체크 및 연관 데이터 가져오기
LOOP AT GT_CER ASSIGNING FIELD-SYMBOL(<FS_CER>).
* 사원번호 존재 여부 확인 & 사원이름 채우기
PERFORM CHECK_EMPLOYEE_CER USING <FS_CER>.
* 자격증 ID 존재 여부 확인 & 자격증명 채우기
PERFORM CHECK_CERTI CHANGING <FS_CER>.
* 테이블 중복 KEY 확인
PERFORM CHECK_CER_EXIST CHANGING <FS_CER>.
* 저장가능 데이터 표시
PERFORM CHECK_AVAILABLE CHANGING <FS_CER>-ZSTATUS
<FS_CER>-ZRESULT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_NEEDED_DATA
*&---------------------------------------------------------------------*
FORM GET_NEEDED_DATA .
* 입력 데이터 점검을 위해 사용할 DB 데이터
SELECT *
FROM ZEDUT_1910
INTO CORRESPONDING FIELDS OF TABLE GT_EMPLOYEE.
SORT GT_EMPLOYEE BY ZEDU.
SELECT *
FROM ZEDUT_1940
INTO CORRESPONDING FIELDS OF TABLE GT_CERTI.
SORT GT_CERTI BY ZNAME3.
SELECT A~ZPROJ A~ZNAME2 B~ZMODULE
INTO CORRESPONDING FIELDS OF TABLE GT_PROJ
FROM ZEDUT_1920 AS A INNER JOIN ZEDUT_1950 AS B
ON A~ZPROJ EQ B~ZPROJ.
SORT GT_PROJ BY ZPROJ.
SELECT *
INTO CORRESPONDING FIELDS OF TABLE GT_ZEDUT1950
FROM ZEDUT_1950.
SORT GT_ZEDUT1950 BY ZEDU.
SELECT *
INTO CORRESPONDING FIELDS OF TABLE GT_ZEDUT1970
FROM ZEDUT_1970.
SORT GT_ZEDUT1970 BY ZEDU.
SELECT *
INTO CORRESPONDING FIELDS OF TABLE GT_DOMAIN
FROM DD07T
WHERE DDLANGUAGE = SY-LANGU
AND DOMNAME = 'ZD_MODULE19'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_EMPLOYEE
*&---------------------------------------------------------------------*
FORM CHECK_EMPLOYEE_DES CHANGING P_DES LIKE GS_DES.
* 사원번호 입력 확인
IF P_DES-ZEDU IS INITIAL.
PERFORM ERROR_MSG USING TEXT-A01
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ELSE.
* 사원번호 DB 존재 확인
SORT GT_EMPLOYEE BY ZEDU.
READ TABLE GT_EMPLOYEE INTO GS_EMPLOYEE
WITH KEY ZEDU = P_DES-ZEDU
BINARY SEARCH.
IF SY-SUBRC <> 0.
PERFORM ERROR_MSG USING TEXT-A02
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ELSEIF SY-SUBRC = 0.
P_DES-ZNAME1 = GS_EMPLOYEE-ZNAME1.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ERROR_MSG
*&---------------------------------------------------------------------*
FORM ERROR_MSG USING P_TEXT
CHANGING P_RESULT P_STATUS.
* 오류 내역 표시
IF P_RESULT IS INITIAL.
P_RESULT = P_TEXT.
ELSE.
CONCATENATE P_RESULT P_TEXT INTO P_RESULT SEPARATED BY ', '.
ENDIF.
P_STATUS = ICON_LED_RED.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_PROJ
*&---------------------------------------------------------------------*
FORM CHECK_PROJ CHANGING P_DES LIKE GS_DES.
* 프로젝트 ID 입력 확인
IF P_DES-ZPROJ IS INITIAL.
PERFORM ERROR_MSG USING TEXT-A03
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ELSE.
* 프로젝트 ID DB 존재 확인
READ TABLE GT_PROJ INTO GS_PROJ
WITH KEY ZPROJ = P_DES-ZPROJ
BINARY SEARCH.
IF SY-SUBRC <> 0.
PERFORM ERROR_MSG USING TEXT-A04
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ELSEIF SY-SUBRC = 0.
P_DES-ZNAME2 = GS_PROJ-ZNAME2.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_AVAILABLE
*&---------------------------------------------------------------------*
FORM CHECK_AVAILABLE CHANGING P_STATUS
P_RESULT.
* 오류 없음 확인
IF P_STATUS IS INITIAL.
P_STATUS = ICON_LED_YELLOW.
P_RESULT = TEXT-A05.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_EMPLOYEE_CER
*&---------------------------------------------------------------------*
FORM CHECK_EMPLOYEE_CER USING P_CER LIKE GS_CER.
* 사원번호 입력 확인
IF P_CER-ZEDU IS INITIAL.
PERFORM ERROR_MSG USING TEXT-A01
CHANGING P_CER-ZRESULT
P_CER-ZSTATUS.
ELSE.
* 사원번호 DB 존재 확인
READ TABLE GT_EMPLOYEE INTO GS_EMPLOYEE
WITH KEY ZEDU = P_CER-ZEDU
BINARY SEARCH.
IF SY-SUBRC <> 0.
PERFORM ERROR_MSG USING TEXT-A02
CHANGING P_CER-ZRESULT
P_CER-ZSTATUS.
ELSEIF SY-SUBRC = 0.
P_CER-ZNAME1 = GS_EMPLOYEE-ZNAME1.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY_ALV_0100_DES
*&---------------------------------------------------------------------*
FORM DISPLAY_ALV USING P_GT.
CALL METHOD GO_GRID->SET_TABLE_FOR_FIRST_DISPLAY
EXPORTING
IS_LAYOUT = GS_LAYOUT
CHANGING
IT_OUTTAB = P_GT
IT_FIELDCATALOG = GT_FCAT
.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_CERTI
*&---------------------------------------------------------------------*
FORM CHECK_CERTI CHANGING P_CER LIKE GS_CER.
* 자격증 ID 입력 확인
IF P_CER-ZCERTI IS INITIAL.
PERFORM ERROR_MSG USING TEXT-A06
CHANGING P_CER-ZRESULT
P_CER-ZSTATUS.
ELSE.
* 자격증 ID DB 존재 확인
SORT GT_CERTI BY ZCERTI.
READ TABLE GT_CERTI INTO GS_CERTI
WITH KEY ZCERTI = P_CER-ZCERTI
BINARY SEARCH.
IF SY-SUBRC <> 0.
PERFORM ERROR_MSG USING TEXT-A07
CHANGING P_CER-ZRESULT
P_CER-ZSTATUS.
ELSEIF SY-SUBRC = 0.
P_CER-ZNAME3 = GS_CERTI-ZNAME3.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SAVE_DES_DATA
*&---------------------------------------------------------------------*
FORM SAVE_DES_DATA .
CHECK GT_DES IS NOT INITIAL.
* 선택 라인 정보
CLEAR: LT_ROW, LS_ROW, GS_ZEDUT1950.
CALL METHOD GO_GRID->GET_SELECTED_ROWS
IMPORTING
ET_ROW_NO = LT_ROW.
* 선택 라인 정보만 저장
IF LT_ROW IS NOT INITIAL.
READ TABLE LT_ROW INTO LS_ROW INDEX 1.
READ TABLE GT_DES ASSIGNING FIELD-SYMBOL(<FS_DES_L>) INDEX LS_ROW-ROW_ID.
PERFORM SAVE_DES_FINAL CHANGING <FS_DES_L>.
* 전체 라인 정보 저장
ELSE.
LOOP AT GT_DES ASSIGNING FIELD-SYMBOL(<FS_DES_T>).
PERFORM SAVE_DES_FINAL CHANGING <FS_DES_T>.
ENDLOOP.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SAVE_CER_DATA
*&---------------------------------------------------------------------*
FORM SAVE_CER_DATA .
CHECK GT_CER IS NOT INITIAL.
* 선택 라인 정보
CLEAR: LT_ROW, LS_ROW, GS_ZEDUT1970.
CALL METHOD GO_GRID->GET_SELECTED_ROWS
IMPORTING
ET_ROW_NO = LT_ROW.
* 선택 라인 정보만 저장
IF LT_ROW IS NOT INITIAL.
READ TABLE LT_ROW INTO LS_ROW INDEX 1.
READ TABLE GT_CER ASSIGNING FIELD-SYMBOL(<FS_CER_L>) INDEX LS_ROW-ROW_ID.
PERFORM SAVE_CER_FINAL CHANGING <FS_CER_L>.
* 전체 라인 정보 저장
ELSE.
LOOP AT GT_CER ASSIGNING FIELD-SYMBOL(<FS_CER_T>).
PERFORM SAVE_CER_FINAL CHANGING <FS_CER_T>.
ENDLOOP.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_DOMAIN
*&---------------------------------------------------------------------*
FORM CHECK_DOMAIN CHANGING P_DES LIKE GS_DES.
* 모듈 입력 확인
IF P_DES-ZMODULE IS INITIAL.
PERFORM ERROR_MSG USING TEXT-A08
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ELSE.
* DOMAIN VALUE 존재 확인
SORT GT_DOMAIN BY DOMVALUE_L.
READ TABLE GT_DOMAIN TRANSPORTING NO FIELDS
WITH KEY DOMVALUE_L = P_DES-ZMODULE
BINARY SEARCH.
IF SY-SUBRC <> 0.
PERFORM ERROR_MSG USING TEXT-A09
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DATA_SAVE_SUCCEED
*&---------------------------------------------------------------------*
FORM DATA_SAVE_SUCCEED CHANGING P_ZSTATUS
P_ZRESULT.
P_ZSTATUS = ICON_LED_GREEN.
P_ZRESULT = '저장 성공'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_EXIST
*&---------------------------------------------------------------------*
FORM CHECK_DES_EXIST CHANGING P_DES LIKE GS_DES.
* DB에 이미 존재하는 데이터인지 확인
READ TABLE GT_ZEDUT1950 WITH KEY ZEDU = P_DES-ZEDU
ZPROJ = P_DES-ZPROJ
ZMODULE = P_DES-ZMODULE
TRANSPORTING NO FIELDS
BINARY SEARCH.
IF SY-SUBRC = 0.
PERFORM ERROR_MSG USING TEXT-A10
CHANGING P_DES-ZRESULT
P_DES-ZSTATUS.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_CER_EXIST
*&---------------------------------------------------------------------*
FORM CHECK_CER_EXIST CHANGING P_CER LIKE GS_CER.
* DB에 이미 존재하는 데이터인지 확인
READ TABLE GT_ZEDUT1970 WITH KEY ZEDU = P_CER-ZEDU
ZCERTI = P_CER-ZCERTI
TRANSPORTING NO FIELDS
BINARY SEARCH.
IF SY-SUBRC = 0.
PERFORM ERROR_MSG USING TEXT-A10
CHANGING P_CER-ZRESULT
P_CER-ZSTATUS.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SAVE_DES_FINAL
*&---------------------------------------------------------------------*
FORM SAVE_DES_FINAL CHANGING GS_DES LIKE GS_DES.
IF GS_DES-ZSTATUS = ICON_LED_YELLOW.
MOVE-CORRESPONDING GS_DES TO GS_ZEDUT1950.
GS_ZEDUT1950-MANDT = SY-MANDT.
GS_ZEDUT1950-AENAM = SY-UNAME.
GS_ZEDUT1950-AEDAT = SY-DATUM.
GS_ZEDUT1950-AEZET = SY-UZEIT.
GS_ZEDUT1950-ERNAM = SY-UNAME.
GS_ZEDUT1950-ERDAT = SY-DATUM.
GS_ZEDUT1950-ERZET = SY-UZEIT.
INSERT INTO ZEDUT_1950 VALUES GS_ZEDUT1950.
IF SY-SUBRC = 0.
PERFORM DATA_SAVE_SUCCEED CHANGING GS_DES-ZSTATUS
GS_DES-ZRESULT.
COMMIT WORK AND WAIT.
MESSAGE S006.
ELSEIF SY-SUBRC <> 0.
MESSAGE E007.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SAVE_CER_FINAL
*&---------------------------------------------------------------------*
FORM SAVE_CER_FINAL CHANGING GS_CER LIKE GS_CER.
IF GS_CER-ZSTATUS = ICON_LED_YELLOW.
MOVE-CORRESPONDING GS_CER TO GS_ZEDUT1970.
GS_ZEDUT1970-MANDT = SY-MANDT.
GS_ZEDUT1970-AENAM = SY-UNAME.
GS_ZEDUT1970-AEDAT = SY-DATUM.
GS_ZEDUT1970-AEZET = SY-UZEIT.
GS_ZEDUT1970-ERNAM = SY-UNAME.
GS_ZEDUT1970-ERDAT = SY-DATUM.
GS_ZEDUT1970-ERZET = SY-UZEIT.
INSERT INTO ZEDUT_1970 VALUES GS_ZEDUT1970.
IF SY-SUBRC = 0.
PERFORM DATA_SAVE_SUCCEED CHANGING GS_CER-ZSTATUS
GS_CER-ZRESULT.
ELSEIF SY-SUBRC <> 0.
MESSAGE E007.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_FUNCTION_KEY
*&---------------------------------------------------------------------*
FORM SET_FUNCTION_KEY .
* SMW0
G_FUNCTION_KEY-ICON_ID = ICON_XLS.
G_FUNCTION_KEY-ICON_TEXT = TEXT-003.
G_FUNCTION_KEY-TEXT = TEXT-003.
SSCRFIELDS-FUNCTXT_01 = G_FUNCTION_KEY.
* OLE2
G_FUNCTION_KEY-ICON_ID = ICON_XLS.
G_FUNCTION_KEY-ICON_TEXT = TEXT-004.
G_FUNCTION_KEY-TEXT = TEXT-004.
SSCRFIELDS-FUNCTXT_02 = G_FUNCTION_KEY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ACT_FUNCTION_KEY
*&---------------------------------------------------------------------*
FORM ACT_FUNCTION_KEY .
CASE SSCRFIELDS-UCOMM.
WHEN 'FC01'.
PERFORM EXCEL_DOWN_SMW0.
WHEN 'FC02'.
PERFORM EXCEL_DOWN_OLE2.
ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXCEL_DOWN_SMW0
*&---------------------------------------------------------------------*
FORM EXCEL_DOWN_SMW0 .
* 다운로드 양식 선택
IF P_DES = 'X'.
LS_KEY-OBJID = 'ZEDU19_0020_EXCEL01'.
LS_KEY-RELID = 'MI'.
ELSEIF P_CER = 'X'.
LS_KEY-OBJID = 'ZEDU19_0020_EXCEL02'.
LS_KEY-RELID = 'MI'.
ENDIF.
* 파일 경로 조회
PERFORM SET_DIRECTORY USING LS_KEY-OBJID.
* 엑셀 다운
PERFORM DOWNLOAD_EXCEL_SMW0 USING LS_KEY-OBJID.
IF SY-SUBRC = 0.
MESSAGE S009.
ELSE.
MESSAGE S010.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_DIRECTORY
*&---------------------------------------------------------------------*
FORM SET_DIRECTORY USING LS_KEY-OBJID.
CLEAR GV_FILE.
CREATE OBJECT OBJFILE.
IF GV_FILE IS NOT INITIAL.
GV_INITIAL_DIR = GV_FILE.
ELSE.
OBJFILE->GET_TEMP_DIRECTORY( CHANGING TEMP_DIR = GV_INITIAL_DIR
EXCEPTIONS CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3 ).
ENDIF.
OBJFILE->DIRECTORY_BROWSE( EXPORTING INITIAL_FOLDER = GV_INITIAL_DIR
CHANGING SELECTED_FOLDER = GV_DIRECTORY
EXCEPTIONS CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3 ).
IF SY-SUBRC = 0.
GV_FILE = GV_DIRECTORY && '\' && LS_KEY-OBJID && '.xlsx'. //'
ELSE.
MESSAGE S008 DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_EXCEL_SMW0
*&---------------------------------------------------------------------*
FORM DOWNLOAD_EXCEL_SMW0 USING LS_KEY_OBJID.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LS_KEY
DESTINATION = GV_FILE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXCEL_DOWN_OLE2
*&---------------------------------------------------------------------*
FORM EXCEL_DOWN_OLE2 .
* 다운로드 양식 선택
IF P_DES = 'X'.
LS_KEY-OBJID = 'ZEDU19_0020_EXCEL01'.
LS_KEY-RELID = 'MI'.
ELSEIF P_CER = 'X'.
LS_KEY-OBJID = 'ZEDU19_0020_EXCEL02'.
LS_KEY-RELID = 'MI'.
ENDIF.
* 파일 경로 조회
PERFORM SET_DIRECTORY USING LS_KEY-OBJID.
* 엑셀 다운
PERFORM DOWNLOAD_EXCEL_OLE USING LS_KEY-OBJID.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_EXCEL_OLE
*&---------------------------------------------------------------------*
FORM DOWNLOAD_EXCEL_OLE USING P_KEY_OBJID.
* OLE OBJECT 생성 & 실행
CREATE OBJECT GO_APPLICATION 'Excel.Application'.
* 화면 DISPLAY 설정 (1을 설정하면 DISPLAY)
SET PROPERTY OF GO_APPLICATION 'Visible' = 1.
* WORKBOOK 및 WORKBOOK 설정 & OPEN
CALL METHOD OF GO_APPLICATION 'Workbooks' = GO_WBOOK.
CALL METHOD OF GO_WBOOK 'Add'.
* 최초 실행 SHEET는 첫번째
CALL METHOD OF GO_APPLICATION 'Worksheets' = GO_SHEET
EXPORTING
#1 = 1.
CALL METHOD OF GO_SHEET 'Activate'.
SET PROPERTY OF GO_SHEET 'Name' = 'Sheet1'.
GET PROPERTY OF GO_APPLICATION 'ActiveWorkbook' = GO_WBOOK.
* 데이터 입력
PERFORM FILL_CELL USING GO_APPLICATION 01: 01 '사원번호',
02 '프로젝트 ID',
03 'Module',
04 '투입월',
05 '종료월'.
* 파일명 설정
CONCATENATE GV_DIRECTORY '\' P_KEY_OBJID '.xlsx' INTO GV_PATH. //'
* 실행 파일 저장
CALL METHOD OF GO_WBOOK 'SaveAs' EXPORTING #1 = GV_PATH.
IF SY-SUBRC = 0.
MESSAGE S009.
ELSE.
MESSAGE S010.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FILL_CELL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> GO_APPLICATION
*& --> P_01
*& --> P_01
*& --> P_
*&---------------------------------------------------------------------*
FORM FILL_CELL USING PV_APPLICATION
PV_ROW
PV_COL
PV_VALUE.
DATA: LV_ECELL TYPE OLE2_OBJECT.
CALL METHOD OF PV_APPLICATION 'Cells' = LV_ECELL
EXPORTING
#1 = PV_ROW
#2 = PV_COL.
SET PROPERTY OF LV_ECELL 'Value' = PV_VALUE.
ENDFORM.
SCREEN 100
PROCESS BEFORE OUTPUT.
MODULE STATUS_0100.
MODULE SET_ALV_0100.
PROCESS AFTER INPUT.
MODULE USER_COMMAND_0100 AT EXIT-COMMAND.
MODULE SAVE_DATA.
3. 개선사항
- DB에 저장된 값이 있는 것은 체크했지만, 업로드 하는 엑셀 자체에 중복된 값이 있는 것은 체크가 필요하다.
- 엑셀 데이터 업로드 시, 날짜 표시 형식이 다른 경우 체크가 필요하다.
2020/08/12또는2020.08.12와 같은 형식을 날짜 타입으로 받으면 오류가 나기 때문에 char 형태로 받은 후 가공하는 코드를 짜야하며, 화폐 단위의 콤마도 마찬가지다.
* 날짜 데이터 가공
CASE LV_TYPE.
WHEN 'C' OR 'g' OR 'N'. "Char, String, Numc
WHEN 'D' OR 'T'. "Date, Time
PERFORM CONV_STRING_REMOVE USING '.'
CHANGING <LV_COMP1>.
PERFORM CONV_STRING_REMOVE USING '-'
CHANGING <LV_COMP1>.
PERFORM CONV_STRING_REMOVE USING '/'
CHANGING <LV_COMP1>.
WHEN OTHERS.
PERFORM CONV_STRING_REMOVE USING GC_QOUTE
CHANGING <LV_COMP1>.
PERFORM CONV_STRING_REMOVE USING ','
CHANGING <LV_COMP1>.
IF <LV_COMP1> = '-'.
<LV_COMP1> = 0.
ENDIF.
ENDCASE.
- AT을 활용한 구문을 사용하면, at 구문 이후의 character값은 다
*난수 값으로 바뀌어서 data handling이 불가능하다. 따라서 일반적으로 지양하고, 만약 사용한다 하더라도 정확하게 sort한 후 사용해야 한다. - At 구문으로 특정 필드가 특정 값일 때의 데이터를 불러오는 경우는 Read table의 with key가 더 편리하다.
* At-end 구문 사용 지양을 위해 변경한 코드
DATA: LT_EXCEL TYPE TABLE OF ALSMEX_TABLINE,
LS_EXCEL TYPE ALSMEX_TABLINE.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = 1
I_BEGIN_ROW = 2
I_END_COL = 15
I_END_ROW = 10000
TABLES
INTERN = GT_EXCEL
* EXCEPTIONS
* INCONSISTENT_PARAMETERS = 1
* UPLOAD_OLE = 2
* OTHERS = 3
.
LT_EXCEL = GT_EXCEL.
SORT LT_EXCEL BY ROW.
DELETE ADJACENT DUPLICATES FROM LT_EXCEL COMPARING ROW.
SORT GT_EXCEL BY ROW.
LOOP AT LT_EXCEL INTO LS_EXCEL.
CLEAR GS_DATA.
READ TABLE GT_EXCEL WITH KEY ROW = LS_EXCEL-ROW
TRANSPORTING NO FIELDS
BINARY SEARCH.
CHECK SY-SUBRC = 0.
LOOP AT GT_EXCEL INTO GS_EXCEL FROM SY-TABIX.
IF GS_EXCEL-ROW <> LS_EXCEL-ROW.
APPEND GS_DATA TO GT_DATA.
EXIT.
ENDIF.
UNASSIGN <FS>.
ASSIGN COMPONENT GS_EXCEL-COL OF STRUCTURE GS_DATA TO <FS>.
IF <FS> IS ASSIGNED.
<FS> = GS_EXCEL-VALUE.
ENDIF.
ENDLOOP.
ENDLOOP.
4. 기타
- SMW0: SAP SERVER에 형식 저장, 이것도 CTS를 태워야 적용이 된다.
- T-CODE AL11: SAP Dictionary 확인 티코드
- AXLM Method 같은 경우 컨트롤이 더 쉽지만 메모리 상의 문제로 6만건 정도만 한번에 올릴 수 있다. GUI로 표기되는 SAP 메소드는 이러한 문제는 없지만 현업에서는 AXLM을 더 많이 쓴다.
- AXLM Method 내부 코드를 보면 OLE로 되어 있다.
- 엑셀 파일 작업시, 프로그램 back단에다가 파일을 불러온다. 따라서 업로드 작업시 엑셀 파일을 열어두면 작업이 꼬일 수 있으니 유의하고, call screen 전에 back단에서 돌아가는 프로그램이 없는지 체크한다.
- CHECK SY-BATCH IS INITIAL.