abap excel

来源：互联网发布：关于性句子知乎编辑：程序博客网时间：2024/05/25 12:20

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.