（原创）自定义抓取特定SHEET页数据的功能模块
利用DOI技术自定义了一个能够抓取EXCEL中特定SHEET页数据的FUNCTION.如下：
FUNCTION源代码：
FUNCTION Z15540_EXCEL_TO_SAP. *"---------------------------------------------------------------------- *"*"本地接口： *" IMPORTING *" VALUE(I_ROW) TYPE INT_5 DEFAULT 2000 *" VALUE(I_FILENAME) TYPE RLGRAP-FILENAME *" REFERENCE(I_HEADERLINE) TYPE CHAR01 DEFAULT 'X' *" REFERENCE(I_SHEET) TYPE INT_5 DEFAULT 1 *" TABLES *" T_DATA TYPE STANDARD TABLE *"---------------------------------------------------------------------- DATA:LV_FILEURL TYPE BDS_URI, LCL_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER, "容器引用对象 LCL_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL, "容器控制器引用对象 LCL_DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY, "对应EXCEL文档层次引用对象 LCL_SHEET TYPE REF TO I_OI_SPREADSHEET, "对应SHEET层次操作的接口引用对象 LCL_ERROR TYPE REF TO I_OI_ERROR, "返回消息 HAS_SPREAD TYPE I, LS_SHEET TYPE ZSHEET. DATA: LCL_STRUCTURE TYPE REF TO CL_ABAP_STRUCTDESCR, LS_RANGE TYPE SOI_RANGE_ITEM, LT_RANGE TYPE TABLE OF SOI_RANGE_ITEM, "为SET_RANGE_DATA方法标明填充数据的范围名及其行列数 LS_CONTENT TYPE SOI_GENERIC_ITEM, LT_CONTENT TYPE TABLE OF SOI_GENERIC_ITEM, "为SET_RANGE_DATA方法提供填充数据 LV_COLUMNS TYPE I, "输出内表列数 LV_LEFT TYPE I, "抓取范围左上角列号 LV_TOP TYPE I, "抓取反复问左上角行号 LV_ROWS TYPE I, LV_SHEET TYPE I, LV_ASSIGN_COL TYPE I. DATA: LT_SHEETNAME TYPE SOI_SHEETS_TABLE, "EXCEL中SHEET名称集合 LS_SHEETNAME TYPE LINE OF SOI_SHEETS_TABLE. DATA LV_COUNT_SHEET TYPE I. FIELD-SYMBOLS:<FS_FIELD>. RANGES LR_SHEET FOR ZSHEET-SHEET. IF I_FILENAME(7) NE 'FILE://'. CONCATENATE 'FILE://' I_FILENAME INTO LV_FILEURL."拼接文件URL ENDIF. LV_ROWS = I_ROW. LV_SHEET = I_SHEET. "实例化控制器 CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL IMPORTING CONTROL = LCL_CONTROL ERROR = LCL_ERROR * RETCODE = . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '实例化控制器失败' TYPE 'E'. ENDIF. "实例化容器 CREATE OBJECT LCL_CONTAINER EXPORTING CONTAINER_NAME = 'CONTAINER1'. . "初始化控制器，绑定容器 CALL METHOD LCL_CONTROL->INIT_CONTROL EXPORTING * DYNPRO_NR = SY-DYNNR * GUI_CONTAINER = ' ' INPLACE_ENABLED = 'X' * INPLACE_MODE = 0 INPLACE_RESIZE_DOCUMENTS = 'X' INPLACE_SCROLL_DOCUMENTS = 'X' * INPLACE_SHOW_TOOLBARS = 'X' NO_FLUSH = 'X' * PARENT_ID = cl_gui_cfw=>dynpro_0 R3_APPLICATION_NAME = 'R/3 TR' * REGISTER_ON_CLOSE_EVENT = ' ' * REGISTER_ON_CUSTOM_EVENT = ' ' * REP_ID = SY-REPID * SHELL_STYLE = 1384185856 PARENT = LCL_CONTAINER * NAME = * AUTOALIGN = 'x' IMPORTING ERROR = LCL_ERROR * RETCODE = * EXCEPTIONS * JAVABEANNOTSUPPORTED = 1 * OTHERS = 2 . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '初始化控制器失败！' TYPE 'E'. ENDIF. "实例化LCL_DOCUMENT CALL METHOD LCL_CONTROL->GET_DOCUMENT_PROXY EXPORTING * DOCUMENT_FORMAT = 'NATIVE' DOCUMENT_TYPE = 'EXCEL.SHEET' "应用类型 NO_FLUSH = 'X' * REGISTER_CONTAINER = ' ' IMPORTING DOCUMENT_PROXY = LCL_DOCUMENT ERROR = LCL_ERROR * RETCODE = . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '实例化LCL_DOCUMENT失败！' TYPE 'E'. ENDIF. "打开EXCEL文件 CALL METHOD LCL_DOCUMENT->OPEN_DOCUMENT EXPORTING * DOCUMENT_TITLE = ' ' DOCUMENT_URL = LV_FILEURL * NO_FLUSH = ' ' OPEN_INPLACE = 'X' OPEN_READONLY = 'X' * PROTECT_DOCUMENT = ' ' * ONSAVE_MACRO = ' ' * STARTUP_MACRO = '' * USER_INFO = IMPORTING ERROR = LCL_ERROR * RETCODE = . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '打开文件夹失败！' TYPE 'E'. ENDIF. "实例化SHEET对象 * IF HAS_SPREAD IS NOT INITIAL. CALL METHOD LCL_DOCUMENT->GET_SPREADSHEET_INTERFACE EXPORTING NO_FLUSH = 'X' IMPORTING ERROR = LCL_ERROR SHEET_INTERFACE = LCL_SHEET * RETCODE = . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '实例化LCL_SHEET失败' TYPE 'E'. ENDIF. * ENDIF. LCL_STRUCTURE ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( T_DATA ). "计算结构的列数 DESCRIBE TABLE LCL_STRUCTURE->COMPONENTS LINES LV_COLUMNS. LV_LEFT = 1. IF I_HEADERLINE IS NOT INITIAL. LV_TOP = 2. ELSE. LV_TOP = 1. ENDIF. "获取EXCEL中的所有SHEET页名称 CALL METHOD LCL_SHEET->GET_SHEETS * EXPORTING * NO_FLUSH = '' * UPDATING = -1 IMPORTING SHEETS = LT_SHEETNAME ERROR = LCL_ERROR * RETCODE = . IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '获取文件SHEET页名称失败！' TYPE 'E'. ENDIF. LOOP AT LT_SHEETNAME INTO LS_SHEETNAME. LV_COUNT_SHEET = LV_COUNT_SHEET + 1. IF LV_COUNT_SHEET = LV_SHEET. ELSE. CONTINUE. ENDIF. "选中SHEET CALL METHOD LCL_SHEET->SELECT_SHEET EXPORTING NAME = LS_SHEETNAME-SHEET_NAME * NO_FLUSH = '' * IMPORTING * ERROR = * RETCODE = . "设置范围,创建一个，名叫RANGE1的范围对象 CALL METHOD LCL_SHEET->INSERT_RANGE_DIM EXPORTING * NO_FLUSH = '' NAME = 'RANGE1' LEFT = LV_LEFT TOP = LV_TOP ROWS = LV_ROWS COLUMNS = LV_COLUMNS * UPDATING = -1 * SHEETNAME = '' * IMPORTING * ERROR = * RETCODE = . CALL METHOD LCL_SHEET->GET_RANGES_NAMES IMPORTING RANGES = LT_RANGE. DELETE LT_RANGE WHERE NAME NE 'RANGE1'. "获取范围数据 CALL METHOD LCL_SHEET->GET_RANGES_DATA * EXPORTING * NO_FLUSH = '' * ALL = '' * UPDATING = -1 * RANGESDEF = IMPORTING CONTENTS = LT_CONTENT ERROR = LCL_ERROR * RETCODE = CHANGING RANGES = LT_RANGE. IF LCL_ERROR->HAS_FAILED IS NOT INITIAL. MESSAGE '获取文件数据失败！' TYPE 'E'. ENDIF. DELETE LT_CONTENT WHERE VALUE IS INITIAL. "转换数据 LOOP AT LT_CONTENT INTO LS_CONTENT. LV_ASSIGN_COL = LS_CONTENT-COLUMN. ASSIGN COMPONENT LV_ASSIGN_COL OF STRUCTURE T_DATA TO <FS_FIELD>. <FS_FIELD> = LS_CONTENT-VALUE. AT END OF ROW. APPEND T_DATA. ENDAT. CLEAR:LS_CONTENT. ENDLOOP. ENDLOOP. "释放SHEET实例 FREE: LCL_SHEET. "关闭文件，释放DOCUMENT是咯 CALL METHOD LCL_DOCUMENT->CLOSE_DOCUMENT. CALL METHOD LCL_DOCUMENT->RELEASE_DOCUMENT. FREE LCL_DOCUMENT. "销毁控制器 CALL METHOD LCL_CONTROL->RELEASE_ALL_DOCUMENTS. CALL METHOD LCL_CONTROL->DESTROY_CONTROL. ENDFUNCTION.
最后给出一个测试程序：
读取目标：可用库存清单（第2个SHEET页）
测试程序代码：
*&---------------------------------------------------------------------* *& Report Z15540_GET_EXCEL *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT Z15540_GET_EXCEL. "数据定义 DATA:BEGIN OF GS_DATA, WERKS TYPE MARD-WERKS, LGORT TYPE MARD-LGORT, MATNR TYPE MARD-MATNR, MAKTX TYPE MAKT-MAKTX, LABST TYPE MARD-LABST, MEINS TYPE MARA-MEINS, END OF GS_DATA. DATA GT_DATA LIKE TABLE OF GS_DATA. DATA LV_FILENAME TYPE RLGRAP-FILENAME. DATA:LT_FILETABLE TYPE FILETABLE WITH HEADER LINE, LV_RC TYPE I. START-OF-SELECTION. CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG * EXPORTING * WINDOW_TITLE = * DEFAULT_EXTENSION = * DEFAULT_FILENAME = * FILE_FILTER = * WITH_ENCODING = * INITIAL_DIRECTORY = * MULTISELECTION = CHANGING FILE_TABLE = LT_FILETABLE[] RC = LV_RC. * USER_ACTION = * FILE_ENCODING = * EXCEPTIONS * FILE_OPEN_DIALOG_FAILED = 1 * CNTL_ERROR = 2 * ERROR_NO_GUI = 3 * NOT_SUPPORTED_BY_GUI = 4 * OTHERS = 5 . IF SY-SUBRC <> 0. * Implement suitable error handling here ENDIF. READ TABLE LT_FILETABLE INDEX 1. IF SY-SUBRC = 0. LV_FILENAME = LT_FILETABLE-FILENAME. ENDIF. CALL FUNCTION 'Z15540_EXCEL_TO_SAP' EXPORTING * I_ROW = 2000 I_FILENAME = LV_FILENAME I_HEADERLINE = '' I_SHEET = 2 TABLES T_DATA = GT_DATA. LOOP AT GT_DATA INTO GS_DATA. WRITE:/ GS_DATA-WERKS,GS_DATA-LGORT,GS_DATA-MATNR,GS_DATA-MAKTX,GS_DATA-LABST,GS_DATA-MEINS. ENDLOOP.
输出结果：
