ABAP_Excel 处理总结

ABAP_Excel 处理总结
1调用原理
1.1创建对象
首先定义OLE对象，创建OLE对象为excel类型。定义格式如下：CREATE OBJECT application 'excel.APPLICATION'.
然后通过excel对象application，创建与excel对应的workbook，sheet，range，cell等对象。
再次通过调用各个对象的方法及属性等，实现对excel的操作。
1.2Excel宏
利用宏可以查看各个excel对象的方法和属性。宏的录制和利用参见文档：
(略)
2方法用例
*&---------------------------------------------------------------------*
*& Report ZDEALEXCEL
*& Author ALEX
*& Date 2009.10
*&
*&---------------------------------------------------------------------*
*& 本程序总结了常用的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) process_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 sscrfields.

DEFINE m_message.
case sy-subrc.
when '0'.
when others.
message 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 sscrfields-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 sscrfields-ucomm EQ 'ONLI'.
IF p_file IS INITIAL.
MESSAGE e803(zdev).
ENDIF.
ELSEIF sscrfields-ucomm EQ 'CLI0'.
PERFORM demo_forms.
ELSEIF sscrfields-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