SAP ABAP EXCEL 导出（多sheet页）

SAP ABAP EXCEL 导出（多sheet页）

前言

有时我们需要将数据分sheet页导出，而SAP的标准导出功能无法满足我们的需求

这时我们就需要自开发导出功能

代码总览

DATA: v_filename  TYPE string,
      wa_excel TYPE ole2_object,
      wa_book  TYPE ole2_object,
      wa_sheet TYPE ole2_object,
      wa_cell  TYPE ole2_object.

DATA: v_index       TYPE i VALUE IS INITIAL,
      v_sheet_index TYPE i VALUE IS INITIAL.

CALL METHOD cl_gui_frontend_services=>directory_browse
  CHANGING
    selected_folder = v_filename.

CONCATENATE v_filename '\ xxx表' '_' sy-datum '_' sy-uzeit
            '.xls'
INTO v_filename.

CREATE OBJECT wa_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF wa_excel 'Visible' = 0.

CALL METHOD OF wa_excel 'Workbooks' = wa_book.
SET PROPERTY OF wa_excel 'SHEETSINNEWWORKBOOK' = 1.

CALL METHOD OF wa_book 'ADD'.

v_sheet_index = 1.

CALL METHOD OF wa_excel 'WORKSHEETS' = wa_sheet
  EXPORTING
    #1 = v_sheet_index.

CALL METHOD OF wa_sheet 'ACTIVATE'.
SET PROPERTY OF wa_sheet 'NAME' = 'sheetname'. 

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL 抬头
  v_index = v_index + 1.
  
  CALL METHOD OF wa_excel 'Cells' = wa_cell
  EXPORTING #1 = v_index #2 = sy-index.
  SET PROPERTY OF wa_cell 'Value' = wa_fieldcat-seltext_l.

ENDLOOP.

GET PROPERTY OF wa_excel 'ActiveWorkbook' = wa_book.
CALL METHOD OF wa_sheet 'SAVEAS'
  EXPORTING
    #1 = v_filename
    #2 = 1.
CALL METHOD OF wa_book 'CLOSE'.
CALL METHOD OF wa_excel 'QUIT'.

MESSAGE '导出 ' && v_filename && ' 已完成！请撒花庆祝！' TYPE 'S'.

代码解析

变量定义

v_filename	导出的文件名
wa_excel	Excel对象
wa_book	工作簿对象
wa_sheet	Sheet对象
wa_cell	单元格对象
v_index	控制 cell 列数
v_sheet_index	控制 sheet 页数

DATA: v_filename  TYPE string,
      wa_excel TYPE ole2_object,
      wa_book  TYPE ole2_object,
      wa_sheet TYPE ole2_object,
      wa_cell  TYPE ole2_object.
DATA: v_index       TYPE i VALUE IS INITIAL,
      v_sheet_index TYPE i VALUE IS INITIAL.

创建 Excel 及 Book 工作簿对象

这里的文件名可放到程序末尾定义

CREATE OBJECT wa_excel ‘EXCEL.APPLICATION’ 创建一个 EXCEL 对象

CALL METHOD OF wa_excel ‘Workbooks’ = wa_book 调用 EXCEL 对象的 Workbooks 方法为该 EXCEL 对象添加一个工作簿对象

CALL METHOD cl_gui_frontend_services=>directory_browse
  CHANGING
    selected_folder = v_filename.

CONCATENATE v_filename '\ xxx表' '_' sy-datum '_' sy-uzeit
            '.xls'
INTO v_filename.

CREATE OBJECT wa_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF wa_excel 'Visible' = 0.

CALL METHOD OF wa_excel 'Workbooks' = wa_book.
SET PROPERTY OF wa_excel 'SHEETSINNEWWORKBOOK' = 1.

CALL METHOD OF wa_book 'ADD'.

新建 sheet 页并填入单元格内容

ALL METHOD OF wa_excel ‘WORKSHEETS’ 调用 EXCEL 的 ‘WORKSHEETS’ 方法传入参数 sheet 页序号为工作簿添加sheet页

CALL METHOD OF wa_sheet ‘ACTIVATE’ 选择当前添加的sheet对象

SET PROPERTY OF wa_sheet ‘NAME’ = ‘sheetname’ 修改当前sheet页名称

这里我们循环 ALV 的列表，将列表名依次添加到我们的 cell 单元格内

CALL METHOD OF wa_excel ‘Cells’ = wa_cell 传入行列获取到指定的单元格对象

SET PROPERTY OF wa_cell ‘Value’ = wa_fieldcat-seltext_l 设置单元格对象的属性 Value 值为我们的 ALV 列表名

v_sheet_index = 1.

CALL METHOD OF wa_excel 'WORKSHEETS' = wa_sheet
  EXPORTING
    #1 = v_sheet_index.

CALL METHOD OF wa_sheet 'ACTIVATE'.
SET PROPERTY OF wa_sheet 'NAME' = 'sheetname'. 

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL 抬头
  v_index = v_index + 1.
  
  CALL METHOD OF wa_excel 'Cells' = wa_cell
EXPORTING #1 = v_index #2 = sy-index.
  SET PROPERTY OF wa_cell 'Value' = wa_fieldcat-seltext_l.

ENDLOOP.

Excel 保存与导出

CALL METHOD OF wa_sheet ‘SAVES’ 调用sheet对象的 SAVES 方法传入文件名保存文件

再依次关闭工作簿并退出EXCEL进程

GET PROPERTY OF wa_excel 'ActiveWorkbook' = wa_book.
CALL METHOD OF wa_sheet 'SAVEAS'
  EXPORTING
    #1 = v_filename
    #2 = 1.
CALL METHOD OF wa_book 'CLOSE'.
CALL METHOD OF wa_excel 'QUIT'.
MESSAGE '导出 ' && v_filename && ' 已完成！请撒花庆祝！' TYPE 'S'.