Excel 파일을 읽는 펑션중 ALSM_EXCEL_TO_INTERNAL_TABLE 이 있다.
이 펑션을 자주 사용하는 편인데 이 펑션의 문제는 한 개의 셀이 50byte가 넘어가면 읽지 못한다.
그래서 수정한 펑션이다.
Source Function : ALSM_EXCEL_TO_INTERNAL_TABLE
FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE .
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" VALUE(FILENAME) LIKE RLGRAP-FILENAME
*" VALUE(I_BEGIN_COL) TYPE I
*" VALUE(I_BEGIN_ROW) TYPE I
*" VALUE(I_END_COL) TYPE I
*" VALUE(I_END_ROW) TYPE I
*" TABLES
*" INTERN STRUCTURE ZALSMEX_TABLINE
*" EXCEPTIONS
*" INCONSISTENT_PARAMETERS
*" UPLOAD_OLE
*"----------------------------------------------------------------------
DATA: EXCEL_TAB TYPE TY_T_SENDER.
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.
* Rckgabewert der Methode "clipboard_export "
* Makro fr 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.
PERFORM SEPARATED_TO_INTERN_CONVERT TABLES EXCEL_TAB INTERN
USING LD_SEPARATOR.
* 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
ENDFUNCTION.
*******************************************************************
* System-defined Include-files. *
*******************************************************************
INCLUDE LZALSMEXTOP. " Global Data
INCLUDE LZALSMEXUXX. " Function Modules
*******************************************************************
* User-defined Include-files (if necessary). *
*******************************************************************
* INCLUDE LAALSMEXF... " Subprograms
* INCLUDE LAALSMEXO... " PBO-Modules
* INCLUDE LAALSMEXI... " PAI-Modules
INCLUDE LZALSMEXF01.
*INCLUDE LALSMEXF01.
*INCLUDE LAALSMEXF01.
*******************************************************************
* INCLUDE LZALSMEXTOP *
*******************************************************************
FUNCTION-POOL ZALSMEX. "MESSAGE-ID ..
TYPE-POOLS: OLE2.
* value of excel-cell
TYPES: TY_D_ITABVALUE TYPE ZALSMEX_TABLINE-VALUE,
* internal table containing the excel data
TY_T_ITAB TYPE ZALSMEX_TABLINE OCCURS 0,
* line type of sender table
BEGIN OF TY_S_SENDERLINE,
LINE(4096) TYPE C,
END OF TY_S_SENDERLINE,
* sender table
TY_T_SENDER TYPE TY_S_SENDERLINE OCCURS 0.
*
CONSTANTS: GC_ESC VALUE '"'.
*----------------------------------------------------------------------*
***INCLUDE LAALSMEXF01 .
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM SEPARATED_TO_INTERN_CONVERT TABLES I_TAB TYPE TY_T_SENDER
I_INTERN TYPE TY_T_ITAB
USING I_SEPARATOR TYPE C.
DATA: L_SIC_TABIX LIKE SY-TABIX,
L_SIC_COL TYPE KCD_EX_COL.
DATA: L_FDPOS LIKE SY-FDPOS.
REFRESH I_INTERN.
LOOP AT I_TAB.
L_SIC_TABIX = SY-TABIX.
L_SIC_COL = 0.
WHILE I_TAB CA I_SEPARATOR.
L_FDPOS = SY-FDPOS.
L_SIC_COL = L_SIC_COL + 1.
PERFORM LINE_TO_CELL_SEPARAT TABLES I_INTERN
USING I_TAB L_SIC_TABIX L_SIC_COL
I_SEPARATOR L_FDPOS.
ENDWHILE.
IF I_TAB <> SPACE.
CLEAR I_INTERN.
I_INTERN-ROW = L_SIC_TABIX.
I_INTERN-COL = L_SIC_COL + 1.
I_INTERN-VALUE = I_TAB.
APPEND I_INTERN.
ENDIF.
ENDLOOP.
ENDFORM. " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
FORM LINE_TO_CELL_SEPARAT TABLES I_INTERN TYPE TY_T_ITAB
USING I_LINE
I_ROW LIKE SY-TABIX
CH_CELL_COL TYPE KCD_EX_COL
I_SEPARATOR TYPE C
I_FDPOS LIKE SY-FDPOS.
DATA: L_STRING TYPE TY_S_SENDERLINE.
DATA L_SIC_INT TYPE I.
CLEAR I_INTERN.
L_SIC_INT = I_FDPOS.
I_INTERN-ROW = I_ROW.
L_STRING = I_LINE.
I_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.
PERFORM LINE_TO_CELL_ESC_SEP USING L_STRING
L_SIC_INT
I_SEPARATOR
I_INTERN-VALUE.
ELSE.
IF L_SIC_INT > 0.
I_INTERN-VALUE = I_LINE(L_SIC_INT).
ENDIF.
ENDIF.
IF L_SIC_INT > 0.
APPEND I_INTERN.
ENDIF.
L_SIC_INT = L_SIC_INT + 1.
I_LINE = I_LINE+L_SIC_INT.
ENDFORM. "line_to_cell_separat
*---------------------------------------------------------------------*
FORM LINE_TO_CELL_ESC_SEP USING I_STRING
I_SIC_INT TYPE I
I_SEPARATOR TYPE C
I_INTERN_VALUE TYPE TY_D_ITABVALUE.
DATA: L_INT TYPE I,
L_CELL_END(2).
FIELD-SYMBOLS: <L_CELL>.
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.
ENDFORM. "line_to_cell_esc_sep
*******************************************************************
* Structure : ZALSMEX_TABLINE *
*******************************************************************
Component | Component Type | Data Type | Length | Decimal Places | Short Description |
ROW | KCD_EX_ROW_N | NUMC | 4 | 0 | Flexible Excel upload: row number |
COL | KCD_EX_COL_N | NUMC | 4 | 0 | Column |
VALUE | CHAR50 | CHAR | 50 | 0 | Comment |
'SAP > Function' 카테고리의 다른 글
날짜 계산 (0) | 2015.11.27 |
---|---|
POPUP관련함수들 (0) | 2010.07.19 |
Call transaction 을 대신하는 펑션 (0) | 2008.02.27 |
기간에 대한 일수를 계산하는 펑션 (0) | 2008.02.27 |