SAP日记-ABAP导出EXCEL的两种方式(OLE,DOI)

SAP日记——ABAP导出EXCEL的两种方法

一、OLE

ABAP通过OLE的方式导出EXCEL，由于数据时一条一条的生成，速度较慢，不适合内表数据较多的场景使用，但是其实现起来比较简单。

REPORT ZTEST1.

TABLES: ZWYM. “自定义的一张表

TYPE-POOLS:SLIS,ole2. “需要用到OLE2

DATA: excel TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

cell TYPE ole2_object.

DATA: application TYPE ole2_object,

book TYPE ole2_object,

books TYPE ole2_object.

DATA: ole_book TYPE ole2_object.

DATA: FILENAME(100) TYPE C VALUE 'D:\data\test.xls'. “定义文件导出的路径

"DATA: GT_ITAB LIKE TABLE OF ZWYM WITH HEADER LINE.

DATA:BEGIN OF GT_ITAB OCCURS 0, “定义内表

INCLUDE STRUCTURE ZWYM.

END OF GT_ITAB.

SELECTION-SCREEN:BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001. “查询条件

SELECT-OPTIONS:S_MATNR FOR ZWYM-MATNR.

SELECTION-SCREEN:END OF BLOCK B1.

START-OF-SELECTION.

PERFORM GET_DATA.

END-OF-SELECTION.

PERFORM gen_excel.

FORM GET_DATA.

SELECT * INTO CORRESPONDING FIELDS OF TABLE GT_ITAB FROM ZWYM W HERE MATNR IN S_MATNR.

ENDFORM. “GET_DATA.

FORM gen_excel.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0 .

MESSAGE '不能生成EXCEL对象,检查系统是否安装 MS EXCEL。' TYPE 'E'. STOP.

ENDIF.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

* Put Excel in background

SET PROPERTY OF excel 'VISIBLE' = 1.

* Create worksheet

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

PERFORM gen_item. “此处调用form gen_item 显示EXCEL明细

GET PROPERTY OF excel 'ActiveSheet' = sheet.

FREE OBJECT sheet.

FREE OBJECT workbook.

GET PROPERTY OF excel 'ActiveWorkbook' = workbook.

CALL METHOD OF workbook 'SAVEAS'

EXPORTING

#1 = filename “保存路径

#2 = 1.

FREE OBJECT sheet.

FREE OBJECT workbook.

FREE OBJECT excel.

ENDFORM. "gen_excel

FORM gen_item.

CALL METHOD OF excel 'WORKSHEETS' = sheet.

CALL METHOD OF sheet 'ADD'.

FREE OBJECT sheet.

CALL METHOD OF excel 'WORKSHEETS' = sheet

EXPORTING

#1 = 1.

CALL METHOD OF sheet 'ACTIVATE'.

SET PROPERTY OF sheet 'NAME' = 'btdata'. “导出的EXCEL表单名

FREE OBJECT sheet. "OK

DEFINE c_cell.

call method of excel 'CELLS' = cell “定义在excel的位置 1为行 2为列

EXPORTING

#1 = &1

#2 = &2.

END-OF-DEFINITION.

DEFINE s_cell.

set property of cell &1 = &2. “填充值 1为VALUE 2为填充内容

END-OF-DEFINITION.

* 写标题行

c_cell 1 1. s_cell 'VALUE' '物料号'.

c_cell 1 2. s_cell 'VALUE' '工厂'.

c_cell 1 3. s_cell 'VALUE' '网格值'.

c_cell 1 4. s_cell 'VALUE' '产品名称'.

c_cell 1 5. s_cell 'VALUE' '颜色'.

c_cell 1 6. s_cell 'VALUE' '尺码'.

DATA: line TYPE i VALUE 1.

* 写内容行

LOOP AT GT_ITAB.

line = line + 1.

c_cell line 1. s_cell:'NumberFormatLocal' '@','VALUE' GT _ITAB-matnr.

c_cell line 2. s_cell:'NumberFormatLocal' '@','VALUE' G T_ITAB-werks.

c_cell line 3. s_cell:'NumberFormatLocal' '@','VALUE' G T_ITAB-J_3ASIZE.

c_cell line 4. s_cell 'VALUE' GT_ITAB-MAKTX.

c_cell line 5. s_cell:'NumberFormatLocal' '@','VALUE' G T_ITAB-J_3AKORD1.

c_cell line 6. s_cell:'NumberFormatLocal' '@','VALUE' G T_ITAB-J_3AKORD2.

ENDLOOP.

ENDFORM.

二、DOI

ABAP 通过DOI可以处理对EXCEL有特殊要求格式的需求。DOI的功能很强大，关于OLE 和DOI的区别可以参考https://www.360docs.net/doc/d619079340.html,/ecathy@126/blog/static/1651261562010529103946880/的博客内容。

DOI实现起来分为两步

1.上载EXCEL模板

TCODE:OAOR

下面的可以不用填写

进去之后如下图：左下角点击“创建”选项卡—>“标准文档类型”—> “Table template”

双击“Table template”弹出文旦导航选择文件

之后在主界面左上角3 会显示上载的模板excel模板如下

之后就是代码

以下是网上下载的代码

可以运行（需要在SE51上面创建一个编号100的屏幕屏幕里面建一个CONTAINER，SE41创建STATUS “SA1”）

REPORT ZTEST2.

TABLES: T001.

TYPE-POOLS: SLIS,VRM, SBDST, SOI."引入必要的类型组

CONSTANTS DOCUMENT_NAME(30) VALUE 'temp'."模板名字

CONSTANTS INPLACE VALUE 'X'."控制参数,在GUI中显示Excel

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."错误信息

* 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)."存放模板的Url

DATA DOCUMENT_TYPE(80)."文档类型

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 T001.

DATA: END OF ITAB.

SELECT-OPTIONS BUKRS FOR T001-BUKRS.

START-OF-SELECTION.

PERFORM GETDATA.

CALL SCREEN 100.

*&------------------------------------------------------------

---------*

*& Form getdata

*&------------------------------------------------------------

---------*

* text

*-------------------------------------------------------------

---------*

* --> p1 text

* <-- p2 text

*-------------------------------------------------------------

---------*

FORM GETDATA .

SELECT *

FROM T001

INTO TABLE ITAB

WHERE BUKRS IN BUKRS.

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 'CANCLE' .

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.

* 获取SAP DOI的控制器接口

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.

"定义BDS实例变量，用于设置文档信息

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 'HRFPM_EXCEL_ST ANDARD',

DOC_CLASSTYPE TYPE SBDST_CLASSTYPE VALUE 'OT',

DOC_OBJECT_KEY TYPE SBDST_OBJECT_KEY VALUE 'TEMP'.

WA_DOC_SIGNATURE-PROP_NAME = 'DESCRIPTION'.

DOCUMENT_TYPE = EXCEL.

WA_DOC_SIGNATURE-PROP_VALUE = P_DOCNAME."赋给文档名字

APPEND WA_DOC_SIGNATURE TO DOC_SIGNATURE."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"获取文档的url地址放到DOC_URIS中

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."获取对象的地址

* 告诉SAP DOI容器开辟一个Excel策略

CALL METHOD CONTROL->GET_DOCUMENT_PROXY EXPORTING

DOCUMENT_TYPE = 'Excel.Sheet'

NO_FLUSH = 'X'

IMPORTING

DOCUMENT_PROXY = DOCUMENT

ERROR = ERRORS.

APPEND ERRORS.

* 根据模板地址打开Excel文件

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.

"获取模板文档的表格分割器接口给SPREADSHEET

CALL METHOD DOCUMENT->GET_SPREADSHEET_INTERFACE EXPORTING

NO_FLUSH = ' '

IMPORTING

SHEET_INTERFACE = SPREADSHEET

ERROR = ERRORS.

APPEND ERRORS.

*激活第一个sheet

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 0.

LOOP AT ITAB.

NUM = SY-TABIX + 2.

PERFORM FILL_CELL USING NUM 1 ITAB-BUKRS."行列值

PERFORM FILL_CELL USING NUM 2 ITAB-SPRAS.

PERFORM FILL_CELL USING NUM 3 ITAB-BUTXT.

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

最后显示结果