Excel 업로드/다운로드 통합 Class
ZCL_EXCEL_UPDOWN
사용 편의성을 위해 각각의 Function으로 개발한 엑셀 업로드, 다운로드 기능을 단일 Class로 만들었습니다.
사용 방법은 기존 Function과 거의 동일합니다.
# 기존 Function
2023.11.15 - [ABAP/소스코드] - [Class] Internal Table 엑셀 다운로드(XLSX 형식)
2023.07.11 - [ABAP/소스코드] - [Function] Internal Table 엑셀 다운로드(XLSX 형식)
2023.07.10 - [ABAP/소스코드] - [Function] ZCM_EXCEL_UPLOAD - 필드 점검 및 변환 엑셀 업로드
# Excel 업로드/다운로드 통합 Class
UPLOAD : 엑셀 업로드 (SAP GUI는 OLE로 처리, WEB GUI는 GUI_UPLOAD후 Excel to Itab 변환)
기타 등등
DOWNLOAD : 엑셀 다운로드(Internal Table to Excel)
CONV_TO_EXCEL : Internal Table to xstring
# 업로드 사용 예시
DATA(GO_EXCEL_UPLOAD) = NEW ZCL_EXCEL_UPDOWN( ).
DATA LD_DATA_UP TYPE REF TO DATA.
CREATE DATA LD_DATA_UP TYPE TABLE OF (P_TABLE). "인터널 테이블 동적 생성
ASSIGN LD_DATA_UP->* TO FIELD-SYMBOL(<GFT_EXCEL>).
GO_EXCEL_UPLOAD->UPLOAD(
exporting
* I_FILENAME =
I_BEGIN_COL = 1
I_BEGIN_ROW = 2
* I_END_COL =
* I_END_ROW =
* I_FIELD_ELEMENT_MSG = ABAP_FALSE
* I_ENABLE_CONV_EXIT = ABAP_TRUE
* I_CHECK_CONV_EXIT = ABAP_FALSE
* I_CHECK_DOMVALUES = ABAP_TRUE
* I_CHECK_TABVALUES = ABAP_TRUE
* I_FORCE_UPLOAD_MODE = ABAP_FALSE
importing
INTERN = DATA(LT_INTERN)
OUTTAB = <GFT_EXCEL>
exceptions
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3
).
"오류가 메시지
DATA(LT_ERROR_MSG) = GO_EXCEL_UPLOAD->GT_ERRORMSG.
"오류 메시지 팝업
GO_EXCEL_UPLOAD->POPUP_ERROR_MSG( GO_EXCEL_UPLOAD->GT_ERRORMSG ).
"데이터 저장 전에 점검
GO_EXCEL_UPLOAD->CONV_INTERN_TO_OUTTAB(
exporting
INTERN = GO_EXCEL_UPLOAD->CONV_OUTTAB_TO_INTERN( OUTTAB = <GFT_EXCEL>
EXCLUDE_BLANK = ABAP_FALSE )
exceptions
EXCEL_DATA_ERROR = 1
OTHERS = 2 ).
IF GO_EXCEL_UPLOAD->GT_ERRORMSG IS NOT INITIAL.
ENDIF.# 소스코드
class ZCL_EXCEL_UPDOWN definition
public
final
create public .
public section.
types:
BEGIN OF TS_ALSMEX_TABLINE,
ROW TYPE N LENGTH 7,
COL TYPE N LENGTH 4,
VALUE TYPE STRING,
END OF TS_ALSMEX_TABLINE .
types:
TT_ALSMEX_TABLINE TYPE STANDARD TABLE OF TS_ALSMEX_TABLINE WITH EMPTY KEY .
types:
BEGIN OF TS_ERROR,
XLS_ROW TYPE TS_ALSMEX_TABLINE-ROW,
XLS_COL(2) TYPE C,
XLS_COLROW(12) TYPE C,
ITAB_ROWNO TYPE I,
ITAB_COLNO TYPE I,
ITAB_FNAME TYPE LVC_FNAME,
ITAB_FIELDTEXT TYPE AS4TEXT,
VALUE_TYPE(100) TYPE C,
VALUE_XLS TYPE TS_ALSMEX_TABLINE-VALUE,
ERR_MSG(120) TYPE C,
END OF TS_ERROR .
types:
TT_ERROR TYPE SORTED TABLE OF TS_ERROR WITH NON-UNIQUE KEY ITAB_ROWNO ITAB_COLNO .
types:
TT_ERROR_ALV TYPE STANDARD TABLE OF TS_ERROR WITH EMPTY KEY .
types:
T_FIELD_TYPE TYPE C LENGTH 100 .
data GT_ERRORMSG type TT_ERROR .
data GV_XLS_ROWS type I .
data GT_DFIES type DDFIELDS .
data GD_OUTTAB type ref to DATA .
class-methods CONV_TO_EXCEL
importing
value(IT_ITAB) type STANDARD TABLE
!IT_FIELDCAT type LVC_T_FCAT optional
!IV_WITHOUT_MANDT type ABAP_BOOL default ABAP_FALSE
exporting
!EV_XSTRING type XSTRING
!EV_SIZE type I
!EV_BINTAB type SOLIX_TAB .
class-methods DOWNLOAD
importing
value(IT_ITAB) type STANDARD TABLE
!IV_DIRECTORY type CSEQUENCE optional
!IV_FILENAME type CSEQUENCE optional
!IT_FIELDCAT type LVC_T_FCAT optional
!IV_WITHOUT_MANDT type ABAP_BOOL default ABAP_FALSE
!IV_AUTO_OPEN type C default SPACE
exporting
!EV_DOWNLOAD_FILENAME type CSEQUENCE
!EV_DOWNLOAD_PATH type CSEQUENCE
exceptions
DOWNLOAD_ERROR
EXTENSION_ERROR .
methods UPLOAD
importing
!I_FILENAME type CSEQUENCE optional
!I_BEGIN_COL type I default 1
!I_BEGIN_ROW type I default 1
!I_END_COL type I optional
!I_END_ROW type I optional
!I_FIELD_ELEMENT_MSG type ABAP_BOOL default ABAP_FALSE
!I_ENABLE_CONV_EXIT type ABAP_BOOL default ABAP_TRUE
!I_CHECK_CONV_EXIT type ABAP_BOOL default ABAP_FALSE
!I_CHECK_DOMVALUES type ABAP_BOOL default ABAP_TRUE
!I_CHECK_TABVALUES type ABAP_BOOL default ABAP_TRUE
!I_FORCE_UPLOAD_MODE type ABAP_BOOL default ABAP_FALSE
exporting
!OUTTAB type STANDARD TABLE
!INTERN type TT_ALSMEX_TABLINE
exceptions
INCONSISTENT_PARAMETERS
UPLOAD_OLE
EXCEL_DATA_ERROR .
methods CONV_INTERN_TO_OUTTAB
importing
!I_FIELD_ELEMENT_MSG type ABAP_BOOL default ABAP_FALSE
!I_ENABLE_CONV_EXIT type ABAP_BOOL default ABAP_TRUE
!I_CHECK_CONV_EXIT type ABAP_BOOL default ABAP_FALSE
!I_CHECK_DOMVALUES type ABAP_BOOL default ABAP_TRUE
!I_CHECK_TABVALUES type ABAP_BOOL default ABAP_TRUE
value(INTERN) type TT_ALSMEX_TABLINE
!EXCLUDE_BLANK type ABAP_BOOL default ABAP_TRUE
exporting
!OUTTAB type STANDARD TABLE
exceptions
EXCEL_DATA_ERROR .
methods CONV_OUTTAB_TO_INTERN
importing
!OUTTAB type STANDARD TABLE
!EXCLUDE_BLANK type ABAP_BOOL default ABAP_TRUE
returning
value(INTERN) type TT_ALSMEX_TABLINE .
methods CONV_FIELD
importing
!IS_DFIES type DFIES
!IV_VALUE type TS_ALSMEX_TABLINE-VALUE
!IV_ROW type TS_ALSMEX_TABLINE-ROW optional
!IV_COL type TS_ALSMEX_TABLINE-COL optional
!IV_ITAB_ROWNO type TS_ERROR-ITAB_ROWNO
value(IV_FIELD_TYPE) type T_FIELD_TYPE optional
!I_ENABLE_CONV_EXIT type ABAP_BOOL default ABAP_TRUE
!I_CHECK_CONV_EXIT type ABAP_BOOL default ABAP_FALSE
changing
!CV_OUTPUT type ANY .
methods POPUP_ERROR_MSG
importing
!I_ERROR_MSG type ANY TABLE
!I_START_COLUMN type I default 1
!I_END_COLUMN type I default 110
!I_START_LINE type I default 1
!I_END_LINE type I default 20 .
methods SPREADSHEET_TO_INTERNAL_TABLE
importing
!FILENAME type CSEQUENCE
!I_BEGIN_COL type I
!I_BEGIN_ROW type I
!I_END_COL type I
!I_END_ROW type I
exporting
value(INTERN) type TT_ALSMEX_TABLINE
exceptions
INCONSISTENT_PARAMETERS
UPLOAD_OLE .
methods ALSM_EXCEL_TO_INTERNAL_TABLE
importing
!FILENAME type CSEQUENCE
!I_BEGIN_COL type I
!I_BEGIN_ROW type I
!I_END_COL type I
!I_END_ROW type I
exporting
value(INTERN) type TT_ALSMEX_TABLINE
exceptions
INCONSISTENT_PARAMETERS
UPLOAD_OLE .
protected section.
private section.
types:
BEGIN OF TS_SENDERLINE,
line(4096) type c,
END OF TS_SENDERLINE .
types:
TT_SENDERLINE TYPE STANDARD TABLE OF TS_SENDERLINE WITH EMPTY KEY .
constants GC_SEPARATOR type C value CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB ##NO_TEXT.
constants GC_ESC type C value '"' ##NO_TEXT.
data GV_BEGIN_COL type I .
data GV_BEGIN_ROW type I .
data GV_END_COL type I .
data GV_END_ROW type I .
methods SEPARATED_TO_INTERN_CONVERT
importing
!IT_ITAB type TT_SENDERLINE
returning
value(ET_ITAB) type TT_ALSMEX_TABLINE .
methods LINE_TO_CELL_SEPARAT
importing
!I_ROW type SY-TABIX
!CH_CELL_COL type KCD_EX_COL
!I_SEPARATOR type C
!I_FDPOS type SY-FDPOS
changing
!I_LINE type TS_SENDERLINE-LINE
!I_INTERN type TT_ALSMEX_TABLINE .
methods LINE_TO_CELL_ESC_SEP
importing
!I_SEPARATOR type C optional
changing
!I_STRING type TS_SENDERLINE-LINE optional
!I_SIC_INT type I optional
!I_INTERN_VALUE type TS_ALSMEX_TABLINE-VALUE .
methods GET_CURRENT_CELL_NUMBER
importing
!I_COL type TS_ALSMEX_TABLINE-COL
!I_ROW type TS_ALSMEX_TABLINE-ROW
!I_ITAB_ROWNO type TS_ERROR-ITAB_ROWNO
!I_ITAB_FNAME type LVC_FNAME
!I_ITAB_FIELDTEXT type AS4TEXT
value(I_FIELD_TYPE) type TS_ERROR-VALUE_TYPE optional
!I_DOMNAME type DOMNAME optional
!I_CHECKTABLE type TABNAME optional
!I_VALUE type TS_ERROR-VALUE_XLS .
methods CONVERT_COLUMN_TO_I
importing
!COLUMN type CSEQUENCE
returning
value(COL_NUMBER) type I .
methods CONVERT_I_TO_COLUMN
importing
!INT_VAL type I
returning
value(COLUMN) type STRING .
methods REMOVE_NOT_NUMBER
importing
!I_VALUE type ANY optional
!I_EXCLUDE_SIGN type ABAP_BOOL default ABAP_TRUE
exporting
value(E_NUMBER) type ANY .
methods REMOVE_SPECIAL_CHAR
importing
!I_VALUE type ANY optional
!I_EXCLUDE_SIGN type ABAP_BOOL default ABAP_TRUE
exporting
value(E_NUMBER) type ANY .
ENDCLASS.
CLASS ZCL_EXCEL_UPDOWN IMPLEMENTATION.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->ALSM_EXCEL_TO_INTERNAL_TABLE
* +-------------------------------------------------------------------------------------------------+
* | [--->] FILENAME TYPE CSEQUENCE
* | [--->] I_BEGIN_COL TYPE I
* | [--->] I_BEGIN_ROW TYPE I
* | [--->] I_END_COL TYPE I
* | [--->] I_END_ROW TYPE I
* | [<---] INTERN TYPE TT_ALSMEX_TABLINE
* | [EXC!] INCONSISTENT_PARAMETERS
* | [EXC!] UPLOAD_OLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
method ALSM_EXCEL_TO_INTERNAL_TABLE.
DATA: excel_tab TYPE TT_SENDERLINE.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
DEFINE m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
END-OF-DEFINITION.
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* Get TAB-sign for separation of fields
CLASS cl_abap_char_utilities DEFINITION LOAD.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
* open file in Excel
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF application 'Workbooks' = workbook.
m_message.
CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
* mark whole spread sheet
CALL METHOD OF worksheet 'Cells' = h_cell
EXPORTING #1 = i_begin_row #2 = i_begin_col.
m_message.
CALL METHOD OF worksheet 'Cells' = h_cell1
EXPORTING #1 = i_end_row #2 = i_end_col.
m_message.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING #1 = h_cell #2 = h_cell1.
m_message.
CALL METHOD OF range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF range 'COPY'.
m_message.
* read clipboard into ABAP
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
intern = SEPARATED_TO_INTERN_CONVERT( IT_ITAB = excel_tab ).
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
* <<<<< End of change note 575877
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->CONVERT_COLUMN_TO_I
* +-------------------------------------------------------------------------------------------------+
* | [--->] COLUMN TYPE CSEQUENCE
* | [<-()] COL_NUMBER TYPE I
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONVERT_COLUMN_TO_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.
input = column.
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.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->CONVERT_I_TO_COLUMN
* +-------------------------------------------------------------------------------------------------+
* | [--->] INT_VAL TYPE I
* | [<-()] COLUMN TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONVERT_I_TO_COLUMN.
data:
cc type sychar02 value '',
frag type f,
comp type i value 1,
c_i_val type i,
c_x_val(2) type x,
input type i.
if int_val is initial or int_val < 1.
exit.
endif.
if int_val = 1.
column = 'A'.
exit.
endif.
column = ''.
frag = int_val.
while frag > 1 .
input = floor( frag ).
c_i_val = input mod 26.
if c_i_val = 0.
c_i_val = 26.
input = input - 25.
endif.
c_x_val = c_i_val + cl_abap_conv_out_ce=>uccp( 'A' ) - 1.
cc = cl_abap_conv_in_ce=>uccp( c_x_val ).
column = cc && column.
frag = input / 26 .
endwhile.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->CONV_FIELD
* +-------------------------------------------------------------------------------------------------+
* | [--->] IS_DFIES TYPE DFIES
* | [--->] IV_VALUE TYPE TS_ALSMEX_TABLINE-VALUE
* | [--->] IV_ROW TYPE TS_ALSMEX_TABLINE-ROW(optional)
* | [--->] IV_COL TYPE TS_ALSMEX_TABLINE-COL(optional)
* | [--->] IV_ITAB_ROWNO TYPE TS_ERROR-ITAB_ROWNO
* | [--->] IV_FIELD_TYPE TYPE T_FIELD_TYPE(optional)
* | [--->] I_ENABLE_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_CHECK_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [<-->] CV_OUTPUT TYPE ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONV_FIELD.
DATA: LV_CX TYPE REF TO CX_ROOT,
LV_VALUE TYPE TS_ALSMEX_TABLINE-VALUE,
LV_TUMLS_DATE TYPE TUMLS_DATE,
LV_FUNC_NAME TYPE RS38L_FNAM.
TRY.
LV_VALUE = IV_VALUE.
"필드 형식에 따라 처리
CASE IS_DFIES-INTTYPE.
WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_DATE. "날짜.
IF IV_FIELD_TYPE IS INITIAL.
IV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '날짜' ELSE 'date' ).
ENDIF.
IF LV_VALUE EQ '0'.
LV_VALUE = ''.
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.
REMOVE_NOT_NUMBER( EXPORTING I_VALUE = LV_VALUE
I_EXCLUDE_SIGN = ' '
IMPORTING E_NUMBER = LV_VALUE ).
IF LV_VALUE IS NOT INITIAL AND STRLEN( LV_VALUE ) NE IS_DFIES-LENG.
"엑셀의 어느 셀 값이 오류인지
GET_CURRENT_CELL_NUMBER( I_COL = IV_COL
I_ROW = IV_ROW
I_ITAB_ROWNO = IV_ITAB_ROWNO
I_ITAB_FNAME = IS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = IS_DFIES-FIELDTEXT
I_FIELD_TYPE = IV_FIELD_TYPE
I_VALUE = IV_VALUE ).
EXIT.
ENDIF.
WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_TIME. "시간
IF IV_FIELD_TYPE IS INITIAL.
IV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '시간' ELSE 'time' ).
ENDIF.
IF LV_VALUE EQ '0'.
LV_VALUE = ''.
ENDIF.
REMOVE_NOT_NUMBER( EXPORTING I_VALUE = LV_VALUE
I_EXCLUDE_SIGN = ' '
IMPORTING E_NUMBER = LV_VALUE ).
IF LV_VALUE IS NOT INITIAL AND STRLEN( LV_VALUE ) NE IS_DFIES-LENG.
"엑셀의 어느 셀 값이 오류인지
GET_CURRENT_CELL_NUMBER( I_COL = IV_COL
I_ROW = IV_ROW
I_ITAB_ROWNO = IV_ITAB_ROWNO
I_ITAB_FNAME = IS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = IS_DFIES-FIELDTEXT
I_FIELD_TYPE = IV_FIELD_TYPE
I_VALUE = IV_VALUE ).
EXIT.
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 IV_FIELD_TYPE IS INITIAL.
IV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '숫자' ELSE 'numeric' ).
ENDIF.
REMOVE_SPECIAL_CHAR( EXPORTING I_VALUE = LV_VALUE
I_EXCLUDE_SIGN = 'X'
IMPORTING E_NUMBER = LV_VALUE ).
WHEN OTHERS.
IF IV_FIELD_TYPE IS INITIAL.
IV_FIELD_TYPE = SWITCH #( SY-LANGU WHEN '3' THEN '문자' ELSE 'character' ).
ENDIF.
"대문자 변환
IF I_ENABLE_CONV_EXIT EQ ABAP_TRUE AND IS_DFIES-LOWERCASE NE 'X' AND IS_DFIES-INTTYPE EQ 'C'.
TRANSLATE LV_VALUE TO UPPER CASE.
ENDIF.
ENDCASE.
"변환 루틴 적용
IF I_ENABLE_CONV_EXIT EQ ABAP_TRUE AND IS_DFIES-CONVEXIT IS NOT INITIAL.
LV_FUNC_NAME = 'CONVERSION_EXIT_' && IS_DFIES-CONVEXIT && '_INPUT'.
TRY.
CALL FUNCTION LV_FUNC_NAME
EXPORTING
INPUT = LV_VALUE
IMPORTING
OUTPUT = CV_OUTPUT
EXCEPTIONS
OTHERS = 1.
CATCH CX_ROOT INTO LV_CX.
MOVE LV_VALUE TO CV_OUTPUT.
SY-SUBRC = 0.
ENDTRY.
IF SY-SUBRC NE 0 AND I_CHECK_CONV_EXIT EQ ABAP_TRUE.
"엑셀의 어느 셀 값이 오류인지
IF SY-MSGID IS NOT INITIAL.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 INTO LV_VALUE.
ENDIF.
GET_CURRENT_CELL_NUMBER( I_COL = IV_COL
I_ROW = IV_ROW
I_ITAB_ROWNO = IV_ITAB_ROWNO
I_ITAB_FNAME = IS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = IS_DFIES-FIELDTEXT
I_FIELD_TYPE = IV_FIELD_TYPE
I_VALUE = LV_VALUE ).
ENDIF.
ELSE.
MOVE LV_VALUE TO CV_OUTPUT.
ENDIF.
CATCH CX_ROOT INTO LV_CX.
"엑셀의 어느 셀 값이 오류인지
GET_CURRENT_CELL_NUMBER( I_COL = IV_COL
I_ROW = IV_ROW
I_ITAB_ROWNO = IV_ITAB_ROWNO
I_ITAB_FNAME = IS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = IS_DFIES-FIELDTEXT
I_FIELD_TYPE = IV_FIELD_TYPE
I_VALUE = IV_VALUE ).
ENDTRY.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->CONV_INTERN_TO_OUTTAB
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_FIELD_ELEMENT_MSG TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [--->] I_ENABLE_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_CHECK_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [--->] I_CHECK_DOMVALUES TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_CHECK_TABVALUES TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] INTERN TYPE TT_ALSMEX_TABLINE
* | [--->] EXCLUDE_BLANK TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [<---] OUTTAB TYPE STANDARD TABLE
* | [EXC!] EXCEL_DATA_ERROR
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONV_INTERN_TO_OUTTAB.
FIELD-SYMBOLS <LFS> TYPE ANY.
DATA: LV_CX TYPE REF TO CX_ROOT,
LV_ERR TYPE STRING,
LV_FIELD_TYPE TYPE T_FIELD_TYPE.
DATA: LV_AMOUNT_INPUT TYPE BAPICURR-BAPICURR,
LV_AMOUNT_OUTPUT TYPE BAPICURR-BAPICURR,
LV_CURRENCY TYPE TCURC-WAERS.
DATA: LT_DD07L TYPE SORTED TABLE OF DD07L WITH NON-UNIQUE KEY DOMNAME DOMVALUE_L DOMVALUE_H,
LR_DOMNAME TYPE RANGE OF DD07L-DOMNAME.
DATA: LD_LINE TYPE REF TO DATA.
FIELD-SYMBOLS: <LFT_OUTTAB> TYPE STANDARD TABLE,
<LFS_OUTTAB> TYPE ANY.
IF OUTTAB IS REQUESTED.
IF GD_OUTTAB IS INITIAL.
GD_OUTTAB = REF #( OUTTAB ).
ENDIF.
GT_DFIES = CL_SALV_DDIC=>GET_BY_DATA( OUTTAB ).
ASSIGN OUTTAB TO <LFT_OUTTAB>.
ELSE.
ASSIGN GD_OUTTAB->* TO <LFT_OUTTAB>.
ENDIF.
IF <LFT_OUTTAB> IS NOT ASSIGNED.
EXIT.
ENDIF.
CREATE DATA LD_LINE LIKE LINE OF <LFT_OUTTAB>.
ASSIGN LD_LINE->* TO <LFS_OUTTAB>.
DATA: LT_DFIES_CURR TYPE DDFIELDS,
LT_DD05P TYPE SORTED TABLE OF DD05P WITH NON-UNIQUE KEY TABNAME FIELDNAME.
CLEAR LT_DFIES_CURR.
CLEAR: GT_ERRORMSG[], GV_XLS_ROWS.
CLEAR <LFT_OUTTAB>.
"도메인 고정값 체크
IF I_CHECK_DOMVALUES EQ ABAP_TRUE.
LR_DOMNAME = VALUE #( FOR LS IN GT_DFIES
WHERE ( VALEXI EQ 'X' AND F4AVAILABL EQ 'X' AND DOMNAME IS NOT INITIAL )
( SIGN = 'I' OPTION = 'EQ' LOW = LS-DOMNAME ) ).
SORT LR_DOMNAME BY LOW. DELETE ADJACENT DUPLICATES FROM LR_DOMNAME COMPARING LOW.
IF LR_DOMNAME[] IS NOT INITIAL.
SELECT
DOMNAME,
DOMVALUE_L,
CASE WHEN DOMVALUE_H EQ @SPACE THEN DOMVALUE_L ELSE DOMVALUE_H END AS DOMVALUE_H
FROM DD07L
WHERE DOMNAME IN @LR_DOMNAME
INTO CORRESPONDING FIELDS OF TABLE @LT_DD07L.
ENDIF.
ENDIF.
"점검테이블 값 체크
IF I_CHECK_TABVALUES EQ ABAP_TRUE.
DATA(LT_DFIES_CHEK) = GT_DFIES.
DELETE LT_DFIES_CHEK WHERE CHECKTABLE IS INITIAL OR NOFORKEY EQ 'X' OR DATATYPE EQ 'CLNT'.
SORT LT_DFIES_CHEK BY TABNAME FIELDNAME.
DELETE ADJACENT DUPLICATES FROM LT_DFIES_CHEK COMPARING TABNAME FIELDNAME.
IF LT_DFIES_CHEK[] IS NOT INITIAL.
SELECT
*
FROM DD05P
FOR ALL ENTRIES IN @LT_DFIES_CHEK
WHERE TABNAME EQ @LT_DFIES_CHEK-TABNAME
AND FIELDNAME EQ @LT_DFIES_CHEK-FIELDNAME
INTO CORRESPONDING FIELDS OF TABLE @LT_DD05P.
ENDIF.
ENDIF.
"금액 필드에 Conversion Input 적용
IF I_ENABLE_CONV_EXIT EQ 'X' AND LINE_EXISTS( GT_DFIES[ DATATYPE = 'CURR' ] ).
LT_DFIES_CURR[] = GT_DFIES[].
DELETE LT_DFIES_CURR WHERE DATATYPE NE 'CURR'.
ENDIF.
SORT INTERN BY ROW COL.
"엑셀 행별로 처리하여 공백까지로 점검
DATA: LV_ROW TYPE TS_ALSMEX_TABLINE-ROW,
LV_COL TYPE TS_ALSMEX_TABLINE-COL,
LV_ITAB_ROWNO TYPE TS_ERROR-ITAB_ROWNO,
LV_VALUE TYPE TS_ALSMEX_TABLINE-VALUE.
LOOP AT INTERN INTO DATA(LS_GROUP_HEAD) GROUP BY ( ROW = LS_GROUP_HEAD-ROW
SIZE = GROUP SIZE
INDEX = GROUP INDEX ) ASCENDING
ASSIGNING FIELD-SYMBOL(<LFS_GROUP>).
CLEAR <LFS_OUTTAB>.
LV_ROW = <LFS_GROUP>-ROW.
LV_ITAB_ROWNO = <LFS_GROUP>-INDEX.
LOOP AT GT_DFIES INTO DATA(LS_DFIES).
LV_COL = SY-TABIX.
CLEAR LV_VALUE.
"엑셀에 데이터가 있나
READ TABLE INTERN INTO DATA(LS_XLS) WITH KEY ROW = LV_ROW
COL = LV_COL BINARY SEARCH.
IF SY-SUBRC EQ 0.
LV_VALUE = LS_XLS-VALUE.
ELSEIF EXCLUDE_BLANK EQ ABAP_FALSE.
LV_VALUE = ''.
ELSE.
CONTINUE.
ENDIF.
UNASSIGN <LFS>.
ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
IF <LFS> IS NOT ASSIGNED.
CONTINUE.
ENDIF.
CLEAR LV_FIELD_TYPE.
IF I_FIELD_ELEMENT_MSG EQ ABAP_TRUE AND LS_DFIES-FIELDTEXT IS NOT INITIAL.
MOVE LS_DFIES-FIELDTEXT TO LV_FIELD_TYPE.
ENDIF.
"필드값 변환
CONV_FIELD( EXPORTING IS_DFIES = LS_DFIES
IV_VALUE = LV_VALUE
IV_ROW = LV_ROW
IV_COL = LV_COL
IV_ITAB_ROWNO = LV_ITAB_ROWNO
IV_FIELD_TYPE = LV_FIELD_TYPE
I_ENABLE_CONV_EXIT = I_ENABLE_CONV_EXIT
I_CHECK_CONV_EXIT = I_CHECK_CONV_EXIT
CHANGING CV_OUTPUT = <LFS> ).
"도메인 값 점검
IF I_CHECK_DOMVALUES EQ ABAP_TRUE AND LS_DFIES-VALEXI EQ 'X' AND LS_DFIES-F4AVAILABL EQ 'X' AND LS_DFIES-DOMNAME IS NOT INITIAL.
DATA LV_IS_OK TYPE C.
CLEAR LV_IS_OK.
LOOP AT LT_DD07L TRANSPORTING NO FIELDS WHERE DOMNAME EQ LS_DFIES-DOMNAME
AND DOMVALUE_L <= <LFS>
AND DOMVALUE_H >= <LFS>.
LV_IS_OK = 'X'.
EXIT.
ENDLOOP.
IF LV_IS_OK NE 'X'.
GET_CURRENT_CELL_NUMBER( I_COL = LV_COL
I_ROW = LV_ROW
I_ITAB_ROWNO = LV_ITAB_ROWNO
I_ITAB_FNAME = LS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = LS_DFIES-FIELDTEXT
I_FIELD_TYPE = LV_FIELD_TYPE
I_DOMNAME = LS_DFIES-DOMNAME
I_VALUE = LV_VALUE ).
ENDIF.
ENDIF.
ENDLOOP.
"금액 필드에 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.
UNASSIGN <LFS>.
ASSIGN COMPONENT LS_DFIES-REFFIELD OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
IF <LFS> IS NOT ASSIGNED.
CONTINUE.
ENDIF.
MOVE <LFS> TO LV_CURRENCY.
UNASSIGN <LFS>.
ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> 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.
"점검테이블 값 점검
IF I_CHECK_TABVALUES EQ ABAP_TRUE.
DATA: LT_WTAB TYPE TABLE OF CHAR255,
LV_ISOK TYPE ABAP_BOOL.
LOOP AT GT_DFIES INTO LS_DFIES WHERE CHECKTABLE IS NOT INITIAL AND NOFORKEY NE 'X' AND DATATYPE NE 'CLNT'.
DATA(LV_FINDEX) = SY-TABIX.
CLEAR LT_WTAB.
LOOP AT LT_DD05P INTO DATA(LS_DD05P) WHERE TABNAME = LS_DFIES-TABNAME AND FIELDNAME = LS_DFIES-FIELDNAME.
UNASSIGN <LFS>.
ASSIGN COMPONENT LS_DD05P-FORKEY OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
IF <LFS> IS ASSIGNED AND <LFS> IS NOT INITIAL.
IF GT_DFIES[ FIELDNAME = LS_DD05P-FORKEY ]-DATATYPE NE 'CLNT'.
APPEND |{ COND #( WHEN LT_WTAB IS NOT INITIAL THEN |AND| ) } { LS_DD05P-CHECKFIELD } EQ '{ <LFS> }'| TO LT_WTAB.
ENDIF.
ENDIF.
ENDLOOP.
IF LT_WTAB IS NOT INITIAL.
SELECT SINGLE
@ABAP_TRUE AS ISOK
FROM (LS_DFIES-CHECKTABLE)
WHERE (LT_WTAB)
INTO @LV_ISOK.
IF SY-SUBRC NE 0.
UNASSIGN <LFS>.
ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
GET_CURRENT_CELL_NUMBER( I_COL = CONV #( LV_FINDEX )
I_ROW = LV_ROW
I_ITAB_ROWNO = LV_ITAB_ROWNO
I_ITAB_FNAME = LS_DFIES-FIELDNAME
I_ITAB_FIELDTEXT = LS_DFIES-FIELDTEXT
I_FIELD_TYPE = CONV #( LS_DFIES-DATATYPE )
I_CHECKTABLE = LS_DFIES-CHECKTABLE
I_VALUE = CONV #( <LFS> ) ).
ENDIF.
ENDIF.
ENDLOOP.
ENDIF.
APPEND <LFS_OUTTAB> TO <LFT_OUTTAB>.
ENDLOOP.
* 구 로직
* LOOP AT INTERN INTO DATA(LS_INTERN).
* AT NEW ROW.
* CLEAR : <LFS_OUTTAB>.
* ENDAT.
*
* READ TABLE GT_DFIES INTO DATA(LS_DFIES) INDEX LS_INTERN-COL.
* IF SY-SUBRC NE 0.
* CONTINUE.
* ENDIF.
*
* UNASSIGN <LFS>.
* ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
* IF <LFS> IS ASSIGNED.
* CLEAR LV_FIELD_TYPE.
*
* IF I_FIELD_ELEMENT_MSG EQ ABAP_TRUE AND LS_DFIES-FIELDTEXT IS NOT INITIAL.
* MOVE LS_DFIES-FIELDTEXT TO LV_FIELD_TYPE.
* ENDIF.
*
* "필드값 변환
* CONV_FIELD( EXPORTING IS_DFIES = LS_DFIES
* IV_VALUE = LS_INTERN-VALUE
* IV_ROW = LS_INTERN-ROW
* IV_COL = LS_INTERN-COL
* IV_FIELD_TYPE = LV_FIELD_TYPE
* I_ENABLE_CONV_EXIT = I_ENABLE_CONV_EXIT
* I_CHECK_CONV_EXIT = I_CHECK_CONV_EXIT
* CHANGING CV_OUTPUT = <LFS> ).
*
* "도메인 값 점검
* IF I_CHECK_DOMVALUES EQ ABAP_TRUE AND LS_DFIES-VALEXI EQ 'X' AND LS_DFIES-F4AVAILABL EQ 'X' AND LS_DFIES-DOMNAME IS NOT INITIAL.
* DATA LV_IS_OK TYPE C.
* CLEAR LV_IS_OK.
* LOOP AT LT_DD07L TRANSPORTING NO FIELDS WHERE DOMNAME EQ LS_DFIES-DOMNAME
* AND DOMVALUE_L <= <LFS>
* AND DOMVALUE_H >= <LFS>.
* LV_IS_OK = 'X'.
* EXIT.
* ENDLOOP.
* IF LV_IS_OK NE 'X'.
* GET_CURRENT_CELL_NUMBER( I_COL = LS_INTERN-COL
* I_ROW = LS_INTERN-ROW
* I_ITAB_FNAME = LS_DFIES-FIELDNAME
* I_ITAB_FIELDTEXT = LS_DFIES-FIELDTEXT
* I_FIELD_TYPE = LV_FIELD_TYPE
* I_DOMNAME = LS_DFIES-DOMNAME
* I_VALUE = LS_INTERN-VALUE ).
* ENDIF.
* ENDIF.
* 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.
* UNASSIGN <LFS>.
* ASSIGN COMPONENT LS_DFIES-REFFIELD OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
* IF <LFS> IS NOT ASSIGNED.
* CONTINUE.
* ENDIF.
* MOVE <LFS> TO LV_CURRENCY.
*
* UNASSIGN <LFS>.
* ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> 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.
*
* "점검테이블 값 점검
* IF I_CHECK_TABVALUES EQ ABAP_TRUE.
* DATA: LT_WTAB TYPE TABLE OF CHAR255,
* LV_ISOK TYPE ABAP_BOOL.
*
* LOOP AT GT_DFIES INTO LS_DFIES WHERE CHECKTABLE IS NOT INITIAL AND NOFORKEY NE 'X' AND DATATYPE NE 'CLNT'.
* DATA(LV_FINDEX) = SY-TABIX.
* CLEAR LT_WTAB.
*
* LOOP AT LT_DD05P INTO DATA(LS_DD05P) WHERE TABNAME = LS_DFIES-TABNAME AND FIELDNAME = LS_DFIES-FIELDNAME.
* UNASSIGN <LFS>.
* ASSIGN COMPONENT LS_DD05P-FORKEY OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
*
* IF <LFS> IS ASSIGNED AND <LFS> IS NOT INITIAL.
* IF GT_DFIES[ FIELDNAME = LS_DD05P-FORKEY ]-DATATYPE NE 'CLNT'.
* APPEND |{ COND #( WHEN LT_WTAB IS NOT INITIAL THEN |AND| ) } { LS_DD05P-CHECKFIELD } EQ '{ <LFS> }'| TO LT_WTAB.
* ENDIF.
* ENDIF.
* ENDLOOP.
*
* IF LT_WTAB IS NOT INITIAL.
* SELECT SINGLE
* @ABAP_TRUE AS ISOK
* FROM (LS_DFIES-CHECKTABLE)
* WHERE (LT_WTAB)
* INTO @LV_ISOK.
* IF SY-SUBRC NE 0.
* UNASSIGN <LFS>.
* ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_OUTTAB> TO <LFS>.
*
* GET_CURRENT_CELL_NUMBER( I_COL = CONV #( LV_FINDEX )
* I_ROW = LS_INTERN-ROW
* I_ITAB_FNAME = LS_DFIES-FIELDNAME
* I_ITAB_FIELDTEXT = LS_DFIES-FIELDTEXT
* I_FIELD_TYPE = CONV #( LS_DFIES-DATATYPE )
* I_CHECKTABLE = LS_DFIES-CHECKTABLE
* I_VALUE = CONV #( <LFS> ) ).
* ENDIF.
* ENDIF.
* ENDLOOP.
* ENDIF.
*
* APPEND <LFS_OUTTAB> TO <LFT_OUTTAB>.
* ENDAT.
* ENDLOOP.
GV_XLS_ROWS = LINES( <LFT_OUTTAB> ).
IF GT_ERRORMSG[] IS NOT INITIAL.
RAISE EXCEL_DATA_ERROR.
ENDIF.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->CONV_OUTTAB_TO_INTERN
* +-------------------------------------------------------------------------------------------------+
* | [--->] OUTTAB TYPE STANDARD TABLE
* | [--->] EXCLUDE_BLANK TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [<-()] INTERN TYPE TT_ALSMEX_TABLINE
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONV_OUTTAB_TO_INTERN.
DATA: LS_INTERN TYPE TS_ALSMEX_TABLINE.
CLEAR INTERN.
LOOP AT OUTTAB ASSIGNING FIELD-SYMBOL(<LFS>).
CLEAR LS_INTERN.
LS_INTERN-ROW = SY-TABIX.
LOOP AT GT_DFIES INTO DATA(LS_DFIES).
LS_INTERN-COL = SY-TABIX.
ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS> TO FIELD-SYMBOL(<LFV>).
IF <LFV> IS ASSIGNED.
IF EXCLUDE_BLANK EQ ABAP_FALSE OR <LFV> IS NOT INITIAL.
LS_INTERN-VALUE = <LFV>.
APPEND LS_INTERN TO INTERN.
ENDIF.
ENDIF.
ENDLOOP.
ENDLOOP.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_EXCEL_UPDOWN=>CONV_TO_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IT_ITAB TYPE STANDARD TABLE
* | [--->] IT_FIELDCAT TYPE LVC_T_FCAT(optional)
* | [--->] IV_WITHOUT_MANDT TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [<---] EV_XSTRING TYPE XSTRING
* | [<---] EV_SIZE TYPE I
* | [<---] EV_BINTAB TYPE SOLIX_TAB
* +--------------------------------------------------------------------------------------</SIGNATURE>
method CONV_TO_EXCEL.
DATA: LT_FIELDCAT TYPE LVC_T_FCAT.
FIELD-SYMBOLS <LFS_TAB> TYPE ANY TABLE.
CLEAR : EV_XSTRING, EV_SIZE.
DATA(LT_DATA) = REF #( IT_ITAB ).
IF IT_FIELDCAT[] IS INITIAL.
ASSIGN LT_DATA->* TO <LFS_TAB>.
TRY .
CL_SALV_TABLE=>FACTORY(
EXPORTING
LIST_DISPLAY = ABAP_FALSE
IMPORTING
R_SALV_TABLE = DATA(LO_SALV_TABLE)
CHANGING
T_TABLE = <LFS_TAB> ).
CATCH CX_SALV_MSG.
ENDTRY.
IF LO_SALV_TABLE IS NOT INITIAL.
LT_FIELDCAT = CL_SALV_CONTROLLER_METADATA=>GET_LVC_FIELDCATALOG( R_COLUMNS = LO_SALV_TABLE->GET_COLUMNS( )
R_AGGREGATIONS = LO_SALV_TABLE->GET_AGGREGATIONS( ) ).
ENDIF.
ELSE.
LT_FIELDCAT[] = IT_FIELDCAT[].
ENDIF.
IF IV_WITHOUT_MANDT EQ ABAP_TRUE.
DELETE LT_FIELDCAT WHERE DATATYPE EQ 'CLNT'.
ENDIF.
DATA(LO_RESULT_DATA) = CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE( R_DATA = LT_DATA
T_FIELDCATALOG = LT_FIELDCAT ).
CL_SALV_BS_TT_UTIL=>IF_SALV_BS_TT_UTIL~TRANSFORM(
EXPORTING
XML_TYPE = IF_SALV_BS_XML=>C_TYPE_XLSX
XML_VERSION = CL_SALV_BS_A_XML_BASE=>GET_VERSION( )
R_RESULT_DATA = LO_RESULT_DATA
XML_FLAVOUR = IF_SALV_BS_C_TT=>C_TT_XML_FLAVOUR_EXPORT
GUI_TYPE = IF_SALV_BS_XML=>C_GUI_TYPE_GUI
IMPORTING
XML = EV_XSTRING ).
IF EV_BINTAB IS REQUESTED.
EV_SIZE = XSTRLEN( EV_XSTRING ).
EV_BINTAB = CL_BCS_CONVERT=>XSTRING_TO_SOLIX( EV_XSTRING ).
ENDIF.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_EXCEL_UPDOWN=>DOWNLOAD
* +-------------------------------------------------------------------------------------------------+
* | [--->] IT_ITAB TYPE STANDARD TABLE
* | [--->] IV_DIRECTORY TYPE CSEQUENCE(optional)
* | [--->] IV_FILENAME TYPE CSEQUENCE(optional)
* | [--->] IT_FIELDCAT TYPE LVC_T_FCAT(optional)
* | [--->] IV_WITHOUT_MANDT TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [--->] IV_AUTO_OPEN TYPE C (default =SPACE)
* | [<---] EV_DOWNLOAD_FILENAME TYPE CSEQUENCE
* | [<---] EV_DOWNLOAD_PATH TYPE CSEQUENCE
* | [EXC!] DOWNLOAD_ERROR
* | [EXC!] EXTENSION_ERROR
* +--------------------------------------------------------------------------------------</SIGNATURE>
method DOWNLOAD.
DATA: LV_EXTENSION_MASK TYPE STRING,
LV_FILENAME TYPE STRING.
DATA: LT_BINTAB TYPE SOLIX_TAB,
LV_XSTRING TYPE XSTRING,
LV_STRING TYPE STRING,
LV_SIZE TYPE I.
DATA: LV_ACTION TYPE I, "ACTION
LV_PATH TYPE STRING,
LV_FILE_PATH TYPE STRING,
LV_DE_FILENAME TYPE STRING.
CLEAR EV_DOWNLOAD_FILENAME.
IF IV_DIRECTORY IS NOT INITIAL AND IV_FILENAME IS NOT INITIAL.
MOVE IV_FILENAME TO LV_DE_FILENAME.
MOVE IV_DIRECTORY TO LV_PATH.
CONCATENATE LV_PATH LV_DE_FILENAME INTO LV_FILE_PATH.
ELSE.
MOVE IV_FILENAME TO LV_DE_FILENAME.
"확장자
CLEAR LV_EXTENSION_MASK.
IF SY-LANGU EQ '3'.
CONCATENATE LV_EXTENSION_MASK 'Excel 통합문서 (*.xlsx)|*.xlsx' INTO LV_EXTENSION_MASK.
ELSE.
CONCATENATE LV_EXTENSION_MASK 'Excel Workbook (*.xlsx)|*.xlsx' INTO LV_EXTENSION_MASK.
ENDIF.
CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG(
EXPORTING
* WINDOW_TITLE =
* DEFAULT_EXTENSION =
DEFAULT_FILE_NAME = LV_DE_FILENAME
* WITH_ENCODING =
FILE_FILTER = LV_EXTENSION_MASK "CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
INITIAL_DIRECTORY = CONV #( IV_DIRECTORY )
* PROMPT_ON_OVERWRITE = 'X'
CHANGING
FILENAME = LV_FILENAME
PATH = LV_PATH
FULLPATH = LV_FILE_PATH
USER_ACTION = LV_ACTION
* FILE_ENCODING =
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
INVALID_DEFAULT_FILE_NAME = 4
others = 5 ).
IF SY-SUBRC <> 0 OR LV_ACTION = CL_GUI_FRONTEND_SERVICES=>ACTION_CANCEL.
EXIT.
ENDIF.
ENDIF.
CONV_TO_EXCEL( EXPORTING IT_ITAB = IT_ITAB
IT_FIELDCAT = IT_FIELDCAT
IV_WITHOUT_MANDT = IV_WITHOUT_MANDT
IMPORTING EV_XSTRING = LV_XSTRING
EV_SIZE = LV_SIZE
EV_BINTAB = LT_BINTAB ).
"다운로드
CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD(
EXPORTING
BIN_FILESIZE = LV_SIZE
FILENAME = LV_FILE_PATH
FILETYPE = 'BIN'
CHANGING
DATA_TAB = LT_BINTAB
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 NE 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING DOWNLOAD_ERROR.
EXIT.
ENDIF.
IF IV_AUTO_OPEN EQ 'X'.
CL_GUI_FRONTEND_SERVICES=>EXECUTE(
EXPORTING
DOCUMENT = LV_FILE_PATH
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
BAD_PARAMETER = 3
FILE_NOT_FOUND = 4
PATH_NOT_FOUND = 5
FILE_EXTENSION_UNKNOWN = 6
ERROR_EXECUTE_FAILED = 7
SYNCHRONOUS_FAILED = 8
NOT_SUPPORTED_BY_GUI = 9
others = 10 ).
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING DOWNLOAD_ERROR.
ENDIF.
ENDIF.
EV_DOWNLOAD_FILENAME = LV_FILENAME.
EV_DOWNLOAD_PATH = LV_PATH.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->GET_CURRENT_CELL_NUMBER
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_COL TYPE TS_ALSMEX_TABLINE-COL
* | [--->] I_ROW TYPE TS_ALSMEX_TABLINE-ROW
* | [--->] I_ITAB_ROWNO TYPE TS_ERROR-ITAB_ROWNO
* | [--->] I_ITAB_FNAME TYPE LVC_FNAME
* | [--->] I_ITAB_FIELDTEXT TYPE AS4TEXT
* | [--->] I_FIELD_TYPE TYPE TS_ERROR-VALUE_TYPE(optional)
* | [--->] I_DOMNAME TYPE DOMNAME(optional)
* | [--->] I_CHECKTABLE TYPE TABNAME(optional)
* | [--->] I_VALUE TYPE TS_ERROR-VALUE_XLS
* +--------------------------------------------------------------------------------------</SIGNATURE>
method GET_CURRENT_CELL_NUMBER.
DATA: LV_XLS_ROW TYPE I,
LV_XLS_COL(2) TYPE C,
LV_XLS_COLROW(12) TYPE C,
LV_ERR_MSG TYPE TS_ERROR-ERR_MSG.
LV_XLS_ROW = I_ROW + GV_BEGIN_ROW - 1.
LV_XLS_COL = CONV #( CONVERT_I_TO_COLUMN( I_COL + GV_BEGIN_COL - 1 ) ).
LV_XLS_COLROW = LV_XLS_COL && LV_XLS_ROW.
DATA(LV_VALUE) = COND #( WHEN I_VALUE IS INITIAL THEN |' '| ELSE I_VALUE ).
IF I_DOMNAME IS NOT INITIAL.
"도메인이 값이 없음
LV_ERR_MSG = |{ CONDENSE( LV_VALUE ) } does not exist in { I_DOMNAME } Domain values (check entry)|.
I_FIELD_TYPE = I_DOMNAME.
ELSEIF I_CHECKTABLE IS NOT INITIAL.
"점검 테이블 값 없음
LV_ERR_MSG = |{ CONDENSE( LV_VALUE ) } does not exist in { I_CHECKTABLE } Table (check entry)|.
I_FIELD_TYPE = I_CHECKTABLE.
ELSE.
LV_ERR_MSG = |{ CONDENSE( LV_VALUE ) } is not { CONDENSE( I_FIELD_TYPE ) }|.
ENDIF.
GT_ERRORMSG = VALUE #( BASE GT_ERRORMSG
( XLS_ROW = LV_XLS_ROW
XLS_COL = LV_XLS_COL
XLS_COLROW = CONDENSE( LV_XLS_COLROW )
ITAB_ROWNO = I_ITAB_ROWNO
ITAB_COLNO = CONV #( I_COL )
ITAB_FNAME = I_ITAB_FNAME
ITAB_FIELDTEXT = I_ITAB_FIELDTEXT
VALUE_XLS = CONDENSE( I_VALUE )
VALUE_TYPE = CONDENSE( I_FIELD_TYPE )
ERR_MSG = LV_ERR_MSG
) ).
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->LINE_TO_CELL_ESC_SEP
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_SEPARATOR TYPE C(optional)
* | [<-->] I_STRING TYPE TS_SENDERLINE-LINE(optional)
* | [<-->] I_SIC_INT TYPE I(optional)
* | [<-->] I_INTERN_VALUE TYPE TS_ALSMEX_TABLINE-VALUE
* +--------------------------------------------------------------------------------------</SIGNATURE>
method LINE_TO_CELL_ESC_SEP.
DATA: l_int TYPE i,
l_cell_end(2).
FIELD-SYMBOLS: <l_cell> TYPE ANY.
l_cell_end = gc_esc.
l_cell_end+1 = i_separator .
IF i_string CS gc_esc.
i_string = i_string+1.
IF i_string CS l_cell_end.
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 2.
i_sic_int = l_int.
i_string = i_string+l_int.
ELSEIF i_string CS gc_esc.
* letzte Celle
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 1.
i_sic_int = l_int.
i_string = i_string+l_int.
l_int = strlen( i_string ).
IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
ELSE.
* MESSAGE x001(kx) . "was ist mit csv-Format
ENDIF.
ENDIF.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->LINE_TO_CELL_SEPARAT
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_ROW TYPE SY-TABIX
* | [--->] CH_CELL_COL TYPE KCD_EX_COL
* | [--->] I_SEPARATOR TYPE C
* | [--->] I_FDPOS TYPE SY-FDPOS
* | [<-->] I_LINE TYPE TS_SENDERLINE-LINE
* | [<-->] I_INTERN TYPE TT_ALSMEX_TABLINE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD LINE_TO_CELL_SEPARAT.
DATA: L_STRING TYPE TS_SENDERLINE-LINE.
DATA L_SIC_INT TYPE I.
DATA LS_INTERN LIKE LINE OF I_INTERN.
L_SIC_INT = I_FDPOS.
LS_INTERN-ROW = I_ROW.
L_STRING = I_LINE.
LS_INTERN-COL = CH_CELL_COL.
* CSV DATEIEN MIT SEPARATOR IN ZELLE: --> ;"ABC;CD";
IF ( I_SEPARATOR = ';' OR I_SEPARATOR = ',' ) AND
L_STRING(1) = GC_ESC.
LINE_TO_CELL_ESC_SEP( exporting I_SEPARATOR = I_SEPARATOR
changing I_SIC_INT = L_SIC_INT
I_STRING = L_STRING
I_INTERN_VALUE = LS_INTERN-VALUE ).
ELSE.
IF L_SIC_INT > 0.
LS_INTERN-VALUE = I_LINE(L_SIC_INT).
ENDIF.
ENDIF.
IF L_SIC_INT > 0.
APPEND LS_INTERN TO I_INTERN.
ENDIF.
L_SIC_INT = L_SIC_INT + 1.
I_LINE = I_LINE+L_SIC_INT.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->POPUP_ERROR_MSG
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_ERROR_MSG TYPE ANY TABLE
* | [--->] I_START_COLUMN TYPE I (default =1)
* | [--->] I_END_COLUMN TYPE I (default =110)
* | [--->] I_START_LINE TYPE I (default =1)
* | [--->] I_END_LINE TYPE I (default =20)
* +--------------------------------------------------------------------------------------</SIGNATURE>
method POPUP_ERROR_MSG.
DATA: LV_LTEXT TYPE SCRTEXT_L,
LO_COLUMN TYPE REF TO CL_SALV_COLUMN_TABLE.
DATA LT_ERRMSG TYPE TT_ERROR_ALV.
LT_ERRMSG = CORRESPONDING #( I_ERROR_MSG ).
"ALV 생성
CALL METHOD CL_SALV_TABLE=>FACTORY
IMPORTING
R_SALV_TABLE = DATA(LO_SALV)
CHANGING
T_TABLE = LT_ERRMSG.
"ALV의 레이아웃 셋팅(줄무늬 패턴, 헤더 텍스트 등)
DATA(LO_DISPLAY) = LO_SALV->GET_DISPLAY_SETTINGS( ).
* LO_DISPLAY->SET_STRIPED_PATTERN( CL_SALV_DISPLAY_SETTINGS=>TRUE ).
LO_DISPLAY->SET_LIST_HEADER( SWITCH #( SY-LANGU WHEN '3' THEN '엑셀 데이터 오류 내역' ELSE 'Excel error data information' ) ).
"ALV 열(필드카타로그) 설정
DATA(LO_COLUMNS) = LO_SALV->GET_COLUMNS( ).
LO_COLUMNS->SET_OPTIMIZE( ABAP_TRUE ). "열 너비 최적화
LOOP AT LO_COLUMNS->GET( ) INTO DATA(LS_COLUMNS).
CLEAR LV_LTEXT.
LO_COLUMN ?= LO_COLUMNS->GET_COLUMN( LS_COLUMNS-COLUMNNAME ).
CASE LS_COLUMNS-COLUMNNAME.
* WHEN 'XLS_ROW'.
* LO_COLUMN->SET_KEY( ABAP_TRUE ).
* LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '행' ELSE 'Row' ).
* WHEN 'XLS_COL'.
* LO_COLUMN->SET_KEY( ABAP_TRUE ).
* LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '열' ELSE 'Col' ).
WHEN 'XLS_COLROW'.
LO_COLUMN->SET_KEY( ABAP_TRUE ).
LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '셀 번호' ELSE 'Excel Cell' ).
WHEN 'ITAB_ROWNO'.
LO_COLUMN->SET_KEY( ABAP_TRUE ).
LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '테이블 행' ELSE 'Table Row' ).
WHEN 'ITAB_FNAME'.
LO_COLUMN->SET_KEY( ABAP_TRUE ).
WHEN 'ITAB_FIELDTEXT'.
WHEN 'VALUE_XLS'.
LV_LTEXT = SWITCH #( SY-LANGU WHEN '3' THEN '엑셀 값' ELSE 'Value' ).
LO_COLUMN->SET_COLOR( VALUE #( COL = 5 INT = 0 INV = 0 ) ).
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' ).
WHEN OTHERS.
LO_COLUMN->SET_TECHNICAL( ABAP_TRUE ).
ENDCASE.
IF LV_LTEXT IS NOT INITIAL.
LO_COLUMN->SET_LONG_TEXT( CONV #( LV_LTEXT ) ).
LO_COLUMN->SET_MEDIUM_TEXT( CONV #( LV_LTEXT ) ).
LO_COLUMN->SET_SHORT_TEXT( CONV #( LV_LTEXT ) ).
ENDIF.
ENDLOOP.
"ALV 를 팝업으로 실행
LO_SALV->SET_SCREEN_POPUP(
START_COLUMN = I_START_COLUMN
END_COLUMN = I_END_COLUMN
START_LINE = I_START_LINE
END_LINE = I_END_LINE ).
"ALV 표시
LO_SALV->DISPLAY( ).
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->REMOVE_NOT_NUMBER
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_VALUE TYPE ANY(optional)
* | [--->] I_EXCLUDE_SIGN TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [<---] E_NUMBER TYPE ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
method REMOVE_NOT_NUMBER.
DATA : LV_OUTPUT TYPE TS_ALSMEX_TABLINE-VALUE.
MOVE I_VALUE TO LV_OUTPUT.
CONDENSE LV_OUTPUT NO-GAPS.
IF I_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 E_NUMBER.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->REMOVE_SPECIAL_CHAR
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_VALUE TYPE ANY(optional)
* | [--->] I_EXCLUDE_SIGN TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [<---] E_NUMBER TYPE ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
method REMOVE_SPECIAL_CHAR.
DATA : LV_OUTPUT TYPE TS_ALSMEX_TABLINE-VALUE.
MOVE I_VALUE TO LV_OUTPUT.
CONDENSE LV_OUTPUT NO-GAPS.
IF I_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 E_NUMBER.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_EXCEL_UPDOWN->SEPARATED_TO_INTERN_CONVERT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IT_ITAB TYPE TT_SENDERLINE
* | [<-()] ET_ITAB TYPE TT_ALSMEX_TABLINE
* +--------------------------------------------------------------------------------------</SIGNATURE>
method SEPARATED_TO_INTERN_CONVERT.
DATA: L_SIC_TABIX LIKE SY-TABIX,
L_SIC_COL TYPE KCD_EX_COL.
DATA: L_FDPOS LIKE SY-FDPOS.
CLEAR ET_ITAB.
LOOP AT IT_ITAB INTO DATA(LS_ITAB).
L_SIC_TABIX = SY-TABIX.
L_SIC_COL = 0.
WHILE LS_ITAB CA GC_SEPARATOR.
L_FDPOS = SY-FDPOS.
L_SIC_COL = L_SIC_COL + 1.
LINE_TO_CELL_SEPARAT(
exporting
I_ROW = L_SIC_TABIX
CH_CELL_COL = L_SIC_COL
I_SEPARATOR = GC_SEPARATOR
I_FDPOS = L_FDPOS
changing
I_LINE = LS_ITAB-LINE
I_INTERN = ET_ITAB
).
ENDWHILE.
IF LS_ITAB NE SPACE.
APPEND VALUE #( ROW = L_SIC_TABIX
COL = L_SIC_COL + 1
VALUE = LS_ITAB ) TO ET_ITAB.
ENDIF.
ENDLOOP.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->SPREADSHEET_TO_INTERNAL_TABLE
* +-------------------------------------------------------------------------------------------------+
* | [--->] FILENAME TYPE CSEQUENCE
* | [--->] I_BEGIN_COL TYPE I
* | [--->] I_BEGIN_ROW TYPE I
* | [--->] I_END_COL TYPE I
* | [--->] I_END_ROW TYPE I
* | [<---] INTERN TYPE TT_ALSMEX_TABLINE
* | [EXC!] INCONSISTENT_PARAMETERS
* | [EXC!] UPLOAD_OLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
method SPREADSHEET_TO_INTERNAL_TABLE.
DATA: LT_DATA_TAB TYPE SOLIX_TAB,
LV_FILELENGTH TYPE I,
LV_HEADER TYPE XSTRING,
LS_INTERN LIKE LINE OF INTERN.
FIELD-SYMBOLS <LFT_XLS> TYPE STANDARD TABLE.
CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD(
EXPORTING
FILENAME = CONV #( FILENAME )
FILETYPE = 'BIN'
IMPORTING
FILELENGTH = LV_FILELENGTH
HEADER = LV_HEADER
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.
RAISE INCONSISTENT_PARAMETERS.
ENDIF.
"EXCEL(XSTRING) TO ITAB
TRY.
DATA(LO_EXCEL_REF) = NEW CL_FDT_XL_SPREADSHEET(
DOCUMENT_NAME = FILENAME
XDOCUMENT = CL_BCS_CONVERT=>SOLIX_TO_XSTRING( LT_DATA_TAB ) ) .
CATCH CX_FDT_EXCEL_CORE.
RAISE UPLOAD_OLE.
ENDTRY .
"GET LIST OF WORKSHEETS
LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES(
IMPORTING
WORKSHEET_NAMES = DATA(LT_WORKSHEETS) ).
IF NOT LT_WORKSHEETS IS INITIAL.
DATA(LO_DATA_REF) = LO_EXCEL_REF->IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET(
LT_WORKSHEETS[ 1 ] ).
"NOW YOU HAVE EXCEL WORK SHEET DATA IN DYANMIC INTERNAL TABLE
ASSIGN LO_DATA_REF->* TO <LFT_XLS>.
ENDIF.
"INTERN 형식으로 변환
DATA(LV_ROW_END) = LINES( <LFT_XLS> ).
IF LV_ROW_END > I_END_ROW.
LV_ROW_END = I_END_ROW.
ENDIF.
IF LV_ROW_END < I_BEGIN_ROW.
EXIT.
ENDIF.
DATA(LT_DFIES) = CL_SALV_DDIC=>GET_BY_DATA( <LFT_XLS> ).
LOOP AT <LFT_XLS> ASSIGNING FIELD-SYMBOL(<LFS_XLS>) FROM I_BEGIN_ROW TO LV_ROW_END.
LS_INTERN-ROW = SY-TABIX - I_BEGIN_ROW + 1.
LOOP AT LT_DFIES INTO DATA(LS_DFIES).
DATA(LV_COLNUM) = CONVERT_COLUMN_TO_I( CONV #( LS_DFIES-FIELDNAME ) ).
IF LV_COLNUM >= I_BEGIN_COL AND LV_COLNUM <= I_END_COL.
LS_INTERN-COL = LV_COLNUM - I_BEGIN_COL + 1.
ASSIGN COMPONENT LS_DFIES-FIELDNAME OF STRUCTURE <LFS_XLS> TO FIELD-SYMBOL(<LFV_XLS>).
IF <LFV_XLS> IS ASSIGNED.
MOVE <LFV_XLS> TO LS_INTERN-VALUE.
ENDIF.
APPEND LS_INTERN TO INTERN. CLEAR LS_INTERN-VALUE.
ENDIF.
ENDLOOP.
ENDLOOP.
endmethod.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_UPDOWN->UPLOAD
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_FILENAME TYPE CSEQUENCE(optional)
* | [--->] I_BEGIN_COL TYPE I (default =1)
* | [--->] I_BEGIN_ROW TYPE I (default =1)
* | [--->] I_END_COL TYPE I(optional)
* | [--->] I_END_ROW TYPE I(optional)
* | [--->] I_FIELD_ELEMENT_MSG TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [--->] I_ENABLE_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_CHECK_CONV_EXIT TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [--->] I_CHECK_DOMVALUES TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_CHECK_TABVALUES TYPE ABAP_BOOL (default =ABAP_TRUE)
* | [--->] I_FORCE_UPLOAD_MODE TYPE ABAP_BOOL (default =ABAP_FALSE)
* | [<---] OUTTAB TYPE STANDARD TABLE
* | [<---] INTERN TYPE TT_ALSMEX_TABLINE
* | [EXC!] INCONSISTENT_PARAMETERS
* | [EXC!] UPLOAD_OLE
* | [EXC!] EXCEL_DATA_ERROR
* +--------------------------------------------------------------------------------------</SIGNATURE>
method UPLOAD.
DATA : LV_FILENAME TYPE STRING,
LV_FILE_EXT TYPE STRING,
LV_MAX_ROW TYPE TS_ALSMEX_TABLINE-ROW.
CLEAR: GV_XLS_ROWS, GT_ERRORMSG.
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.
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 NE 0 OR LV_ACTION = CL_GUI_FRONTEND_SERVICES=>ACTION_CANCEL
OR LT_FILETAB[] IS INITIAL.
EXIT.
ENDIF.
LV_FILENAME = LT_FILETAB[ 1 ]-FILENAME.
ELSE.
MOVE I_FILENAME TO LV_FILENAME.
ENDIF.
GV_BEGIN_COL = I_BEGIN_COL.
GV_BEGIN_ROW = I_BEGIN_ROW.
GV_END_COL = I_END_COL.
GV_END_ROW = I_END_ROW.
IF GV_BEGIN_COL IS INITIAL.
GV_BEGIN_COL = 1.
ENDIF.
IF GV_BEGIN_ROW IS INITIAL.
GV_BEGIN_ROW = 1.
ENDIF.
IF GV_END_COL IS INITIAL.
GV_END_COL = 150.
ENDIF.
IF GV_END_ROW IS INITIAL.
"확장자에 따라 최대 행 갯수 결정
LV_MAX_ROW = 9999999.
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'.
GV_END_ROW = 1048576.
ELSE.
GV_END_ROW = 65000.
ENDIF.
IF GV_END_ROW > LV_MAX_ROW.
GV_END_ROW = LV_MAX_ROW.
ENDIF.
ENDIF.
IF GV_BEGIN_ROW > GV_END_ROW OR GV_BEGIN_COL > GV_END_COL.
EXIT.
ENDIF.
DATA LV_IS_ITS TYPE C.
CALL FUNCTION 'GUI_IS_ITS'
IMPORTING
RETURN = LV_IS_ITS.
"엑셀 TO 인터널테이블 함수 호출
IF LV_IS_ITS IS INITIAL AND I_FORCE_UPLOAD_MODE EQ ABAP_FALSE.
ALSM_EXCEL_TO_INTERNAL_TABLE(
exporting
FILENAME = CONV #( LV_FILENAME )
I_BEGIN_COL = GV_BEGIN_COL
I_BEGIN_ROW = GV_BEGIN_ROW
I_END_COL = GV_END_COL
I_END_ROW = GV_END_ROW
importing
INTERN = INTERN
exceptions
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3
).
ELSE. "WEB GUI에서 사용시
SPREADSHEET_TO_INTERNAL_TABLE(
exporting
FILENAME = LV_FILENAME
I_BEGIN_COL = GV_BEGIN_COL
I_BEGIN_ROW = GV_BEGIN_ROW
I_END_COL = GV_END_COL
I_END_ROW = GV_END_ROW
importing
INTERN = INTERN
exceptions
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3
).
ENDIF.
IF SY-SUBRC <> 0.
IF SY-SUBRC EQ 1.
RAISE INCONSISTENT_PARAMETERS.
ELSE.
RAISE UPLOAD_OLE.
ENDIF.
ENDIF.
IF INTERN[] IS INITIAL.
EXIT.
ENDIF.
"Internal Table의 필드 형식으로 엑셀 값 변환
IF OUTTAB IS REQUESTED.
GT_DFIES = CL_SALV_DDIC=>GET_BY_DATA( OUTTAB ).
GD_OUTTAB = REF #( OUTTAB ).
CONV_INTERN_TO_OUTTAB(
exporting
I_FIELD_ELEMENT_MSG = I_FIELD_ELEMENT_MSG
I_ENABLE_CONV_EXIT = I_ENABLE_CONV_EXIT
I_CHECK_CONV_EXIT = I_CHECK_CONV_EXIT
I_CHECK_DOMVALUES = I_CHECK_DOMVALUES
I_CHECK_TABVALUES = I_CHECK_TABVALUES
INTERN = INTERN
exceptions
EXCEL_DATA_ERROR = 1
OTHERS = 2
).
if sy-subrc <> 0.
RAISE EXCEL_DATA_ERROR.
endif.
ELSE.
LOOP AT INTERN INTO DATA(LS_INTERN) GROUP BY ( ROW = LS_INTERN-ROW
SIZE = GROUP SIZE
INDEX = GROUP INDEX ) ASCENDING
ASSIGNING FIELD-SYMBOL(<LFS_GROUP>).
ADD 1 TO GV_XLS_ROWS.
ENDLOOP.
ENDIF.
endmethod.
ENDCLASS.
'ABAP > 소스코드' 카테고리의 다른 글
| [Report] 운영에서 소스코드 편집 프로그램(YEDITPR) (0) | 2024.10.24 |
|---|---|
| [Class] Internal Table/Structure 일괄 Conversion - ZCL_MASS_CONV_ALPHA (0) | 2023.11.22 |
| [Report] 일괄 번역 편집기(MASS_TRANSLATE) (2) | 2023.11.15 |
| [Class] Internal Table 엑셀 다운로드(XLSX 형식) (0) | 2023.11.15 |
| [Form] Report 프로그램에 백그라운드 작업 정보 표시 (0) | 2023.09.20 |
댓글