OLE导出: sap中abap 导出excel的方法很多,ole是其中一种,但其导出速度比较慢,一般只应用于对excel格式有特别要求的场所
REPORT YGL_MYOLE.
INCLUDE OLE2INCL.
* OLE OBJECT
DATA:
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 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 'ACTIVESHEET' = MYSHEET.
GET PROPERTY OF MYEXCEL 'ACTIVEWORKBOOK' = MYWORKBOOK.
* 保存文件
CALL METHOD OF MYWORKBOOK 'SAVEAS' EXPORTING #1 = 'C:/TMP/AA.XLS' #2 = 1.
CALL METHOD OF MYWORKBOOK 'CLOSE'.
* 推出excel
CALL METHOD OF MYEXCEL 'QUIT'.
* 释放对象
FREE OBJECT MYSHEET.
FREE OBJECT MYWORKBOOK.
FREE OBJECT MYEXCEL.
*******************************************************************************
实例:
TYPE -POOLS: 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 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,
* FONT 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 FLUSH.
* GET PROPERTY OF CELL 'FONT' = FONT.
* SET PROPERTY OF FONT 'BOLD' = BOLD .
ENDFORM . "FILL_CELL
* 处理进度条
FORM p_process USING i_processed LIKE sy-tabix
i_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_processed * 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_text2 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窗口当作一个片屏幕控件放 入屏幕的容器中。
具体请参考:http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm
实例: DOI功能很强大,可以做到很多功能,而且可以使用VBA扩展新功能,比如可以做出如下图效果:
更多的东西请看在线帮助里"The Spreadsheet Interface"一章,有详细的结构定义和方法说明。
运行报表前首先要在事务代码OAOR里面新建一个Excel模板,然后调用DOI的类把这个程序调出来后写入数据。
给一个简单的DOI模板,这个模板是单元格一格一格填充的,其实可以一块一起填充。
*&---------------------------------------------------------------------*
*& Report Z_Rocky_TEST
*&
*&---------------------------------------------------------------------*
*& Rocky Wang
*& RockyTech (AT) 126.com
*& RockTech.yculblog.com
*&---------------------------------------------------------------------*
REPORT z_rocky_test.
TABLES: 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 TYPE 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 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_values.
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.
PERFORM getdata.
CALL SCREEN 100.
*&---------------------------------------------------------------------*
*& Form getdata
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM getdata .
SELECT *
FROM makt
INTO TABLE itab
WHERE makt~matnr IN matnr.
ENDFORM. " getdata
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS 'SA1'.
IF flag = space .
PERFORM create_basic_objects USING '' '' '' '' document_name.
PERFORM output_to_excel.
ENDIF.
ENDMODULE. "STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
flag = 'X'.
save_ok = ok_code.
CLEAR ok_code.
CASE save_ok.
WHEN 'STOP' .
IF NOT document IS INITIAL.
CALL METHOD document->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 document.
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 text
* -->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 = error.
* 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_application_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_document_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 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 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_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_flush = '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_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 = ' '
IMPORTING
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.
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
*----------------------------------------------------------------------*
FORM 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: 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