效果图
第一个sheet页：
第二个sheet页：
*整体思路： *先打开excel，整理好数据 *1.拷贝模板，复制sheet页 PERFORM frm_add_excel_sheet . *2.在遍历中打开sheet页，根据sheet页填充单元格 PERFORM frm_open_excel_sheet USING sy-tabix . *3.主程序 定义变量
CONSTANTS: c_local_path(10) TYPE c VALUE 'C:\ERP\', " 文件夹本地路径 c_str TYPE w3objid VALUE '检修计划信息导出', " EXCEL模板-申请表汇总 c_objid_header TYPE w3objid VALUE 'ZPMRPT004'. " EXCEL模板-检修计划ole DATA: gv_filename TYPE rlgrap-filename, " 文件本地路径 l_cell_content TYPE string. DATA: gv_file_name TYPE string. " 文件本地路径 DATA: gv_path TYPE string. DATA: gv_fullpath TYPE string. "文档全名 CLEAR gv_filename. * 获取下载文件路径 PERFORM f_frm_download_excel_module. CHECK gv_filename IS NOT INITIAL. * CONDENSE l_filename NO-GAPS. * 显示处理进度 PERFORM f_process_indcator USING text-002 0 . "初始化GUI * 下载模板 PERFORM f_download_excel_fromserver USING c_objid_header gv_filename. "从服务器中下载EXCEL模板到L_FILENAME中 * 显示处理进度 PERFORM f_process_indcator USING text-004 0 . "初始化GUI" * 初始化OLE PERFORM f_initialization USING g_excel g_wbook gv_filename g_sheet CHANGING g_excel1. g_tempsheet = g_sheet. PERFORM frm_deal_data. PERFORM frm_process_data. PERFORM frm_add_excel_sheet . LOOP AT gt_item INTO gs_item. PERFORM frm_open_excel_sheet USING sy-tabix . PERFORM f_write_head_row USING gs_item. PERFORM f_write_mat_row USING gs_item . PERFORM f_write_sp_row USING gs_item. ENDLOOP. SET PROPERTY OF g_excel1 'CutCopyMode' = 0. * SET PROPERTY OF l_excel1 'Visible' = 1. * 关闭退出OLE PERFORM f_close_file CHANGING g_excel1 sy-subrc.子FORM 以下均为主程序跳转具体代码。
获取下载文件路径
*&---------------------------------------------------------------------* *& Form F_FRM_DOWNLOAD_EXCEL_MODULE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* form f_frm_download_excel_module . data: l_str type w3objid. "下载下来的文件名称 if sy-ucomm = 'FC_OLE'."分厂汇总表 l_str = c_str1. elseif sy-ucomm = 'MX_OLE'."明细表 l_str = c_str2. elseif sy-ucomm = 'LHA_OLE'."炼化汇总表 l_str = c_str. elseif sy-ucomm = 'EXPORT'."检修计划ole l_str = c_str. elseif sy-ucomm = 'EXPORT_B'."检修计划ole l_str = c_str3. endif. concatenate c_local_path l_str sy-datum sy-uzeit '.xlsx' into gv_filename. gv_file_name = gv_filename. * 获取文件名称 call method cl_gui_frontend_services=>file_save_dialog exporting window_title = 'Excel file name' default_extension = 'xlsx' default_file_name = gv_file_name changing filename = gv_file_name path = gv_path fullpath = gv_fullpath. if sy-subrc = 0. gv_filename = gv_fullpath. endif. endform.显示处理进度
form f_process_indcator using pl_text type string pl_percentage type n. * 显示程序处理进度 call function 'SAPGUI_PROGRESS_INDICATOR' exporting percentage = pl_percentage "进度 text = pl_text. "文本 endform. "FRM_PROCESS_DICATOR下载模板
form f_download_excel_fromserver using pl_objid type wwwdatatab-objid pl_dest type rlgrap-filename. data: l_objdata like wwwdatatab, l_mime like w3mime, l_objnam type string, l_rc like sy-subrc, l_errtxt type string. concatenate pl_objid '.xlsx' into l_objnam. condense l_objnam no-gaps. * 检查模板是否存在 select single relid objid from wwwdata into (l_objdata-relid, l_objdata-objid) where srtf2 = 0 "计数器 and relid = 'MI' "区域 and objid = pl_objid. "对象名 * 模板不存在 if sy-subrc <> 0 or l_objdata-objid = space. message e002(zfico) with l_objnam. endif. * 下载模板到指定路径 call function 'DOWNLOAD_WEB_OBJECT' exporting key = l_objdata "对象名 destination = pl_dest "用于装载/卸载的局部文件 importing rc = l_rc. * 下载模板失败 if l_rc <> 0. message e001(zfico) with l_objnam. endif. endform. "FRM_DOWNLOAD_EXCEL_FROMSERVER显示处理进度
form f_process_indcator using pl_text type string pl_percentage type n. * 显示程序处理进度 call function 'SAPGUI_PROGRESS_INDICATOR' exporting percentage = pl_percentage "进度 text = pl_text. "文本 endform. "FRM_PROCESS_DICATOR初始化OLE
form f_initialization using excel wbook filename tempsheet changing p_excel1 type ole2_object. * 创建OLE运用文件 create object excel 'Excel.Application'. if sy-subrc <> 0. message e899(mm) with '创建EXCEL文件失败'. endif. p_excel1 = excel. call method of excel 'Workbooks' = wbook. call method of wbook 'Open' = wbook exporting #1 = filename. if sy-subrc <> 0. message e899(mm) with 'Can not OPEN EXCEL File:' filename. endif. * 设置文件的可显示状态 set property of excel 'Visible' = 1. * SET PROPERTY OF P_EXCEL1 'Visible' = 1. set property of excel 'screenupdating' = 1. * 设置模板 call method of wbook 'Sheets' = tempsheet exporting #1 = 1. endform. " F_INITIALIZATION添加sheet页
FORM frm_add_excel_sheet . DATA: l_str TYPE string. DATA: l_dx TYPE i. DATA: l_lines TYPE i. CLEAR: l_str. l_dx = 1. CLEAR: l_lines. DESCRIBE TABLE gt_item LINES l_lines. l_lines = l_lines - 1. DO l_lines TIMES. "拷贝现有 sheet CALL METHOD OF g_sheet 'copy' EXPORTING #1 = g_sheet. "sheet 重命名 CLEAR: l_str. l_dx = l_dx + 1. l_str = l_dx. CONDENSE l_str NO-GAPS. CONCATENATE '第' l_str INTO l_str. CONDENSE l_str NO-GAPS. CONCATENATE l_str '页' INTO l_str. CONDENSE l_str NO-GAPS. SET PROPERTY OF g_sheet 'name' = l_str. ENDDO. * " 创建sheet并添加到工作表 * set property of g_excel 'SheetsInNewWorkbook' = l_lines. "如需多个sheets,将1改成相应的值 * call method of g_wbook 'ADD' = g_sheet. * CALL METHOD OF g_sheet 'ACTIVATE'. ENDFORM.打开sheet页
FORM frm_open_excel_sheet USING p_sy_tabix TYPE sytabix . DATA: l_dx TYPE i. g_tabix = p_sy_tabix. l_dx = g_tabix. CALL METHOD OF g_wbook 'SHEETS' = g_sheet EXPORTING #l_dx = l_dx. CALL METHOD OF g_sheet 'ACTIVATE'. ENDFORM.为单元格赋值
form f_write_cell using sheet row col value. * DATA: CELL TYPE OLE2_OBJECT. call method of sheet 'Cells' = g_cell no flush exporting #1 = row "行 #2 = col. "列 * GET PROPERTY OF g_cell 'Font' = g_font. * set property of g_font 'Bold' = 0 . set property of g_cell 'Value' = value no flush. * set property of g_cell 'horizontalAlignment' = 3. "3表示在单元格中居中显示 endform. " F_WRITE_CELL写入行项目（拷贝行，插入行）
FORM f_write_mat_row USING gs_item TYPE ty_item . DATA: l_num TYPE i. DATA: l_num1 TYPE i. DATA: l_flag TYPE c. l_num = 6. REFRESH gt_item_tmp. gt_item_tmp = gt_item_mat_info. DELETE gt_item_tmp WHERE qmnum <> gs_item-qmnum OR serial <> gs_item-serial. LOOP AT gt_item_tmp INTO gs_item_mat_info." where qmnum = gs_item-qmnum and serial = gs_item-serial. CLEAR: l_num1,l_flag. l_num = l_num + 1. l_num1 = l_num + 1. AT LAST. l_flag = 'X'. ENDAT. * 拷贝行 IF l_flag IS INITIAL . PERFORM f_paste_rows_mutl_sheet USING g_sheet l_num l_num l_num1 l_num1. ENDIF. PERFORM f_write_cell USING g_sheet l_num 'A' gs_item_mat_info-xh. "序号 PERFORM f_write_cell USING g_sheet l_num 'B' gs_item_mat_info-matkl. "序号 PERFORM f_write_cell USING g_sheet l_num 'C' gs_item_mat_info-matklx. "序号 PERFORM f_write_cell USING g_sheet l_num 'E' gs_item_mat_info-matnr. "序号 PERFORM f_write_cell USING g_sheet l_num 'F' gs_item_mat_info-maktx. "序号 PERFORM f_write_cell USING g_sheet l_num 'I' gs_item_mat_info-meins. "序号 PERFORM f_write_cell USING g_sheet l_num 'J' gs_item_mat_info-jhsl. "序号 PERFORM f_write_cell USING g_sheet l_num 'K' gs_item_mat_info-verpr_yg. "序号 PERFORM f_write_cell USING g_sheet l_num 'L' gs_item_mat_info-jshj. "序号 PERFORM f_write_cell USING g_sheet l_num 'M' gs_item_mat_info-tbcj. "序号 PERFORM f_write_cell USING g_sheet l_num 'N' gs_item_mat_info-zbz. "序号 ENDLOOP. CLEAR: g_index. IF l_num1 IS INITIAL. l_num1 = 8. ELSE. g_index = l_num1 . ENDIF. * 总计行:写入 SORT gt_item_mat_sum BY qmnum serial. READ TABLE gt_item_mat_sum INTO gs_item_mat_sum WITH KEY qmnum = gs_item-qmnum serial = gs_item-serial BINARY SEARCH. IF sy-subrc = 0. PERFORM f_write_cell USING g_sheet l_num1 'J' gs_item_mat_sum-jhsl. "计划数量 PERFORM f_write_cell USING g_sheet l_num1 'L' gs_item_mat_sum-jshj. "价税合计 ENDIF. ENDFORM.拷贝行
FORM f_paste_rows_mutl_sheet USING i_application TYPE ole2_object i_start_source_row TYPE i i_end_source_row TYPE i i_start_target_row TYPE i i_end_target_row TYPE i. DATA:l_row_str1 TYPE string, l_row_str2 TYPE string, l_row_str TYPE string. DATA:l_range TYPE ole2_object. DATA: l_tabix TYPE i. l_row_str1 = i_start_source_row. l_row_str2 = i_end_source_row. CONCATENATE l_row_str1 ':' l_row_str2 INTO l_row_str. CONDENSE l_row_str NO-GAPS. *定位行 l_tabix = g_tabix. CALL METHOD OF i_application 'rows' = l_range EXPORTING #l_tabix = l_row_str. *选择行 CALL METHOD OF l_range 'select'. *复制行 CALL METHOD OF l_range 'COPY'. l_row_str1 = i_start_target_row. l_row_str2 = i_end_target_row. CONCATENATE l_row_str1 ':' l_row_str2 INTO l_row_str. CONDENSE l_row_str NO-GAPS. *定位行 CALL METHOD OF i_application 'rows' = l_range EXPORTING #l_tabix = l_row_str. *选择行 CALL METHOD OF l_range 'select'. *插入行 CALL METHOD OF l_range 'insert'. ENDFORM.关闭退出OLE
form f_close_file changing pl_application type ole2_object pl_subrc type sy-subrc. data l_workbook type ole2_object. * 得到当前活动的工作簿 get property of pl_application 'ACTIVEWORKBOOK' = l_workbook. * 保存工作簿 call method of l_workbook 'SAVE'. * 关闭工作簿 call method of l_workbook 'CLOSE'. * 退出Excel进程 call method of pl_application 'QUIT'. * 退出是否成功状态返回值 pl_subrc = sy-subrc. endform. "f_close_file