OLE填充EXCEL详解编程语言

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

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

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.

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.

*&---------------------------------------------------------------------* 
*& 向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

  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.