SAP将内表的数据导出到本地xls文件abap实例INCLUDEOLE2INCL

目的：将abap中内表的数据导出到本地（local）的xls文件中。

常用的方法有GUI_DOWNLOAD,但是该函数下载的是一些无转换文档，特别是TXT文件的导出。

如果要实现excel文件，并且把字段按照不同列进行输出，需要使用到object：OLE2_OBJECT来实现。

一个实例如下：

INCLUDE OLE2INCL

* ALV呼出
  CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY‘
    EXPORTING
      I_CALLBACK_PROGRAM          = SY-REPID
      I_CALLBACK_PF_STATUS_SET    = ‘PF_STATUS_SET‘
      I_CALLBACK_USER_COMMAND     = ‘PF_USER_COMMAND‘
*     i_callback_html_top_of_page = ‘TOP_OF_PAGE‘
      IS_LAYOUT                   = PS_IN_LAYOUT
      IT_FIELDCAT                 = PT_IN_FIELDCAT
      I_SAVE                      = ‘X‘
    TABLES
      T_OUTTAB                    = GT_ALVDATA
    EXCEPTIONS
      PROGRAM_ERROR               = 1
      OTHERS                      = 2.
  IF SY-SUBRC <> 0.
*   Implement suitable error handling here
    MESSAGE ID SY-MSGID TYPE ‘S‘ NUMBER SY-MSGNO DISPLAY LIKE ‘E‘
                                   WITH SY-MSGV1
                                        SY-MSGV2
                                        SY-MSGV3
                                        SY-MSGV4.
    LEAVE LIST-PROCESSING.
  ENDIF.

ALV自定义按钮处理

*&---------------------------------------------------------------------*
*&      Form  PF_USER_COMMAND
*&---------------------------------------------------------------------*
*       USER_COMMAND
*----------------------------------------------------------------------*
*      -->P_UCOMM      text
*      -->PS_SELFIELD  text
*----------------------------------------------------------------------*
FORM PF_USER_COMMAND USING P_UCOMM     LIKE SY-UCOMM
                           PS_SELFIELD TYPE SLIS_SELFIELD.
  CASE P_UCOMM.
    WHEN ‘DOWN‘.
*     下载数据到xls文件
      PERFORM FRM_DOWNLOAD_DATA.
    WHEN OTHERS.
  ENDCASE.
  PS_SELFIELD-REFRESH = ‘X‘.
ENDFORM.                    "PF_USER_COMMAND

*&---------------------------------------------------------------------*
*&      Form  FRM_DOWNLOAD_DATA
*&---------------------------------------------------------------------*
*       下载数据到xls文件
*----------------------------------------------------------------------*
FORM FRM_DOWNLOAD_DATA .
  DATA: LV_EXCEL       TYPE OLE2_OBJECT,
        LV_SHEET       TYPE OLE2_OBJECT,
        LV_CELL        TYPE OLE2_OBJECT,
        LV_WORKBOOK    TYPE OLE2_OBJECT,
        LV_XLSNAME     TYPE STRING,
        LV_LINE        TYPE I      VALUE 1, "行号
        LV_COLS        TYPE I      VALUE 0, "行号
        LW_ALVDATA     TYPE GTYP_ALVDATA.
* 获取下载文件完整路径
  PERFORM GET_SAVE_PATH CHANGING LV_XLSNAME.
* 启动Excel
  CREATE OBJECT LV_EXCEL ‘EXCEL.APPLICATION‘.
  IF SY-SUBRC <> 0.
    WRITE: / ‘启动Excel失败。‘.
    STOP.
  ENDIF.
  CALL METHOD OF
      LV_EXCEL
      ‘WORKBOOKS‘ = LV_WORKBOOK.
* 使excel 可视
  SET PROPERTY OF LV_EXCEL ‘VISIBLE‘             = 0. "0：不可视，1：可视
  SET PROPERTY OF LV_EXCEL ‘SHEETSINNEWWORKBOOK‘ = 1.
  "如果是读取excel文件中的内容 则是直接打开工作簿第一页
  CALL METHOD OF
      LV_WORKBOOK
      ‘ADD‘.
* 例如：CALL
*       METHOD OF EXCEL ‘WORKSHEETS‘  = SHEET  EXPORTING #1 = 1.
*  CLEAR LV_LINE.
  LOOP AT GT_ALVDATA INTO LW_ALVDATA.
    LV_COLS = 1.
*   列名的设定
    IF SY-TABIX = 1.
*     设定单元&＃26684;（交货）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘交货‘
                                   LV_COLS.
*     设定单元&＃26684;（物料号）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘物料号‘
                                   LV_COLS.
*     设定单元&＃26684;（交货日期）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘交货日期‘
                                   LV_COLS.
*     设定单元&＃26684;（实际已交货量（按销售单位））
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘实际已交货量（按销售单位）‘
                                   LV_COLS.
*     设定单元&＃26684;-列（售达方）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘售达方‘
                                   LV_COLS.
*     设定单元&＃26684;-列（工厂）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘工厂‘
                                   LV_COLS.
*     设定单元&＃26684;-列（销售单价（SA中的单价））
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘销售单价（SA中的单价）‘
                                   LV_COLS.
*     设定单元&＃26684;-列（销售金额）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘销售金额‘
                                   LV_COLS.
*     设定单元&＃26684;-列（标准价&＃26684;）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘标准价&＃26684;‘
                                   LV_COLS.
*     设定单元&＃26684;-列（标准成本总额）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘标准成本总额‘
                                   LV_COLS.
*     设定单元&＃26684;-列（天数）
      PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                   LV_LINE
                                   ‘天数‘
                                   LV_COLS.
    ENDIF.
    LV_LINE = LV_LINE &＃43; 1.   "Excel 中行号从1开始
    LV_COLS = 1.
*   设定单元&＃26684;（交货）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-VBELN
                                 LV_COLS.
*   设定单元&＃26684;（物料号）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-MATNR
                                 LV_COLS.
*   设定单元&＃26684;（交货日期）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-LFDAT
                                 LV_COLS.
*   设定单元&＃26684;（实际已交货量（按销售单位））
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-LFIMG
                                 LV_COLS.
*   设定单元&＃26684;-列（售达方）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-KUNAG
                                 LV_COLS.
*   设定单元&＃26684;-列（工厂）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-WERKS
                                 LV_COLS.
*   设定单元&＃26684;-列（销售单价（SA中的单价））
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-KBETR
                                 LV_COLS.
*   设定单元&＃26684;-列（销售金额）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-SAL_AMOUNT
                                 LV_COLS.
*   设定单元&＃26684;-列（标准价&＃26684;）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-STPRS
                                 LV_COLS.
*   设定单元&＃26684;-列（标准成本总额）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-SUM_AMOUNT
                                 LV_COLS.
*   设定单元&＃26684;-列（天数）
    PERFORM SET_EXCEL_CELL USING LV_EXCEL
                                 LV_LINE
                                 LW_ALVDATA-ATAGE
                                 LV_COLS.
  ENDLOOP.
  GET PROPERTY OF LV_EXCEL ‘ACTIVESHEET‘    = LV_SHEET.     "激活工作簿
  GET PROPERTY OF LV_EXCEL ‘ACTIVEWORKBOOK‘ = LV_WORKBOOK.  "激活工作区
  CALL METHOD OF
      LV_WORKBOOK
      ‘SAVEAS‘
    EXPORTING
      #1          = LV_XLSNAME
      #2          = 1.
  "将excel文件保存
  CALL METHOD  OF LV_WORKBOOK ‘CLOSE‘.                  "关闭工作区
  CALL METHOD OF
      LV_EXCEL
      ‘QUIT‘.
  "退出excel
*WRITE:/ XLSNAME,‘DONE‘.
  "退出成功，输出done
  FREE OBJECT LV_SHEET.                               "释放操作
  FREE OBJECT LV_WORKBOOK.
  FREE OBJECT LV_EXCEL.
ENDFORM.                    " FRM_DOWNLOAD_DATA
*&---------------------------------------------------------------------*
*&      Form  SET_EXCEL_CELL
*&---------------------------------------------------------------------*
*       设定单元&＃26684;-列
*----------------------------------------------------------------------*
*      -->P_IN_EXCEL     excel对象
*      -->P_IN_LINE      行号
*      -->P_IN_FIELD     项目
*      <--P_OUT_COLS     列号
*----------------------------------------------------------------------*
FORM SET_EXCEL_CELL  USING  P_IN_EXCEL  TYPE OLE2_OBJECT
                            P_IN_LINE   TYPE I
                            P_IN_FIELD  TYPE ANY
                            P_OUT_COLS  TYPE I.
  DATA: LV_CELL   TYPE OLE2_OBJECT.
* 指定单元&＃26684;
  CALL METHOD OF
      P_IN_EXCEL
      ‘CELLS‘    = LV_CELL
    EXPORTING
      #1         = P_IN_LINE
      #2         = P_OUT_COLS.
* 写入&＃20540;
  SET  PROPERTY OF LV_CELL
                   ‘VALUE‘ = P_IN_FIELD.
  P_OUT_COLS = P_OUT_COLS &＃43; 1.
ENDFORM.                    " SET_EXCEL_CELL
*&---------------------------------------------------------------------*
*&      Form  GET_SAVE_PATH
*&---------------------------------------------------------------------*
*       获取下载文件完整路径
*----------------------------------------------------------------------*
*      <--P_IN_PATH  text
*----------------------------------------------------------------------*
FORM GET_SAVE_PATH  CHANGING P_IN_PATH  TYPE STRING.
  DATA: LV_FILENAME   TYPE STRING,
        LV_PATH       TYPE STRING.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
      WINDOW_TITLE         = ‘下载文件‘
      FILE_FILTER          = ‘*.xls‘
    CHANGING
      FILENAME             = LV_FILENAME
      PATH                 = LV_PATH
      FULLPATH             = P_IN_PATH
    EXCEPTIONS
      CNTL_ERROR           = 1
      ERROR_NO_GUI         = 2
      NOT_SUPPORTED_BY_GUI = 3
      OTHERS               = 4.
  IF SY-SUBRC <> 0.
*   Implement suitable error handling here
  ENDIF.
ENDFORM.                    " GET_SAVE_PATH

效果图如下：

SAP 将内表的数据导出到本地xls文件 abap实例 INCLUDE OLE2INCL,布布扣,bubuko.com