sap自动生成凭证（SAP日记-ABAP导出EXCEL的两种方式）

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

REPORT ZTEST1.TABLES: ZWYM. “自定义的一张表TYPE-POOLS:SLIS,ole2. “需要用到OLE2DATA: 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 worksheetSET 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' = sheetEXPORTING#1 = 1.CALL METHOD OF sheet 'ACTIVATE'.SET PROPERTY OF sheet 'NAME' = 'btdata'. “导出的EXCEL表单名FREE OBJECT sheet. "OKDEFINE 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的区别可以参考http://blog.163.com/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中显示ExcelDATA: 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 inputDATA: 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)."存放模板的UrlDATA 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 T001INTO TABLE ITABWHERE 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_NAMEP_CLASSNAMEP_CLASSTYPEP_OBJ_KEYP_DOCNAME.CHECK INITIALIZED IS INITIAL.* 获取SAP DOI的控制器接口CALL METHODC_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROLIMPORTINGCONTROL = CONTROLERROR = ERROR.* check no errors occuredCALL METHOD ERROR->RAISE_MESSAGEEXPORTINGTYPE = 'E'."实例化容器实例CREATE OBJECT CONTAINEREXPORTINGCONTAINER_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_CONTROLEXPORTINGR3_APPLICATION_NAME = L_APP_NAMEINPLACE_ENABLED = INPLACEINPLACE_SCROLL_DOCUMENTS = 'X'PARENT = CONTAINERREGISTER_ON_CLOSE_EVENT = 'X'REGISTER_ON_CUSTOM_EVENT = 'X'NO_FLUSH = 'X'IMPORTINGERROR = ERRORS.* save error object in collectionAPPEND 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_STANDARD',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"获取文档信息EXPORTINGCLASSNAME = DOC_CLASSNAMECLASSTYPE = DOC_CLASSTYPEOBJECT_KEY = DOC_OBJECT_KEYCHANGINGCOMPONENTS = DOC_COMPONENTSSIGNATURE = DOC_SIGNATURE.CALL METHOD BDS_INSTANCE->GET_WITH_URL"获取文档的url地址放到DOC_URIS中EXPORTINGCLASSNAME = DOC_CLASSNAMECLASSTYPE = DOC_CLASSTYPEOBJECT_KEY = DOC_OBJECT_KEYCHANGINGURIS = DOC_URISSIGNATURE = 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_PROXYEXPORTINGDOCUMENT_TYPE = 'Excel.Sheet'NO_FLUSH = 'X'IMPORTINGDOCUMENT_PROXY = DOCUMENTERROR = ERRORS.APPEND ERRORS.* 根据模板地址打开Excel文件CALL METHOD DOCUMENT->OPEN_DOCUMENTEXPORTINGOPEN_INPLACE = INPLACEDOCUMENT_URL = ITEM_URL.DATA: HAS TYPE I.CALL METHOD DOCUMENT->HAS_SPREADSHEET_INTERFACEEXPORTINGNO_FLUSH = ''IMPORTINGIS_AVAILABLE = HASERROR = ERRORS.APPEND ERRORS."获取模板文档的表格分割器接口给SPREADSHEETCALL METHOD DOCUMENT->GET_SPREADSHEET_INTERFACEEXPORTINGNO_FLUSH = ' 'IMPORTINGSHEET_INTERFACE = SPREADSHEETERROR = ERRORS.APPEND ERRORS.*激活第一个sheetCALL METHOD SPREADSHEET->SELECT_SHEETEXPORTINGNAME = '表整理'* NO_FLUSH = ' 'IMPORTINGERROR = 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_DIMEXPORTINGNAME = 'cell'NO_FLUSH = 'X'TOP = ILEFT = JROWS = ROWS_NUMBERCOLUMNS = COLUMNS_NUMBERIMPORTINGERROR = 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 dataCALL METHOD SPREADSHEET->SET_RANGES_DATAEXPORTINGRANGES = RANGESCONTENTS = EXCEL_INPUTNO_FLUSH = 'X'IMPORTINGERROR = ERRORS.APPEND ERRORS.CALL METHOD SPREADSHEET->FIT_WIDESTEXPORTINGNAME = SPACENO_FLUSH = 'X'.REFRESH: RANGES, EXCEL_INPUT.ENDFORM. "fill_cell

最后显示结果

相关参考：

1. http://blog.sina.com.cn/s/blog_4d1570de0100rsxp.html

2. http://wenku.baidu.com/link?url=SrSs_HPDwgVDw4l7p_YoO21bEymUKxDxQlxQ59M80QWj6P4-OyJtEHhGB-0hxahXI8HiL_OKTi6gDfK1uHiSRstKwPHDYbsobyt7dZQGdUe

3 http://blog.chinaunix.net/uid-9308264-id-2005222.html

4 http://www.cnblogs.com/VerySky/articles/3040097.html