OLE对EXCEL操作总结_ole对excel操作总结

OLE对EXCEL操作总结由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“ole对excel操作总结”。

方法用例

*&-------* *& 本程序总结了常用的ABAP对EXCEL的操作，包括如下子例程 *& 方法名 方法说明 *&(1)create_excel 创建excel *&(2)insert_row 插入行 *&(3)fill_cell 填充单元格 *&(4)columnwidth 调整列宽 *&(5)create_sheet 创建sheet *&(6)copy_sheet_all sheet复制 *&(7)copy_sheet_area sheet区域复制 *&(8)copy_cells 复制单元格

*&(9)range_borders 边框粗细 *&(10)row_color 行颜色 *&(11)preview_excel 预览 *&(12)free_object 释放对象 *&(13)close_excel 关闭excel *&(14)download_excel_fromserver 从服务器下载excel模板（进入模板事务代码SMW0）

*&(15)open_excel_hide 隐蔽打开excel *&(16)open_excel 打开excel *&(17)open_excel_sheet 打开指定的excel的sheet *&(18)open_excel_by_sheet 打开sheet *&(19)select_range 选择范围 *&(20)clear_data 清除数据 *&(21)merge_cells 合并单元格 *&(22)proce_indcator 进度指示 *&(23)delete_row 删除行 *&(24)add_comment 添加注释 *&(25)hide_columns 隐藏列 *&(26)delete_columns 删除列 *&(27)hide_row 隐藏行 * 保护工作区 lock_unlock_excel ：未完成 * 保护整个工作表 lock_sheet ： 未完成*& 本示例程序作为ABAP对EXCEL操作的参考,可以根据需要做适当的调整 *&-------* REPORT zdealexcel.TYPE-POOLS ole2.TABLES crfields.DEFINE m_meage.case sy-subrc.when '0'.when others.meage e000(zmeg)with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.endcase.END-OF-DEFINITION.DATA: application TYPE ole2_object, “excel object workbook TYPE ole2_object, ”excel workbook objcet sheet TYPE ole2_object, “workbook sheet object columns TYPE ole2_object, ”sheet col objcet rows TYPE ole2_object, “sheet row objcet range TYPE ole2_object, ”range range1 TYPE ole2_object, “range1 font TYPE ole2_object, ”font cell TYPE ole2_object, “cell cell1 TYPE ole2_object, ”cell1 sheet1 TYPE ole2_object, “workbook sheet object borders TYPE ole2_object.”borders

DATA: index TYPE i VALUE 0.*定义模板下载到本地保存的全名称

DATA: p_down_file LIKE sapb-sappfad.“定义模板下载到本地保存的全名称

*演示excel操作

SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN: PUSHBUTTON 01(12)but0 USER-COMMAND cli0, PUSHBUTTON 20(15)but2 USER-COMMAND cli2.SELECTION-SCREEN END OF LINE.*导入数据块

SELECTION-SCREEN BEGIN OF BLOCK status2 WITH FRAME TITLE text-f02.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT 1(20)text-002.PARAMETERS: p_file LIKE rlgrap-filename.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK status2.*导出数据块

SELECTION-SCREEN BEGIN OF BLOCK status3 WITH FRAME TITLE text-f03.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT 1(20)text-003.PARAMETERS: p_down LIKE rlgrap-filename.SELECTION-SCREEN: PUSHBUTTON 75(20)but1 USER-COMMAND cli1.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK status3.INITIALIZATION.CREATE OBJECT application 'excel.APPLICATION'.but0 = '演示excel操作'.”初始化导出button显示名称 but1 = '导出'.“初始化导出button显示名称 but2 = '从模板导出excel'.”从模板导出excel

*-------------------------设置文件上载-------------------------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.CALL FUNCTION 'WS_FILENAME_GET' EXPORTING def_path = p_file mask = ',*.*.' mode = 'O' title = text-f01 IMPORTING filename = p_file EXCEPTIONS inv_winsys = 1 no_batch = 2 selection_cancel = 3 selection_error = 4 OTHERS = 5.CHECK sy-subrc = 0 AND NOT p_file IS INITIAL.*-------------------------设置文件下载-------------------------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_down.CALL FUNCTION 'WS_FILENAME_GET' EXPORTING def_path = p_down mask = ',*.xls,*.xls.' mode = 'S' title = text-f02 IMPORTING filename = p_down EXCEPTIONS inv_winsys = 1 no_batch = 2 selection_cancel = 3 selection_error = 4 OTHERS = 5.CHECK sy-subrc = 0 AND NOT p_down IS INITIAL.* AT SELECTION-SCREEN.IF crfields-ucomm EQ 'CLI1'.IF p_down IS INITIAL.MESSAGE e804(zdev).ELSE.p_down_file = p_down.PERFORM download_excel_fromserver USING 'ZHRI0004' p_down_file.“下载模板到本地

MESSAGE i600(zdev)WITH '模板导出成功'.ENDIF.ELSEIF crfields-ucomm EQ 'ONLI'.IF p_file IS INITIAL.MESSAGE e803(zdev).ENDIF.ELSEIF crfields-ucomm EQ 'CLI0'.PERFORM demo_forms.ELSEIF crfields-ucomm EQ 'CLI2'.PERFORM demo_forms_server USING p_file.ENDIF.START-OF-SELECTION.PERFORM open_excel_hide USING p_file.PERFORM close_excel.PERFORM open_excel USING p_file.PERFORM close_excel.PERFORM open_excel_sheet USING 'sheet2' p_file.DO 10 TIMES.PERFORM insert_row USING 1.PERFORM fill_cell USING 1 1 0 'e'.PERFORM fill_cell USING 1 2 0 'f'.PERFORM fill_cell USING 1 3 0 'g'.index = index + 1.ENDDO.PERFORM close_excel.PERFORM free_object.*&-------* *& Form demo_forms_server *&-------* * 从SMW0下载excel模板

*--------* *-->P_FILE excel本地路径

*--------* FORM demo_forms_server USING p_file.p_down_file = p_file.PERFORM proce_indcator USING '程序正在下载模板' 0.PERFORM download_excel_fromserver USING 'ZHRI0004' p_down_file.PERFORM proce_indcator USING '程序正在初始化OLE控件' 0.PERFORM proce_indcator USING '程序正在打开excel文档' 0.PERFORM open_excel_sheet USING '处分处罚信息批导入' p_file.DO 10 TIMES.PERFORM insert_row USING 5.PERFORM fill_cell USING 5 1 0 'e'.PERFORM fill_cell USING 5 2 0 'f'.PERFORM fill_cell USING 5 3 0 'g'.index = index + 1.ENDDO.PERFORM clear_data USING 1 6 2 7.PERFORM merge_cells USING 1 6 2 7.PERFORM add_comment USING 8 1 'OK'.PERFORM close_excel.PERFORM free_object.ENDFORM.”demo_forms_server

*&-------* *& Form demo_forms *&-------* * 演示用form *--------* FORM demo_forms.PERFORM create_excel.DO 10 TIMES.PERFORM insert_row USING 1.PERFORM fill_cell USING 1 1 0 'a'.PERFORM fill_cell USING 1 2 0 'b'.PERFORM fill_cell USING 1 3 0 'c'.index = index + 1.ENDDO.PERFORM columnwidth USING 'A:C' ''.PERFORM create_sheet.PERFORM create_sheet.PERFORM copy_sheet_all.PERFORM copy_sheet_area.PERFORM copy_cells.PERFORM range_borders.PERFORM row_color USING 1.PERFORM preview_excel.PERFORM del_sheet USING 'sheet2'.PERFORM free_object.ENDFORM.“demo_forms

*&-------* *&(1)Form create_excel *&-------* * 创建excel *--------* FORM create_excel.IF sy-subrc 0.MESSAGE 'EXCEL ERROR' TYPE 'S' DISPLAY LIKE 'E'.STOP.ENDIF.CALL METHOD OF application 'WORKBOOKS' = workbook.SET PROPERTY OF application 'VISIBLE' = 1.SET PROPERTY OF application 'SHEETSINNEWWORKBOOK' = 1.CALL METHOD OF workbook 'ADD' = sheet.CALL METHOD OF sheet 'ACTIVE'.ENDFORM.”create_excel

*&-------* *&(2)Form insert_row *&-------* * 根据行号插入一行

*--------* *-->I_ROW 行号

*--------* FORM insert_row USING i_row.CALL METHOD OF application 'ROWS' = rows EXPORTING #1 = i_row.CALL METHOD OF rows 'INSERT'.ENDFORM.“insert_row

*&-------* *&(3)Form fill_cell *&-------* * 填充单元格

*--------* *-->I_ROW 行号 *-->I_COL 列号 *-->BOLD 是否加粗 *-->P_VALUE 值

*--------* FORM fill_cell USING i_row i_col bold p_value.CALL METHOD OF application 'CELLS' = cell EXPORTING #1 = i_row #2 = i_col.SET PROPERTY OF cell 'VALUE' = p_value.SET PROPERTY OF cell 'HORIZONTALALIGNMENT' = 2.GET PROPERTY OF cell 'FONT' = font.SET PROPERTY OF font 'BOLD' = bold.SET PROPERTY OF font 'COLORINDEX' = 41.CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '2'.SET PROPERTY OF borders 'LINESTYLE' = '0'.SET PROPERTY OF borders 'WEIGHT' = 0.ENDFORM.”fill_cell

*&-------* *&(4)Form columnwidth *&-------* * 设置列宽为自动或是固定值

*--------* *-->COLNUM 列号,可以为单个列或是多个列 *-->WIDTH 列宽值

*--------* FORM columnwidth USING colnum width.CALL METHOD OF application 'COLUMNS' = columns EXPORTING #1 = colnum.IF width = ''.CALL METHOD OF columns 'AutoFit'.ELSE.SET PROPERTY OF columns 'COLUMNWIDTH' = width.ENDIF.ENDFORM.“columnwidth

*&-------* *&(5)Form create_sheet *&-------* * 创建sheet *--------* FORM create_sheet.CALL METHOD OF application 'sheets' = sheet.CALL METHOD OF sheet 'ADD'.ENDFORM.”create_sheet

*&-------* *&(6)Form copy_sheet_all *&-------* * 复制sheet全部内容到另一个sheet *--------* FORM copy_sheet_all.CALL METHOD OF application 'Sheets' = sheet EXPORTING #1 = 'Sheet1'.CALL METHOD OF sheet 'Cells' = cell.CALL METHOD OF cell 'copy'.CALL METHOD OF application 'Sheets' = sheet EXPORTING #1 = 'Sheet3'.CALL METHOD OF application 'Range' = range EXPORTING #1 = 'A1'.CALL METHOD OF range 'PasteSpecial'.ENDFORM.“copy_sheet_all

*&-------* *&(7)Form copy_sheet_area *&-------* * 复制sheet部分内容到另一个sheet *--------* FORM copy_sheet_area.CALL METHOD OF application 'Sheets' = sheet EXPORTING #1 = 'Sheet1'.CALL METHOD OF sheet 'Range' = range EXPORTING #1 = 'A1' #2 = 'B10'.CALL METHOD OF range 'copy'.CALL METHOD OF application 'Sheets' = sheet1 EXPORTING #1 = 'Sheet2'.CALL METHOD OF sheet1 'Range' = range1 EXPORTING #1 = 'A1' #2 = 'B10'.CALL METHOD OF range1 'PasteSpecial'.ENDFORM.”copy_sheet_area

*&-------* *&(8)Form copy_cells *&-------* * 在一个sheet内复制单元格

*--------* FORM copy_cells.CALL METHOD OF application 'Sheets' = sheet EXPORTING #1 = 'Sheet1'.CALL METHOD OF sheet 'Range' = range EXPORTING #1 = 'A1' #2 = 'B10'.CALL METHOD OF range 'copy'.CALL METHOD OF sheet 'Range' = range1 EXPORTING #1 = 'A12' #2 = 'B22'.CALL METHOD OF range1 'PasteSpecial'.ENDFORM.“copy_cells

*&-------* *&(9)Form range_borders *&-------* * 指定range加边框

*--------* FORM range_borders.CALL METHOD OF application 'Range' = range EXPORTING #1 = 'A1' #2 = 'B10'.CALL METHOD OF range 'BORDERS' = borders.SET PROPERTY OF borders 'LINESTYLE' = 1.SET PROPERTY OF borders 'WEIGHT' = 10.ENDFORM.”range_borders

*&-------* *&(10)Form row_color *&-------* * 设定行背景色

*--------* *-->I_ROW 行号

*--------* FORM row_color USING i_row.CALL METHOD OF application 'ROWS' = rows EXPORTING #1 = i_row.GET PROPERTY OF rows 'Interior' = font.SET PROPERTY OF font 'ColorIndex' = '10'.ENDFORM.“row_color *&-------* *&(11)Form preview_excel *&-------* * 预览excel *--------* FORM preview_excel.SET PROPERTY OF application 'VISIBLE' = 1.CALL METHOD OF sheet 'PRINTPREVIEW'.* m_meage.ENDFORM.”preview_excel

*&-------* *&(12)Form free_object *&-------* * 释放object *--------* FORM free_object.FREE OBJECT font.FREE OBJECT range.FREE OBJECT range1.FREE OBJECT columns.FREE OBJECT rows.FREE OBJECT cell.FREE OBJECT cell1.FREE OBJECT sheet1.FREE OBJECT sheet.FREE OBJECT workbook.FREE OBJECT application.ENDFORM.“free_object

*&-------* *&(13)Form close_excel *&-------* * 关闭excel并保存

*--------* FORM close_excel.CALL METHOD OF workbook 'Save'.CALL METHOD OF workbook 'Close' EXPORTING #1 = 0.CALL METHOD OF application 'Quit'.ENDFORM.”close_excel

*&-------* *&(14)Form download_excel_fromserver *&-------* * 从服务器下载excel模板

*--------* *-->P_OBJID 模板名称 *-->P_DEST 存档路径

*--------* FORM download_excel_fromserver USING p_objid LIKE www.daodoc.comment *&-------* * 添加注释

*--------* *-->I_ROW 行号 *-->I_COL 列号 *-->P_TEXT 注释内容

*--------* FORM add_comment USING i_row i_col p_text.CALL METHOD OF application 'Cells' = cell EXPORTING #1 = i_row #2 = i_col.CALL METHOD OF cell 'AddComment' = cell1.CALL METHOD OF cell1 'Text' EXPORTING #1 = p_text.CALL METHOD OF cell1 'Shape' = font.SET PROPERTY OF font 'Width' = '20'.SET PROPERTY OF font 'Height' = '20'.ENDFORM.“add_comment

*&-------* *&（25）Form hide_columns *&-------* * 隐藏列

*--------* *-->COLNUM 输入列号

*--------* FORM hide_columns USING colnum.CALL METHOD OF application 'COLUMNS' = columns EXPORTING #1 = colnum.SET PROPERTY OF columns 'Hidden' = 'true'.ENDFORM.”hide_columns

*&-------* *&(26)Form delete_columns *&-------* * 删除列

*--------* *-->COLNUM 输入列号

*--------* FORM delete_columns USING colnum.CALL METHOD OF application 'COLUMNS' = columns EXPORTING #1 = colnum.CALL METHOD OF columns 'DELETE'.ENDFORM.“delete_columns

*&-------* *&（27）Form hide_row *&-------* * 隐藏行

*--------* *-->I_ROW 输入行号

*--------* FORM hide_row USING i_row.CALL METHOD OF application 'ROWS' = rows EXPORTING #1 = i_row.SET PROPERTY OF rows 'Hidden' = 'true'.ENDFORM.”hide_row 2 注意事项 2.1 Sheet名称

Sheet名为中文时，在调用sheet对象时往往会调用不到，用英文字母不存在该问题。2.2 释放对象

程序结束时需要释放对象，否则Excel有可能无法关闭。释放对象代码如下： FORM free_object.FREE OBJECT font.FREE OBJECT range.FREE OBJECT range1.FREE OBJECT columns.FREE OBJECT rows.FREE OBJECT cell.FREE OBJECT cell1.FREE OBJECT sheet1.FREE OBJECT sheet.FREE OBJECT workbook.FREE OBJECT application.ENDFORM.