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

拼搏现实的明天。 2024-02-05 18:02 45阅读 0赞

#### 文章目录 ####

*  前言
 *  实现步骤
 *   *  1. 公用参数定义
     *  2. 选择文件保存路径
     *  3. 打开excel模板
     *  4.填充数据
     *  5.保存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的需求，使用以上的方式就可以实现，但一些过程的耗时过长，应考虑优化。