SAP ABAP OLE 操作EXCEL小结(15页).doc
-
资源ID：35023873
资源大小：68KB
全文页数：17页
- 资源格式： DOC
下载积分：15金币
快捷下载
会员登录下载
微信登录下载
三方登录下载：
微信扫一扫登录
|
友情提示
2、PDF文件下载后，可能会被浏览器默认打开，此种情况可以点击浏览器菜单，保存网页到桌面，就可以正常下载了。
3、本站不支持迅雷下载，请使用电脑自带的IE浏览器，或者360浏览器、谷歌浏览器下载即可。
4、本站资源下载后的文档和图纸-无水印,预览文档经过压缩，下载后原文更清晰。
5、试题试卷类文档，如果标题没有明确说明有答案则都视为没有答案，请知晓。
|
SAP ABAP OLE 操作EXCEL小结(15页).doc
- ABAP直接操作EXCEL实现数据处理。这里是用OLE实现操作EXCEL的。现在还有 DOI操作EXCEL等方法。以后用到再做介绍。此处不是直接生成EXCEL文件，而是从服务器已经上传的EXCEL模板中下载模板然后打开修改实现数据保存。也可以直接创建新的EXCEL文件往里面传递数据并设置格式。这里不做介绍。 1.采用模板文件实现。上传EXCEL模板 T-code：SMW0 WebRFC 的二进制数据 包：MI 点击按钮，上传excel模板。模板数据保存在系统表wwwdata中。 2.在程序中下载模板*下载EXCEL模板FORMFORM 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字体，对齐方式等功能。什么时候用到了再做研究。希望对大家有所帮助。第 17 页-
