abap对excel处理
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 counter
DATA: gv_linno TYPE i. "line number
DATA: gv_colno TYPE i. "column number
DATA: gv_value TYPE i. "data
data: 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 title
form 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. "merged
form 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. "merged
form 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. "merged
form 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. "merged
form 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.
*----copy
CALL 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.
个人补充：
上面 CALL METHOD OF gs_excel 'Cells' = gs_cell1
4过出不来，不知道是不是版本问题。统一改用
CALL METHOD OF gs_sheet 'Cells' = gs_cell1 就好了
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/superying/archive/2009/01/05/3710992.aspx
