ABAP中读取EXCEL中不同的SHEET数据
作者:侯志宇
SAP提供了标准的读取EXCEL的函数(ALSM_EXCEL_TO_INTERNAL_TABLE),但是此标准函数无法满足对同一EXCEL进行不同SHEET的数据读取,一下方法就是教你如何通过修改程序来实现ALSM_EXCEL_TO_INTERNAL_TABLE读取多个SHEET;
一、拷贝ALSM_EXCEL_TO_INTERNAL_TABLE函数,拷贝时函数组选择自定义函数组
二、将ALSM_EXCEL_TO_INTERNAL_TABLE函数中的LALSMEXTOP中的数据拷贝都你自定义函数的TOP中;
三、将函数ALSM_EXCEL_TO_INTERNAL_TABLE中的
INCLUDE LALSMEXUXX.INCLUDE LALSMEXF01.引用到你自定义的函数中;
四、将ALSM_EXCEL_TO_INTERNAL_TABLE中的代码修改如下:
function ZALSM_EXCEL_TO_INTERNAL_TABLE . data: EXCEL_TAB type TY_T_SENDER. * Makro für Fehlerbehandlung der Methods
* check parameters * Get TAB-sign for separation of fields * open file in Excel *--ADD HOUZHIYU call method of WORKSHEET 'Activate'.
* mark whole spread sheet call method of WORKSHEET 'RANGE' = RANGE * copy marked area (whole spread sheet) into Clippboard * read clipboard into ABAP perform SEPARATED_TO_INTERN_CONVERT tables EXCEL_TAB INTERN * clear clipboard * quit Excel and free ABAP Object - unfortunately, this does not kill * >>>>> Begin of change note 575877 |
五、程序调用代码如下:
*&---------------------------------------------------------------------*
*& Report ZPMI1224
*&开发人:侯志宇
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
report ZPMI1224.
tables: ZZPMTTZSML,"通知书抬头表
ZZPMTTZSSB,"通知书设备表
ZZPMTTZSXM,"通知书项目表
ZZPMTTEMPLATE."模板
data: LT_ZZPMTTZSML like table of ZZPMTTZSML with header line,"通知书抬头表
LT_ZZPMTTZSSB like table of ZZPMTTZSSB with header line,"通知书设备表
LT_ZZPMTTZSXM like table of ZZPMTTZSXM with header line,"通知书项目表
LT_ZZPMTTEMPLATE like table of ZZPMTTEMPLATE with header line."模板
selection-screen begin of block BLOCK1 with frame title TEXT-001.
parameters: P_FILE like RLGRAP-FILENAME obligatory.
parameters: P_BOX type C as checkbox default 'X'."P_BOX为X则是创建,为空则是修改
selection-screen end of block BLOCK1.
*&-------------------------------------------------------------------*
* AT SELECTION-SCREEN
*&-------------------------------------------------------------------*
at selection-screen on value-request for P_FILE.
* 选择文件
perform GET_FILENAME using P_FILE.
start-of-selection.
*---加载文件
*--取得通知书抬头表数据
perform UPOLOAD_FILE tables LT_ZZPMTTZSML using 'ZZPMTTZSML'.
*--取得通知书设备表
perform UPOLOAD_FILE tables LT_ZZPMTTZSSB using 'ZZPMTTZSSB'.
*--取得通知书项目表
perform UPOLOAD_FILE tables LT_ZZPMTTZSXM using 'ZZPMTTZSXM'.
*--取得模板
perform UPOLOAD_FILE tables LT_ZZPMTTEMPLATE using 'ZZPMTTEMPLATE'.
*--->导入数据
perform FRM_SAVE.
*&---------------------------------------------------------------------*
*& Form get_filename
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* 选择文件
*----------------------------------------------------------------------*
form GET_FILENAME using P_P_FILE.
"出现打开文件的对话框
call function 'WS_FILENAME_GET'
exporting
MASK = ',*.*,*.*.'
MODE = 'O' "S为保存,O为打开
importing
FILENAME = P_P_FILE
exceptions
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL = 3
SELECTION_ERROR = 4
others = 5.
endform. "GET_FILENAME
*&---------------------------------------------------------------------*
*& Form UPOLOAD_FILE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* 加载文件
*----------------------------------------------------------------------*
form UPOLOAD_FILE tables LT_INPUT type standard table using SHEET_NAME.
data GT_UPLOAD like table of ALSMEX_TABLINE with header line.
data: LS_I type I.
field-symbols <F>.
call function 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
FILENAME = P_FILE
I_BEGIN_COL = '1'
I_BEGIN_ROW = '2'
I_END_COL = '14'
I_END_ROW = '50000'
SHEET_NAME = SHEET_NAME"指定SHEET名
tables
INTERN = GT_UPLOAD
exceptions
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
others = 3.
if SY-SUBRC = 0.
*--->处理读取出的数据
sort GT_UPLOAD by ROW COL.
loop at GT_UPLOAD.
LS_I = GT_UPLOAD-COL.
assign component LS_I of structure LT_INPUT to <F>.
<F> = GT_UPLOAD-VALUE.
at end of ROW.
append LT_INPUT to LT_INPUT.
clear:LT_INPUT.
endat.
endloop.
else.
write: / 'EXCEL UPLOAD FAILED ', P_FILE, SY-SUBRC.
endif.
endform. " UPOLOAD_FILE
*&---------------------------------------------------------------------*
*& Form FRM_SAVE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* 导入数据
*----------------------------------------------------------------------*
form FRM_SAVE .
endform. " FRM_SAVE