OLE实现Excel下载

货铺QQ群号：834508274

这里的Excel格式略微复杂：

一般会用smw0先上传模板，

然后客户下载时，打开模板，填充数据，最后下载到本地。

本例使用上面的模板。

下面是demo（用电脑版观看更合适~）

首先给数据行命名：rowno

仅仅命名第一条数据行，

然后给每一个需要赋值的单元格命名

其他各字段类似

【其实是可以直接用行列号直接赋值的，我只是为了读代码的时候更清晰，自己给列加了描述，个人习惯而已】

代码如下：

【仅仅是主要逻辑代码，字段取值赋值部分未做详细处理】

REPORT  zlm_test_031.

INCLUDE ole2incl.
*&---------------------------------------------------------------------*
*&数据定义区
*&---------------------------------------------------------------------*

DATA:
      "头尾信息
      BEGIN OF str_head,
        dbd     TYPE string,             "调拨单号
        hz      TYPE string,             "汇总信息
        dcdw    TYPE string,             "调出单位
        dcck    TYPE string,             "调入仓库
        lxfs    TYPE string,             "联系方式
        drdw    TYPE string,             "调入单位
        drdd    TYPE string,             "调入地点
        ddrq    TYPE string,             "调动日期
        END OF str_head,

      "数据信息
      BEGIN OF str_data,
        xh      TYPE string,              "序号
        matnr   TYPE string,              "物料编码
        maktx   TYPE string,              "物料描述
        ggxh    TYPE string,              "规格型号
        dw      TYPE string,              "单位
        sl      TYPE string,              "数量
        dj      TYPE string,              "单价
        zj      TYPE string,              "总价
        banfn   TYPE string,              "采购单号
        bnfpo   TYPE string,              "行项目号
        bz      TYPE string,              "备注
        END OF str_data.

DATA: l_msg       TYPE char100.                                         "提示消息

DATA:
      l_head         LIKE str_head,
      l_it_data      LIKE TABLE OF str_data,
      l_wa_data      LIKE LINE OF l_it_data.


*&---------------------------------------------------------------------*
*&excel相关变量定义
*&---------------------------------------------------------------------*

DATA: excelobj     TYPE ole2_object,           " Excel object
      wbookobj     TYPE ole2_object,           " Workbook object
      wsheetobj    TYPE ole2_object,           " Worksheet object
      rangeobj     TYPE ole2_object,           " Range object
      cellobj      TYPE ole2_object,           " Cell object
      excel_saved_name     TYPE rlgrap-filename VALUE 'E:\PRINT.xlsx'.

*&---------------------------------------------------------------------*
*&主逻辑区
*&---------------------------------------------------------------------*
START-OF-SELECTION.


  "组织数据
  PERFORM zform_get_data.
  "打开模板，创建excel文件
  PERFORM zform_open_template_excel.
  "填充excel文件
  PERFORM zform_field_excel.


*&---------------------------------------------------------------------*
*&form区
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*&      Form  zform_open_template_excel
*&---------------------------------------------------------------------*
*       text 打开模板文件
*----------------------------------------------------------------------*
FORM zform_open_template_excel.
  CREATE OBJECT excelobj 'Excel.Application'.             " Create excel object
  IF sy-subrc NE 0.
    WRITE: / 'Excel init failed, return code is ', sy-subrc.
    EXIT.
  ELSE.
    SET PROPERTY OF excelobj 'Visible' = 1.               " Set excel visible
    CALL METHOD OF excelobj                               " Create wbook object
    'Workbooks' = wbookobj.
    CALL METHOD OF
        wbookobj
        'Open'   = wbookobj
      EXPORTING
        #1       = excel_saved_name.                               "srcfile1.
    CALL METHOD OF wbookobj                               " Create wsheet object
         'Worksheets' = wsheetobj EXPORTING #1 = 'sheet1'.
    CALL METHOD OF
        wsheetobj
        'Activate'.
  ENDIF.
ENDFORM.                    "zform_open_template_excel


*&---------------------------------------------------------------------*
*&      Form  zform_get_data
*&---------------------------------------------------------------------*
*       text 组织数据
*----------------------------------------------------------------------*
FORM zform_get_data.

  "头信息
  l_head-dbd     = 'OOXX-2013-001'.                                     "调拨单号
  l_head-hz      = '本次共调拨物资  2   种        合计金额：173100 元'.   "汇总信息
  l_head-dcdw    = '北京公司'.                                           "调出单位
  l_head-dcck    = '北京燕郊应急物资储备仓库'.                            "调入仓库
  l_head-lxfs    = '张三13910898691'.                                    "联系方式
  l_head-drdw    = '青海公司'.                                           "调入单位
  l_head-drdd    = '青海玉树'.                                           "调入地点
  l_head-ddrq    = sy-datum.                                            "调动日期


  "数据信息
  l_wa_data-xh      = '1'.                                     "序号
  l_wa_data-matnr   = '500064522'.                             "物料编码
  l_wa_data-maktx   = '全方位自动泛光工作灯'.                  "物料描述
  l_wa_data-ggxh    = 'XL'.                                    "规格型号
  l_wa_data-dw      = '台'.                                    "单位
  l_wa_data-sl      = '10'.                                    "数量
  l_wa_data-dj      = '10679.99'.                              "单价
  l_wa_data-zj      = '106799.9'.                              "总价
  l_wa_data-banfn   = '3000000001'.                            "采购单号
  l_wa_data-bnfpo   = '10'.                                    "行项目号
  l_wa_data-bz      = '正常'.                                  "备注

  APPEND l_wa_data TO l_it_data.
  CLEAR l_wa_data.

  l_wa_data-xh      = '2'.                                     "序号
  l_wa_data-matnr   = '500064523'.                             "物料编码
  l_wa_data-maktx   = '工作灯（不带发电机）'.                  "物料描述
  l_wa_data-ggxh    = 'SL'.                                    "规格型号
  l_wa_data-dw      = '台'.                                    "单位
  l_wa_data-sl      = '20'.                                    "数量
  l_wa_data-dj      = '3315.005'.                              "单价
  l_wa_data-zj      = '66300.1'.                              "总价
  l_wa_data-banfn   = '3000000001'.                            "采购单号
  l_wa_data-bnfpo   = '10'.                                    "行项目号
  l_wa_data-bz      = '正常'.                                  "备注
  APPEND l_wa_data TO l_it_data.
  CLEAR l_wa_data.

ENDFORM.                    "zform_get_data

*&---------------------------------------------------------------------*
*&      Form  zform_field_excel
*&---------------------------------------------------------------------*
*       text  填充excel文件
*----------------------------------------------------------------------*
FORM zform_field_excel.

  "填充头
  PERFORM fill_range USING 'DBD'    l_head-dbd .               "调拨单号
  PERFORM fill_range USING 'HZ'     l_head-hz  .               "汇总信息
  PERFORM fill_range USING 'DCDW'   l_head-dcdw.               "调出单位
  PERFORM fill_range USING 'DCKW'   l_head-dcck.               "调入仓库
  PERFORM fill_range USING 'LXFS'   l_head-lxfs.               "联系方式
  PERFORM fill_range USING 'DRDW'   l_head-drdw.               "调入单位
  PERFORM fill_range USING 'DRDD'   l_head-drdd.               "调入地点
  PERFORM fill_range USING 'DDRQ'   l_head-ddrq.               "调动日期

  "填充数据行
  LOOP AT l_it_data INTO l_wa_data.
    PERFORM fill_range USING 'XH'      l_wa_data-xh    .      "序号
    PERFORM fill_range USING 'MATNR'   l_wa_data-matnr .      "物料编码
    PERFORM fill_range USING 'MAKTX'   l_wa_data-maktx .      "物料描述
    PERFORM fill_range USING 'GGXH '   l_wa_data-ggxh  .      "规格型号
    PERFORM fill_range USING 'DW'      l_wa_data-dw    .      "单位
    PERFORM fill_range USING 'SL'      l_wa_data-sl    .      "数量
    PERFORM fill_range USING 'DJ'      l_wa_data-dj    .      "单价
    PERFORM fill_range USING 'ZJ'      l_wa_data-zj    .      "总价
    PERFORM fill_range USING 'BANFN'   l_wa_data-banfn .      "采购单号
    PERFORM fill_range USING 'BNFPO'   l_wa_data-bnfpo .      "行项目号
    PERFORM fill_range USING 'BZ'      l_wa_data-bz    .      "备注

    PERFORM insert_row USING 'ROWNO'.                           "插入一新行，必须！
  ENDLOOP.

  "保存
  PERFORM fill_save USING excel_saved_name.

  "释放资源
  CALL METHOD OF
      wbookobj
      'CLOSE'.

  CALL METHOD OF
      excelobj
      'QUIT'.


  FREE OBJECT rangeobj       .
  FREE OBJECT wsheetobj      .
  FREE OBJECT wbookobj       .
  FREE OBJECT excelobj       .

ENDFORM.                    "zform_print


***********************************************************************
*&EXCEL执行用的form
***********************************************************************
*---------------------------------------------------------------------*
*       FORM insert_row                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_RANGE)                                                *
*---------------------------------------------------------------------*
FORM insert_row USING value(f_range).
  CALL METHOD OF
      wsheetobj
      'range'   = rangeobj
    EXPORTING
      #1        = f_range.
  CALL METHOD OF
      rangeobj
      'copy'.
  CALL METHOD OF
      rangeobj
      'select'.
  CALL METHOD OF
      rangeobj
      'insert'.
  CALL METHOD OF
      rangeobj
      'clearcontents'.
ENDFORM.                    "insert_row
*---------------------------------------------------------------------*
*       FORM fill_range                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_RANGE)                                                *
*  -->  VALUE(F_VALUE)                                                *
*---------------------------------------------------------------------*
FORM fill_range USING value(f_range)
                      value(f_value).
  CALL METHOD OF
      wsheetobj
      'Range'   = rangeobj
    EXPORTING
      #1        = f_range.
  SET PROPERTY OF rangeobj 'Value' = f_value.
ENDFORM.                    "fill_range
*---------------------------------------------------------------------*
*       FORM fill_COPY                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM fill_copy.
  CALL METHOD OF
      excelobj
      'run'

    EXPORTING
      #1       = 'COPY'.
ENDFORM.                    "fill_copy
*---------------------------------------------------------------------*
*       FORM fill_rowno                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM fill_rowno.
  CALL METHOD OF
      excelobj
      'run'

    EXPORTING
      #1       = 'ROWNO'.
ENDFORM.                    "fill_rowno
*---------------------------------------------------------------------*
*       FORM fill_delrows                                             *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM fill_delrows.
  CALL METHOD OF
      excelobj
      'run'

    EXPORTING
      #1       = 'DELROWS'.
ENDFORM.                    "fill_delrows
*---------------------------------------------------------------------*
*       FORM fill_run                                                 *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(NAME)                                                   *
*---------------------------------------------------------------------*
FORM fill_run USING value(name).
  CALL METHOD OF
      excelobj
      'run'

    EXPORTING
      #1       = name.
ENDFORM.                    "fill_run
*---------------------------------------------------------------------*
*       FORM fill_save                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_SAVE)                                                 *
*---------------------------------------------------------------------*
FORM fill_save USING value(f_save).
  CALL METHOD OF
      wbookobj
      'SaveAs' = wbookobj
    EXPORTING
      #1       = f_save.
ENDFORM.                    "fill_save

可能里面部分excel的方法没有用到，也没做删除