The ABAP program completes the import and export of Excel files and the application of OLE technology

Today, I learned how to use Microsoft's OLE technology and use the ABAP program to import and export Excel files.

(1) commonly used Ole functions:

Call method, call object, free object, get property, set property.

Set property: Set property of <Object> <property >=< value>

Get property: Get property of <Object> <property> = Variable

Call method: Call method of <Object> <method> <value> exporting # <para >=< value>.

The output can be multiple parameters.

Release object: Free object <Object>.

(2) reading files from Excel to the ABAP program

A: Use the function to read the Excel file into the ABAP table.

First, define the internal table read into excel. The ABAP format is defined.

Then you need to input a file name parameter and modify the parameter to enter the path of the Excel file to be read.

Call the function to directly read the internal table from a known file name.

Finally, you can read the internal table cyclically and print the table.

SPAN {font-family: "Courier New"; font-size: 10pt; color: #000000; Background: # ffffff ;}. l1s31 {font-style. italic; color: #808080 ;}. l1s32 {color: # 3399ff ;}. l1s33 {color: #4da619 ;}. l1s52 {color: # 0000ff;} report ytest67.
Data: Begin of iexcel occurs 0.
Include structure alsmex_tabline.
Data: End of iexcel.

Parameters: fn1 like RLGRAP-FILENAME for memory ID m01.

"Call function 'alsm _ excel_to_internal_table'
Call function 'alsm _ excel_to_internal_table'
Exporting
Filename = fn1
I _begin_col = 1
I _begin_row = 1
I _end_col= 100
I _end_row= 100
Tables
Intern = iexcel
Exceptions
Inconsistent_parameters = 1
Upload_ole = 2
Others = 3
.
If sy-subrc <> 0.
Write:/'An error occurred while reading the file! ', Fn1.
* Message ID SY-MSGID type SY-MSGTY number SY-MSGNO
* With SY-MSGV1 SY-MSGV2 SY-MSGV3.
Else.
Loop at iexcel.
Write IEXCEL-VALUE (20 ).
At end of row.
Write :/.
Endat.
Endloop.
Endif.

B: Read data directly from Excel

You must first define the OLE variable and have the input file name parameter (same as above)

Start Excel again to create an Excel Object

SPAN {font-family: "Courier New"; font-size: 10pt; color: #000000; Background: # ffffff ;}. l1s31 {font-style. italic; color: #808080 ;}. l1s32 {color: # 3399ff ;}. l1s33 {color: #4da619 ;}. l1s52 {color: # 0000ff;} report ytest68.

Include ole2incl.
* Define Ole Variables
Data: Excel type ole2_object,
Sheet Type ole2_object,
Cell type ole2_object,
Workbook type ole2_object.

Data: val1 (30) type C.
Data: r1 type I,
R2 type I.
* Input file name Parameters
Parameters: fn1 like RLGRAP-FILENAME for memory ID m01.

* Start Excel
Create object Excel 'excel. application'. "Create an Excel Object
*
Call method of Excel 'workbook' = Workbook. "Call the Excel Method
If SY-SUBRC Ne 0.
Write:/'excel error! '.
Leave program.
Endif.
* Make Excel visible
Set property of Excel 'visible '= 1.
* Open an Excel file
Call method of workbook 'open' exporting #1 = fn1.

If SY-SUBRC Ne 0.
Write:/'processing error! '.
Leave program.
Endif.
* Page 1
Call method of Excel 'worksheets '= sheet exporting #1 = 1.
* Read the first 10 rows and 10 columns
Do 10 times.
Write:/'row:', SY-INDEX.
R1 = SY-INDEX.
Do 10 times.
R2 = SY-INDEX.
* Specify the cell and read the value attribute.
Call method of Excel 'cells '= cell exporting #1 = R1 #2 = R2.
Get property of cell 'value' = val1. "to read the content in an Excel file, use get property of Excel.
"If you are writing an Excel file, use set property of Excel.
Write: val1.
Enddo.
Enddo.

(3) transfer data to excel and save it

SPAN {font-family: "Courier New"; font-size: 10pt; color: #000000; Background: # ffffff ;}. l1s31 {font-style. italic; color: #808080 ;}. l1s32 {color: # 3399ff ;}. l1s33 {color: #4da619 ;}. l1s52 {color: # 0000ff;} report ytest69.

Include ole2incl.
* Define Ole Variables
Data: Excel type ole2_object,
Sheet Type ole2_object,
Cell type ole2_object,
Workbook type ole2_object.

Data: val1 (30) type C.
Data: r1 type I,
R2 type I.
* Input file name Parameters
Parameters: fn1 like RLGRAP-FILENAME for memory ID m01.

* Start Excel
Create object Excel 'excel. application '.

If SY-SUBRC Ne 0.
Write :/''.
Stop.
Endif.

Call method of Excel 'workbook' = workbook.
* Make Excel visible
Set property of Excel 'visible '= 1.

* Create a worksheet
Set property of Excel 'sheetsinnewworkbook' = 1. "to read the content in an Excel file, open the first page of the workbook.
Call method of workbook 'add'. "Example: Call method of Excel 'worksheets '= sheet exporting #1 = 1.
* 10 columns in the first 10 rows
Do 10 times.
R1 = SY-INDEX.
Val1 + 1 (4) = 'row :'.
Val1 + 6 (4) = R1.
Do 10 times.
R2 = SY-INDEX.
Val1 + 15 (4) = 'column :'.
Val1 + 19 (4) = R2.
* Specify cells and write values
Call method of Excel 'cells '= cell exporting #1 = R1 #2 = R2.
Set property of cell 'value' = val1.
Enddo.
Enddo.

* Saving files
Get property of Excel 'activesheet '= sheet. "Activate the workbook.
Get property of Excel 'activeworkbook' = Workbook. "Activate Workspace
Call method of workbook 'saveas' exporting #1 = fn1 #2 = 1. "Save the Excel file as the fn1 path
Call method of workbook 'close'. "Close Workspace
Call method of Excel 'quit'. "Exit Excel
Write 'done'. "logout successful, output done

Free object sheet. "Release Operation
Free object workbook.
Free object excel.