Skip to Content
0
Sep 09, 2018 at 09:20 AM

download alv to excel in multiple sheet

476 Views

Hi,

I want to download some data in multiple excel tabs. I have seen many links regarding OLE concept but I am not familiar with that. I copied a program from the link https://www.erpworkbench.com/abap/ms/format-excel.htm.

I have modified the code to display the records in sheet 2 of the excel. But I cant seem to display the same data in either sheet 1 or sheet 3. I tried to write the same code for sheet 1 or sheet 3 after freeing the objects, but only the sheet mentioned in the code first gets populated.

Below is the code:

INCLUDE ole2incl.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cells TYPE ole2_object,
cell1 TYPE ole2_object,
cell2 TYPE ole2_object,
range TYPE ole2_object,
font TYPE ole2_object,
column TYPE ole2_object,
shading TYPE ole2_object,
border TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.
Data: ld_colindx type I, "column index
ld_rowindx type i. "row index

types: begin of t_data,
field1 type string,
field2 type string,
field3 type string,
field4 type string,
field5 type string,
field6 type string,
field7 type string,
field8 type string,
field9 type string,
field10 type string,
field11 type string,
field12 type string,
field13 type string,
field14 type string,
field15 type string,
field16 type string,
end of t_data.
data: it_header type STANDARD TABLE OF t_data,
wa_header like LINE OF it_header,
it_data type STANDARD TABLE OF t_data,
it_data2 type standard table of t_data,
wa_data like LINE OF it_data.

*field symbol to hold values
field-symbols: <fs>.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.

************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

wa_header-field1 = 'Header1'.
wa_header-field2 = 'Header2'.
wa_header-field3 = 'Header3'.
wa_header-field4 = 'Header4'.
wa_header-field5 = 'Header5'.
wa_header-field6 = 'Header6'.
wa_header-field7 = 'Header7'.
wa_header-field8 = 'Header8'.
wa_header-field9 = 'Header9'.
wa_header-field10 = 'Longer Header10'.
wa_header-field11 = 'Header11'.
wa_header-field12 = 'Header12'.
wa_header-field13 = 'Header13'.
wa_header-field14 = 'Header14'.
wa_header-field15 = 'Header15'.
wa_header-field16 = 'Header16'.
APPEND wa_header to it_header.


wa_data-field1 = 'Column1 data'.
wa_data-field2 = 'Column1 data'.
wa_data-field3 = 'Column1 data'.
wa_data-field4 = 'Column1 data'.
wa_data-field5 = 'Column1 data'.
wa_data-field6 = 'Column1 data'.
wa_data-field7 = 'Column1 data'.
wa_data-field8 = 'Column1 data'.
wa_data-field9 = 'Column1 data'.
wa_data-field10 = 'Column1 data'.
wa_data-field11 = 'Column1 data'.
wa_data-field12 = 'Column1 data'.
wa_data-field13 = 'Column1 data'.
wa_data-field14 = 'Column1 data'.
wa_data-field15 = 'Column1 data'.
wa_data-field16 = 'Column1 data'.
APPEND wa_DATA to it_DATA.
APPEND wa_DATA to it_DATA.
it_data2[] = it_data[].

CREATE OBJECT application 'excel.application'.
SET PROPERTY OF application 'visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.

set property of application 'SheetsInNewWorkbook' = 3. "total num ber of sheets
CALL METHOD OF workbook 'Add'.

CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 2. "target sheet no. which downloads data
CALL METHOD OF sheet 'Activate'.
SET PROPERTY OF sheet 'Name' = 'Sheet2'.

***********************************************
* Download header data to excel spreadsheet *
***********************************************
ld_rowindx = 1. "start at row 1 for headings
LOOP AT it_header INTO wa_header.
*Use sy-tabix for row index
ld_rowindx = sy-tabix.

* Fill columns for current row
Clear ld_colindx.
Do.
* Assign <fs> to table columns
Assign component sy-index of structure wa_header to <fs>.
If sy-subrc ne 0.
Exit.
Endif.
ld_Colindx = sy-index.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING #1 = ld_rowindx
#2 = ld_colindx.


SET PROPERTY OF cells 'Value' = <fs>.
Enddo.
ENDLOOP.

***********************************************
* Download data to excel spreadsheet *
***********************************************
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 2.

clear: ld_rowindx, ld_colindx.
LOOP AT it_data into wa_data.
ld_rowindx = sy-tabix + 1. "start at row 2 (leave 1st for for headings

* Fill columns for current row
Clear ld_colindx.
Do.
Assign component sy-index of structure wa_data to <fs>.
If sy-subrc ne 0.
Exit.
Endif.
ld_colindx = sy-index.
CALL METHOD OF sheet 'Cells' = cells
EXPORTING #1 = ld_rowindx
#2 = ld_colindx.


SET PROPERTY OF cells 'Value' = <fs>.
Enddo.
ENDLOOP.


CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 2.


***********************************************
* Capture range of cells to be modified *
***********************************************
*Start of range Cell
CALL METHOD of application 'Cells' = cell1
EXPORTING
#1 = 1 "down
#2 = 1. "across
*End of range Cell
CALL METHOD of application 'Cells' = cell2
EXPORTING
#1 = 1 "down
#2 = 16. "across

CALL METHOD of application 'Range' = range
EXPORTING
#1 = cell1
#2 = cell2.

***********************************************
* Modify properties of cell range *
***********************************************
* SET FONT DETAILS of range
get PROPERTY OF range 'Font' = font.
set PROPERTY OF font 'Bold' = 1.
set property of font 'Size' = 10.

* SET CELL SHADING PROPERTIES of range
call method of range 'INTERIOR' = shading.
set property of shading 'ColorIndex' = 15. "colour - change number for diff colours
set property of shading 'Pattern' = 1. "pattern - solid, striped etc
free object shading.

* CHANGE RANGE VALUES - include all lines (1,2 and 3)
free range.
CALL METHOD of application 'Cells' = cell1 "start cell
EXPORTING
#1 = 1 "down
#2 = 1. "across

CALL METHOD of application 'Cells' = cell2 "end cell
EXPORTING
#1 = 3 "down
#2 = 16. "across

CALL METHOD of application 'Range' = range
EXPORTING
#1 = cell1
#2 = cell2.

* SET BORDER PROPERTIES of range
call method of range 'BORDERS' = BORDER exporting #1 = '1'. "left
set property of border 'LineStyle' = '1'. "line style solid, dashed...
set property of border 'WEIGHT' = 2. "max = 4
free object border.

call method of range 'BORDERS' = BORDER exporting #1 = '2'. "right
set property of border 'LineStyle' = '1'.
set property of border 'WEIGHT' = 2. "max = 4
free object border.

call method of range 'BORDERS' = BORDER exporting #1 = '3'. "top
set property of border 'LineStyle' = '1'.
set property of border 'WEIGHT' = 2. "max = 4
free object border.

call method of range 'BORDERS' = BORDER exporting #1 = '4'. "bottom
set property of border 'LineStyle' = '1'.
set property of border 'WEIGHT' = 2. "max = 4
free object border.

* Overwites all cell values in range to equal 'test'
* SET PROPERTY OF range 'VALUE' = 'test'.


***********************************************
* Set Columns to auto fit to width of text *
***********************************************
CALL METHOD OF application 'Columns' = COLUMN.
CALL METHOD OF COLUMN 'Autofit'.
free object COLUMN.

***********************************************
* Save excel speadsheet to particular filename*
***********************************************
CALL METHOD OF sheet 'SaveAs'
EXPORTING #1 = 'c:\temp\exceldoc2.xls' "filename
#2 = 1. "fileFormat


free object sheet.
free object workbook.
free object application.

The output shows in sheet 2 as :

How can I display these same records in sheet1 and sheet 3 ? Where and what change do I need to make in the code ?

Regards,

Manish

Attachments

screenshot001.jpg (123.2 kB)