SAP ABAP 按照EXCEL模板导出，适用于ITEM行不固定

• TYPE 定义部分

  DATA: gt_fieldcat TYPE lvc_t_fcat,
        gs_layout   TYPE lvc_s_layo,
        gr_grid     TYPE REF TO cl_gui_alv_grid.

• 新增行

  " 在sheet页 第row行复制第count行
  FORM frm_excel_row_insert  USING u_sheet
                                   u_row
                                   u_count.
    DATA lc_range TYPE ole2_object.
    DATA lc_h_borders  TYPE ole2_object.
  
    CALL METHOD OF u_sheet 'Rows' = lc_range
      EXPORTING
        #1  = u_count.
  
    CALL METHOD OF lc_range 'Copy'.  "COPY
    CALL METHOD OF u_sheet 'Rows'      = lc_range
      EXPORTING
        #1 = u_row.
    CALL METHOD OF lc_range 'Insert'.
    CALL METHOD OF lc_range 'ClearContents'. "是否需要清空Cell
  
  ENDFORM.

•  给单元格赋值

  " 在row行 col列 赋值value
  FORM frm_fill_value USING uv_row
                            uv_col
                            uv_value.
    CALL METHOD OF gv_excel 'CELLS' = gv_cell
      EXPORTING
          #1 = uv_row
          #2 = uv_col.
    SET PROPERTY OF gv_cell 'VALUE' =  uv_value.
  ENDFORM.

• 代码主体部分

  FORM frm_dc_excel.
    DATA: ls_destination TYPE string.
    DATA: lv_object_id(20) TYPE c, "模板名称
          lo_doc_table     TYPE w3mime OCCURS 0,
          lv_doc_size      TYPE i,
          lv_doc_format    TYPE c,
          lv_doc_type      TYPE c.
    DATA: lv_fname     TYPE string,
          lv_init_path TYPE string,
          lv_title     TYPE string,
          lv_path      TYPE string,
          lv_fpath     TYPE string,
          lv_file      TYPE string.
    DATA: lo_objdata    TYPE wwwdatatab. "object name
    DATA: lv_rc         TYPE sy-subrc.   "return code
    DATA: lv_filter     TYPE string.
    DATA: lv_p_file TYPE rlgrap-filename .
    DATA: lv_row TYPE i.
    DATA: lv_rowc TYPE i.
    DATA: lv_col TYPE i.
    DATA: lv_index TYPE i.
    DATA: lv_flag TYPE char1,
          lv_str  TYPE char50.
    DATA: lv_date TYPE char10.
    CLEAR lv_object_id.
  
    CLEAR:gt_head,gt_item,lv_index,lv_col,lv_row.
  
  *" 将选中的条目分别放到头行表中
    LOOP AT gt_alv INTO gs_alv WHERE sel = 'X'.
      MOVE-CORRESPONDING gs_alv TO gs_head.
      APPEND gs_head TO gt_head.
    ENDLOOP.
  
  *  对抬头去重
    IF gt_head IS INITIAL.
  *    未勾选
      MESSAGE '请勾选需要导出的凭证' TYPE 'S' DISPLAY LIKE 'E'.
      EXIT.
    ELSE.
  *    去重
      DELETE ADJACENT DUPLICATES FROM gt_head COMPARING ALL FIELDS.
      IF lines( gt_head ) NE 1.
  *    不止一张单据
        MESSAGE TEXT-003 TYPE 'S' DISPLAY LIKE 'E'.
        EXIT.
      ELSE.
        READ TABLE gt_head INTO gs_head INDEX 1.
        CHECK sy-subrc EQ 0.
      ENDIF.
    ENDIF.
  
    " SMW0上传文件模板名称
    lv_object_id = 'ZSDC006'.
  
    "获取桌面路径
    CALL METHOD cl_gui_frontend_services=>get_desktop_directory
      CHANGING
        desktop_directory    = lv_init_path
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
    lv_file = '报价单' && sy-datum && sy-uzeit .
    "得到保存文件的地址
    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title         = lv_title
        default_extension    = 'xlsx'
        default_file_name    = lv_file
        initial_directory    = lv_init_path
        file_filter          = lv_filter
        prompt_on_overwrite  = abap_true
      CHANGING
        filename             = lv_fname
        path                 = lv_path
        fullpath             = lv_fpath
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.
    IF sy-subrc = 0.
      lv_p_file = lv_fpath.
    ELSE.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      MESSAGE '获取保存地址失败！' TYPE 'S' DISPLAY LIKE 'E'.
      EXIT.
    ENDIF.
  
    "获取模板信息
    CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA' ##ARG_OK ##FM_PAR_MIS
      EXPORTING
        object_id        = lv_object_id
      IMPORTING
        data_size        = lv_doc_size
        document_format  = lv_doc_format
        document_type    = lv_doc_type
      TABLES
        data_table       = lo_doc_table
      EXCEPTIONS
        object_not_found = 1
        internal_error   = 2
        OTHERS           = 3.
    IF sy-subrc NE 0.
      EXIT.
    ENDIF.
  
    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
    WHERE srtf2 = 0 AND relid = 'MI' AND objid = lv_object_id.
    CHECK sy-subrc EQ 0.
  
    "下载模板
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = lv_p_file "下载地址
      IMPORTING
        rc          = lv_rc.
  
    "打开EXCEL
    CREATE OBJECT gv_excel 'EXCEL.APPLICATION'.
    SET PROPERTY OF gv_excel  'VISIBLE' = 1.
    CALL METHOD OF gv_excel 'WORKBOOKS' = gv_wbook.
    CALL METHOD OF gv_wbook 'Open' "打开EXCEL
      EXPORTING
        #1 = lv_p_file.
  
    CALL METHOD OF
         gv_excel
         'WORKSHEETS' = gv_sheet
      EXPORTING
         #1           = 'SHEET1'. "EXCEL sheet页名称
  
    CALL METHOD OF gv_sheet 'ACTIVATE'. "激活
  
  
  *  GET PROPERTY OF gv_excel 'ActiveWorkbook' =  gv_wbook.
  
  *  "写入数据
  * 抬头
    PERFORM frm_fill_value USING 2 1  gs_head-butxt.
    lv_str = |Add：{ gs_head-zadrnr3 }|.
    PERFORM frm_fill_value USING 3 1 lv_str.
    lv_str = |Tel：{ gs_head-zadrnr3 }|.
    PERFORM frm_fill_value USING 4 1 lv_str.
    lv_str = |Fax：{ gs_head-zadrnr3 }|.
    PERFORM frm_fill_value USING 5 1 lv_str.
    CLEAR:lv_str.
  
  * 日期
    lv_date = |{ sy-datum+0(4) }.{ sy-datum+4(2) }.{ sy-datum+6(2) }|.
    PERFORM frm_fill_value USING 3 5  lv_date.
    PERFORM frm_fill_value USING 3 6  gs_head-vbeln.
  
  *  售达方
    PERFORM frm_fill_value USING 8  2  gs_head-zname1.
    PERFORM frm_fill_value USING 9  2  gs_head-zrut1.
    PERFORM frm_fill_value USING 10 2  gs_head-zgj1.
    PERFORM frm_fill_value USING 11 2  gs_head-zcity1.
    PERFORM frm_fill_value USING 12 2  gs_head-zzipcode1.
    PERFORM frm_fill_value USING 13 2  gs_head-zemail1.
    PERFORM frm_fill_value USING 14 2  gs_head-zadrnr1.
    PERFORM frm_fill_value USING 15 2  gs_head-zlxr1.
    PERFORM frm_fill_value USING 16 2  gs_head-zgddh1.
  
  *  送达方
    PERFORM frm_fill_value USING 8  5  gs_head-zname2.
    PERFORM frm_fill_value USING 9  5  gs_head-zrut2.
    PERFORM frm_fill_value USING 10 5  gs_head-zgj2.
    PERFORM frm_fill_value USING 11 5  gs_head-zcity2.
    PERFORM frm_fill_value USING 12 5  gs_head-zzipcode2.
    PERFORM frm_fill_value USING 13 5  gs_head-zemail2.
    PERFORM frm_fill_value USING 14 5  gs_head-zadrnr2.
    PERFORM frm_fill_value USING 15 5  gs_head-zlxr2.
    PERFORM frm_fill_value USING 16 5  gs_head-zgddh2.
  
  * 国际贸易条款 25
    PERFORM frm_fill_value USING 17 2  gs_head-inco1.
  * 结算币别 26
    PERFORM frm_fill_value USING 17 6  gs_head-waerk.
  
  * item行项目
    lv_row = 19.
    lv_index = 0.
    " 模板里仅有一行空行 item
    LOOP AT gt_alv INTO gs_alv WHERE vbeln = gs_head-vbeln.
      lv_index = lv_index + 1.
  
  *   新行
      "第二行时才需新增行
      IF lv_index > 1.
        PERFORM frm_excel_row_insert USING gv_sheet lv_row lv_row.
        lv_row = lv_row  + 1.
      ENDIF.
  
  *   填充数据
      PERFORM frm_fill_value USING lv_row 1 gs_alv-atwrt.
      PERFORM frm_fill_value USING lv_row 2 gs_alv-atwrt1.
      PERFORM frm_fill_value USING lv_row 3 gs_alv-maktx.
      PERFORM frm_fill_value USING lv_row 4 gs_alv-kwmeng.
      PERFORM frm_fill_value USING lv_row 5 gs_alv-cmpre.
      PERFORM frm_fill_value USING lv_row 6 gs_alv-zamount.
  
      CLEAR:gs_alv.
  
    ENDLOOP.
  
  *  Subtotal 33
    lv_row = lv_row + 1.
    " A B C D E列已合并，从模板里看为第2列，但实际所在列为F列第6列
    PERFORM frm_fill_value USING lv_row 6  gs_head-zsubtotal.
  
  *  底部
    lv_row = lv_row + 7.
    PERFORM frm_fill_value USING lv_row 2  gs_head-vtext.
    lv_row = lv_row + 1.
    PERFORM frm_fill_value USING lv_row 2  gs_head-bankl.
    lv_row = lv_row + 1.
    PERFORM frm_fill_value USING lv_row 2  gs_head-banka.
    lv_row = lv_row + 1.
    PERFORM frm_fill_value USING lv_row 2  gs_head-bankn.
    lv_row = lv_row + 1.
    PERFORM frm_fill_value USING lv_row 2  gs_head-koinh.
    lv_row = lv_row + 1.
    PERFORM frm_fill_value USING lv_row 2  gs_head-stras.
  
    GET PROPERTY OF gv_excel 'ActiveSheet' = gv_sheet. "获取活动SHEET
  
    FREE OBJECT gv_sheet.
  
    FREE OBJECT gv_wbook.
  
    GET PROPERTY OF gv_excel 'ActiveWorkbook' = gv_wbook.
  
    CALL METHOD OF gv_wbook 'SAVE'.
  
    SET PROPERTY OF gv_excel 'Visible' = 1.
  
  *  CALL METHOD OF gv_wbook 'CLOSE'.
  *  CALL METHOD OF gv_excel 'QUIT'. "注释部分为不显示直接退出
  
    FREE OBJECT gv_cell.
    FREE OBJECT gv_sheet.
    FREE OBJECT gv_wbook.
    FREE OBJECT gv_excel.
  
  ENDFORM.
  

模板 蓝色部分为需填充数据部分 

 第一行为logo 暂不显示