ABAP使用OLE方法总结
ABAP使用OLE方法总结
SAP ABAP OLE 操作EXCEL小结
ABAP直接操作EXCEL实现数据处理。这里是用OLE实现操作EXCEL的。现在还有DOI操作EXCEL等方法。以后用到再做介绍。此处不是直接生成EXCEL文件，而是从服务器已经上传的EXCEL模板中下载模板然后打开修改实现数据保存。也可以直接创建新的EXCEL文件往里面传递数据并设置格式。这里不做介绍。
1.采用模板文件实现。上传EXCEL模板
T-code：SMW0 WebRFC 的二进制数据 包：MI点击模板数据保存在系统表wwwdata中。test
2.在程序中下载模板
*下载EXCEL模板FORM 按钮，上传excel模板。
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". ENDIF. fname = ls_destination. ENDIF. ENDFORM. "fm_excel 3.下载完模板后，打开模板文件，填入数据 CREATE OBJECT 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 *&---------------------------------------------------------------------* *& 向excel中的指定行插入N行 *&---------------------------------------------------------------------* FORM excel_row_insert USING lcobj_sheet lc_row lc_count. DATA lc_range TYPE ole2_object. DATA h_borders TYPE ole2_object. DO lc_count TIMES. CALL METHOD OF lcobj_sheet "Rows" = lc_range EXPORTING #1 = 6. CALL METHOD OF lc_range "Copy". “COPY第6行插入一个新行 CALL METHOD OF lcobj_sheet "Rows" = lc_range EXPORTING #1 = lc_row. CALL METHOD OF lc_range "Insert". CALL METHOD OF lc_range "ClearContents". "是否需要清空Cell ENDDO. ENDFORM. "excel_row_insert *&---------------------------------------------------------------------* *& Form fill_range *&---------------------------------------------------------------------* * text 填充EXCEL 单元格 *----------------------------------------------------------------------* * -->VALUE(F_ROW) text * -->VALUE(F_COL) text * -->VALUE(F_VALUE) text *----------------------------------------------------------------------* FORM fill_range USING value(f_row) value(f_col) value(f_value). DATA: row TYPE i, col TYPE i. row = f_row. col = f_col. CALL METHOD OF excel "CELLS" = cell EXPORTING #1 = row #2 = col. SET PROPERTY OF cell "VALUE" = f_value. ENDFORM. "fill_range *&---------------------------------------------------------------------* *& Form borderrange *&---------------------------------------------------------------------* * text：设置EXCEL中所插入的数据行边框线格式 *----------------------------------------------------------------------* * -->LCOBJ_EXCEL text * -->RANGE text *----------------------------------------------------------------------* FORM borderrange USING lcobj_excel range . DATA: lc_cell TYPE ole2_object , lc_borders TYPE ole2_object . CALL METHOD OF lcobj_excel "RANGE" = lc_cell EXPORTING #1 = range. DO 4 TIMES . CALL METHOD OF lc_cell "BORDERS" = lc_borders EXPORTING #1 = sy-index. SET PROPERTY OF lc_borders "LineStyle" = "1". SET PROPERTY OF lc_borders "WEIGHT" = 2. "4=max SET PROPERTY OF lc_borders "ColorIndex" = "1". ENDDO. FREE OBJECT lc_borders. FREE OBJECT lc_cell. ENDFORM. "borderrange
暂时只用到了这么多，还有ABAP设置EXCEL字体，对齐方式等功能。什么时候用到了再
做研究。希望对大家有所帮助。
