三步搞定ABAP DOI操作EXCEL

ABAP可以使用OLE与DOI两种方式实现操作EXCEL。使用OLE时,每个单元格的值和样式都需要写代码实现,特别是对于不规则的格式, 代码量巨大. 而DOI是从服务器已经上传的EXCEL模板中下载模板然后打开修改实现数据保存。当然,也可以直接创建新的EXCEL文件往里面传递数据并设置格式(这样好像又走回老路了,不是我们要的, OLE与DOI的区别就好像是面向过程与面向对象的区别).

简单来说,DOI操作EXCEL主要有以下几步:

准备工作：上传Excel模板

第一步：下载Excel到本地

第二步：打开Excel文档

第三步: 写入数据

----------------------------------------------------正文-------------------------------------------------------------------------------------

准备工作：上传Excel模板:

1.输入Tcode: smw0 如图所示:

2.选择二进制数据选择,回车后进入查询界面,包: MI

3.点击执行:进入显示列表,点击新建图标:

4.输入对象名称与描述后点击打开文件选择框,选择文件后,会提示选择包,

好了,可以写代码了(复制可以直接运行,前提是已经上传模板),

REPORT zr04_ole_templet.

DATA:c_export_filename_xls TYPE string VALUE ‘ZRRFQ01_1.XLS’, "导出模板默认文件名 ‘数据导入模板’
c_objid_xls TYPE wwwdatatab-objid VALUE ‘ZRRFQ01_1.XLS’. "存放模板的对象id

DATA: lo_objdata LIKE wwwdatatab, "Excel模板对象
ls_destination LIKE rlgrap-filename ,"下载保存的目标路径
lc_path TYPE string, "存储路径
lc_fullpath TYPE string, "文件完整路径
li_rc LIKE sy-subrc. "返回值

DATA: excel_obj TYPE ole2_object,
book_obj TYPE ole2_object,
sheet_obj TYPE ole2_object,
cell_obj TYPE ole2_object.

START-OF-SELECTION.

第一步: 下载Excel到本地
PERFORM download_xls_template.

第二步：打开Excel文档
PERFORM open_excel.

第三步: 写入数据
PERFORM write_excel.

&---------------------------------------------------------------------
*& Form download_xls_template
&---------------------------------------------------------------------

•   text
  

----------------------------------------------------------------------
FORM download_xls_template.

• 获取保存路径
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
  CHANGING
  desktop_directory = lc_path.
  IF lc_path IS INITIAL.
  lc_path = ‘C:\TEMP’.
  ENDIF.
  CONCATENATE lc_path ‘’ c_export_filename_xls INTO lc_fullpath.

• 检查模板是否存在
  SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
  WHERE srtf2 = 0 AND relid = ‘MI’ AND objid = c_objid_xls.
  IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
  MESSAGE e000(zpp001) WITH c_export_filename_xls.
  ENDIF.

• 下载模板
  ls_destination = lc_fullpath.
  CALL FUNCTION ‘DOWNLOAD_WEB_OBJECT’
  EXPORTING
  key = lo_objdata
  destination = ls_destination
  IMPORTING
  rc = li_rc.
  IF li_rc NE 0.
  MESSAGE e001(zpp001) WITH c_export_filename_xls.
  ENDIF.
  ENDFORM. "download_xls_template

&---------------------------------------------------------------------
*& Form open_excel
&---------------------------------------------------------------------

•   text
  

----------------------------------------------------------------------
FORM open_excel.

CREATE OBJECT excel_obj ‘excel.APPLICATION’.
IF sy-subrc NE 0.
MESSAGE ‘EXCEL创建错误’ TYPE ‘S’ DISPLAY LIKE ‘E’.
STOP.
ENDIF.
CALL METHOD OF excel_obj ‘WORKBOOKS’ = book_obj.
SET PROPERTY OF excel_obj ‘VISIBLE’ = 1.
SET PROPERTY OF excel_obj ‘SheetsInNewWorkbook’ = 1.

• 打开excel文件 , (新建使用:CALL METHOD OF book_obj ‘Add’ = sheet_obj)
  CALL METHOD OF book_obj ‘Open’ = sheet_obj
  EXPORTING #1 = ls_destination.
  CALL METHOD OF sheet_obj ‘ACTIVATE’.
  FREE OBJECT sheet_obj. "OK
  ENDFORM. "open_excel

&---------------------------------------------------------------------
*& Form write_excel
&---------------------------------------------------------------------

•   text
  

----------------------------------------------------------------------
FORM write_excel.
DATA: it_spfli LIKE spfli OCCURS 10 WITH HEADER LINE.
DATA: h TYPE i. "行号
*取数据
SELECT * FROM spfli INTO TABLE it_spfli UP TO 10 ROWS.

• 输出Excel表头,自定义格式的列等
  PERFORM fill_cell USING 1 1 1 ‘Flug’(001).
  PERFORM fill_cell USING 1 2 1 ‘Nr’(002).
  PERFORM fill_cell USING 1 3 1 ‘Von’(003).
  PERFORM fill_cell USING 1 4 1 ‘Nach’(004).
  PERFORM fill_cell USING 1 5 1 ‘Zeit’(005).
• 复制数据到Excel,针对固定格式
  LOOP AT it_spfli.
  h = sy-tabix + 1.
  PERFORM fill_cell USING h 1 0 it_spfli-carrid.
  PERFORM fill_cell USING h 2 0 it_spfli-connid.
  PERFORM fill_cell USING h 3 0 it_spfli-cityfrom.
  PERFORM fill_cell USING h 4 0 it_spfli-cityto.
  PERFORM fill_cell USING h 5 0 it_spfli-deptime.
  ENDLOOP.
  FREE OBJECT cell_obj.
  ENDFORM. "write_excel

&---------------------------------------------------------------------
*& Form FILL_CELL
&---------------------------------------------------------------------

• row: 行号,
• col: 列号,
• bold: 字体是否加粗,0,否,1是.
• val: 填充值
  ----------------------------------------------------------------------
  FORM fill_cell USING row col bold val.
  CALL METHOD OF excel_obj ‘CELLS’ = cell_obj
  EXPORTING #1 = row #2 = col.
  SET PROPERTY OF cell_obj ‘VALUE’ = val.
  FREE OBJECT cell_obj.
  ENDFORM. "FILL_CELL

效果图: