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 WHERE 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' GT_ITAB-werks.
c_cell line 3. s_cell:'NumberFormatLocal' '@','VALUE' GT_ITAB-J_3ASIZE.
c_cell line 4. s_cell 'VALUE' GT_ITAB-MAKTX.
c_cell line 5. s_cell:'NumberFormatLocal' '@','VALUE' GT_ITAB-J_3AKORD1.
c_cell line 6. s_cell:'NumberFormatLocal' '@','VALUE' GT_ITAB-J_3AKORD2.
ENDLOOP.
ENDFORM.
二、DOI
ABAP 通过DOI可以处理对EXCEL有特殊要求格式的需求。DOI的功能很强大，关于OLE和DOI的区别可以参考blog.163/ecathy@126/blog/static/1651261562010529103946880/的博客内容。打开excel很慢
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.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论