ABAP-OLE2

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.