用OLEDOI实现ABAP导出EXCEL表格

《用OLEDOI实现ABAP导出EXCEL表格》由会员分享，可在线阅读，更多相关《用OLEDOI实现ABAP导出EXCEL表格（11页珍藏版）》请在装配图网上搜索。

1、OLE导出： sap中abap 导出excel的方法很多，ole是其中一种，但其导出速度比较慢，一般只应用于对excel格式有特别要求的场所 REPORT YGL_MYOLE.INCLUDE OLE2INCL.* OLE OBJECTDATA: MYEXCEL TYPE OLE2_OBJECT, MYSHEET TYPE OLE2_OBJECT, MYCELL TYPE OLE2_OBJECT, MYWORKBOOK TYPE OLE2_OBJECT.*创建excel进程CREATE OBJECT MYEXCEL EXCEL.APPLICATION.* 创建工作表CALL METHOD OF

2、MYEXCEL WORKBOOKS = MYWORKBOOK.* 创建sheet并添加到工作表SET PROPERTY OF MYEXCEL SHEETSINNEWWORKBOOK = 1.CALL METHOD OF MYWORKBOOK ADD.* 选中excel中的cell，第一行的第二列。CALL METHOD OF MYEXCEL CELLS = MYCELL EXPORTING #1 = 1 #2 = 2.* 设置被选中的cell的值SET PROTERTY OF MYCELL VALUE = HELLO WORD. 你要输出的内容GET PROPERTY OF MYEXCEL A

3、CTIVESHEET = MYSHEET.GET PROPERTY OF MYEXCEL ACTIVEWORKBOOK = MYWORKBOOK.* 保存文件CALL METHOD OF MYWORKBOOK SAVEAS EXPORTING #1 = C:TMPAA.XLS #2 = 1.CALL METHOD OF MYWORKBOOK CLOSE.* 退出excelCALL METHOD OF MYEXCEL QUIT.* 释放对象FREE OBJECT MYSHEET.FREE OBJECT MYWORKBOOK.FREE OBJECT MYEXCEL. * 实例： TYPE -POO

4、LS: ole2,slis.DEFINE add_field. wa_field-fieldname = &1 . wa_field-reptext_ddic = &2 . wa_field-no_zero = X . 去掉0 * WA_FIELD_EIDT = X. append wa_field to it_field.END -OF -DEFINITION .* 定义变量和字段组 DATA : g_repid TYPE sy-repid,gs_layout TYPE slis_layout_alv,wa_field TYPE slis_fieldcat_alv,it_field TYPE

5、 slis_t_fieldcat_alv.* * OLE EXCEL变量 DATA : excel TYPE ole2_object,workbook TYPE ole2_object,sheet TYPE ole2_object,cell TYPE ole2_object.* CELL1 TYPE OLE2_OBJECT, * COLUMN TYPE OLE2_OBJECT, * RANGE TYPE OLE2_OBJECT, * BORDERS TYPE OLE2_OBJECT, * button TYPE OLE2_OBJECT, * int TYPE OLE2_OBJECT, * FO

6、NT TYPE OLE2_OBJECT. * ROW TYPE OLE2_OBJECT. * 填充单元格 FORM fill_cell USING i j val.* CALL METHOD OF EXCEL COLUMNS = COLUMN. * CALL METHOD OF COLUMN AUTOFIT. 导出EXCEL自动适应宽度 CALL METHOD OF excel CELLS = cell NO FLUSH EXPORTING #1 = i #2 = j. SET PROPERTY OF cell VALUE = val no flush. FREE OBJECT cell NO

7、 FLUSH.* GET PROPERTY OF CELL FONT = FONT. * SET PROPERTY OF FONT BOLD = BOLD . ENDFORM . FILL_CELL * 处理进度条 FORM p_process USING i_processed LIKE sy-tabixi_total LIKE sy-tabix. DATA :i_percent TYPE i , i_text2(72 ) TYPE c , i_times TYPE i , i_proc(10 ) TYPE c . i_times = 0 . i_percent = ( i_processe

8、d * 100 ) DIV i_total. i_times = ( i_percent - STRLEN ( i_proc ) ) DIV 10 . DO i_times TIMES . CONCATENATE i_proc INTO i_proc. CONDENSE i_proc. ENDDO . IF i_percent 0 . WRITE i_percent TO i_text2. CONDENSE i_text2. CONCATENATE i_text2 % i_proc INTO i_text2. ENDIF . CONCATENATE 正在导出EXCEL,目前的进度为 i_tex

9、t2 INTO i_text2 SEPARATED BY space. 合并文本 CONDENSE i_text2. 缩进文本 * 启动指示钟 CALL FUNCTION SAPGUI_PROGRESS_INDICATOR EXPORTING * PERCENTAGE = I_PERCENT text = i_text2 EXCEPTIONS OTHERS = 1 .ENDFORM . P_PROCESS DOI：推荐用DOI方式导数据到Excel，而不要用OLE，DOI是调用SAP自带的类来操作的，可控性更高，并且可以把Excel窗口当作一个片屏幕控件放入屏幕的容器中。 8323c4f/fr

10、ameset.htm 实例： DOI功能很强大，可以做到很多功能，而且可以使用VBA扩展新功能，比如可以做出如下图效果：更多的东西请看在线帮助里The Spreadsheet Interface一章，有详细的结构定义和方法说明。运行报表前首先要在事务代码OAOR里面新建一个Excel模板，然后调用DOI的类把这个程序调出来后写入数据。给一个简单的DOI模板，这个模板是单元格一格一格填充的，其实可以一块一起填充。 *&-*& Report Z_Rocky_TEST*&*&-*& Rocky Wang*& RockyTech (AT) *&-*REPORT z_rocky_test. TABLES

11、: makt.*-* excel related declaring*-*TYPE-POOLS: slis,vrm, sbdst, soi.CONSTANTS document_name(30) VALUE TEST.CONSTANTS inplace VALUE X.DATA: flag .DATA: container TYPE REF TO cl_gui_custom_container, control TYPE REF TO i_oi_container_control, document TYPE REF TO i_oi_document_proxy, spreadsheet TY

12、PE REF TO i_oi_spreadsheet, error TYPE REF TO i_oi_error, errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.CONTROLS: exceldata TYPE TABLEVIEW USING SCREEN 0100. DATA: tablename(10), okcode(15), row(4), column(4), data(39).* spreadsheet interface structures for Excel data input DATA: rangeitem

13、 TYPE soi_range_item.DATA: ranges TYPE soi_range_list.DATA: excel_input TYPE soi_generic_table.DATA: excel_input_wa TYPE soi_generic_item.DATA: initialized(1), retcode TYPE soi_ret_string.DATA: item_url(256), already_done, newname(40).DATA document_type(80).DATA: app TYPE vrm_id, applist TYPE vrm_va

14、lues.DATA: excel(80) VALUE Excel.Sheet.DATA: line_count TYPE i, column_count TYPE i.DATA: ok_code TYPE sy-ucomm, save_ok TYPE sy-ucomm.CLASS c_oi_errors DEFINITION LOAD. DATA: BEGIN OF itab OCCURS 0. INCLUDE STRUCTURE makt.DATA: END OF itab.SELECT-OPTIONS matnr FOR makt-matnr. START-OF-SELECTION. PE

15、RFORM getdata. CALL SCREEN 100.*&-* *& Form getdata*&-* text*-* - p1 text* close_document. FREE document. ENDIF. IF NOT control IS INITIAL. CALL METHOD control-destroy_control. FREE control. ENDIF. LEAVE PROGRAM. WHEN BACK . IF NOT document IS INITIAL. CALL METHOD document-close_document. FREE docum

16、ent. ENDIF. IF NOT control IS INITIAL. CALL METHOD control-destroy_control. FREE control. ENDIF. SET SCREEN 0. quit the program set screen 1000. ENDCASE.ENDMODULE. USER_COMMAND_0100 INPUT *&-*& Form CREATE_BASIC_OBJECTS*&-* text*-* -P_APP_NAME text* -P_CLASSNAME text* -P_CLASSTYPE text* -P_OBJ_KEY t

17、ext* -P_DOCNAME text*-*FORM create_basic_objects USING p_app_name p_classname p_classtype p_obj_key p_docname. CHECK initialized IS INITIAL.* first get the SAP DOI i_oi_container_control interface CALL METHOD c_oi_container_control_creator=get_container_control IMPORTING control = control error = er

18、ror.* check no errors occured CALL METHOD error-raise_message EXPORTING type = E. CREATE OBJECT container EXPORTING container_name = CONTAINER. DATA l_app_name(200). IF p_app_name IS INITIAL. l_app_name = TEST. ELSE. l_app_name = p_app_name. ENDIF. CALL METHOD control-init_control EXPORTING r3_appli

19、cation_name = l_app_name inplace_enabled = inplace inplace_scroll_documents = X parent = container register_on_close_event = X register_on_custom_event = X no_flush = X IMPORTING error = errors.* save error object in collection APPEND errors. CLEAR item_url. DATA: bds_instance TYPE REF TO cl_bds_doc

20、ument_set. DATA: doc_signature TYPE sbdst_signature, wa_doc_signature LIKE LINE OF doc_signature, doc_components TYPE sbdst_components, doc_uris TYPE sbdst_uri, wa_doc_uris LIKE LINE OF doc_uris.*以下三个值为Tcode:OAOR里面新建模板文件的参数 DATA: doc_classname TYPE sbdst_classname VALUE PICTURES, doc_classtype TYPE

21、sbdst_classtype VALUE OT, doc_object_key TYPE sbdst_object_key VALUE ZEXCEL. wa_doc_signature-prop_name = DESCRIPTION. app = excel. IF app = excel. document_type = excel. wa_doc_signature-prop_value = p_docname. ELSE. ENDIF. APPEND wa_doc_signature TO doc_signature. CREATE OBJECT bds_instance. CALL

22、METHOD bds_instance-get_info EXPORTING classname = doc_classname classtype = doc_classtype object_key = doc_object_key CHANGING components = doc_components signature = doc_signature. CALL METHOD bds_instance-get_with_url EXPORTING classname = doc_classname classtype = doc_classtype object_key = doc_

23、object_key CHANGING uris = doc_uris signature = doc_signature. FREE bds_instance. READ TABLE doc_uris INTO wa_doc_uris INDEX 1. item_url = wa_doc_uris-uri.* ask the SAP DOI container for a i_oi_document_proxy for Excel CALL METHOD control-get_document_proxy EXPORTING document_type = Excel.Sheet no_f

24、lush = X IMPORTING document_proxy = document error = errors. APPEND errors.* open a document saved in business document service. CALL METHOD document-open_document EXPORTING open_inplace = inplace document_url = item_url. DATA: has TYPE i. CALL METHOD document-has_spreadsheet_interface EXPORTING no_

25、flush = IMPORTING is_available = has error = errors. APPEND errors. CALL METHOD document-get_spreadsheet_interface EXPORTING no_flush = IMPORTING sheet_interface = spreadsheet error = errors. APPEND errors.* Activate sheet 1 CALL METHOD spreadsheet-select_sheet EXPORTING name = 表整理* NO_FLUSH = IMPOR

26、TING error = errors.* RETCODE = . APPEND errors. LOOP AT errors. CALL METHOD errors-raise_message EXPORTING type = E. ENDLOOP. FREE errors. initialized = X.ENDFORM. CREATE_BASIC_OBJECTS*&-* *& Form output_to_excel*&-* fill the EXCEL sheet*-*FORM output_to_excel. DATA num TYPE i VALUE 1. LOOP AT itab

27、. num = num + sy-tabix. PERFORM fill_cell USING num 1 itab-matnr. PERFORM fill_cell USING num 2 itab-spras. PERFORM fill_cell USING num 3 itab-maktx. PERFORM fill_cell USING num 4 itab-maktg. num = 1. ENDLOOP.ENDFORM. output_to_excel*&-* *& Form FILL_CELL*&-* text*-* -I text* -J text* -VAL text*-*FO

28、RM fill_cell USING i j val. DATA: columns_number TYPE i, rows_number TYPE i. columns_number = 1. rows_number = 1. CALL METHOD spreadsheet-insert_range_dim EXPORTING name = cell no_flush = X top = i left = j rows = rows_number columns = columns_number IMPORTING error = errors. APPEND errors. REFRESH:

29、 ranges, excel_input. rangeitem-name = cell. rangeitem-columns = 1. rangeitem-rows = 1. APPEND rangeitem TO ranges. excel_input_wa-column = 1. excel_input_wa-row = 1. excel_input_wa-value = val. APPEND excel_input_wa TO excel_input.* set data CALL METHOD spreadsheet-set_ranges_data EXPORTING ranges = ranges contents = excel_input no_flush = X IMPORTING error = errors. APPEND errors. CALL METHOD spreadsheet-fit_widest EXPORTING name = space no_flush = X. REFRESH: ranges, excel_input.ENDFORM. fill_cell 2010/05/31/5635607.aspx