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. "number of cycles
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 'workbook' = 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 'topmarggin' = '25 '.
Set property of gs_page 'bottommarggin' = '25 '.
Set property of gs_page 'leftmargin '= '25 '.
Set property of gs_page 'rightmarggin' = '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 'raptext' = 1.
Set property of gs_cell1 'horizontalalignment '= alignment.
"& Horizontal orientation 2 left alignment, 3 center, 4 right alignment
Set property of gs_cell1 'verticalignment '= 2.
"& Vertical direction: 1 to top, 2 to center, 3 to bottom
Set property of gs_cell1 'numberformatlocal' = '@'.
"& Set the data format
Set property of gs_cell1 'value' = value.
Endform. "Format
*------------------------------
* This function is used to merge Excel cells.
*------------------------------
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
*---------------------------------
* Generate cells with borders around them.
*---------------------------------
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
*--------------------------------------
*---------------------------------
* Generate a cell with a border between the left and right
*---------------------------------
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 parameters: 1-left, 2-right, 3-top, 4-bottom, 5-oblique, 6-oblique /;
* & Linestyle values: 1 and 7-segments, 2-segments, 4-segments, and 9-segments)
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
*---------------------------------
* Generate a cell with borders for 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
*-------------------------------------------------
* Set the Row Height and column width.
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 'workbook' = 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.
Personal supplement:
Above call method of gs_excel 'cells '= gs_cell1
4. I don't know if it is a version issue. Unified Switch
Call method of gs_sheet 'cells '= gs_cell1.
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/superying/archive/2009/01/05/3710992.aspx