SAP ABAP OLE 操作EXCEL

ole 操作

SAP ABAP OLE 操作EXCEL小结






ABAP直接操作EXCEL实现数据处理。这里是用OLE实现操作EXCEL的。现在还有



DOI操作EXCEL等方法。以后用到再做介绍。此处不是直接生成EXCEL文件，而是从



服务器已经上传的EXCEL模板中下载模板然后打开修改实现数据保存。也可以直接创建



新的EXCEL文件往里面传递数据并设置格式。这里不做介绍。







1.采用模板文件实现。上传EXCEL模板




T-code：SMW0 WebRFC 的二进制数据 包：MI







点击按钮，上传excel模板。



模板数据保存在系统表wwwdata中。







2.在程序中下载模板



*下载EXCEL模板FORM



FORM temp_excel_get.
DATA: lo_objdata LIKE wwwdatatab,
lo_mime LIKE w3mime,
lc_filename TYPE string VALUE"test01" ,
lc_fullpath TYPE string VALUE"D:\test\" ,
lc_path TYPE string VALUE"D:\test\" ,
ls_destination LIKE rlgrap-filename,
ls_objnam TYPE string,
li_rc LIKE sy-subrc,
ls_errtxt TYPE string.
DATA:p_objid TYPE wwwdatatab-objid,
p_dest LIKE sapb-sappfad.
p_objid = "ZTEST.XLS". “此处为EXCEL模板名称
CALL METHOD cl_gui_frontend_services=>file_save_dialog ”调用保存对话框
EXPORTING
default_extension = "XLS"
default_file_name = lc_filename
CHANGING
filename = lc_filename
path = lc_path
fullpath = lc_fullpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF lc_fullpath = "".
MESSAGE "不能打开excel" TYPE "E".
ENDIF.
IF sy-subrc = 0.
p_dest = lc_fullpath.
* concatenate p_objid ".XLS" into ls_objnam.
CONDENSE ls_objnam NO-GAPS.
SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
WHERE srtf2 = 0 AND relid = "MI" AND objid = p_objid.



*检查表wwwdata中是否存在所指定的模板文件
IF sy-subrc NE 0 OR lo_objdata-objid EQ space.“如果不存在，则给出错误提示
CONCATENATE "模板文件" ls_objnam "不存在" INTO ls_errtxt.
MESSAGE ls_errtxt TYPE "I".
ENDIF.
ls_destination = p_dest. ”保存路径



*如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下
CALL FUNCTION "DOWNLOAD_WEB_OBJECT"
EXPORTING
key = lo_objdata
destination = ls_destination
IMPORTING
rc = li_rc.
IF li_rc NE 0.
CONCATENATE "模板文件：" ls_objnam "下载失败" INTO ls_errtxt.
MESSAGE ls_errtxt TYPE "E".
ENDI
F.
fname = ls_destination.
ENDIF.
ENDFORM. "fm_excel







3.下载完模板后，打开模板文件，填入数据








CREATE OBJE

ole 操作

CT excel "EXCEL.APPLICATION". "Create EXCEL OBJECT
IF sy-subrc NE 0.
EXIT.
ENDIF.
SET PROPERTY OF excel "Visible" = 0. "1/0 是否显示EXCEL



CALL METHOD OF excel "Workbooks" = workbook.



CALL METHOD OF workbook "Open"
EXPORTING
#1 = fname. ."打开上面下载路径下的excel文件



CALL METHOD OF excel "Sheets" = sheet
EXPORTING
#1 = 1.



CALL METHOD OF sheet "Select" .



CALL METHOD OF sheet "ACTIVATE". “sheet 激活



SET PROPERTY OF sheet "NAME" = sheetname. “设定sheet名称



*此处假设内表itab 中已经存在需要写入excel中的数据



*并且假如从模板的第7行开始插入数据



LOOP AT itab INTO wa.
tab = wa-tabix + 6.
*在excel中插入一行
PERFORM excel_row_insert USING sheet tab 1.



*填充所插入行的每个单元格的数据
PERFORM fill_range USING tab 1 wa-t01.
PERFORM fill_range USING tab 2 wa-t02.
PERFORM fill_range USING tab 3 wa-t03.
PERFORM fill_range USING tab 4 wa-t04.
PERFORM fill_range USING tab 5 wa-t05.
PERFORM fill_range USING tab 6 wa-t06.
ENDLOOP.



*设置EXCEL中所插入的数据行边框线格式为黑色有边框




bod = tab.
CONDENSE bod NO-GAPS.
CONCATENATE "A6:F" bod INTO bod.



PERFORM borderrange USING excel bod.



PERFORM sub_excel_save."保存excel数据







*&---------------------------------------------------------------------*
*& Form sub_excel_save
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM sub_excel_save.
GET PROPERTY OF excel "ActiveSheet" = sheet. “获取活动SHEET
FREE OBJECT sheet.
FREE OBJECT workbook.



GET PROPERTY OF excel "ActiveWorkbook" = workbook.
CALL METHOD OF workbook "SAVE".



SET PROPERTY OF excel "Visible" = 1. "是否显示EXCEL 此处显示不退出



* SET PROPERTY OF excel "Visible" = 1.



* CALL METHOD OF workbook "CLOSE".
* CALL METHOD OF excel "QUIT". 注释部分为不显示直接退出



FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT excel.
ENDFORM. "save_book




< …… 此处隐藏：3662字，全部文档内容请下载后查看。喜欢就下载吧 ……