飞沐网站设计,app的网站域名注册,小夜仿115资源网源码,wordpresscom下载SAP提供了标准的读取EXCEL的函数#xff08;ALSM_EXCEL_TO_INTERNAL_TABLE#xff09;#xff0c;但是此标准函数无法满足对同一EXCEL 进行不同SHEET的数据读取#xff0c;一下方法就是教你如何通过修改程序来实现ALSM_EXCEL_TO_INTERNAL_TABLE读取多个SHEET#xff1b;
…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中的代码修改如下
将代码 GET PROPERTY OF application ACTIVESHEET worksheet.m_message. 替换为 IF sheet_name space.用默认模式GET PROPERTY OF application ACTIVESHEET worksheet.m_message.ELSE.
*--可以实现读取多个sheetCALL METHOD OF application WORKSHEETS worksheetEXPORTING#1 sheet_name.CALL METHOD OF worksheet Activate.m_message.ENDIF. 结果代码如下
FUNCTION alsm_excel_to_internal_table .
*----------------------------------------------------------------------
**Lokale Schnittstelle:
* 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 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 MethodsDEFINE m_message.case sy-subrc.when 0.when 1.message id sy-msgid type sy-msgty number sy-msgnowith sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.when others. raise upload_ole.endcase.END-OF-DEFINITION.* check parametersIF 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 fieldsCLASS cl_abap_char_utilities DEFINITION LOAD.ld_separator cl_abap_char_utilitieshorizontal_tab.* open file in ExcelIF 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.**START BEGIN OF Change By--激活excel表sheet页--02.08.2023 16:57:55-Reagon2008IF sheet_name space.用默认模式GET PROPERTY OF application ACTIVESHEET worksheet.m_message.ELSE.
*--可以实现读取多个sheetCALL METHOD OF application WORKSHEETS worksheetEXPORTING#1 sheet_name.CALL METHOD OF worksheet Activate.m_message.ENDIF.
**End OF Change By--激活excel表sheet页--02.08.2023 16:57:55-Reagon2008* mark whole spread sheetCALL METHOD OF worksheet Cells h_cellEXPORTING #1 i_begin_row #2 i_begin_col.m_message.CALL METHOD OF worksheet Cells h_cell1EXPORTING #1 i_end_row #2 i_end_col.m_message.CALL METHOD OF worksheet RANGE rangeEXPORTING #1 h_cell #2 h_cell1.m_message.CALL METHOD OF range SELECT.m_message.* copy marked area (whole spread sheet) into ClippboardCALL METHOD OF range COPY.m_message.* read clipboard into ABAPCALL METHOD cl_gui_frontend_servicesclipboard_importIMPORTINGdata excel_tabEXCEPTIONScntl_error 1
* ERROR_NO_GUI 2
* NOT_SUPPORTED_BY_GUI 3OTHERS 4.IF sy-subrc 0.MESSAGE a037(alsmex).ENDIF.PERFORM separated_to_intern_convert TABLES excel_tab internUSING ld_separator.* clear clipboardREFRESH excel_tab.CALL METHOD cl_gui_frontend_servicesclipboard_exportIMPORTINGdata excel_tabCHANGINGrc ld_rcEXCEPTIONScntl_error 1
* ERROR_NO_GUI 2
* NOT_SUPPORTED_BY_GUI 3OTHERS 4.* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel processCALL METHOD OF application QUIT.m_message.* Begin of change note 575877
* to kill the Excel process its necessary to free all used objectsFREE 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
五、程序调用代码如下
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_GETEXPORTINGmask ,*.*,*.*.mode O S为保存O为打开IMPORTINGfilename p_p_fileEXCEPTIONSinv_winsys 1no_batch 2selection_cancel 3selection_error 4OTHERS 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_TABLEEXPORTINGfilename p_filei_begin_col 1i_begin_row 2i_end_col 14i_end_row 50000sheet_name sheet_name 指定SHEET名TABLESintern gt_uploadEXCEPTIONSinconsistent_parameters 1upload_ole 2OTHERS 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
