abap开发中ole自动输出excel表格

##abap开发中ole自动输出excel表格，并存入数据，保存数据

"ole相关定义

DATA: v_excel TYPE ole2_object,
v_sheet TYPE ole2_object,
v_book TYPE ole2_object,
v_rows TYPE ole2_object,
v_cell TYPE ole2_object,
cells TYPE ole2_object,
cells1 TYPE ole2_object,
range TYPE ole2_object,
selection TYPE ole2_object,
v_sheet_number TYPE i.
DATA: lv_file TYPE string,
lo_shapes TYPE ole2_object,
lo_cell TYPE ole2_object,
lo_textbox TYPE ole2_object,
lo_frame TYPE ole2_object,
lo_fill TYPE ole2_object,
lo_line TYPE ole2_object,
lo_characters TYPE ole2_object.

ole相关代码

*–设置excel可见
CALL METHOD OF v_excel ‘Worksheets’ = v_sheet
EXPORTING
#1 = 1.
CALL METHOD OF v_sheet ‘Activate’.
SET PROPERTY OF v_excel ‘Visible’ = 1.

*–打开excel模板
PERFORM frm_open_excel USING l_fullpath.

*–向excel写数据
PERFORM frm_write_excel_sheet.

• 关闭EXCEL并保存
  PERFORM ffm_save_excel USING l_fullpath.

*–打开excel模板
FORM frm_open_excel USING p_l_fullpath.

CREATE OBJECT v_excel ‘Excel.Application’.

CALL METHOD OF v_excel ‘Workbooks’ = v_book.

CALL METHOD OF v_book ‘Open’ = v_book
EXPORTING
#1 = p_l_fullpath.

CALL METHOD OF v_book ‘Sheets’ = v_sheet
EXPORTING
#1 = 1.

写入数据

*–向excel写数据

FORM frm_write_excel_sheet .
DATA: l_title(20),
l_uname(20),
l_lines TYPE i,
l_mod TYPE i,
l_count TYPE i,
l_index TYPE i,
l_ct TYPE i.

DATA lc_range TYPE ole2_object.

CONCATENATE pn-begda(4) ‘年’ pn-begda+4(2) ‘月工资发放明细表’ INTO l_title.
SELECT SINGLE adrp~name_text
INTO l_uname
FROM usr21
INNER JOIN adrp ON usr21~persnumber = adrp~persnumber
WHERE usr21~bname = sy-uname
AND adrp~date_from <= sy-datum
AND adrp~date_to >= sy-datum.
PERFORM frm_write_cell USING 1 2 l_title. "标题
READ TABLE gt_data INTO gs_data INDEX 1.

"excel数据填写格式
FORM frm_write_cell USING pa_row
pa_col
pa_val.
CALL METHOD OF v_sheet ‘Cells’ = v_cell
EXPORTING
#1 = pa_row
#2 = pa_col.
SET PROPERTY OF v_cell ‘Value’ = pa_val.
ENDFORM.
PERFORM frm_write_cell USING 2 4 gs_data-butxt. "公司

PERFORM frm_write_cell USING 6 23 l_uname. "制表人

DESCRIBE TABLE gt_data LINES l_lines.

IF l_lines > 1.
l_count = l_lines - 1.
PERFORM excel_row_insert USING v_sheet
4
l_count.
ENDIF.

LOOP AT gt_data INTO gs_data.
l_index = sy-tabix + 3.
PERFORM frm_write_cell USING l_index 1 gs_data-no. "序号
PERFORM frm_write_cell USING l_index 2 gs_data-stext1. "事业部
PERFORM frm_write_cell USING l_index 3 gs_data-stext_o. "末级
PERFORM frm_write_cell USING l_index 4 gs_data-stext. "岗位
PERFORM frm_write_cell USING l_index 5 gs_data-pernr. "工号
PERFORM frm_write_cell USING l_index 6 gs_data-sname. "姓名
PERFORM frm_write_cell USING l_index 7 gs_data-rlzrq. "入离职日期
PERFORM frm_write_cell USING l_index 8 gs_data-az101. "工资总额
PERFORM frm_write_cell USING l_index 9 gs_data-asb. "社保
PERFORM frm_write_cell USING l_index 10 gs_data-az362. "公积金

PERFORM frm_write_cell USING l_index 11  gs_data-a3804.    "年金
PERFORM frm_write_cell USING l_index 12  gs_data-az403.    "所得税
PERFORM frm_write_cell USING l_index 13  gs_data-a3805.    "税后年金
PERFORM frm_write_cell USING l_index 14  gs_data-a3002.    "手续费
PERFORM frm_write_cell USING l_index 15  gs_data-a3001.    "工会费
PERFORM frm_write_cell USING l_index 16  gs_data-a3009.    "电话费
PERFORM frm_write_cell USING l_index 17  gs_data-azf.    "杂费
PERFORM frm_write_cell USING l_index 18  gs_data-a3008.    "代扣社保
PERFORM frm_write_cell USING l_index 19  gs_data-a3011.    "扣货款
PERFORM frm_write_cell USING l_index 20  gs_data-a3025.    "扣借款

PERFORM frm_write_cell USING l_index 21  gs_data-a3105.    "预留
PERFORM frm_write_cell USING l_index 22  gs_data-a3024.    "暂扣货款
PERFORM frm_write_cell USING l_index 23  gs_data-afn .    "返纳工资
PERFORM frm_write_cell USING l_index 24  gs_data-azsf.    "实发

ENDLOOP.

• PERFORM F_SELECTRANGE USING 8 4 24 20.

ENDFORM.

• 关闭EXCEL并保存

FORM ffm_save_excel USING p_l_fullpath.

• SET PROPERTY OF V_EXCEL ‘DisplayAlerts’ = 0.
• CALL METHOD OF V_BOOK ‘SAVEAS’
• EXPORTING

•  #1 = P_L_FULLPATH.
  

• CALL METHOD OF V_BOOK ‘Exit’ = V_BOOK.
• FREE OBJECT V_EXCEL.
  CALL METHOD OF v_book ‘Save’.
  CALL METHOD OF v_book ‘Close’
  EXPORTING
  #1 = 0.
  CALL METHOD OF v_book ‘Quit’.

FREE OBJECT v_excel.
FREE OBJECT v_sheet.
FREE OBJECT v_cell.
FREE OBJECT v_book.
ENDFORM.

“excel填入数据模板

FORM frm_write_cell USING pa_row
pa_col
pa_val.
CALL METHOD OF v_sheet ‘Cells’ = v_cell
EXPORTING
#1 = pa_row
#2 = pa_col.
SET PROPERTY OF v_cell ‘Value’ = pa_val.
ENDFORM.

结束语

abap新手开发第一篇，若有问题，请大家留言，谢谢大家