前言
提示:以下是本篇文章正文内容,下面案例可供参考
一、封装上载函数
基于SAP标准函数ALSM_EXCEL_TO_INTERNAL_TABLE,重新封装。
FUNCTION Z_ALSM_EXCEL_TO_INTERNAL_TABLE .
*"----------------------------------------------------------------------
*"*"本地接口:
*" 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
*" VALUE(I_SHEET_NAME) TYPE CHAR50 OPTIONAL
*" TABLES
*" INTERN STRUCTURE ZBCS_ALSMEX_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.
* 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'.
"WPS无法导入问题
IF SY-SUBRC NE 0.
CREATE OBJECT APPLICATION 'ET.Application'.
ENDIF.
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.
*-----------------------------------------------------------------------*
*--CHANGED BY YANGSEN
*-----------------------------------------------------------------------*
IF I_SHEET_NAME = SPACE."用默认模式
GET PROPERTY OF APPLICATION 'ACTIVESHEET' = WORKSHEET.
M_MESSAGE.
ELSE.
*-->可以实现读取多个sheet
CALL METHOD OF APPLICATION 'WORKSHEETS' = WORKSHEET
EXPORTING
#1 = I_SHEET_NAME.
CALL METHOD OF WORKSHEET 'Activate'.
M_MESSAGE.
ENDIF.
* 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.
*--------------------------------------------------------------------*
"重复从剪切板读取数据
DO 10 TIMES.
* 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).
ELSE.
IF EXCEL_TAB[] IS INITIAL.
WAIT UP TO 1 SECONDS.
ELSE.
EXIT.
ENDIF.
ENDIF.
ENDDO.
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.
二、函数调用
DATA: LT_EXCEL TYPE STANDARD TABLE OF ZBCS_ALSMEX_TABLINE, "导入文件数据内表
LV_ROWNO TYPE I. "导入模板excel行号
CALL FUNCTION 'Z_ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FNAME
I_BEGIN_COL = 1
I_BEGIN_ROW = 5
I_END_COL = 28
I_END_ROW = 65535
TABLES
INTERN = LT_EXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0. "读取EXCEL 数据失败
MESSAGE TEXT-M09 TYPE ZIF_BC_CONS=>CNS_E.
ELSE.
LOOP AT LT_EXCEL INTO DATA(LS_EXCEL).
ASSIGN COMPONENT LS_EXCEL-COL OF STRUCTURE GS_OUT TO FIELD-SYMBOL(<LFS_DATA>).
CHECK SY-SUBRC EQ 0 .
<LFS_DATA> = LS_EXCEL-VALUE.
AT END OF ROW.
APPEND GS_OUT TO GT_OUT.
CLEAR GS_OUT.
ENDAT.
ENDLOOP.
ENDIF.