先把基本的FORM写好：
1.行列属性
FORM row_column USING p_r p_width p_type. CASE p_type. WHEN 'R'. "行高 CALL METHOD OF gs_excel 'CELLS' = gs_cells EXPORTING #1 = p_r #2 = 1. GET PROPERTY OF gs_cells 'rows' = gs_rows. SET PROPERTY OF gs_rows 'rowheight' = p_width. WHEN 'C'. "列宽 CALL METHOD OF gs_excel 'CELLS' = gs_cells EXPORTING #1 = 1 #2 = p_r. GET PROPERTY OF gs_cells 'columns' = gs_columns . SET PROPERTY OF gs_columns 'columnwidth' = p_width. ENDCASE. FREE OBJECT gs_rows. FREE OBJECT gs_columns. FREE OBJECT gs_cells. ENDFORM. " PRM_EXPORT_EXCEL1
2.指定行列边框，字体
FORM merged USING x1 y1 x2 y2 p_linestyle. *--Selecting cell area to be merged. CALL METHOD OF gs_excel 'Cells' = gs_cell1 EXPORTING #1 = x1 #2 = y1. CALL METHOD OF gs_excel 'Cells' = gs_cell2 EXPORTING #1 = x2 #2 = y2. CALL METHOD OF gs_excel 'Range' = gs_range EXPORTING #1 = gs_cell1 #2 = gs_cell2. CALL METHOD OF gs_range 'Select'. *--Merging CALL METHOD OF gs_range 'Merge' . GET PROPERTY OF gs_range 'borders' = gs_borders . SET PROPERTY OF gs_borders 'weight' = '2'. SET PROPERTY OF gs_borders 'linestyle' = p_linestyle. FREE OBJECT gs_range. FREE OBJECT gs_borders. ENDFORM. " MERGED
3.单元格填充
FORM fill_cell_gs USING p_r1 p_r2 p_bold p_size p_linestyle p_value. CALL METHOD OF gs_excel 'CELLS' = gs_cells EXPORTING #1 = p_r1 #2 = p_r2. * 设置被选中单元格的对齐方式 " && 水平方向 2左对齐，3居中，4右对齐 SET PROPERTY OF gs_cells 'horizontalAlignment' = 3. "&& 垂直方向 1靠上 ，2居中，3靠下 SET PROPERTY OF gs_cells 'VerticalAlignment' = 2. "自动换行 SET PROPERTY OF gs_cells 'WrapText' = 1. "设置边框属性 GET PROPERTY OF gs_cells 'borders' = gs_borders . SET PROPERTY OF gs_borders 'weight' = '2'. SET PROPERTY OF gs_borders 'linestyle' = p_linestyle. "设置字体属性 CALL METHOD OF gs_cells 'FONT' = gs_font. SET PROPERTY OF gs_font 'BOLD' = p_bold. "1:粗体 0:普通字体 SET PROPERTY OF gs_font 'SIZE' = p_size. "字体大小 "设置单元格的值 SET PROPERTY OF gs_cells 'VALUE' = p_value. FREE OBJECT gs_font. FREE OBJECT gs_borders. FREE OBJECT gs_cells. ENDFORM.
3.2居中填充单元格
FORM fill_cell_gs2 USING p_r1 p_r2 p_bold p_size p_linestyle p_value. CALL METHOD OF gs_excel 'CELLS' = gs_cells EXPORTING #1 = p_r1 #2 = p_r2. * 设置被选中单元格的对齐方式 " && 水平方向 2左对齐，3居中，4右对齐 SET PROPERTY OF gs_cells 'horizontalAlignment' = 2. "&& 垂直方向 1靠上 ，2居中，3靠下 SET PROPERTY OF gs_cells 'VerticalAlignment' = 2. "自动换行 SET PROPERTY OF gs_cells 'WrapText' = 1. "设置边框属性 GET PROPERTY OF gs_cells 'borders' = gs_borders . SET PROPERTY OF gs_borders 'weight' = '2'. SET PROPERTY OF gs_borders 'linestyle' = p_linestyle. "设置字体属性 CALL METHOD OF gs_cells 'FONT' = gs_font. SET PROPERTY OF gs_font 'BOLD' = p_bold. "1:粗体 0:普通字体 SET PROPERTY OF gs_font 'SIZE' = p_size. "字体大小 "设置单元格的值 SET PROPERTY OF gs_cells 'VALUE' = p_value. FREE OBJECT gs_font. FREE OBJECT gs_borders. FREE OBJECT gs_cells. ENDFORM.
4.插入行：
*&---------------------------------------------------------------------* *& 向excel中的指定行插入N行 *&---------------------------------------------------------------------* FORM excel_row_insert USING lcobj_sheet lc_row lc_count. DATA lc_range TYPE ole2_object. DATA h_borders TYPE ole2_object. DATA:p_row TYPE i. p_row = lc_row - 1. DO lc_count TIMES. CALL METHOD OF lcobj_sheet 'Rows' = lc_range EXPORTING #1 = p_row. CALL METHOD OF lc_range 'Copy'. "copy第6行插入一个新行 CALL METHOD OF lcobj_sheet 'Rows' = lc_range EXPORTING #1 = p_row. CALL METHOD OF lc_range 'Insert'. CALL METHOD OF lc_range 'ClearContents'. "是否需要清空Cell ENDDO. ENDFORM. "excel_row_insert
5.正式的数据填充。excel打开，关闭，保存
CREATE OBJECT gs_excel 'EXCEL.APPLICATION'. *设置EXCEL可见 SET PROPERTY OF gs_excel 'Visible' = 0. *创建工作表 CALL METHOD OF gs_excel 'Workbooks' = gs_workbook. CALL METHOD OF gs_workbook 'Open' EXPORTING #1 = gv_file3. "打开上面下载路径下的excel文件 *选中相应sheet设置名称 CALL METHOD OF gs_excel 'WORKSHEETS' = gs_sheet EXPORTING #1 = 1. CALL METHOD OF gs_sheet 'Select' . CALL METHOD OF gs_sheet 'ACTIVATE'. ls_head-ktext = m_kunnr."利润中心先赋值 SELECT SINGLE name1 INTO ls_head-m_kunnr FROM kna1 WHERE kunnr = s_kunnr-low. CONCATENATE s_gjahr-low+0(4) '年' s_monat-low+0(2) '月对账单' INTO lv_str1. PERFORM fill_cell_gs2 USING: 3 1 1 16 0 lv_str1."抬头填充 PERFORM fill_cell_gs2 USING: 4 2 0 11 0 ls_head-m_kunnr,"购买方 2 8 0 11 0 s_kunnr-low,"客户编码 * 4 4 0 11 0 s_kunnr-low,"客户编码 4 6 0 11 0 m_bukrs."供货方 CLEAR:gs_line1. CONCATENATE '一、' s_gjahr-low '年' s_monat-low '月期间，我司按约定为贵司送货明细如下：' INTO gs_line1. PERFORM fill_cell_gs2 USING: 8 1 0 11 0 gs_line1." lv_cur_line = 10. SORT gt_shows BY wadat_ist ASCENDING. LOOP AT gt_shows INTO gw_show. "电话联系人 "ZM业务助理 供货方 IF ls_head-g_cname = '' OR ls_head-ghf_tel = ''. SELECT SINGLE * INTO CORRESPONDING FIELDS OF gs_vbpa FROM vbpa WHERE vbeln = gw_show-vbeln AND parvw = 'ZM'. IF sy-subrc = 0. SELECT SINGLE * INTO CORRESPONDING FIELDS OF gs_pa0002 FROM pa0002 WHERE pernr = gs_vbpa-pernr. SELECT * INTO CORRESPONDING FIELDS OF TABLE gt_pa0105 FROM pa0105 WHERE pernr = gs_vbpa-pernr AND subty IN ('0005','0020'). IF gs_pa0002-nachn <> ''. ls_head-g_cname = gs_pa0002-nachn. ENDIF. SORT gt_pa0105 BY pernr subty ASCENDING . IF ls_head-ghf_tel IS INITIAL . LOOP AT gt_pa0105. IF gt_pa0105-subty = '0005'. ls_head-ghf_tel = gt_pa0105-usrid. ELSEIF gt_pa0105-subty = '0020'. IF gt_pa0105-usrid_long+20(20) <> ''. gt_pa0105-usrid_long+20(1) = '-'. CONCATENATE ls_head-ghf_tel gt_pa0105-usrid_long INTO ls_head-ghf_tel SEPARATED BY '/'. CONDENSE ls_head-ghf_tel NO-GAPS. ELSE. CONDENSE gt_pa0105-usrid_long NO-GAPS. CONCATENATE ls_head-ghf_tel gt_pa0105-usrid_long INTO ls_head-ghf_tel SEPARATED BY '/'. ENDIF. ENDIF. ENDLOOP. ENDIF. ENDIF. ENDIF. "ZP 联系人 购货方 IF ls_head-m_name = '' OR ls_head-m_tel = ''. SELECT SINGLE * INTO CORRESPONDING FIELDS OF gs_vbpa FROM vbpa WHERE vbeln = gw_show-vgbel AND parvw = 'ZP'. IF sy-subrc = 0. SELECT SINGLE * INTO CORRESPONDING FIELDS OF gs_knvk FROM knvk WHERE parnr = gs_vbpa-parnr AND kunnr = gw_show-kunnr. SELECT SINGLE * INTO CORRESPONDING FIELDS OF gs_adcp FROM adcp WHERE persnumber = gs_vbpa-adrnp. IF gs_knvk-name1 <> ''. ls_head-m_name = gs_knvk-name1. ENDIF. IF gs_adcp-tel_number <> ''. ls_head-m_tel = gs_adcp-tel_number. ENDIF. IF gs_adcp-fax_number <> ''. CONCATENATE ls_head-m_tel gs_adcp-fax_number INTO ls_head-m_tel SEPARATED BY '/'. ENDIF. ENDIF. ENDIF. "电话联系人 PERFORM fill_cell_gs USING:lv_cur_line 1 0 11 1 gw_show-wadat_ist, lv_cur_line 2 0 11 1 gw_show-bstkd, lv_cur_line 3 0 11 1 gw_show-vbeln, lv_cur_line 4 0 11 1 gw_show-arktx, lv_cur_line 5 0 11 1 gw_show-zgg, lv_cur_line 6 0 11 1 gw_show-vrkme, lv_cur_line 7 0 11 1 gw_show-lfimg, lv_cur_line 8 0 11 1 gw_show-zdj, lv_cur_line 9 0 11 1 gw_show-kzwi1. lv_count = lv_count + gw_show-kzwi1. lv_cur_line = lv_cur_line + 1. AT LAST. gv_flag = 'X'. PERFORM fill_cell_gs USING:lv_cur_line 9 0 11 1 lv_count."汇总金额 lv_cur_line = lv_cur_line + 1. ENDAT. IF gv_flag = ''. PERFORM excel_row_insert USING:gs_sheet lv_cur_line 1."未结束，插入空白行 等待下一循环 ENDIF. ENDLOOP. "set PERFORM fill_cell_gs2 USING: 5 6 0 11 0 ls_head-g_cname."供货方联系人信息 PERFORM fill_cell_gs2 USING: 6 6 0 11 0 ls_head-ghf_tel." PERFORM fill_cell_gs2 USING: 5 2 0 11 0 ls_head-m_name."购货方联系人信息 PERFORM fill_cell_gs2 USING: 6 2 0 11 0 ls_head-m_tel." "set "截止，货款余额 READ TABLE gt_kxmx WITH KEY kunnr = gw_show-kunnr. IF sy-subrc = 0. gt_kxmx-dm1 = gt_kxmx-dm1 + lv_count. WRITE gt_kxmx-dm1 TO lv_swzf. WRITE gt_kxmx-wgq TO lv_wgq. WRITE gt_kxmx-ygq TO lv_ygq. ENDIF. CLEAR:gs_line1. CONCATENATE '二、截止' s_bldat-high+0(4) '年' s_bldat-high+4(2) '月' s_bldat-high+6(2) '日， ' ls_head-m_kunnr '尚未支付广东正业科技股份有限公司' ls_head-ktext '货款 ' lv_swzf ' 元，' '其中本月未过期货款 ' lv_wgq ' 元，已到期货款 ' lv_ygq ' 元，具体明细如下：' INTO gs_line1. * CONDENSE gs_line1 NO-GAPS."去空格 PERFORM fill_cell_gs2 USING:lv_cur_line 1 0 11 1 gs_line1." lv_cur_line = lv_cur_line + 2."未清清单开始需加2 CLEAR:gv_flag. SORT ct_bsid BY gjahr ASCENDING monat ASCENDING. LOOP AT ct_bsid. PERFORM fill_cell_gs USING:lv_cur_line 1 0 12 1 ct_bsid-gjahr,"年 lv_cur_line 3 0 12 1 ct_bsid-monat,"月 lv_cur_line 5 0 12 1 ct_bsid-dmbtr."未清合计 lv_cur_line = lv_cur_line + 1. AT LAST. gv_flag = 'X'. ENDAT. IF gv_flag = ''. PERFORM excel_row_insert USING:gs_sheet lv_cur_line 1."未结束，插入空白行 等待下一循环 ENDIF. ENDLOOP. IF ct_bsid[] IS INITIAL.. lv_cur_line = lv_cur_line + 1."add by ly 20160216 ENDIF. CLEAR gs_line1. CONCATENATE '请 ' ls_head-m_kunnr '收到本对账单之日起于5个工作日内予以签章确认，并回传至广东正业科技股份有限公司。' '否则，广东正业科技股份有限公司视同贵司对本账单确认无误！' INTO gs_line1. PERFORM fill_cell_gs2 USING:lv_cur_line 1 0 11 1 gs_line1." PERFORM merged USING lv_cur_line 1 lv_cur_line 9 0."不知道什么原因，多出个边框 lv_cur_line = lv_cur_line + 3. PERFORM fill_cell_gs2 USING:lv_cur_line 2 0 11 0 ls_head-g_cname."供货方制表 PERFORM fill_cell_gs2 USING:lv_cur_line 7 0 11 0 ls_head-m_kunnr."购货方 lv_cur_line = lv_cur_line + 1. PERFORM fill_cell_gs2 USING:lv_cur_line 2 0 11 0 sy-datum."制表日期 GET PROPERTY OF gs_excel 'ActiveWorkbook' = gs_workbook. CALL METHOD OF gs_workbook 'SAVE'. IF sy-subrc = 0. MESSAGE '数据已导出' TYPE 'S'. ELSE. MESSAGE '数据导出失败' TYPE 'S' DISPLAY LIKE 'E'. ENDIF. * CALL METHOD OF gs_workbook 'CLOSE'. CALL METHOD OF gs_excel 'QUIT'. FREE OBJECT gs_sheet. FREE OBJECT gs_workbook. FREE OBJECT gs_excel.
剩下的自己研究。。。
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/18630.html