ABAP导出EXCEL表格

　　使用事先做好的模板，上传至SAP，事务码SMW0，

 

 

回到报表开发中，得到保存路径（给用户自己选）

*选择保存路径
  CALL METHOD cl_gui_frontend_services=>directory_browse
    CHANGING
      selected_folder      = lv_selected_folder
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
  ENDIF.

 定义：

DATA: go_excel    TYPE ole2_object,
      go_workbook TYPE ole2_object,
      go_sheet    TYPE ole2_object.

DATA: lv_destination  TYPE rlgrap-filename.
DATA: ls_key          TYPE wwwdatatab.

* 首先下载模版
    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF ls_key WHERE relid = 'MI' AND objid = 'SMW0中上传模板的对象名称'.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = ls_key
        destination = lv_destination.

 

* 打开模版
    CREATE OBJECT go_excel 'EXCEL.APPLICATION'.
    IF sy-subrc NE 0.
    ENDIF.
    SET PROPERTY OF go_excel 'VISIBLE' = 1.
    CALL METHOD OF go_excel 'WORKBOOKS' = go_workbook.
    CALL METHOD OF go_workbook 'OPEN'
      EXPORTING
        #1 = lv_destination.
    CALL METHOD OF go_excel 'SHEETS' = go_sheet
        EXPORTING
            #1 = 1.
    CALL METHOD OF go_sheet 'ACTIVATE'.

 进行填值

PERFORM fill_range USING 1 1 gs_head-butxt. "公司名称

PERFORM excel_row_insert USING go_sheet lv_loop2 1 lv_loop2. "复制并插入一行

再填值

PERFORM fill_range USING lv_loop2 2  gs_output-matnr. "
最后关闭表格

PERFORM sub_excel_save. "关闭

 

更改颜色参考 https://www.cnblogs.com/ybinlog/p/13797312.html

 

附上PERFORM内容

 

*&---------------------------------------------------------------------*
*& Form SUB_EXCEL_SAVE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM sub_excel_save .
  GET PROPERTY OF go_excel 'ACTIVESHEET' = go_sheet. "获取活动SHEET
  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.

  GET PROPERTY OF go_excel 'ACTIVEWORKBOOK' = go_workbook.


*  SET PROPERTY OF go_excel 'VISIBLE' = 0.  "是否显示EXCEL 此处显示不退出

  CALL METHOD OF go_workbook 'SAVE'.
  CALL METHOD OF go_excel 'QUIT'.


  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.
  FREE OBJECT go_excel.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FILL_RANGE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*      -->P_1      text
*      -->P_1      text
*      -->P_GS_HEAD_BUTXT  text
*&---------------------------------------------------------------------*
FORM fill_range  USING   VALUE(f_row)
                         VALUE(f_col)
                         VALUE(f_value).

  DATA: row     TYPE i,
        col     TYPE i,
        lo_cell TYPE ole2_object.
  row = f_row.
  col = f_col.
  CALL METHOD OF go_excel 'CELLS' = lo_cell
    EXPORTING
    #1 = row
    #2 = col.
  SET PROPERTY OF lo_cell 'VALUE' = f_value.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXCEL_ROW_INSERT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*      -->P_GO_SHEET  text
*      -->P_LV_LOOP2  text
*      -->P_1      text
*&---------------------------------------------------------------------*
FORM excel_row_insert  USING lcobj_sheet
                            lc_row
                            lc_count
                            lv_rowcopy.
  DATA lc_range TYPE ole2_object.
  DATA h_borders  TYPE ole2_object.
  DO lc_count TIMES.
    CALL METHOD OF lcobj_sheet 'ROWS' = lc_range
      EXPORTING #1 = lv_rowcopy.
    CALL METHOD OF lc_range 'COPY'. "COPY第10行插入一个新行
    CALL METHOD OF lcobj_sheet 'ROWS' = lc_range
      EXPORTING #1 = lc_row.
    CALL METHOD OF lc_range 'INSERT'.
    CALL METHOD OF lc_range 'CLEARCONTENTS'. "是否需要清空CELL
  ENDDO.
ENDFORM.

 

 

第二种方法：

直接导出表格内容到EXCEL，速度快，没有定制的格式

  DATA: lv_selected_folder TYPE string.
  DATA: lv_meins TYPE meins.
*选择保存路径
  CALL METHOD cl_gui_frontend_services=>directory_browse
    CHANGING
      selected_folder      = lv_selected_folder
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
  ENDIF.

  DATA: go_excel    TYPE ole2_object,
        go_workbook TYPE ole2_object,
        go_sheet    TYPE ole2_object,
        go_cell     TYPE ole2_object,
        go_column   TYPE ole2_object,
        go_interior TYPE ole2_object,
        go_range    TYPE ole2_object.

  DATA: lv_loop TYPE i.
  DATA: lv_destination  TYPE string."rlgrap-filename.
  DATA: ls_key          TYPE wwwdatatab.
  DATA: lv_str3 TYPE string.
  DATA: lt_str   LIKE TABLE OF lv_str3 WITH HEADER LINE.
  GET TIME.
  lv_destination = |{ lv_selected_folder }\\{ sy-datum }{ sy-uzeit }.xlsx|.

  LOOP AT gt_output INTO DATA(ls_output) WHERE zsel EQ 'X'.
*进行填值

*    wa_item-vbeln  = ''. "销售订单
*    wa_item-audat  = ''. "下单日期
*    wa_item-bstdk  = ''. "客户参考日期
    wa_item-auart  = 'ZKE1'. "单据类型
    wa_item-vkbur  = ls_output-vkbur. "销售部门
    wa_item-bstnk  = ls_output-bstkd. "客户参考/客户采购单号
    wa_item-cpmc  = ls_output-wgbez. "商品编号
    wa_item-kwmeng  = ls_output-zwdzsl. "数量
*    wa_item-txt_01  = ''. "行项目文本
    wa_item-lgort  = ls_output-lgort. "库存地点
    wa_item-vsbed  = ls_output-vsbed. "装运条件
    wa_item-ihrez  = ls_output-ihrez. "您的参考/客户联系人
    "单位转换
    CLEAR lv_meins.
    CALL FUNCTION 'CONVERSION_EXIT_CUNIT_OUTPUT'
      EXPORTING
        input          = ls_output-vrkme
        language       = '1'
      IMPORTING
        output         = lv_meins
      EXCEPTIONS
        unit_not_found = 1
        OTHERS         = 2.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ENDIF.
    IF '*' CO lv_meins.
      lv_meins = ls_output-vrkme.
    ELSEIF lv_meins NE ''.
*       lv_meins = ls_output-vrkme.
    ENDIF.

    wa_item-kmein  = lv_meins. "单位
*    wa_item-txt_04  = ''. "抬头文本
    wa_item-kbetr  = ls_output-cmpre. "单价
    wa_item-kpein  = ls_output-kursk. "价格系数
    wa_item-zongje  = ls_output-zchze. "总金额
    wa_item-waerk  = ls_output-waerk. "货币
    wa_item-zterm  = ls_output-zterm. "付款条件
    wa_item-matnr_01  = ls_output-matnr. "物料编码
*    wa_item-ggxh  = ''. "规格型号
    wa_item-cpmc_01  = ls_output-arktx. "物料描述
*    wa_item-edatu  = ''."请求交货日期
    wa_item-pstyv  = 'ZKEN'. "行项目类别
    wa_item-vkorg  = ls_output-vkorg. "销售组织
    wa_item-vtweg  = ls_output-vtweg. "分销渠道
    wa_item-spart  = ls_output-spart. "产品组
    wa_item-vkgrp  = ls_output-vkgrp. "销售组
    wa_item-kunnr  = ls_output-kunag. "客户编号
    wa_item-kunnr_01  = ls_output-kunnr. "送达方
    wa_item-werks  = ls_output-werks. "工厂
*    wa_item-augru  = ''. "订单原因
    wa_item-dn  = ls_output-dn. "VMI1交货单
    wa_item-dnposnr  = ls_output-dnposnr. "VMI1行项目
    APPEND wa_item TO gt_item[].
    CLEAR: wa_item,ls_output.
  ENDLOOP.
  DATA:BEGIN OF t_fieldnames  OCCURS 0,
         name TYPE char20,
       END OF t_fieldnames.
  t_fieldnames-name = '销售订单'.
  APPEND t_fieldnames.
  t_fieldnames-name = '下单日期'.
  APPEND t_fieldnames.
  t_fieldnames-name = '客户参考日期'.
  APPEND t_fieldnames.
  t_fieldnames-name = '单据类型'.
  APPEND t_fieldnames.
  t_fieldnames-name = '销售部门'.
  APPEND t_fieldnames.
  t_fieldnames-name = '客户参考/客户采购单号'.
  APPEND t_fieldnames.
  t_fieldnames-name = '商品编号'.
  APPEND t_fieldnames.
  t_fieldnames-name = '数量'.
  APPEND t_fieldnames.
  t_fieldnames-name = '行项目文本'.
  APPEND t_fieldnames.
  t_fieldnames-name = '库存地点'.
  APPEND t_fieldnames.
  t_fieldnames-name = '装运条件'.
  APPEND t_fieldnames.
  t_fieldnames-name = '您的参考/客户联系人'.
  APPEND t_fieldnames.
  t_fieldnames-name = '单位'.
  APPEND t_fieldnames.
  t_fieldnames-name = '抬头文本'.
  APPEND t_fieldnames.
  t_fieldnames-name = '单价'.
  APPEND t_fieldnames.
  t_fieldnames-name = '价格系数'.
  APPEND t_fieldnames.
  t_fieldnames-name = '总金额'.
  APPEND t_fieldnames.
  t_fieldnames-name = '货币'.
  APPEND t_fieldnames.
  t_fieldnames-name = '付款条件'.
  APPEND t_fieldnames.
  t_fieldnames-name = '物料编码'.
  APPEND t_fieldnames.
  t_fieldnames-name = '规格型号'.
  APPEND t_fieldnames.
  t_fieldnames-name = '物料描述'.
  APPEND t_fieldnames.
  t_fieldnames-name = '请求交货日期'.
  APPEND t_fieldnames.
  t_fieldnames-name = '行项目类别'.
  APPEND t_fieldnames.
  t_fieldnames-name = '销售组织'.
  APPEND t_fieldnames.
  t_fieldnames-name = '分销渠道'.
  APPEND t_fieldnames.
  t_fieldnames-name = '产品组'.
  APPEND t_fieldnames.
  t_fieldnames-name = '销售组'.
  APPEND t_fieldnames.
  t_fieldnames-name = '客户编号'.
  APPEND t_fieldnames.
  t_fieldnames-name = '送达方'.
  APPEND t_fieldnames.
  t_fieldnames-name = '工厂'.
  APPEND t_fieldnames.
  t_fieldnames-name = '订单原因'.
  APPEND t_fieldnames.
  t_fieldnames-name = 'VMI1交货单'.
  APPEND t_fieldnames.
  t_fieldnames-name = 'VMI1行项目'.
  APPEND t_fieldnames.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename   = lv_destination
      filetype   = 'DAT'
      "这里一般用DAT，如果用ASC则1000-不会显示为-1000，而dat会显示为-1000，如果用DBF则不会有缩进，即字符前面的空格会被除去,而且字符的前导0也会输出。
      codepage   = '8404'
    TABLES
      data_tab   = gt_item
      fieldnames = t_fieldnames.