*&---------------------------------------------------------------------*
*& Include ZHREXCEL*&---------------------------------------------------------------------**&---------------------------------------------------------------------**& Form templetdownload*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->L_SRTFD text*----------------------------------------------------------------------*FORM templetdownload USING l_srtfd.*袁伦勇增加 PERFORM templetdownload_new USING l_srtfd."新版本下载程序* DATA ext(10).* DATA l_filename TYPE string.* DATA lin TYPE i.* DATA length TYPE i.** DATA l_filename(128).* DATA: data_container LIKE soli OCCURS 0 WITH HEADER LINE.* DATA: f(128),f1(10).* CLEAR data_container.* REFRESH data_container.* IMPORT data_container FROM DATABASE zhroffice(cn) ID l_srtfd.* DESCRIBE TABLE data_container LINES lin.* SPLIT zhroffice-filename AT '.' INTO f f1.* ext = f1.* TRANSLATE ext TO UPPER CASE.* IF lin = 0.* CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT'* EXPORTING* titel = '下载文件'* textline1 = '数据库错误!没有模板，请重新上载模板！'* EXCEPTIONS* OTHERS = 1.* EXIT.* ENDIF.** READ TABLE data_container INDEX lin.* length = data_container-line.* CONCATENATE 'C:\' f sy-datum '.' f1 INTO l_filename.** CALL FUNCTION 'GUI_DOWNLOAD'* EXPORTING* bin_filesize = length* filename = l_filename* filetype = 'BIN'* IMPORTING* filelength = length* TABLES* data_tab = data_container** FIELDNAMES =* EXCEPTIONS* file_write_error = 1* no_batch = 2* gui_refuse_filetransfer = 3* invalid_type = 4* no_authority = 5* unknown_error = 6* header_not_allowed = 7* separator_not_allowed = 8* filesize_not_allowed = 9* header_too_long = 10* dp_error_create = 11* dp_error_send = 12* dp_error_write = 13* unknown_dp_error = 14* access_denied = 15* dp_out_of_memory = 16* disk_full = 17* dp_timeout = 18* file_not_found = 19* dataprovider_exception = 20* control_flush_error = 21* OTHERS = 22* .* IF sy-subrc NE 0.* CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT'* EXPORTING* titel = '下载文件'* textline1 = '下载失败！'* EXCEPTIONS* OTHERS = 1.* EXIT.* ENDIF.* CASE ext.* WHEN 'XLS'.* PERFORM startexcel USING l_filename l_srtfd.* WHEN 'DOC'.* WHEN OTHERS.* ENDCASE.ENDFORM. "templetdownload*&---------------------------------------------------------------------**& Form templetdownload_new*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->L_SRTFD text*----------------------------------------------------------------------*FORM templetdownload_new USING l_srtfd. DATA ext(10). DATA l_filename TYPE string. DATA lin TYPE i. DATA length TYPE i.* DATA l_filename(128). TYPES: BEGIN OF testline, line(256) TYPE x, END OF testline. DATA: data_container TYPE TABLE OF testline WITH HEADER LINE. DATA: f(128),f1(10). DATA: g_workdir TYPE string. CLEAR data_container. REFRESH data_container. IMPORT data_container FROM DATABASE zhr_office(cn) ID l_srtfd. DESCRIBE TABLE data_container LINES lin. SPLIT zhr_office-filename AT '.' INTO f f1. ext = f1. TRANSLATE ext TO UPPER CASE. IF lin = 0. CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT' EXPORTING titel = '下载文件' textline1 = '数据库错误!没有模板，请重新上载模板！' EXCEPTIONS OTHERS = 1. EXIT. ENDIF. CALL METHOD cl_gui_frontend_services=>get_sapgui_workdir CHANGING sapworkdir = g_workdir* EXCEPTIONS* GET_SAPWORKDIR_FAILED = 1* CNTL_ERROR = 2* ERROR_NO_GUI = 3* NOT_SUPPORTED_BY_GUI = 4* others = 5 . IF sy-subrc <> 0.* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. ENDIF. READ TABLE data_container INDEX lin. length = data_container-line. CONCATENATE g_workdir '\' f sy-datum '.' f1 INTO l_filename. CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING bin_filesize = length filename = l_filename filetype = 'BIN' IMPORTING filelength = length TABLES data_tab = data_container* FIELDNAMES = EXCEPTIONS file_write_error = 1 no_batch = 2 gui_refuse_filetransfer = 3 invalid_type = 4 no_authority = 5 unknown_error = 6 header_not_allowed = 7 separator_not_allowed = 8 filesize_not_allowed = 9 header_too_long = 10 dp_error_create = 11 dp_error_send = 12 dp_error_write = 13 unknown_dp_error = 14 access_denied = 15 dp_out_of_memory = 16 disk_full = 17 dp_timeout = 18 file_not_found = 19 dataprovider_exception = 20 control_flush_error = 21 OTHERS = 22 . IF sy-subrc NE 0. CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT' EXPORTING titel = '下载文件' textline1 = '下载失败！' EXCEPTIONS OTHERS = 1. EXIT. ENDIF. CASE ext. WHEN 'XLS'. PERFORM startexcel_new USING l_filename 'SHEET1'. PERFORM close_excel_tmp USING l_filename ."关闭模板,新建电子表格 WHEN 'DOC'. WHEN OTHERS. ENDCASE.ENDFORM. "templetdownload_new*&---------------------------------------------------------------------**& Form startexcel_new*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->L_FILENAME text* -->SHEET_NAME text*----------------------------------------------------------------------*FORM startexcel_new USING l_filename sheet_name . CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 1 text = '正在生成EXCEL文件，请稍候'.* start Excel****origin version CREATE OBJECT v_excel 'EXCEL.APPLICATION'. IF g_display IS INITIAL. SET PROPERTY OF v_excel 'Visible' = 0. ELSE. SET PROPERTY OF v_excel 'Visible' = 1. ENDIF. CALL METHOD OF v_excel 'Workbooks' = t_books. CALL METHOD OF t_books 'Open' = t_book EXPORTING #1 = l_filename. CALL METHOD OF t_book 'Worksheets' = t_sheet EXPORTING #1 = sheet_name. CALL METHOD OF t_sheet 'activate'.ENDFORM. "startexcel_new*---------------------------------------------------------------------** FORM StartExcel **---------------------------------------------------------------------** ........ **---------------------------------------------------------------------** --> L_FILENAME **---------------------------------------------------------------------*FORM startexcel USING l_filename sheet_name. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 1 text = '正在生成EXCEL文件，请稍候'.* start Excel****origin version CREATE OBJECT v_excel 'EXCEL.APPLICATION'. IF g_display IS INITIAL. SET PROPERTY OF v_excel 'Visible' = 0. ELSE. SET PROPERTY OF v_excel 'Visible' = 1. ENDIF. CALL METHOD OF v_excel 'Workbooks' = l_books. CALL METHOD OF l_books 'Open' = l_book EXPORTING #1 = l_filename. CALL METHOD OF l_book 'Worksheets' = sheet EXPORTING #1 = sheet_name. CALL METHOD OF sheet 'activate'.************************************************** CREATE OBJECT v_excel 'EXCEL.APPLICATION'.* SET PROPERTY OF v_excel 'Visible' = 1.* CALL METHOD OF v_excel 'Workbooks' = t_books.* CALL METHOD OF t_books 'Open' = t_book EXPORTING #1 = l_filename.* CALL METHOD OF t_book 'Worksheets' = t_sheet* EXPORTING* #1 = sheet_name.* CALL METHOD OF t_sheet 'activate'.* CALL METHOD OF v_excel 'Range' = t_range* EXPORTING* #1 = 'A1'* #2 = 'Z50'.* CALL METHOD OF t_range 'Select'.* CALL METHOD OF t_range 'Cut'.* CALL METHOD OF v_excel 'Range' = t_range* EXPORTING* #1 = 'A1'* #2 = 'ZZ100'.* CALL METHOD OF t_range 'Select'.* CALL METHOD OF t_range 'Cut'.* CALL METHOD OF v_excel 'Workbooks' = l_books.** SET PROPERTY OF v_excel 'SheetsInNewWorkbook' = 1 .* CALL METHOD OF l_books 'ADD'.* CALL METHOD OF v_excel 'Worksheets' = sheet* EXPORTING* #1 = 1.* CALL METHOD OF sheet 'ACTIVATE'.* SET PROPERTY OF sheet 'NAME' = 'Sheet1'.* CALL METHOD OF v_excel 'Range' = a_range* EXPORTING* #1 = 'A1'* #2 = 'A1'.* CALL METHOD OF a_range 'Select' .* CALL METHOD OF a_range 'Paste' .ENDFORM. "startexcel*&---------------------------------------------------------------------**& Form DISPLAYEXCEL*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM displayexcel. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 100 text = '正在生成EXCEL文件，请稍候'. IF g_display IS INITIAL. SET PROPERTY OF v_excel 'Visible' = 1. ENDIF. PERFORM endexcel.ENDFORM. "DISPLAYEXCEL*---------------------------------------------------------------------** FORM startmerge **---------------------------------------------------------------------** ........ **---------------------------------------------------------------------*FORM startmerge . CALL METHOD OF v_excel 'RUN' EXPORTING #1 = 'startmerge'.ENDFORM. "startmerge*---------------------------------------------------------------------** FORM runmacro **---------------------------------------------------------------------** ........ **---------------------------------------------------------------------** --> MACRONAME **---------------------------------------------------------------------*FORM runmacro USING macroname. CALL METHOD OF v_excel 'RUN' EXPORTING #1 = macroname.ENDFORM. "runmacro*---------------------------------------------------------------------** FORM endexcel **---------------------------------------------------------------------** ........ **---------------------------------------------------------------------*FORM endexcel. FREE OBJECT l_books. FREE OBJECT l_book. FREE OBJECT v_excel.ENDFORM. "endexcel*---------------------------------------------------------------------** FORM set_excel_cell **---------------------------------------------------------------------** 在指定单元格内填充值，并且自动画单元格边框线 ** 如无需表格线请使用set_excel_cell_noborder*---------------------------------------------------------------------** --> L_ROW ** --> L_COLUMN ** --> L_VALUE **---------------------------------------------------------------------*FORM set_excel_cell USING l_row " cell row serial l_column " cell column serial l_value. " cell value IF l_value NE '0'. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 50 text = '正在生成EXCEL文件，请稍候'.* set cell properties CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = l_row #2 = l_column. SET PROPERTY OF cell 'Value' = l_value. PERFORM border USING '1' '2'. ELSEIF l_value = 0. CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = l_row #2 = l_column. SET PROPERTY OF cell 'Value' = ' '. PERFORM border USING '1' '2'. ENDIF.* free object FREE OBJECT cell.* ENDIF.ENDFORM. " FRM_EXCEL_CELL*---------------------------------------------------------------------** FORM set_excel_cell_noborder **---------------------------------------------------------------------** 在指定单元格内填充值 ,没有边线 **---------------------------------------------------------------------** --> L_ROW ** --> L_COLUMN ** --> L_VALUE **---------------------------------------------------------------------*FORM set_excel_cell_noborder USING l_row " cell row serial l_column " cell column serial l_value. " cell value IF l_value NE '0' OR l_value IS NOT INITIAL. CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = l_row #2 = l_column. SET PROPERTY OF cell 'Value' = l_value. FREE OBJECT cell. ENDIF.ENDFORM. " FRM_EXCEL_CELL_noborder*---------------------------------------------------------------------** FORM set_excel_style_2 **---------------------------------------------------------------------** 在指定单元格内填充值，并且自动画单元格边框线(虚线) **---------------------------------------------------------------------** --> L_ROW ** --> L_COLUMN ** --> L_VALUE **---------------------------------------------------------------------*FORM set_excel_cell_style_2 USING l_row " cell row serial l_column " cell column serial l_value. " cell value IF l_value NE '0'. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 50 text = '正在生成EXCEL文件，请稍候'.* set cell properties CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = l_row #2 = l_column. SET PROPERTY OF cell 'Value' = l_value. PERFORM border USING '1' '1'. ELSEIF l_value = 0. CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = l_row #2 = l_column. SET PROPERTY OF cell 'Value' = ' '. ENDIF.* free object FREE OBJECT cell.* ENDIF.ENDFORM. " FRM_EXCEL_CELL*---------------------------------------------------------------------** FORM set_excel_range **---------------------------------------------------------------------** 在指定的范围内填值，行列必须为确定的值 **---------------------------------------------------------------------** --> L_START_CELL ** --> L_END_CELL ** --> L_VALUE ** --> DATA ** --> : ** --> L_RANGE **---------------------------------------------------------------------*FORM set_excel_range USING l_start_cell " start cell l_end_cell " end cell l_value . " cell value DATA: l_range TYPE ole2_object. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 50 text = '正在生成EXCEL文件，请稍候'.* set range properties CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_start_cell #2 = l_end_cell. SET PROPERTY OF range 'MergeCells' = 1. SET PROPERTY OF range 'Value' = l_value. FREE OBJECT range.ENDFORM. " FRM_EXCEL_RANGE*&---------------------------------------------------------------------**& Form border*&---------------------------------------------------------------------** 画表格线*----------------------------------------------------------------------** -->PLINESTYLE text* -->PWEIGHT text*----------------------------------------------------------------------*FORM border USING plinestyle pweight .*left CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '1'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.** right CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '2'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.* top CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '3'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.* bottom CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '4'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.ENDFORM. "border*&---------------------------------------------------------------------**& Form Insert_row*&---------------------------------------------------------------------** 插入新行*----------------------------------------------------------------------** -->ROW_NO text*----------------------------------------------------------------------*FORM insert_row USING row_no. CALL METHOD OF v_excel 'ROWS' = row EXPORTING #1 = row_no. CALL METHOD OF row 'INSERT' NO FLUSH.ENDFORM. "Insert_row*&---------------------------------------------------------------------**& Form fill_excel_range_merge*&---------------------------------------------------------------------** 在指定范围内填值，行列数为变量，并自动合并单元格*----------------------------------------------------------------------** -->P_ROW_B 左上角的行值* -->P_COL_B 左上角的列值* -->P_ROW_E 右下角的行指* -->P_COL_E 右下角的列值* -->P_VALUE 值* -->P_BORDER 带边框线=1, 不带边框线=0* -->P_ALIGN 居中= 1, 不居中=0*----------------------------------------------------------------------*FORM fill_excel_range_merge USING p_row_b p_col_b p_row_e p_col_e p_value p_border p_align. DATA: l_cell1 TYPE ole2_object. DATA: l_cell2 TYPE ole2_object. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 50 text = '正在生成EXCEL文件，请稍候'. CALL METHOD OF v_excel 'Cells' = l_cell1 EXPORTING #1 = p_row_b #2 = p_col_b. CALL METHOD OF v_excel 'Cells' = l_cell2 EXPORTING #1 = p_row_e #2 = p_col_e. CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_cell1 #2 = l_cell2. SET PROPERTY OF range 'MergeCells' = 1. SET PROPERTY OF range 'Value' = p_value. IF p_border = 1. PERFORM range_border USING '1' '2'. ENDIF. IF p_align = 1. SET PROPERTY OF range 'HorizontalAlignment' = -4108 . SET PROPERTY OF range 'VerticalAlignment' = -4108 . ENDIF. FREE OBJECT range.ENDFORM. "fill_excel_range_merge*&---------------------------------------------------------------------**& Form range_border*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->PLINESTYLE text* -->PWEIGHT text*----------------------------------------------------------------------*FORM range_border USING plinestyle pweight .*left CALL METHOD OF range 'BORDERS' = borders EXPORTING #1 = '1'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.** right CALL METHOD OF range 'BORDERS' = borders EXPORTING #1 = '2'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.* top CALL METHOD OF range 'BORDERS' = borders EXPORTING #1 = '3'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.* bottom CALL METHOD OF range 'BORDERS' = borders EXPORTING #1 = '4'. SET PROPERTY OF borders 'Linestyle' = plinestyle . SET PROPERTY OF borders 'Weight' = pweight . FREE OBJECT borders.ENDFORM. "range_border*&---------------------------------------------------------------------**& Form set_excel_range_border*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->L_START_CELL text* -->L_END_CELL text* -->L_VALUE text* -->L_BORDER text* -->L_ALIGN text*----------------------------------------------------------------------*FORM set_excel_range_border USING l_start_cell " start cell l_end_cell " end cell l_value " cell value l_border " border l_align " alignment . DATA: l_range TYPE ole2_object.* set range properties CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_start_cell #2 = l_end_cell. SET PROPERTY OF range 'MergeCells' = 1. SET PROPERTY OF range 'Value' = l_value. IF l_border = 1. PERFORM range_border USING '1' '2'. ENDIF. IF l_align = 1. SET PROPERTY OF range 'HorizontalAlignment' = -4108 . SET PROPERTY OF range 'VerticalAlignment' = -4108 . ENDIF. FREE OBJECT range.ENDFORM. "*&---------------------------------------------------------------------**& Form COPY_EXCEL_DATA_S*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->F_ROW_B text 开始左行* -->F_COL_B text 开始左列* -->F_ROW_E text 结束右行* -->F_COL_E text 结束右列* -->P_SKIN text 跳动几行粘贴的拷贝数据,以F_ROW_E参考*----------------------------------------------------------------------*FORM copy_excel_data_s USING f_row_b f_col_b f_row_e f_col_e p_skin. DATA:pasterow TYPE i. DATA:t_row_b TYPE i. DATA:t_col_b TYPE i. DATA:t_row_e TYPE i. DATA:t_col_e TYPE i. pasterow = f_row_e + p_skin . t_row_b = pasterow. t_col_b = f_col_b. t_row_e = f_row_e - f_row_b + pasterow. t_col_e = f_col_e. PERFORM copy_excel_data USING f_row_b f_col_b f_row_e f_col_e t_row_b t_col_b t_row_e t_col_e.ENDFORM. "COPY_EXCEL_DATA_S*&---------------------------------------------------------------------**& Form COPY_EXCEL_DATA*&---------------------------------------------------------------------** 拷贝数据另外位置*----------------------------------------------------------------------** -->F_ROW_B text 开始左行* -->F_COL_B text 开始左列* -->F_ROW_E text 结束右行* -->F_COL_E text 结束右列* -->T_ROW_B text 目标左行* -->T_COL_B text 目标左列* -->T_ROW_E text 目标右行* -->T_COL_E text 目标右列*----------------------------------------------------------------------*FORM copy_excel_data USING f_row_b f_col_b f_row_e f_col_e t_row_b t_col_b t_row_e t_col_e. DATA: l_cell1 TYPE ole2_object. DATA: l_cell2 TYPE ole2_object. DATA: l_cell3 TYPE ole2_object. DATA: l_cell4 TYPE ole2_object. CALL METHOD OF v_excel 'Cells' = l_cell1 EXPORTING #1 = f_row_b #2 = f_col_b. CALL METHOD OF v_excel 'Cells' = l_cell2 EXPORTING #1 = f_row_e #2 = f_col_e. CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_cell1 #2 = l_cell2.* SET PROPERTY OF range 'MergeCells' = 1.**** Copy Data* call method of v_excel 'Worksheets' = sheet* exporting #1 = 1.* CALL METHOD OF sheet 'Range' = range EXPORTING #1 = 'B2' #2 =* 'G2'. CALL METHOD OF range 'select'. CALL METHOD OF range 'copy'.* sheetnum = sheetnum + 1 .* condense sheetnum .*** Paste data* CALL METHOD OF v_excel 'Worksheets' = SHEET* EXPORTING #1 = 2 .* CALL METHOD OF SHEET 'RANGE' = range EXPORTING #1 = 'B2' . CALL METHOD OF v_excel 'Cells' = l_cell3 EXPORTING #1 = t_row_b #2 = t_col_b. CALL METHOD OF v_excel 'Cells' = l_cell4 EXPORTING #1 = t_row_e #2 = t_col_e. CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_cell3 #2 = l_cell4. CALL METHOD OF range 'select'. CALL METHOD OF sheet 'PasteSpecial'. FREE OBJECT range.ENDFORM. "COPY_EXCEL_DATA*&---------------------------------------------------------------------**& Form EXCEL_CELL_FILL*&---------------------------------------------------------------------** 填充单元格,可以控制边框风格,水平/垂直对齐方式等*----------------------------------------------------------------------** -->P_ROW text 行* -->P_COL text 列* -->P_VALUE text 值* -->TOP_LINESTYLE text 上边框样式* -->TOP_LINEWEGIHT text 上边框粗细* -->BOTT_LINESTYLE text 边框样式* -->BOTT_LINEWEGIHT text 边框粗细* -->LEFT_LINESTYLE text 边框样式* -->LEFT_LINEWEGIHT text 边框粗细* -->RIGHT_LINESTYLE text 边框样式* -->RIGHT_LINEWEGIHT text 边框粗细* -->V_ALIGN text 水平对齐 1:居中 0:缺省* -->H_ALIGN text 垂直对齐 1:居中 0:缺省*----------------------------------------------------------------------*FORM excel_cell_fill USING p_row p_col p_value top_linestyle top_linewegiht bott_linestyle bott_linewegiht left_linestyle left_linewegiht right_linestyle right_linewegiht v_align h_align. CALL METHOD OF v_excel 'Cells' = cell EXPORTING #1 = p_row #2 = p_col."定位 SET PROPERTY OF cell 'Value' = p_value."赋值*边框设置* top CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '3'. SET PROPERTY OF borders 'Linestyle' = top_linestyle . SET PROPERTY OF borders 'Weight' = top_linewegiht . FREE OBJECT borders.* bottom CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '4'. SET PROPERTY OF borders 'Linestyle' = bott_linestyle . SET PROPERTY OF borders 'Weight' = bott_linewegiht . FREE OBJECT borders.*left CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '1'. SET PROPERTY OF borders 'Linestyle' = left_linestyle . SET PROPERTY OF borders 'Weight' = left_linewegiht . FREE OBJECT borders.** right CALL METHOD OF cell 'BORDERS' = borders EXPORTING #1 = '2'. SET PROPERTY OF borders 'Linestyle' = right_linestyle . SET PROPERTY OF borders 'Weight' = right_linewegiht . FREE OBJECT borders. IF v_align = 1. SET PROPERTY OF cell 'VerticalAlignment' = -4108 . ENDIF. IF h_align = 1. SET PROPERTY OF cell 'HorizontalAlignment' = -4108 . ENDIF.* free object FREE OBJECT cell.ENDFORM. "EXCEL_CELL_FILL*&---------------------------------------------------------------------**& Form close_excel_tmp*&---------------------------------------------------------------------** 关闭模板，新建电子表格*----------------------------------------------------------------------*FORM close_excel_tmp USING p_filename. DATA: l_filename LIKE rlgrap-filename. DATA: l_cell1 TYPE ole2_object. DATA: l_cell2 TYPE ole2_object. DATA: l_cell3 TYPE ole2_object. DATA: l_cell4 TYPE ole2_object. DATA:s_cells TYPE ole2_object. DATA:d_cells TYPE ole2_object.* DATA: l_books2 TYPE ole2_object.* DATA: l_book2 TYPE ole2_object.* DATA: l_sheet TYPE ole2_object.* DATA: range2 TYPE ole2_object.* DATA: d_excel TYPE ole2_object. " Excel object CALL METHOD OF v_excel 'Cells' = l_cell1 EXPORTING #1 = 1 #2 = 1.* CALL METHOD OF v_excel 'Cells' = l_cell2* EXPORTING* #1 = 100* #2 = 100.* CALL METHOD OF v_excel 'Range' = range* EXPORTING* #1 = l_cell1* #2 = l_cell2.* CALL METHOD OF range 'select'.* CALL METHOD OF range 'copy'. CALL METHOD OF t_sheet 'Cells' = s_cells. CALL METHOD OF s_cells 'select'. CALL METHOD OF s_cells 'copy'.* CALL METHOD OF v_excel 'Workbooks' = l_books. CALL METHOD OF l_books 'Add' = l_book EXPORTING #1 =''. CALL METHOD OF l_book 'Worksheets' = sheet EXPORTING #1 = 'Sheet1'. SET PROPERTY OF sheet 'Name' = 'Sheet1'. CALL METHOD OF sheet 'activate'. CALL METHOD OF sheet 'Cells' = d_cells. CALL METHOD OF d_cells 'select'. CALL METHOD OF sheet 'paste'.* CALL METHOD OF v_excel 'Cells' = l_cell3* EXPORTING* #1 = 1* #2 = 1.* CALL METHOD OF v_excel 'Cells' = l_cell4* EXPORTING* #1 = 100* #2 = 100.* CALL METHOD OF v_excel 'Range' = range* EXPORTING* #1 = l_cell3* #2 = l_cell4.* CALL METHOD OF l_book 'ActiveSheet'.* CALL METHOD OF sheet 'select'.* CALL METHOD OF l_cell1 'select'. PERFORM set_excel_range USING 'AA200' 'AA200' ''. CALL METHOD OF t_book 'Activate'. CALL METHOD OF t_book 'Close' EXPORTING #1 = 0. FREE OBJECT t_book. FREE OBJECT t_books. l_filename = p_filename. CALL FUNCTION 'WS_FILE_DELETE' EXPORTING file = l_filename EXCEPTIONS OTHERS = 1.ENDFORM. "close_excel_tmp*&---------------------------------------------------------------------**& Form Insert_photo*&---------------------------------------------------------------------** 插入本地图片到excel中*----------------------------------------------------------------------** -->P_ROW 插入行* -->P_COL 插入列* -->P_FILENAME 本地图片文件名* -->P_SCALEWIDTH 横向缩放 比如 0.18 = 缩为18%* -->P_SCALEHEIGHT 纵向缩放*----------------------------------------------------------------------*FORM insert_photo USING p_row p_col p_filename p_scalewidth p_scaleheight. DATA: l_filename LIKE rlgrap-filename. DATA: gs_pictures TYPE ole2_object . DATA: gs_picture TYPE ole2_object. DATA: gs_shaperange TYPE ole2_object. DATA: gs_cell2 TYPE ole2_object. CALL METHOD OF v_excel 'Cells' = gs_cell2 EXPORTING #1 = p_row #2 = p_col. CALL METHOD OF gs_cell2 'Select'. GET PROPERTY OF l_books 'Application' = v_application . GET PROPERTY OF v_application 'ActiveSheet' = sheet . CALL METHOD OF sheet 'Pictures' = gs_pictures. CALL METHOD OF gs_pictures 'Insert' = gs_picture EXPORTING #1 = 'C:\photo.jpg'. CALL METHOD OF gs_picture 'Select'. GET PROPERTY OF gs_picture 'ShapeRange' = gs_shaperange . CALL METHOD OF gs_shaperange 'ScaleWidth' EXPORTING #1 = p_scalewidth #2 = 0. CALL METHOD OF gs_shaperange 'ScaleHeight' EXPORTING #1 = p_scaleheight #2 = 0. l_filename = p_filename. CALL FUNCTION 'WS_FILE_DELETE' EXPORTING file = l_filename EXCEPTIONS OTHERS = 1. FREE: gs_pictures,gs_picture,gs_shaperange,gs_cell2.ENDFORM. "Insert_photo*&---------------------------------------------------------------------**& Form copy_lines*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->P_ROW text* -->P_ROW_COUNT text*----------------------------------------------------------------------*FORM copy_lines USING p_row p_row_count. DATA gs_rows TYPE ole2_object.* DATA gs_rows2 TYPE ole2_object.* CHECK p_row_count > 1 AND* p_row_count < 99. DO p_row_count TIMES. CALL METHOD OF v_excel 'Rows' = gs_rows EXPORTING #1 = p_row. CALL METHOD OF gs_rows 'Select'. CALL METHOD OF gs_rows 'Copy'. CALL METHOD OF gs_rows 'Insert' EXPORTING #1 = -4121. ENDDO.ENDFORM. "COPY_LINES*&---------------------------------------------------------------------**& Form delete_row*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->ROW_NO text*----------------------------------------------------------------------*FORM delete_row USING row_no. CALL METHOD OF v_excel 'ROWS' = row EXPORTING #1 = row_no. CALL METHOD OF row 'DELETE' NO FLUSH.ENDFORM. "delete_row*&---------------------------------------------------------------------**& Form delete_range*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->A text 例子： A1* -->B text 例子： A999* 则 删除A列*----------------------------------------------------------------------*FORM delete_range USING a b. CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = a #2 = b. CALL METHOD OF range 'DELETE' NO FLUSH.ENDFORM. "delete_range*---------------------------------------------------------------------** ADD BY LIHAO 多sheet输出*----------------------------------------------------------------------*FORM templetdownload_new001 USING l_srtfd. DATA ext(10). DATA l_filename TYPE string. DATA lin TYPE i. DATA length TYPE i. TYPES: BEGIN OF testline, line(256) TYPE x, END OF testline. DATA: data_container TYPE TABLE OF testline WITH HEADER LINE. DATA: f(128),f1(10). CLEAR data_container. REFRESH data_container. IMPORT data_container FROM DATABASE zhr_office(cn) ID l_srtfd. DESCRIBE TABLE data_container LINES lin. SPLIT zhr_office-filename AT '.' INTO f f1. ext = f1. TRANSLATE ext TO UPPER CASE. IF lin = 0. CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT' EXPORTING titel = '下载文件' textline1 = '数据库错误!没有模板，请重新上载模板！' EXCEPTIONS OTHERS = 1. EXIT. ENDIF. READ TABLE data_container INDEX lin. length = data_container-line. CONCATENATE 'C:\' f sy-datum '.' f1 INTO l_filename. CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING bin_filesize = length filename = l_filename filetype = 'BIN' IMPORTING filelength = length TABLES data_tab = data_container EXCEPTIONS file_write_error = 1 no_batch = 2 gui_refuse_filetransfer = 3 invalid_type = 4 no_authority = 5 unknown_error = 6 header_not_allowed = 7 separator_not_allowed = 8 filesize_not_allowed = 9 header_too_long = 10 dp_error_create = 11 dp_error_send = 12 dp_error_write = 13 unknown_dp_error = 14 access_denied = 15 dp_out_of_memory = 16 disk_full = 17 dp_timeout = 18 file_not_found = 19 dataprovider_exception = 20 control_flush_error = 21 OTHERS = 22. CASE ext. WHEN 'XLS'. PERFORM startexcel_new001 USING l_filename 'SHEET1'. WHEN 'DOC'. WHEN OTHERS. ENDCASE.ENDFORM. "templetdownload_new*&---------------------------------------------------------------------**& Form startexcel_new1*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->L_FILENAME text* -->SHEET_NAME text*----------------------------------------------------------------------*FORM startexcel_new001 USING l_filename sheet_name . CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = 1 text = '正在生成EXCEL文件，请稍候'. CALL METHOD OF t_books 'Open' = t_book EXPORTING #1 = l_filename. CALL METHOD OF t_book 'Worksheets' = t_sheet EXPORTING #1 = sheet_name. CALL METHOD OF t_sheet 'activate'.ENDFORM. "startexcel_new*&---------------------------------------------------------------------**& Form START_EXCEL*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM start_excel001. CREATE OBJECT v_excel 'EXCEL.APPLICATION'. IF g_display IS INITIAL. SET PROPERTY OF v_excel 'Visible' = 0. ELSE. SET PROPERTY OF v_excel 'Visible' = 1. ENDIF. CALL METHOD OF v_excel 'Workbooks' = t_books.ENDFORM. "START_EXCEL*&---------------------------------------------------------------------**& Form CLOSE_EXCEL*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM close_excel001. SET PROPERTY OF v_excel 'DisplayAlerts' = 0 . CALL METHOD OF v_excel 'QUIT' . "DisplayAlerts 属性设置为 False FREE OBJECT t_books. """"" FREE OBJECT v_excel. """"ENDFORM. "CLOSE_EXCEL*----------------------------------------------------------------------------**激活不同的sheet*-----------------------------------------------------------------------------*FORM excel_sheet_active001 USING sheet_name . CALL METHOD OF v_excel 'WORKSHEETS' = sheet EXPORTING #1 = sheet_name. CALL METHOD OF sheet 'ACTIVATE'. FREE OBJECT sheet. "OKENDFORM. "EXCEL_SHEET_ACTIVE*----------------------------------------------------------------------**修改sheet名称*-----------------------------------------------------------------------*FORM excel_sheet_name_init001 USING sheet_name sheet_newname. CALL METHOD OF v_excel 'WORKSHEETS' = sheet EXPORTING #1 = sheet_name. CALL METHOD OF sheet 'ACTIVATE'. SET PROPERTY OF sheet 'NAME' = sheet_newname . FREE OBJECT sheet. "OKENDFORM. "EXCEL_SHEET_NAME_INIT*&---------------------------------------------------------------------**& Form copy_mem2excel*&---------------------------------------------------------------------** text复制剪切板内容到excel*----------------------------------------------------------------------** -->ROW 行号* -->COL 列号*----------------------------------------------------------------------*FORM copy_mem2excel USING row col. DATA: l_cell TYPE ole2_object. DATA: activesheet TYPE ole2_object. CALL METHOD OF v_excel 'Cells' = l_cell EXPORTING #1 = row #2 = col. CALL METHOD OF v_excel 'Range' = range EXPORTING #1 = l_cell #2 = l_cell.* CALL METHOD OF range 'select'. GET PROPERTY OF v_excel 'Activesheet' = activesheet. CALL METHOD OF activesheet 'Paste'. FREE OBJECT range.ENDFORM. "copy_mem2excel