SAP OLE常规操作
OLE常规操作
现在很多例子,ole相关对象都是用的全局变量,尝试用类成员变量封装了常规操作。
1. 打开Excel
METHOD open_excel .
" 创建excel对象
CREATE OBJECT mv_excel 'EXCEL.APPLICATION'.
" 设置OLE操作可见
SET PROPERTY OF mv_excel 'Visible' = 1 .
" 设置excel提示不可见
SET PROPERTY OF mv_excel 'DISPLAYALERTS' = 0 .
" 创建工作簿对象
CALL METHOD OF
mv_excel
'Workbooks' = mv_books.
" 打开指定工作簿,mv_template_file 必须是全路径名称
CALL METHOD OF
mv_books
'Open' = mv_book
EXPORTING
#1 = mv_template_file .
" 创建工作表对象
CALL METHOD OF
mv_book
'Worksheets' = mv_sheets.
ENDMETHOD.
2. 复制粘贴数据
METHOD paste2sheet .
" 刷新
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
" 选择指定工作表
CALL METHOD OF
mv_book
'WORKSHEETS' = mv_sheet
EXPORTING
#1 = sheet_name.
" 激活工作表
CALL METHOD OF mv_sheet 'Activate'.
" 选择单元格
CALL METHOD OF mv_sheet
'Cells' = mv_cell
EXPORTING
#1 = row
#2 = col .
CALL METHOD OF mv_cell 'Select' .
" 调用系统剪切板,粘贴数据,
" 内表lt_str中所有字段cl_abap_char_utilities=>horizontal_tab拼接
DATA: lv_rc TYPE i .
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lt_str
CHANGING
rc = lv_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
CALL METHOD OF mv_sheet 'Paste' .
ENDMETHOD.
3. 执行宏
宏命令是工作表的功能,这里用mv_excel调用
#1是宏名称
#2宏参数,参数可不指定类型,多个参数依次用 #3 #4
METHOD run_marco .
CALL METHOD OF mv_excel 'RUN'
EXPORTING
#1 = `执行宏`
#2 = process_type.
ENDMETHOD.
4. 添加工作表并命名
一个工作簿(mv_book)可以有多个工作表(mv_sheets),添加工作表使用 mv_sheets.
excel打开后会有默认的sheet1,不管光标在哪,新增的sheet都是在原来的基础上+1.
#1 也可以用数字,数字代表工作表所在的位置
假如有3个工作表,顺序是 sheet2,sheet3,sheet1 , 那么 #1 = 2 选择的是 sheet3
METHOD add_blank_sheet .
CALL METHOD OF mv_sheets 'Add' .
mv_index = mv_index + 1 .
DATA(new_sheet) = |Sheet{ mv_index }|.
" #1 通过工作表名字选择
CALL METHOD OF
mv_book
'WORKSHEETS' = mv_sheet
EXPORTING
#1 = new_sheet.
CALL METHOD OF mv_sheet 'Activate'.
" 重命名
SET PROPERTY OF mv_sheet 'Name' = sheet_name .
ENDMETHOD.
5. 关闭工作簿
METHOD close_excel .
" 保存
CALL METHOD OF mv_book 'SAVE' .
" 退出,防止被占用
CALL METHOD OF mv_book 'Quit' .
ENDMETHOD.
6. 下载模板
METHOD download_template .
DATA: lv_name TYPE string,
lv_dir TYPE string,
lv_full_path TYPE string.
" 这里默认后缀名是 xls ,导入的模板也需要是xls格式
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'xls' "FILE format
default_file_name = 'AA' "default name
CHANGING
filename = lv_name "default file download path
path = lv_dir "file save path
fullpath = lv_full_path "module save path
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF lv_full_path = '' OR lv_dir IS INITIAL.
MESSAGE '请选择下载路径' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
DATA: lv_destination TYPE rlgrap-filename,
ls_objdata TYPE wwwdatatab.
lv_destination = lv_full_path.
SELECT SINGLE relid,
objid
FROM wwwdata
INTO CORRESPONDING FIELDS OF @ls_objdata
WHERE srtf2 = 0
AND relid = 'MI'
AND objid = @template_id.
IF sy-subrc <> 0 OR ls_objdata-objid = space.
MESSAGE '模板不存在,请上载模板后再进行下载'
TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
DATA: lv_temp_file(65).
DATA: lv_key TYPE wwwdatatab .
lv_key-relid = 'MI' .
lv_key-objid = template_id .
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = lv_key
destination = lv_destination
CHANGING
temp = mv_template_file.
ENDMETHOD.
7. 其他操作
SAP OLE代码操作和EXCEL VBA操作一致,相关关键字也一样。如果不清楚用什么代码实现,可以启用EXCLE宏,手工录入宏命令,之后查看代码,再用OLE代码模拟。