SAP ABAP-DOI技术输出EXCEL文档（１）

2025-10-28 20:34:47

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

声明：本网站引用、摘录或转载内容仅供网站访问者交流或参考，不代表本站立场，如存在版权或非法内容，请联系站长删除，联系邮箱：site.kefu@qq.com。