ABAP操作EXECL详细

REPORT  ZVR013.INCLUDE ole2incl.DATA: gs_excel          TYPE ole2_object,      gs_wbooklist      TYPE ole2_object,      gs_application    TYPE ole2_object,      gs_wbook          TYPE ole2_object,      gs_activesheet    TYPE ole2_object,      gs_sheets         TYPE ole2_object,      gs_newsheet       TYPE ole2_object,      gs_cell1          TYPE ole2_object,      gs_cell2          TYPE ole2_object,      gs_cells          TYPE ole2_object,      gs_font           TYPE ole2_object,      gs_page           TYPE ole2_object,      gs_range type ole2_object,      gs_borders type ole2_object.DATA: gv_sheet_name(20) TYPE c.DATA: gv_outer_index    LIKE sy-index.DATA: gv_intex(2)       TYPE c.DATA: gv_line_cntr TYPE i.   "line counterDATA: gv_linno TYPE i.       "line numberDATA: gv_colno TYPE i.       "column numberDATA: gv_value TYPE i.       "datadata: x1 type p, x2 type p, y1 type p, y2 type p.data: value(100),value1(100).data: name(20),size(2).data: bold type p, underline type p, Alignment type p.data: row type p value 11, line type p value 1. "循环次数data: row1 type p,line1 type p.data: quantity(20),amount(20).data: rowheight(10),columnwidth(10).data: page type p, pg_index type p.*-----------------------------------------------------------*-----------------------------------------------------------CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.SET PROPERTY OF gs_excel 'Visible' = 1.GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist.GET PROPERTY OF gs_wbooklist 'Application' = gs_application.SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1.CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet.SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.get property of gs_activesheet 'pagesetup' = gs_page.set property of gs_page 'TopMargin' = '25'.set property of gs_page 'BottomMargin' = '25'.set property of gs_page 'LeftMargin' = '25'.set property of gs_page 'RightMargin' = '25'.*--Formatting the titleform format using gs_cell1 value name size bold alignment underline.  condense value.  condense name.  condense size.  GET PROPERTY OF gs_cell1 'Font' = gs_font .  SET PROPERTY OF gs_font 'Name' = name.  SET PROPERTY OF gs_font 'Size' = size.  SET PROPERTY OF gs_font 'Bold' = bold.           "Not bold  SET PROPERTY OF gs_font 'Italic' = '1'.         "Not Italic  SET PROPERTY OF gs_font 'Underline' = underline.  "Not underlined  SET PROPERTY OF gs_cell1 'WrapText' = 1.  SET PROPERTY OF gs_cell1 'horizontalAlignment' = alignment.  " && 水平方向　2左对齐，3居中，4右对齐  SET PROPERTY OF gs_cell1 'VerticalAlignment' = 2.  "&& 垂直方向　1靠上　，2居中，3靠下  set property of gs_cell1 'NumberFormatLocal' = '@'.  "&& 设置数据格式  SET PROPERTY OF gs_cell1 'Value' = value.endform.                    "format*------------------------------*该函数用来合并excel单元格*------------------------------form merged using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .endform.                    "merged*---------------------------------*生成四周有边框的单元格*---------------------------------form merged_borders using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  GET PROPERTY OF gs_cells 'borders' = gs_range .  SET PROPERTY OF gs_range 'weight' = '2'.  SET PROPERTY OF gs_range 'linestyle' = '1'.  FREE OBJECT gs_range.endform.                    "merged*--------------------------------------*---------------------------------*生成左右有边框的单元格*---------------------------------form sides_borders using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '1'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.  SET PROPERTY OF gs_borders 'WEIGHT' = 2.  FREE OBJECT gs_borders.  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '2'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '7'.*& (其中Borders参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/；*&LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)  SET PROPERTY OF gs_borders 'WEIGHT' = 2.  FREE OBJECT gs_borders.endform.                    "merged*---------------------------------form left_borders using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '1'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.  SET PROPERTY OF gs_borders 'WEIGHT' = 2.  FREE OBJECT gs_borders.endform.                    "mergedform right_borders using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '2'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '1'.  SET PROPERTY OF gs_borders 'WEIGHT' = 2.  FREE OBJECT gs_borders.endform.                    "merged*---------------------------------*生成bottom有边框的单元格*---------------------------------form bottom_borders using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '4'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '7'.  SET PROPERTY OF gs_borders 'WEIGHT' = 2.  FREE OBJECT gs_borders.endform.                    "mergedform bottom_lines using x1 y1 x2 y2.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  CALL METHOD OF gs_cells 'BORDERS' = gs_borders    EXPORTING      #1 = '4'.  SET PROPERTY OF gs_borders 'LINESTYLE' = '9'.  SET PROPERTY OF gs_borders 'WEIGHT' = 5.  FREE OBJECT gs_borders.endform.                    "merged*-------------------------------------------------*设置行高和列宽。form row_column using x1 y1 x2 y2 rowheight columnwidth.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  get property of gs_cells 'rows' = gs_borders.  SET PROPERTY OF gs_borders 'rowheight' = rowheight.  GET PROPERTY OF gs_cells 'columns' = gs_range .  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.  FREE OBJECT gs_borders.  FREE OBJECT gs_range.endform.                    "mergedform rowheight using x1 y1 x2 y2 rowheight.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .  get property of gs_cells 'rows' = gs_borders.  SET PROPERTY OF gs_borders 'rowheight' = rowheight.*  GET PROPERTY OF gs_cells 'columns' = gs_range .*  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.  FREE OBJECT gs_borders.  FREE OBJECT gs_range.endform.                    "mergedform columnwidth using x1 y1 x2 y2 columnwidth.*--Selecting cell area to be merged.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.  CALL METHOD OF gs_cells 'Select'.*--Merging  CALL METHOD OF gs_cells 'Merge' .*  get property of gs_cells 'rows' = gs_borders.*  SET PROPERTY OF gs_borders 'rowheight' = rowheight.  GET PROPERTY OF gs_cells 'columns' = gs_range .  SET PROPERTY OF gs_range 'columnwidth' = columnwidth.  FREE OBJECT gs_borders.  FREE OBJECT gs_range.endform.                    "merged********************************************************form pages using l_file x y.x1 = 1.y1 = 1.x2 = x.y2 = y.*do page times.*_-create excel      CONCATENATE 'Sheet' '1' INTO gv_sheet_name.      CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.      SET PROPERTY OF gs_excel 'Visible' = 1.      GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist.*----copyCALL METHOD OF gs_wbooklist 'open' = gs_range      EXPORTING      #1 = l_file.  CALL METHOD OF gs_excel 'Worksheets' = gs_borders      EXPORTING #1 = 1.  CALL METHOD OF gs_borders 'Activate'.  CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = 1      #2 = 1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x      #2 = y.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.CALL METHOD OF gs_cells 'copy'.*----PasteSpecial*-create  CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.  GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet.  SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = 1      #2 = 1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x      #2 = y.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.CALL METHOD OF gs_cells 'PasteSpecial'.CALL METHOD OF gs_cells 'copy'.call method OF gs_range 'Activate'.CALL METHOD OF gs_range 'close'.call method OF gs_activesheet 'Activate'.endform.*-------------------------------------------------form pages1 using x y.do page times.      x1 = x1 + x.      y1 = 1.      x2 = x2 + x.      y2 = y.CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = 1      #2 = 1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x      #2 = y.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.CALL METHOD OF gs_cells 'copy'.   CALL METHOD OF gs_excel 'Cells' = gs_cell1    EXPORTING      #1 = x1      #2 = y1.  CALL METHOD OF gs_excel 'Cells' = gs_cell2    EXPORTING      #1 = x2      #2 = y2.  CALL METHOD OF gs_excel 'Range' = gs_cells    EXPORTING      #1 = gs_cell1      #2 = gs_cell2.CALL METHOD OF gs_cells 'PasteSpecial'.enddo.endform.