【ABAP】SMW0 + OLE2实现SAP按模板导出EXCEL文件

---

前言

SAP支持使用系统功能导出excel文件，但如果要求以用户的模板来导出对应的文件，就需要另寻出路。这篇博文主要讨论一下OLE这门技术在SAP导出EXCEL方面的效果。

---

实现步骤

使用 Tcode:SMW0 命令，将excel模板文件上传到服务器后继续下面的步骤

1. 公用参数定义

data:  application type ole2_object,
	   workbook    type ole2_object,
       sheet       type ole2_object,
       cell        type ole2_object.
data: l_fullname type string.

2. 选择文件保存路径

form frm_select_path changing p_fullname type string.
  data: l_filename type string,
        l_path type string.
  clear: l_filename, l_path, p_fullname.
  
  "设置默认文件名称
  l_filename = '测试文件.xls'.

  call method cl_gui_frontend_services=>file_save_dialog
    exporting
      default_extension     = 'XLS'
      default_file_name     = l_filename
    changing
      filename              = l_filename
      path  				= l_path
      fullpath  			= p_fullname
    exceptions
      cntl_error  			= 1
      error_no_gui 			= 2
      not_supported_by_gui  = 3
      others 				= 4.
  if sy-subrc <> 0 .
    message s001(00) display like 'E'.
  endif.
  if p_fullname = space.
    "错误逻辑处理
  endif.
endform.

3. 打开excel模板

form frm_create_excel using p_fullname type string.
  create object application 'Excel.Application'. "创建excel对象
  if sy-subrc ne 0.
    message '不能创建Excel对象' type 'E'.
    exit.
  endif.
  set property of application 'VISIBLE' = 0. "设置excel可视化属性 0不可见 1可见
  call method of application 'WORKBOOKS' = workbook. "激活工作簿

  "按照指定路径打开工作簿
  call method of workbook 'OPEN' = workbook
    exporting
      #1 = p_fullname.
  "设置工作表
  call method of application 'WORKSHEETS' = sheet
    exporting
      #1 = 1.
  
  call method of sheet 'SELECT'.
  "激活工作表
  call method of sheet 'ACTIVATE'.
endform.

4.填充数据

以下为举例操作。正式使用时，当模板中需要填写的字段比较多，通过以下的方式进行对应数据填写，速度会极其缓慢，内表数据仅有五十几条在博主的电脑上就会达到两分钟左右，应考虑优化

form frm_fill_data.
  data: l_row type i.
  "模板有表头，所以不是从第一行开始填写
  l_row = 3.
  loop at it_tab into data(is_tab).
    perform fill_cell using l_row 1 is_tab-matnr.
    perform fill_cell using l_row 2 is_tab-maktx.
    perform fill_cell using l_row 3 is_tab-meins.
    perform fill_cell using l_row 4 is_tab-matkl.
    perform fill_cell using l_row 5 is_tab-bismt.
    add 1 to l_row.
    clear is_tab.
  endloop.
endform.

form fill_cell using p_row p_col p_value.
  call method of application 'CELLS' = cell
  	exporting
  	  #1 = p_row
  	  #2 = p_col.
  set property of cell 'VALUE' = p_value.
endform.

5.保存excel

form frm_excel_save.
  get property of application 'ActiveSheet' = sheet.
  get property of application 'ActiveWorkbook' = workbook.
  call method of workbook 'SAVE'.

  set property of application 'VISIBLE' = 1.
  call method of workbook 'OPEN'.
  "或者选择直接关闭
  "set property of application 'VISIBLE' = 0.
  "call method of workbook 'CLOSE'.
  "call method of application 'QUIT'.

  free object sheet.
  free object workbook.
  free object application.
endform.

---

总结

对于按照用户模板导出excel的需求，使用以上的方式就可以实现，但一些过程的耗时过长，应考虑优化。