SAP ABAP-DOI技术输出EXCEL文档（１）
DOI技术是ABAP旯皱镢涛程序编程中常用的一种技术,这种技术在使用中有许多不常用的技巧,灵活使用这种技巧可以让你在编程过程中如虎添翼,简化你的程序逻辑与代码复杂度。本文告诉你如何全屏幕伍啪怪顼显示DOI输出的EXCEL文档。
创建一个空屏幕上,只在屏幕上激活PAI、PBO事件下的FUNCTIONMOUDLE。干嘛非要在屏幕上放一个CONTAINER组件呢,固定后那么难看。完全可以动态创建嘛!动态创建CL_GUI_CONTAINER这个类。动态创建的好处在于,CONTAINER的大小不是固定的,完全随着屏幕的大小而创建。
注意检查屏幕是否支持对象的使用。
OAOR上传模版。其实如果输出的EXCEL比较简单,就可以不使用模版。本文先输出一个简单的EXCEL文件,不使用上传的EXCEL模版。
*&---------------------------------------------------------------------*
*& Report ZTRAINING26 ABAP DOI技术输出EXCEL文档 *
*& *
*&---------------------------------------------------------------------*
*& Created by Xavery Hsueh(薛现军) on 2015-07-26. *
*& *
*&---------------------------------------------------------------------*
REPORT ztraining26 NO STANDARD PAGE HEADING.
*************************************************************************
* 声明数据库表 Declaration of database *
*************************************************************************
TABLES:mara. "物料主数据
*调用相关对象使用的变量
TYPE-POOLS:vrm, sbdst, soi.
*************************************************************************
* 定义结构类型 Define the structure's type *
*************************************************************************
* 结果内表结构
TYPES:BEGIN OF ty_result,
matnr TYPE mara-matnr, "物料编号
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
mtart TYPE mara-mtart,
matkl TYPE mara-matkl,
meins TYPE mara-meins,
prdha TYPE mara-prdha,
maktx TYPE makt-maktx,
END OF ty_result.
TYPES: BEGIN OF ty_docu_descr,
document_name(40),
document_id(64),
END OF ty_docu_descr.
* EXCEL的数据块
TYPES:BEGIN OF ty_excel,
areano(2) TYPE n,
border TYPE c,
top TYPE i,
left TYPE i,
rows TYPE i,
cols TYPE i,
content TYPE soi_generic_table,
END OF ty_excel.
*************************************************************************
* 定义变量与内表 Define the variants and Internal tables *
*************************************************************************
DATA:gt_result TYPE TABLE OF ty_result WITH HEADER LINE. "结果内表
*@---------------------------------------------------------------------*
* SAP Desktop Office Integration Interfaces
DATA: cl_container TYPE REF TO cl_gui_container,
cl_control TYPE REF TO i_oi_container_control,
cl_docu_proxy TYPE REF TO i_oi_document_proxy,
cl_spreadsheet TYPE REF TO i_oi_spreadsheet,
cl_error TYPE REF TO i_oi_error,
cl_errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.
* spreadsheet interface structures for Excel data input
DATA:gs_cellitem TYPE soi_generic_item,
gs_rangeitem TYPE soi_range_item,
gt_ranges TYPE soi_range_list,
gv_initialized TYPE c,
gv_retcode TYPE soi_ret_string,
gt_excel_format TYPE soi_format_table,
gs_format LIKE LINE OF gt_excel_format.
DATA:gt_imt_tab TYPE TABLE OF ty_excel,
gs_imt_tab TYPE ty_excel,
gt_content TYPE soi_generic_table,
gs_content TYPE soi_generic_item,
gv_macro TYPE text100,
gv_sheet TYPE char20,
gv_cell_fit TYPE c VALUE 'X'.
DATA:gv_app TYPE vrm_id,
gt_applist TYPE vrm_values,
gv_excel TYPE text80 VALUE 'Excel.Sheet', "EXCEL的表单
gv_docu_type TYPE text80,
gv_url(256) TYPE c,
gv_has_activex TYPE c,
gv_has TYPE i,
gv_line TYPE i,
gv_col TYPE i,
"字段所在的列数
gv_row TYPE i.
**************************************************************************
宏定义 Define the macro *
*************************************************************************
DEFINE m_value.
clear &1.
&1-key = &2.
&1-text = &3.
append &1.
END-OF-DEFINITION.
* 将数据对应到EXCEL的单元格,并添加到内表中
DEFINE m_excel_cell.
clear gs_content.
gv_col = gv_col + 1.
gs_content-row = gv_row.
gs_content-column = gv_col.
gs_content-value = &1.
shift gs_content-value left deleting leading space.
append gs_content to gt_content.
END-OF-DEFINITION.
*************************************************************************
* 选择屏幕 Customize the selection-screen *
************************************************************************
*SELECTION-SCREEN BEGIN OF BLOCK xavery WITH FRAME TITLE text-001.
SELECT-OPTIONS:s_matnr FOR mara-matnr MEMORY ID mat. "物料编号
SELECTION-SCREEN END OF BLOCK xavery.
*************************************************************************
* 执行程序事件 Executing the program's events
**************************************************************************
INITIALIZATION.
START-OF-SELECTION.
PERFORM sub_query_mara.
END-OF-SELECTION.
PERFORM sub_process_excel.
CALL SCREEN 0100.
*&---------------------------------------------------------------------*
*& Form sub_query_mara
*&---------------------------------------------------------------------*
* 从物料主数据表MARA中取数
*----------------------------------------------------------------------*
FORM sub_query_mara .* 取物料主数据
SELECT mara~matnr
mara~ersda
mara~ernam
mara~mtart
mara~matkl
mara~meins
mara~prdha
makt~maktx
FROM mara INNER JOIN makt ON mara~matnr = makt~matnr
INTO CORRESPONDING FIELDS OF TABLE gt_result
WHERE mara~matnr IN s_matnr AND
makt~spras = sy-langu.
ENDFORM.
*************************************************************************
* Dialog Modules PBO and PAI
************************************************************************
MODULE status_0100 OUTPUT.
SET PF-STATUS 'XAVERY_HSUEH'.
SET TITLEBAR 'TIT_100' WITH '活动界面示例'.
PERFORM sub_create_basic_object.
PERFORM sub_set_cell_value.
PERFORM sub_set_sheet.
ENDMODULE. "status_0100 OUTPUT
*&---------------------------------------------------------------------*
*& Dialog Modules PAI
*&---------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
LEAVE TO SCREEN 0.
ENDMODULE. "USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
*& Form SUB_CREATE_BASIC_OBJECT
*&---------------------------------------------------------------------*
* 创建相关的对象
*----------------------------------------------------------------------*
FORM sub_create_basic_object .
CALL FUNCTION 'GUI_HAS_ACTIVEX'
IMPORTING
return = gv_has_activex.
IF gv_has_activex IS INITIAL.
MESSAGE e007(demoofficeintegratio).
ENDIF.
CHECK gv_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 = cl_control
error = cl_error.
* check no errors occured
CALL METHOD cl_error->raise_message
EXPORTING type = 'E'.
* CONTIANER初始化
cl_container = cl_gui_container=>screen0.
* initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
CALL METHOD cl_control->init_control
EXPORTING r3_application_name = 'RBasis' "#EC NOTEXT
inplace_enabled = 'X'
inplace_scroll_documents = 'X'
parent = cl_container
register_on_close_event = 'X'
register_on_custom_event = 'X'
no_flush = 'X'
IMPORTING
error = cl_errors.
* save error object in collection
APPEND cl_errors.
CALL METHOD cl_gui_cfw=>dispatch.
* ask the SAP DOI container for a i_oi_document_proxy for Excel
CALL METHOD cl_control->get_document_proxy
EXPORTING
document_type = 'Excel.Sheet'
no_flush = 'X'*
REGISTER_CONTAINER = 'X'
IMPORTING
document_proxy = cl_docu_proxy
error = cl_errors.
APPEND cl_errors.
* 创建EXCEL文档
CALL METHOD cl_docu_proxy->create_document
EXPORTING
open_inplace = 'X'
no_flush = 'X'
document_title = '明细表'
IMPORTING
error = cl_errors.
* 检查标识: Whether Interface Is Supported For This Doc. Type
CALL METHOD cl_docu_proxy->has_spreadsheet_interface
EXPORTING
no_flush = 'X'
IMPORTING
is_available = gv_has
error = cl_errors.
APPEND cl_errors.
CALL METHOD cl_docu_proxy->get_spreadsheet_interface
EXPORTING
no_flush = 'X'
IMPORTING
sheet_interface = cl_spreadsheet
error = cl_errors.
APPEND cl_errors.
FREE cl_errors.
gv_initialized = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form sub_process_excel
*&---------------------------------------------------------------------*
* 处理结果内表
*----------------------------------------------------------------------*
FORM sub_process_excel .
* 将表格抬头数据添加到内表
CLEAR gs_imt_tab.
REFRESH gt_content.
gv_col = 0.
gv_row = 1.
m_excel_cell '物料编码'.
m_excel_cell '物料类型'.
m_excel_cell '物料组'.
m_excel_cell '物料描述'.
gs_imt_tab-areano = 1.
gs_imt_tab-border = 'X'.
gs_imt_tab-top = 1. "输出起始单元格行
gs_imt_tab-left = 1. "输出起始单元格列
gs_imt_tab-rows = 1.
gs_imt_tab-cols = 4.
gs_imt_tab-content = gt_content.
APPEND gs_imt_tab TO gt_imt_tab.
* 将表格数据添加到内表
REFRESH gt_content.
gv_row = 0.
LOOP AT gt_result.
gv_col = 0.
gv_row = gv_row + 1.
m_excel_cell gt_result-matnr.
m_excel_cell gt_result-mtart.
m_excel_cell gt_result-matkl.
m_excel_cell gt_result-meins.
m_excel_cell gt_result-maktx.
ENDLOOP.
gv_line = gv_row.
gs_imt_tab-areano = 2.
gs_imt_tab-border = 'X'.
gs_imt_tab-top = 2. "数据从第二行开始
gs_imt_tab-left = 1. "数据从第一列开始
gs_imt_tab-rows = gv_row.
gs_imt_tab-cols = gv_col.
gs_imt_tab-content = gt_content.
APPEND gs_imt_tab TO gt_imt_tab.
CLEAR gs_imt_tab.
REFRESH gt_content.
gv_col = 0.
gv_row = 1.
m_excel_cell '执行日期'.
m_excel_cell sy-datum.
m_excel_cell '登录用户'.
m_excel_cell sy-uname.
gs_imt_tab-areano = 3.
gs_imt_tab-top = gv_line + 2. "输出起始单元格行
gs_imt_tab-left = 1. "输出起始单元格列
gs_imt_tab-rows = 1. "共N行 n = 1
gs_imt_tab-cols = 4. "共N列 n = 4
gs_imt_tab-content = gt_content.
APPEND gs_imt_tab TO gt_imt_tab.
ENDFORM. " sub_process_excel
*&---------------------------------------------------------------------*
*& Form SUB_SET_CELL_VALUE
*&---------------------------------------------------------------------*
* 设置EXCEL文档的单元格的内容*----------------------------------------------------------------------*
FORM sub_set_cell_value .
LOOP AT gt_imt_tab INTO gs_imt_tab.
* 给EXCEL添加数据块
CALL METHOD cl_spreadsheet->insert_range_dim
EXPORTING
name = 'cell'
no_flush = 'X'
top = gs_imt_tab-top
left = gs_imt_tab-left
rows = gs_imt_tab-rows
columns = gs_imt_tab-cols
IMPORTING
error = cl_errors.
APPEND cl_errors.
* 设定输出数据的内容
REFRESH gt_ranges.
gs_rangeitem-name = 'cell'.
gs_rangeitem-columns = gs_imt_tab-cols.
gs_rangeitem-rows = gs_imt_tab-rows.
gs_rangeitem-code = 4.
APPEND gs_rangeitem TO gt_ranges.
* set data
CALL METHOD cl_spreadsheet->set_ranges_data
EXPORTING
ranges = gt_ranges
contents = gs_imt_tab-content
no_flush = 'X'
IMPORTING
error = cl_errors.
APPEND cl_errors.
* 设置 FRAME.
IF gs_imt_tab-border = 'X'.
CALL METHOD cl_spreadsheet->set_frame
EXPORTING
rangename = 'cell'
typ = '127'
color = '1'
no_flush = 'X'
IMPORTING
error = cl_errors.
ENDIF.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SUB_SET_SHEET
*&---------------------------------------------------------------------*
* 设置EXCEL文档的SHEET页的格式
*----------------------------------------------------------------------*
FORM sub_set_sheet .* 自动调整单元格宽度
IF gv_cell_fit = 'X'.
CALL METHOD cl_spreadsheet->fit_widest
EXPORTING
name = space
no_flush = 'X'.
ENDIF.
* 修改WORK SHEET 的名字
gv_sheet = '物料主数据'.
CALL METHOD cl_spreadsheet->set_sheet_name
EXPORTING
newname = gv_sheet
oldname = 'Sheet1'
IMPORTING
error = cl_errors.
ENDFORM. " SUB_SET_SHEET