ABAP processing Excel

Source: Internet
Author: User
Tags x2 y2

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.