[Function] ZCM_EXCEL_UPLOAD - 필드 점검 및 변환 엑셀 업로드

ZCM_EXCEL_UPLOAD 필드 점검 및 변환 엑셀 업로드

ALSM_EXCEL_TO_INTERNAL_TABLE

# 2023.09.06 수정 : 소스코드를 좀더 간결하게 수정, 금액필드의 통화에따른 Input형식 변환 추가

 

ABAP에서 엑셀 업로드 프로그램 개발시 정해진 양식에 올바르게 값을 넣지 않을 경우(주로 숫자 형식의 항목에 문자를 넣을 경우) 아래와 같이 필드 컨버전 오류가 발생하게 됩니다.

이런 경우 많은 개발자들이 Excel 데이터를 받은 후에 숫자에 해당하는 항목을 replace 문으로 특수문자를 제거하는 식으로 하고 있으나 후 처리를 위해 잔손이 많이 가고, 사용자들의 기행으로 인해 완벽한 예외처리가 되지 않아 종종 덤프가 발생하게 됩니다.

 

엑셀 업로드의 근본적인 문제 해결과 개발 편의성을 위해 CL_SALV_DDIC 를 이용하여 

데이터를 받을 Internal table의 필드 형식에 맞추어 자동 변환 및 필드값을 점검하는 Function을 개발하였습니다.

 

ABAP에서 엑셀 업로드 기능 구현시 아래 Function을 사용하면 후 처리 없이 Excel 데이터를 Internal Table로 즉시 변환이 가능하며, 금액 필드의 경우 Internal Table에 사용한 스트럭처/테이블에 참조필드가 존재하면 통화에 해당 하는 Input 형식으로 변환됩니다.

 

# 사용예시

1. 엑셀 업로드 예시 소스코드

"엑셀 업로드 데이터
TYPES: BEGIN OF T_EXCEL,
  BUKRS TYPE T001-BUKRS,    "회사코드
  WERKS TYPE MSEG-WERKS,    "플랜트
  LIFNR TYPE LFA1-LIFNR,    "공급업체
  BUDAT TYPE MKPF-BUDAT,    "전기일
  MATNR TYPE MSEG-MATNR,    "자재코드
  MENGE TYPE MSEG-MENGE,    "수량
  MEINS TYPE MSEG-MEINS,    "단위
  EXBWR TYPE MSEG-EXBWR,    "금액
  WAERS TYPE MSEG-WAERS,    "통화
  END OF T_EXCEL.
DATA LT_EXCEL TYPE STANDARD TABLE OF T_EXCEL.

CLEAR LT_EXCEL.
CALL FUNCTION 'ZCM_EXCEL_UPLOAD'
  EXPORTING
*   I_FILENAME                    =
    I_BEGIN_COL                   = 1
    I_BEGIN_ROW                   = 2
*   I_END_COL                     =
*   I_END_ROW                     =
    I_FIELD_ELEMENT_MSG           = 'X'
    I_ENABLE_CONV_EXIT            = 'X'
*   I_CHECK_CONV_EXIT             = ' '
*   I_STOP_FIRST_ERROR            = ' '
  TABLES
    OUTTAB                        = LT_EXCEL
*   INTERN                        =
  EXCEPTIONS
    INCONSISTENT_PARAMETERS       = 1
    UPLOAD_OLE                    = 2
    EXCEL_DATA_ERROR              = 3
    OTHERS                        = 4
          .
IF SY-SUBRC <> 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

ZCL_SALV_TEST=>DISPLAY( LT_EXCEL ).

2. 엑셀 데이터

  -. 일자, 수량, 금액, 공급업체와 같은 항목에 대해 필드 형식에 맞추어 자동 변환

3. 업로드 데이터

  -. 일자, 수량, 금액, 공급업체와 같은 항목에 대해 필드 형식에 맞추어 자동 변환

 

필드 점검 오류 예시

 

# 소스코드

FUNCTION ZCM_EXCEL_UPLAOD .
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(I_FILENAME) OPTIONAL
*"     VALUE(I_BEGIN_COL) TYPE  I DEFAULT 1
*"     VALUE(I_BEGIN_ROW) TYPE  I DEFAULT 1
*"     VALUE(I_END_COL) TYPE  I OPTIONAL
*"     VALUE(I_END_ROW) TYPE  I OPTIONAL
*"     VALUE(I_FIELD_ELEMENT_MSG) TYPE  C DEFAULT 'X'
*"     VALUE(I_ENABLE_CONV_EXIT) TYPE  C DEFAULT 'X'
*"     VALUE(I_CHECK_CONV_EXIT) TYPE  C DEFAULT SPACE
*"     VALUE(I_STOP_FIRST_ERROR) TYPE  C DEFAULT SPACE
*"  TABLES
*"      OUTTAB OPTIONAL
*"      INTERN STRUCTURE  YALSMEX_TABLINE OPTIONAL
*"----------------------------------------------------------------------

TYPES : BEGIN OF T_ERROR,
    XLS_ROW LIKE INTERN-ROW,
    XLS_COL(2) TYPE C,
    XLS_COLROW(12) TYPE C,
    VALUE_TYPE(100) TYPE C,
    VALUE_XLS LIKE INTERN-VALUE,
    ERR_MSG(120) TYPE C,
    END OF T_ERROR.

  DATA : LV_FILENAME  TYPE STRING,
         LV_FILE_EXT  TYPE STRING,
         LV_MAX_ROW   LIKE INTERN-ROW.

  DATA : LT_ERROR_MSG TYPE STANDARD TABLE OF T_ERROR.

  IF I_FILENAME IS INITIAL.   "파일 경로가 입력되지 않았을 경우
    "파일을 선택하는 팝업을 호출한다
    DATA: LV_ACTION  TYPE I,                "ACTION
          LT_FILETAB TYPE FILETABLE,        "FILE TABLE
          LV_RC      TYPE I.                "RETURN CODE
    DATA: LV_FILE_PATH TYPE STRING.

    CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
      EXPORTING
*       WINDOW_TITLE            = G_TITLE
*       DEFAULT_FILENAME        = FILE_NAME
*       DEFAULT_EXTENSION       = 'TXT'
        FILE_FILTER             = CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
*       INITIAL_DIRECTORY       = FILE_PATH
        MULTISELECTION          = ABAP_FALSE
      CHANGING
        FILE_TABLE              = LT_FILETAB
        RC                      = LV_RC
        USER_ACTION             = LV_ACTION
      EXCEPTIONS
        FILE_OPEN_DIALOG_FAILED = 1
        CNTL_ERROR              = 2
        ERROR_NO_GUI            = 3
        OTHERS                  = 4.
*
    IF SY-SUBRC = 0.
      IF LV_ACTION = CL_GUI_FRONTEND_SERVICES=>ACTION_CANCEL.
        EXIT.
      ENDIF.
    ENDIF.

    READ TABLE LT_FILETAB INTO LV_FILE_PATH INDEX 1.
    IF SY-SUBRC EQ 0.
      MOVE LV_FILE_PATH TO LV_FILENAME.
    ENDIF.
  ELSE.
    MOVE I_FILENAME TO LV_FILENAME.
  ENDIF.

  IF LV_FILENAME IS INITIAL.
    EXIT.
  ENDIF.

  IF I_BEGIN_COL IS INITIAL.
    I_BEGIN_COL = 1.
  ENDIF.
  IF I_BEGIN_ROW IS INITIAL.
    I_BEGIN_ROW = 1.
  ENDIF.
  IF I_END_COL IS INITIAL.
    I_END_COL = 150.
  ENDIF.
  IF I_END_ROW IS INITIAL.
    "확장자에 따라 최대 행 갯수 결정
    CLEAR: LV_FILE_EXT.
    CALL FUNCTION 'CRM_IC_WZ_SPLIT_FILE_EXTENSION'
      EXPORTING
        IV_FILENAME_WITH_EXT = LV_FILENAME
      IMPORTING
        EV_EXTENSION         = LV_FILE_EXT.
    LV_FILE_EXT = |{ LV_FILE_EXT CASE = (CL_ABAP_FORMAT=>C_UPPER) }|.
    IF LV_FILE_EXT = 'XLSX'.
      I_END_ROW = 1048576.
    ELSE.
      I_END_ROW = 65000.
    ENDIF.
  ENDIF.

  IF I_BEGIN_ROW > I_END_ROW OR I_BEGIN_COL > I_END_COL.
    EXIT.
  ENDIF.

  LV_MAX_ROW = 9999999999.
  IF I_END_ROW > I_BEGIN_ROW + LV_MAX_ROW.
    I_END_ROW = I_BEGIN_ROW + LV_MAX_ROW.
  ENDIF.

  "엑셀 TO 인터널테이블 함수 호출
  DATA LV_FILENAME_RLGRAP TYPE RLGRAP-FILENAME.
  LV_FILENAME_RLGRAP = LV_FILENAME.

  CALL FUNCTION 'YALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                      = LV_FILENAME_RLGRAP
      I_BEGIN_COL                   = I_BEGIN_COL
      I_BEGIN_ROW                   = I_BEGIN_ROW
      I_END_COL                     = I_END_COL
      I_END_ROW                     = I_END_ROW
    TABLES
      INTERN                        = INTERN
    EXCEPTIONS
      INCONSISTENT_PARAMETERS       = 1
      UPLOAD_OLE                    = 2
      OTHERS                        = 3
            .
  IF SY-SUBRC <> 0.
    IF SY-SUBRC EQ 1.
      RAISE INCONSISTENT_PARAMETERS.
    ELSE.
      RAISE UPLOAD_OLE.
    ENDIF.
  ENDIF.

  IF INTERN[] IS INITIAL.
    EXIT.
  ENDIF.

  IF OUTTAB IS REQUESTED.
    CLEAR: LT_ERROR_MSG[].

    FIELD-SYMBOLS <LFS> TYPE ANY.
    DATA: LV_VALUE LIKE INTERN-VALUE.

    DATA: LV_CX        TYPE REF TO CX_ROOT,
          LV_ERR       TYPE STRING,
          LV_FIELD_TYPE(100) TYPE C,
          LV_FUNC_NAME TYPE RS38L_FNAM.
    DATA: LV_AMOUNT_INPUT   TYPE BAPICURR-BAPICURR,
          LV_AMOUNT_OUTPUT  TYPE BAPICURR-BAPICURR,
          LV_CURRENCY TYPE TCURC-WAERS.
    DATA: LV_TUMLS_DATE TYPE TUMLS_DATE.

    "인터널테이블의 필드 형식
    DATA(LT_DFIES) = CL_SALV_DDIC=>GET_BY_DATA( OUTTAB[] ).

    DATA LT_DFIES_CURR LIKE LT_DFIES.
    CLEAR LT_DFIES_CURR.

    "금액 필드에 Conversion Input 적용
    IF I_ENABLE_CONV_EXIT EQ 'X' AND LINE_EXISTS( LT_DFIES[ DATATYPE = 'CURR' ] ).
      LT_DFIES_CURR[] = LT_DFIES[].
      DELETE LT_DFIES_CURR WHERE DATATYPE NE 'CURR'.
    ENDIF.

    SORT INTERN BY ROW COL.

    LOOP AT INTERN INTO DATA(LS_INTERN).
      AT NEW ROW.
        CLEAR : OUTTAB.
      ENDAT.

      READ TABLE LT_DFIES INTO DATA(LS_DFIES) INDEX LS_INTERN-COL.
      IF SY-SUBRC NE 0.
        CONTINUE.
      ENDIF.

      DATA(LV_TABFIELD) = 'OUTTAB-' && LS_DFIES-FIELDNAME.
      CONDENSE LV_TABFIELD.
      UNASSIGN <LFS>.
      ASSIGN (LV_TABFIELD) TO <LFS>.

      IF <LFS> IS ASSIGNED.
        CLEAR LV_FIELD_TYPE.
        LV_VALUE = LS_INTERN-VALUE.

        TRY.
          IF I_FIELD_ELEMENT_MSG EQ 'X' AND LS_DFIES-FIELDTEXT IS NOT INITIAL.
            MOVE LS_DFIES-FIELDTEXT TO LV_FIELD_TYPE.
          ENDIF.

          "필드 형식에 따라 처리
          CASE LS_DFIES-INTTYPE.
            WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_DATE.    "날짜.
              IF LV_FIELD_TYPE IS INITIAL.
                LV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '날짜' ELSE 'date' ).
              ENDIF.

              MOVE LV_VALUE TO LV_TUMLS_DATE.
              "date format YYYY/MM/DD
              FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN LV_TUMLS_DATE.
              IF sy-subrc = 0.
                CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
                  EXPORTING
                    date_in             = LV_TUMLS_DATE
                    date_format_in      = 'DYMD'
                    to_output_format    = ' '
                    to_internal_format  = 'X'
                  IMPORTING
                    date_out            = LV_TUMLS_DATE
                  EXCEPTIONS
                    illegal_date        = 1
                    illegal_date_format = 2
                    no_user_date_format = 3
                    OTHERS              = 4.
              ELSE.
                " date format DD/MM/YYYY
                FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN LV_TUMLS_DATE.
                IF sy-subrc = 0.
                  CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
                    EXPORTING
                      date_in             = LV_TUMLS_DATE
                      date_format_in      = 'DDMY'
                      to_output_format    = ' '
                      to_internal_format  = 'X'
                    IMPORTING
                      date_out            = LV_TUMLS_DATE
                    EXCEPTIONS
                      illegal_date        = 1
                      illegal_date_format = 2
                      no_user_date_format = 3
                      OTHERS              = 4.
                ENDIF.
              ENDIF.
              IF SY-SUBRC EQ 0.
                MOVE LV_TUMLS_DATE TO LV_VALUE.
              ENDIF.

              PERFORM REMOVE_SPECIAL_CHAR  USING LV_VALUE SPACE CHANGING LV_VALUE.
              IF LV_VALUE IS NOT INITIAL AND STRLEN( LV_VALUE ) NE LS_DFIES-LENG.
                "엑셀의 어느 셀 값이 오류인지
                PERFORM GET_CURRENT_CELL_NUMBER TABLES LT_ERROR_MSG
                                                USING LS_INTERN-COL LS_INTERN-ROW
                                                      I_BEGIN_COL   I_BEGIN_ROW
                                                      LV_FIELD_TYPE LS_INTERN-VALUE
                                                      I_STOP_FIRST_ERROR.
                CONTINUE.
              ENDIF.
            WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_TIME.    "시간
              IF LV_FIELD_TYPE IS INITIAL.
                LV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '시간' ELSE 'time' ).
              ENDIF.

              PERFORM REMOVE_SPECIAL_CHAR  USING LV_VALUE SPACE CHANGING LV_VALUE.
              IF LV_VALUE IS NOT INITIAL AND STRLEN( LV_VALUE ) NE LS_DFIES-LENG.
                "엑셀의 어느 셀 값이 오류인지
                PERFORM GET_CURRENT_CELL_NUMBER TABLES LT_ERROR_MSG
                                                USING LS_INTERN-COL LS_INTERN-ROW
                                                      I_BEGIN_COL   I_BEGIN_ROW
                                                      LV_FIELD_TYPE LS_INTERN-VALUE
                                                      I_STOP_FIRST_ERROR.
                CONTINUE.
              ENDIF.
            WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_PACKED  "숫자
               OR CL_ABAP_TYPEDESCR=>TYPEKIND_NUM
               OR CL_ABAP_TYPEDESCR=>TYPEKIND_INT
               OR CL_ABAP_TYPEDESCR=>TYPEKIND_FLOAT.
              IF LV_FIELD_TYPE IS INITIAL.
                LV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '숫자' ELSE 'numeric' ).
              ENDIF.

              PERFORM REMOVE_SPECIAL_CHAR  USING LV_VALUE 'X' CHANGING LV_VALUE.
            WHEN OTHERS.
              IF LV_FIELD_TYPE IS INITIAL.
                LV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '문자' ELSE 'character' ).
              ENDIF.
          ENDCASE.

          "대문자 변환
          IF I_ENABLE_CONV_EXIT EQ 'X' AND LS_DFIES-LOWERCASE NE 'X' AND LS_DFIES-INTTYPE EQ 'C'.
            TRANSLATE LV_VALUE TO UPPER CASE.
          ENDIF.

          "변환 루틴 적용
          IF I_ENABLE_CONV_EXIT EQ 'X' AND LS_DFIES-CONVEXIT IS NOT INITIAL.
            LV_FUNC_NAME = 'CONVERSION_EXIT_' && LS_DFIES-CONVEXIT && '_INPUT'.
            TRY.
              CALL FUNCTION LV_FUNC_NAME
                EXPORTING
                  INPUT         = LV_VALUE
                IMPORTING
                  OUTPUT        = <LFS>
                EXCEPTIONS
                  OTHERS        = 1
                        .
            CATCH CX_ROOT INTO LV_CX.
              MOVE LV_VALUE TO <LFS>.
              SY-SUBRC = 0.
            ENDTRY.
            IF SY-SUBRC NE 0 AND I_CHECK_CONV_EXIT EQ 'X'.
              "엑셀의 어느 셀 값이 오류인지
              MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 INTO LV_VALUE.
              PERFORM GET_CURRENT_CELL_NUMBER TABLES LT_ERROR_MSG
                                              USING LS_INTERN-COL LS_INTERN-ROW
                                                    I_BEGIN_COL   I_BEGIN_ROW
                                                    LV_FIELD_TYPE LV_VALUE
                                                    I_STOP_FIRST_ERROR.
            ENDIF.
          ELSE.
            MOVE LV_VALUE TO <LFS>.
          ENDIF.
        CATCH CX_ROOT INTO LV_CX.
          LV_ERR = LV_CX->GET_TEXT( ).

          "엑셀의 어느 셀 값이 오류인지
          PERFORM GET_CURRENT_CELL_NUMBER TABLES LT_ERROR_MSG
                                           USING LS_INTERN-COL LS_INTERN-ROW
                                                I_BEGIN_COL   I_BEGIN_ROW
                                                LV_FIELD_TYPE LS_INTERN-VALUE
                                                I_STOP_FIRST_ERROR.
        ENDTRY.
      ENDIF.

      AT END OF ROW.
        "금액 필드에 Conversion Input 적용
        IF LT_DFIES_CURR[] IS NOT INITIAL.
          LOOP AT LT_DFIES_CURR INTO LS_DFIES.
            IF LS_DFIES-REFTABLE EQ LS_DFIES-PRECFIELD AND LS_DFIES-REFFIELD IS NOT INITIAL.
              LV_TABFIELD = 'OUTTAB-' && LS_DFIES-REFFIELD.
              CONDENSE LV_TABFIELD.
              UNASSIGN <LFS>.
              ASSIGN (LV_TABFIELD) TO <LFS>.
              IF <LFS> IS NOT ASSIGNED.
                CONTINUE.
              ENDIF.
              MOVE <LFS> TO LV_CURRENCY.

              LV_TABFIELD = 'OUTTAB-' && LS_DFIES-FIELDNAME.
              CONDENSE LV_TABFIELD.
              UNASSIGN <LFS>.
              ASSIGN (LV_TABFIELD) TO <LFS>.

              MOVE <LFS> TO LV_AMOUNT_INPUT.

              IF LV_CURRENCY IS INITIAL OR LV_AMOUNT_INPUT IS INITIAL.
                CONTINUE.
              ENDIF.
              CALL FUNCTION 'CURRENCY_AMOUNT_BAPI_TO_SAP'
                EXPORTING
                  CURRENCY                    = LV_CURRENCY
                  BAPI_AMOUNT                 = LV_AMOUNT_INPUT
                IMPORTING
                  SAP_AMOUNT                  = LV_AMOUNT_OUTPUT
                EXCEPTIONS
                  BAPI_AMOUNT_INCORRECT       = 1
                  OTHERS                      = 2
                        .
              IF SY-SUBRC <> 0.
*   Implement suitable error handling here
              ENDIF.
              MOVE LV_AMOUNT_OUTPUT TO <LFS>.
            ENDIF.
          ENDLOOP.
        ENDIF.

        APPEND OUTTAB.
      ENDAT.
    ENDLOOP.

    IF LT_ERROR_MSG[] IS NOT INITIAL AND I_STOP_FIRST_ERROR IS INITIAL.
      SORT LT_ERROR_MSG BY XLS_ROW XLS_COL.
      PERFORM POPUP_ERROR_MSG TABLES LT_ERROR_MSG.
      MESSAGE E000(0K) WITH 'Excel data error' RAISING EXCEL_DATA_ERROR.
    ENDIF.
  ENDIF.
ENDFUNCTION.

FORM GET_CURRENT_CELL_NUMBER TABLES PT_ERROR_MSG
                             USING P_COL
                                   P_ROW
                                   P_BEGIN_COL
                                   P_BEGIN_ROW
                                   P_FIELD_TYPE
                                   P_VALUE
                                   P_STOP_FIRST_ERROR.
  DATA : LV_MOD        TYPE INT4,
         LV_DIV        TYPE INT4,
         LV_HEX        TYPE STRING VALUE 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
         LV_XLS_ROW    TYPE I,
         LV_XLS_COL(2) TYPE C,
         LV_XLS_COLROW(12) TYPE C.

  CLEAR : LV_XLS_ROW, LV_XLS_COL.
  LV_DIV = P_COL + P_BEGIN_COL - 1.

  DO.
    LV_MOD = ( LV_DIV - 1 ) MOD 26.
    LV_XLS_COL = LV_HEX+LV_MOD(1) && LV_XLS_COL.
    CONDENSE LV_XLS_COL.
    LV_DIV = ( LV_DIV - LV_MOD ) DIV 26.
    IF LV_DIV EQ 0.
      EXIT.
    ENDIF.
  ENDDO.
  LV_XLS_ROW = P_ROW + P_BEGIN_ROW - 1.
  LV_XLS_COLROW = LV_XLS_COL && LV_XLS_ROW.
  CONDENSE LV_XLS_COLROW.
  CONDENSE P_VALUE.
  CONDENSE P_FIELD_TYPE.

  IF P_STOP_FIRST_ERROR EQ 'X'.
    MESSAGE E020(ZCM01) WITH LV_XLS_COLROW P_FIELD_TYPE P_VALUE RAISING EXCEL_DATA_ERROR.  "&1 셀 값이 '&2' 형식과 다릅니다.[&3]
  ELSE.
    FIELD-SYMBOLS <LFS>.
    DATA : LV_TAB_FIELD(100) TYPE C.

    LV_TAB_FIELD = 'PT_ERROR_MSG-XLS_ROW'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      <LFS> = LV_XLS_ROW.
    ENDIF.

    LV_TAB_FIELD = 'PT_ERROR_MSG-XLS_COL'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      <LFS> = LV_XLS_COL.
    ENDIF.

    LV_TAB_FIELD = 'PT_ERROR_MSG-XLS_COLROW'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      <LFS> = LV_XLS_COLROW.
    ENDIF.

    LV_TAB_FIELD = 'PT_ERROR_MSG-VALUE_XLS'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      <LFS> = P_VALUE.
    ENDIF.

    LV_TAB_FIELD = 'PT_ERROR_MSG-VALUE_TYPE'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      <LFS> = P_FIELD_TYPE.
    ENDIF.

    LV_TAB_FIELD = 'PT_ERROR_MSG-ERR_MSG'.
    ASSIGN (LV_TAB_FIELD) TO <LFS>.
    IF <LFS> IS ASSIGNED.
      MESSAGE E020(ZCM01) WITH LV_XLS_COLROW P_FIELD_TYPE P_VALUE INTO <LFS>.
    ENDIF.

    APPEND PT_ERROR_MSG.
  ENDIF.
ENDFORM.

"숫자를 제외한 모든 문자 제거
FORM REMOVE_NOT_NUMBER  USING P_VALUE
                              P_EXCLUDE_SIGN
                      CHANGING P_NUMBER.
  DATA : LV_OUTPUT(50)     TYPE C.

  MOVE P_VALUE TO LV_OUTPUT.
  CONDENSE LV_OUTPUT NO-GAPS.

  IF P_EXCLUDE_SIGN EQ 'X'.
    REPLACE ALL OCCURRENCES OF REGEX '[^[:digit:]|.|-]' IN LV_OUTPUT WITH SPACE.
    IF LV_OUTPUT EQ '-' OR LV_OUTPUT EQ '.'.
      CLEAR LV_OUTPUT.
    ENDIF.
  ELSE.
    REPLACE ALL OCCURRENCES OF REGEX '[^[:digit:]]' IN LV_OUTPUT WITH SPACE.
  ENDIF.
  MOVE LV_OUTPUT TO P_NUMBER.
ENDFORM.

"특수 문자 제거
FORM REMOVE_SPECIAL_CHAR USING P_VALUE
                               P_EXCLUDE_SIGN    "- . 부호와 소수점은 남김
                      CHANGING P_NUMBER.
  DATA : LV_OUTPUT(50)    TYPE C.

  MOVE P_VALUE TO LV_OUTPUT.
  CONDENSE LV_OUTPUT NO-GAPS.

  IF P_EXCLUDE_SIGN EQ 'X'.
    REPLACE ALL OCCURRENCES OF REGEX '[^[:alnum:]|.|-]' IN LV_OUTPUT WITH SPACE.
    IF LV_OUTPUT EQ '-' OR LV_OUTPUT EQ '.'.
      CLEAR LV_OUTPUT.
    ENDIF.
  ELSE.
    REPLACE ALL OCCURRENCES OF REGEX '[^[:alnum:]]' IN LV_OUTPUT WITH SPACE.
  ENDIF.
  MOVE LV_OUTPUT TO P_NUMBER.
ENDFORM.

FORM POPUP_ERROR_MSG TABLES PT_ERROR_MSG.
  DATA: LV_LTEXT  TYPE SCRTEXT_L,
        LR_COLUMN TYPE REF TO CL_SALV_COLUMN_TABLE.

  "ALV 생성
  CALL METHOD CL_SALV_TABLE=>FACTORY
    IMPORTING
      R_SALV_TABLE = DATA(LR_SALV)
    CHANGING
      T_TABLE      = PT_ERROR_MSG[].

  "ALV의 레이아웃 셋팅(줄무늬 패턴, 헤더 텍스트 등)
  DATA(LR_DISPLAY) = LR_SALV->GET_DISPLAY_SETTINGS( ).
  LR_DISPLAY->SET_STRIPED_PATTERN( CL_SALV_DISPLAY_SETTINGS=>TRUE ).
  LR_DISPLAY->SET_LIST_HEADER( SWITCH #( SY-LANGU WHEN '3' THEN '엑셀 데이터 오류 내역' ELSE 'Excel error data information' ) ).


  "ALV 열(필드카타로그) 설정
  DATA(LR_COLUMNS) = LR_SALV->GET_COLUMNS( ).
  LR_COLUMNS->SET_OPTIMIZE( ABAP_TRUE ).    "열 너비 최적화

  LOOP AT LR_COLUMNS->GET( ) INTO DATA(LS_COLUMNS).
    CLEAR LV_LTEXT.
    LR_COLUMN ?= LR_COLUMNS->GET_COLUMN( LS_COLUMNS-COLUMNNAME ).

    CASE LS_COLUMNS-COLUMNNAME.
      WHEN 'XLS_ROW'.
        LR_COLUMN->SET_KEY( ABAP_TRUE ).
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '행' ELSE 'Row' ).
      WHEN 'XLS_COL'.
        LR_COLUMN->SET_KEY( ABAP_TRUE ).
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '열' ELSE 'Col' ).
      WHEN 'XLS_COLROW'.
        LR_COLUMN->SET_KEY( ABAP_TRUE ).
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '셀' ELSE 'Cell' ).
      WHEN 'VALUE_XLS'.
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '엑셀 값' ELSE 'Value' ).
      WHEN 'VALUE_TYPE'.
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '셀 형식' ELSE 'Available type' ).
      WHEN 'ERR_MSG'.
        LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '오류 메시지' ELSE 'Error message' ).
    ENDCASE.

    IF LV_LTEXT IS NOT INITIAL.
      LR_COLUMN->SET_LONG_TEXT( CONV #( LV_LTEXT ) ).
      LR_COLUMN->SET_MEDIUM_TEXT( CONV #( LV_LTEXT ) ).
      LR_COLUMN->SET_SHORT_TEXT( CONV #( LV_LTEXT ) ).
    ENDIF.
  ENDLOOP.

  "ALV 를 팝업으로 실행
  LR_SALV->SET_SCREEN_POPUP(
    START_COLUMN = 1
    END_COLUMN   = 100
    START_LINE   = 1
    END_LINE     = 20 ).

  "ALV 표시
  LR_SALV->DISPLAY( ).
ENDFORM.