如何采用SMW0存储模板和OLED操作Excel的方式生成Excel单据

啊呵嘿

已于 2022-08-31 14:15:04 修改

阅读量876

点赞数

分类专栏： ABAP ERP SAP 文章标签：制造

于 2022-08-31 14:12:23 首次发布

本文链接：https://blog.csdn.net/weixin_43670776/article/details/126622348

版权

SAP 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

ABAP

3 篇文章 0 订阅

订阅专栏

ERP

3 篇文章 0 订阅

订阅专栏

简介：

在SAP的SD模块中，我们常常要打印一些单据，如发票或者出货单，一般情况下我们会采用smartforms的方式来设计单据模板，然后生成PDF，但是用户此次要求使用如下的excel模板生成一个excel的单据，本文介绍如何生成一个excel格式单据

正文：

首先我们需要通过Tcode SMW0来上传一个我们设计好的单据模板，上传方式如下

选择二进制数据

直接执行

新建一个对象，选择我们已经建好的excel模板上传到服务器即可，文中我先行建立一个较为简单的人员信息模板，用以参考

实际内容如下

然后我们就可以在程式中下载这个excel模板对其操作了，下载代码如下：

FORM fm_down_model USING excel_name .
  DATA:lo_objdata     LIKE wwwdatatab,
       lo_mime        LIKE w3mime,
       ls_destination LIKE rlgrap-filename,
       ls_objnam      TYPE string,
       li_rc          LIKE sy-subrc,
       ls_errtxt      TYPE string.
  DATA:p_objid TYPE wwwdatatab-objid,
       p_dest  LIKE sapb-sappfad.
  gc_path                 = 'D:\'.
  CONCATENATE gc_path  sy-uname '_' sy-datum '_' sy-uzeit '.xls' INTO gc_fullpath.
  p_objid                 = excel_name.
  IF sy-subrc = 0.
    CONCATENATE p_objid '.XLS' INTO ls_objnam.
    CONDENSE ls_objnam NO-GAPS.
    SELECT SINGLE relid objid INTO CORRESPONDING FIELDS OF lo_objdata
      FROM wwwdata  WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.
    IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
      CONCATENATE '模板文件' ls_objnam '不存在，请用TCODE:SMW0进行加载' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'I'.
    ENDIF.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = gc_fullpath     " 'C:\Temp\test************.xls' .
      IMPORTING
        rc          = li_rc.
    IF li_rc NE 0.
      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'E'.
    ENDIF.
  ENDIF.
ENDFORM.                    " FRM_DOWN_MODEL

将excel下载到本地后，我们可以通过abap的oled类对此excel进行操作，写入我们想要写入的数据，打开excel操作如下

FORM fm_openexcel.
  DATA: column  TYPE ole2_object.
*  PERFORM schedule USING '打开文件..' 90 .
  CREATE OBJECT xl_appl 'EXCEL.APPLICATION'.
  SET PROPERTY OF xl_appl 'Visible' = 1.
  CALL METHOD OF
    xl_appl
      'Workbooks' = xl_wkbks.
  CALL METHOD OF
    xl_wkbks
    'Open'
    EXPORTING
      #1 = gc_fullpath
      #2 = 0
      #3 = 0.             "只读参数
  IF sy-subrc <> 0.
    PERFORM free_object.
    MESSAGE e001(00) WITH '打开模板文件失败,请检查参数是否正确!'.
  ENDIF.
  CALL METHOD OF
      xl_appl
      'Worksheets' = xl_sheet
    EXPORTING
      #1           = 'sheet1'.
*  SET PROPERTY OF xl_sheet 'NAME' = 's1'.
  GET PROPERTY OF xl_appl 'ActiveWorkbook' =  xl_wkbks. "必须加上,否则无法保存excel.
ENDFORM.                    "fm_openexcel

打开之后将需要写入的值写入到对应单元格

LOOP AT ITAB."写入单元格数据
    CALL METHOD OF
      xl_appl
      'CELLS' = cell_obj
    EXPORTING
      #1      = ITAB-row
      #2      = ITAB-col.
  SET PROPERTY OF cell_obj 'VALUE' = ITAB-VALUE.
  FREE OBJECT cell_obj.
 ENDLOOP.

最后保存并关闭单元格就可以了

CALL METHOD OF xl_wkbks 'SAVE'."保存
CALL METHOD OF xl_wkbks 'CLOSE'. "关闭工作区
CALL METHOD OF xl_appl 'QUIT'."退出excel

我在表里面填了姓名为jack,年龄为26，结果显示正确

完整代码如下

*&---------------------------------------------------------------------*
*& Report YTESTEXCEL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT YTESTEXCEL.

*定义变量
DATA: gc_filename LIKE rlgrap-filename,
      gc_path     LIKE rlgrap-filename,
      gc_fullpath LIKE rlgrap-filename.
*创建对象实例，用于打开Excel程序
DATA: xl_appl           TYPE ole2_object,
      xl_sheet          TYPE ole2_object,
      xl_init_sheet     TYPE ole2_object,
      xl_template_sheet TYPE ole2_object,
      xl_wkbks          TYPE ole2_object,
      xl_cell           TYPE ole2_object,
      xl_row            TYPE ole2_object,
      cell_obj          TYPE ole2_object,
      borders_obj       TYPE ole2_object,
      font_obj          TYPE ole2_object,
      blankrow          TYPE i VALUE 0,
      currentrow        TYPE i VALUE 0.
 
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-000.
  PARAMETERS: p_name(20).
  PARAMETERS: p_age type I.
SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

          .

  PERFORM fm_down_model USING 'YTESTEXCEL_MOUDLE'.
 "打开Excel
  PERFORM fm_openexcel.
  "写入数据
  PERFORM fm_filldata_into_cell.
  "WRITE: '数据写入完成'.
  MESSAGE '数据加载成功！' TYPE 'S'.
  PERFORM fm_saveexcel.

*----------------------------------------------------------------------*
*& Form  FM_DOWN_MODEL    excel模板下载
*&---------------------------------------------------------------------*
*& text excel_name 传递的是制定模版的名称（上传模版时创建的模版对象号）
*&---------------------------------------------------------------------*
FORM fm_down_model USING excel_name .
  DATA:lo_objdata     LIKE wwwdatatab,
       lo_mime        LIKE w3mime,
       ls_destination LIKE rlgrap-filename,
       ls_objnam      TYPE string,
       li_rc          LIKE sy-subrc,
       ls_errtxt      TYPE string.
  DATA:p_objid TYPE wwwdatatab-objid,
       p_dest  LIKE sapb-sappfad.
  gc_path                 = 'D:\'.
  CONCATENATE gc_path  sy-uname '_' sy-datum '_' sy-uzeit '.xls' INTO gc_fullpath.
  p_objid                 = excel_name.
  IF sy-subrc = 0.
    CONCATENATE p_objid '.XLS' INTO ls_objnam.
    CONDENSE ls_objnam NO-GAPS.
    SELECT SINGLE relid objid INTO CORRESPONDING FIELDS OF lo_objdata
      FROM wwwdata  WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.
    IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
      CONCATENATE '模板文件' ls_objnam '不存在，请用TCODE:SMW0进行加载' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'I'.
    ENDIF.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = gc_fullpath     " 'C:\Temp\test************.xls' .
      IMPORTING
        rc          = li_rc.
    IF li_rc NE 0.
      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'E'.
    ENDIF.
  ENDIF.
ENDFORM.                    " FRM_DOWN_MODEL

*&---------------------------------------------------------------------*
*& Form  fm_openexcel  open excel
*&---------------------------------------------------------------------*
FORM fm_openexcel.
  DATA: column  TYPE ole2_object.
*  PERFORM schedule USING '打开文件..' 90 .
  CREATE OBJECT xl_appl 'EXCEL.APPLICATION'.
  SET PROPERTY OF xl_appl 'Visible' = 1.
  CALL METHOD OF
    xl_appl
      'Workbooks' = xl_wkbks.
  CALL METHOD OF
    xl_wkbks
    'Open'
    EXPORTING
      #1 = gc_fullpath
      #2 = 0
      #3 = 0.             "只读参数
  IF sy-subrc <> 0.
    PERFORM free_object.
    MESSAGE e001(00) WITH '打开模板文件失败,请检查参数是否正确!'.
  ENDIF.
  CALL METHOD OF
      xl_appl
      'Worksheets' = xl_sheet
    EXPORTING
      #1           = 'sheet1'.
*  SET PROPERTY OF xl_sheet 'NAME' = 's1'.
  GET PROPERTY OF xl_appl 'ActiveWorkbook' =  xl_wkbks. "必须加上,否则无法保存excel.
ENDFORM.                    "fm_openexcel

*&---------------------------------------------------------------------*
*&      Form free_object.
*&---------------------------------------------------------------------*
FORM free_object.
  FREE OBJECT xl_row.
  FREE OBJECT xl_cell.
  FREE OBJECT xl_sheet.
  FREE OBJECT xl_init_sheet.
  FREE OBJECT xl_template_sheet.
  FREE OBJECT xl_wkbks.
  FREE OBJECT xl_appl.
ENDFORM.                    "free_object

*&---------------------------------------------------------------------*
*&      Form  FM_FILLDATA_INTO_CELL 在Excel中写入数据
*----------------------------------------------------------------------*
FORM fm_filldata_into_cell .
  PERFORM excel_write_cell USING xl_appl  1  2 p_name.
  PERFORM excel_write_cell USING xl_appl  2  2 p_age.
ENDFORM.


*&---------------------------------------------------------------------*
*& Form EXCEL_WRITE_CELL
*&---------------------------------------------------------------------*
* text 为指定的列添加字段 和 赋值
*----------------------------------------------------------------------*
FORM excel_write_cell USING xls   TYPE ole2_object
                            row   TYPE i
                            col   TYPE i
                            fval.
  CALL METHOD OF
      xls
      'CELLS' = cell_obj
    EXPORTING
      #1      = row
      #2      = col.
  SET PROPERTY OF cell_obj 'VALUE' = fval.
  FREE OBJECT cell_obj.
ENDFORM. " EXCEL_WRITE_CELL
*&---------------------------------------------------------------------*
*& Form fm_saveexcel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM fm_saveexcel .
CALL METHOD OF xl_wkbks 'SAVE'."保存
CALL METHOD OF xl_wkbks 'CLOSE'. "关闭工作区
CALL METHOD OF xl_appl 'QUIT'."退出excel
ENDFORM.

结尾：

本文中我只借助了一个十分简单的模板下载操作的方式来分享，OLED操作excel仍然具有很多其他的作用，如合并单元格，字体样式修改，添加边框等等，可以自己摸索，如果经常需要使用，也可以将其封装成class或者function，如下是我做的一个简单的function，仅可用于模板填值，不涉及格式修改，

具体OLED如何操作excel可参考文章

https://www.cnblogs.com/hhelibeb/p/5787396.html

FUNCTION ZUSE_EXCEL_MOUDLE.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     REFERENCE(OBJID) TYPE  W3OBJID
*"     REFERENCE(OBJSTR) TYPE  STRING
*"     REFERENCE(DOWNPATH) TYPE  LOCALFILE
*"----------------------------------------------------------------------
 DATA:lo_objdata     LIKE wwwdatatab,
       lo_mime        LIKE w3mime,
       ls_destination LIKE rlgrap-filename,
       ls_objnam      TYPE string,
       li_rc          LIKE sy-subrc,
       ls_errtxt      TYPE string.
  DATA:p_objid TYPE wwwdatatab-objid,
       p_dest  LIKE sapb-sappfad.
  DATA: xl_appl           TYPE ole2_object,
      xl_sheet          TYPE ole2_object,
      xl_init_sheet     TYPE ole2_object,
      xl_template_sheet TYPE ole2_object,
      xl_wkbks          TYPE ole2_object,
      xl_cell           TYPE ole2_object,
       cell_obj          TYPE ole2_object,
      xl_row            TYPE ole2_object.
  DATA :BEGIN OF itab occurs 0,
    ROW TYPE I,
    COL TYPE I,
    VALUE TYPE string,
    END OF itab.
  p_objid                 = OBJID.
  IF sy-subrc = 0.
    CONCATENATE p_objid '.XLS' INTO ls_objnam.
    CONDENSE ls_objnam NO-GAPS.
    SELECT SINGLE relid objid INTO CORRESPONDING FIELDS OF lo_objdata
      FROM wwwdata  WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.
    IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
      CONCATENATE '模板文件' ls_objnam '不存在，请用TCODE:SMW0进行加载' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'I'.
    ENDIF.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = DOWNPATH     " 'C:\Temp\test************.xls' .
      IMPORTING
        rc          = li_rc.
    IF li_rc NE 0.
      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'E'.
    ENDIF.
  ENDIF.

 CREATE OBJECT xl_appl 'EXCEL.APPLICATION'.
  SET PROPERTY OF xl_appl 'Visible' = 1.
  CALL METHOD OF
    xl_appl
      'Workbooks' = xl_wkbks.
  CALL METHOD OF
    xl_wkbks
    'Open'
    EXPORTING
      #1 = DOWNPATH
      #2 = 0
      #3 = 0.             "只读参数
  IF sy-subrc <> 0.
     FREE OBJECT xl_row.
     FREE OBJECT xl_cell.
     FREE OBJECT xl_sheet.
     FREE OBJECT xl_init_sheet.
     FREE OBJECT xl_template_sheet.
     FREE OBJECT xl_wkbks.
     FREE OBJECT xl_appl.
    MESSAGE e001(00) WITH '打开模板文件失败,请检查参数是否正确!'.
  ENDIF.
  CALL METHOD OF
      xl_appl
      'Worksheets' = xl_sheet
    EXPORTING
      #1           = 'sheet1'.
*  SET PROPERTY OF xl_sheet 'NAME' = 's1'.
  GET PROPERTY OF xl_appl 'ActiveWorkbook' =  xl_wkbks. "必须加上,否则无法保存excel.

 CALL METHOD cl_fdt_json=>json_to_data
  EXPORTING
     iv_json = OBJSTR
   CHANGING
     ca_data = itab[].
 LOOP AT ITAB.
    CALL METHOD OF
      xl_appl
      'CELLS' = cell_obj
    EXPORTING
      #1      = ITAB-row
      #2      = ITAB-col.
  SET PROPERTY OF cell_obj 'VALUE' = ITAB-VALUE.
  FREE OBJECT cell_obj.
 ENDLOOP.

CALL METHOD OF xl_wkbks 'SAVE'."保存
CALL METHOD OF xl_wkbks 'CLOSE'. "关闭工作区
CALL METHOD OF xl_appl 'QUIT'."退出excel
ENDFUNCTION.