topic Re: Download Data to multiple Excel Sheets in Application Development and Automation Discussions

Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 06:52:51 GMT

Hi All,

I have a report program which downloads the data to multiple excel files.

Now, the change is I need to download the same data to multiple excel sheets (in the same file) instead of multiple excel files.

Is there any function module to download the data to multiple sheets in the same file.

Please reply urgently.

Good Reward points would be awarded.

Note: the file has to be downloaded from application server to desktop and the original program uses "GUI_DOWNLOAD" function to download the file:

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = ld_fullpath

filetype = 'ASC'

APPEND = 'X'

write_field_separator = 'X'

CONFIRM_OVERWRITE = 'X'

TABLES

data_tab = it_datatab[]

EXCEPTIONS

file_open_error = 1

file_write_error = 2

OTHERS = 3.

Thanks,

Vishal.

Re: Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 07:12:32 GMT

I don't think this is possible with GUI_DOWNLOAD. But you can do that with OLE objects.

CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.

create a new workbook

CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.

Check this link for more info;

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/204d1bb8-489d-2910-d0b5-cdddb3227820

Re: Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 07:17:47 GMT

Hi,

try following,

REPORT ZSIRI NO STANDARD PAGE HEADING.

this report demonstrates how to send some ABAP data to an
EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

handles for OLE objects

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_MAPL TYPE OLE2_OBJECT, " list of workbooks

H_MAP TYPE OLE2_OBJECT, " workbook

H_ZL TYPE OLE2_OBJECT, " cell

H_F TYPE OLE2_OBJECT. " font

TABLES: SPFLI.

DATA H TYPE I.

table of flights

DATA: IT_SPFLI LIKE SPFLI OCCURS 10 WITH HEADER LINE.

&----

*& Event START-OF-SELECTION

&----

START-OF-SELECTION.

read flights

SELECT * FROM SPFLI INTO TABLE IT_SPFLI UP TO 10 ROWS.

display header

ULINE (61).

WRITE: / SY-VLINE NO-GAP,

(3) 'Flg'(001) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(4) 'Nr'(002) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(20) 'Von'(003) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(20) 'Nach'(004) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(8) 'Zeit'(005) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP.

ULINE /(61).

display flights

LOOP AT IT_SPFLI.

WRITE: / SY-VLINE NO-GAP,

IT_SPFLI-CARRID COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CONNID COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CITYFROM COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CITYTO COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-DEPTIME COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP.

ENDLOOP.

ULINE /(61).

tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-007

EXCEPTIONS

OTHERS = 1.

start Excel

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\kis_excel.xls'

PERFORM ERR_HDL.
tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-008

EXCEPTIONS

OTHERS = 1.

get list of workbooks, initially empty

CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

PERFORM ERR_HDL.

add a new workbook

CALL METHOD OF H_MAPL 'Add' = H_MAP.

PERFORM ERR_HDL.

tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-009

EXCEPTIONS

OTHERS = 1.

output column headings to active Excel sheet

PERFORM FILL_CELL USING 1 1 1 'Flug'(001).

PERFORM FILL_CELL USING 1 2 0 'Nr'(002).

PERFORM FILL_CELL USING 1 3 1 'Von'(003).

PERFORM FILL_CELL USING 1 4 1 'Nach'(004).

PERFORM FILL_CELL USING 1 5 1 'Zeit'(005).

LOOP AT IT_SPFLI.

copy flights to active EXCEL sheet

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 IT_SPFLI-CARRID.

PERFORM FILL_CELL USING H 2 0 IT_SPFLI-CONNID.

PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM.

PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO.

PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME.

ENDLOOP.

changes by Kishore - start
CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

CALL METHOD OF H_EXCEL 'Worksheets' = H_MAPL." EXPORTING #1 = 2.

PERFORM ERR_HDL.

add a new workbook

CALL METHOD OF H_MAPL 'Add' = H_MAP EXPORTING #1 = 2.

PERFORM ERR_HDL.

tell user what is going on

SET PROPERTY OF H_MAP 'NAME' = 'COPY'.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-009

EXCEPTIONS

OTHERS = 1.

output column headings to active Excel sheet

PERFORM FILL_CELL USING 1 1 1 'Flug'(001).

PERFORM FILL_CELL USING 1 2 0 'Nr'(002).

PERFORM FILL_CELL USING 1 3 1 'Von'(003).

PERFORM FILL_CELL USING 1 4 1 'Nach'(004).

PERFORM FILL_CELL USING 1 5 1 'Zeit'(005).

LOOP AT IT_SPFLI.

copy flights to active EXCEL sheet

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 IT_SPFLI-CARRID.

PERFORM FILL_CELL USING H 2 0 IT_SPFLI-CONNID.

PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM.

PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO.

PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME.

ENDLOOP.

changes by Kishore - end
disconnect from Excel
CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'C:\SKV.XLS'.

FREE OBJECT H_EXCEL.

PERFORM ERR_HDL.

----

FORM FILL_CELL *

----

sets cell at coordinates i,j to value val boldtype bold *

----

FORM FILL_CELL USING I J BOLD VAL.

CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J.

PERFORM ERR_HDL.

SET PROPERTY OF H_ZL 'Value' = VAL .

PERFORM ERR_HDL.

GET PROPERTY OF H_ZL 'Font' = H_F.

PERFORM ERR_HDL.

SET PROPERTY OF H_F 'Bold' = BOLD .

PERFORM ERR_HDL.

ENDFORM.

&----

*& Form ERR_HDL

&----

outputs OLE error if any *

----

--> p1 text
<-- p2 text

----

FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

For moreinformation refer

http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

Regards,

Dhan

Re: Download Data to multiple Excel Sheets

former_member556412 — Tue, 17 Jun 2008 07:28:42 GMT

Hi,

follow this link, for a similar kind of download program.

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/c05db2ca-569e-2910-0784-fc06cc3be31d

Regards,

Bhanu.

Re: Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 14:14:28 GMT

Hi All,

I am using the following code to put the data in the excel sheet:

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = int.

CALL METHOD OF sheet 'Activate'.

SET PROPERTY OF sheet 'Name' = filename.

LOOP AT it_datatab into wa_datatab.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Value' = wa_datatab-str.

ENDLOOP.

However, this logic is working for downloading to the first 3 sheets and Not working for the sheets 4,5,6, etc...(as by default excel has 3 sheets and to create the 4th sheet onwards we need to right click an existing sheet and click on insert option).

I need to create these sheets and put the data in them...tried a lot of options it didn't work....could anyone give me a sample code for creating a new 4th sheet.

I would be thanksful if anyone could help me with this logic.

and Ofcos Good Reward points would be awarded.

Vishal.

Re: Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 14:21:05 GMT

Hello,

check this code

report zole123.

INCLUDE ole2incl.

DATA: count TYPE i,

application TYPE ole2_object,

workbook TYPE ole2_object,

excel TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

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, place(50), END OF itab3.

************************************************************************

*START-OF-SELECTION

START-OF-SELECTION.

APPEND: 'name1' TO itab1, 'surname1' TO itab2,

'worli' TO itab3,

'nam2' TO itab1, 'surname2' TO itab2,

'chowpatty' TO itab3,

'name3' TO itab1, 'surname3' TO itab2,

'versova' TO itab3,

'name4' TO itab1, 'surname4' TO itab2,

'grant road' TO itab3,

'name5' TO itab1, 'surname5' TO itab2,

'gaon' TO itab3,

'name6' TO itab1, 'surname6' TO itab2,

'mahim' TO itab3.

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

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

SET PROPERTY OF excel 'VISIBLE' = 1.

Create worksheet

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

DO 3 TIMES.

IF sy-index GT 1.

CALL METHOD OF excel 'WORKSHEETS' = sheet.

CALL METHOD OF sheet 'ADD'.

FREE OBJECT sheet.

ENDIF.

ENDDO.

count = 1.

DO 3 TIMES.

CALL METHOD OF excel 'WORKSHEETS' = sheet

EXPORTING

#1 = count.

perform get_sheet_name using scnt sname.

CASE count.

WHEN '1'.

SET PROPERTY OF sheet 'NAME' = 'firstName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab1.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab1-first_name.

SET PROPERTY OF cells 'Value' = itab1-first_name.

ENDLOOP.

WHEN '2'.

SET PROPERTY OF sheet 'NAME' = 'LastName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab2.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab2-last_name.

SET PROPERTY OF cells 'Value' = itab2-last_name.

ENDLOOP.

WHEN '3'.

SET PROPERTY OF sheet 'NAME' = 'place'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab3.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab3-place.

SET PROPERTY OF cells 'Value' = itab3-place.

ENDLOOP.

ENDCASE.

count = count + 1.

ENDDO.

Save excel speadsheet to particular filename

GET PROPERTY OF excel 'ActiveSheet' = sheet.

CALL METHOD OF sheet 'SaveAs'

EXPORTING #1 = 'c:\temp\exceldoc1.xls' "filename

#2 = 1. "fileFormat

Note: to make headings, change the -1 to +1 where specified in the above code and add the following where i have mentioned to add it

index = row_max * ( sy-tabix - 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'value' = header1.

Re: Download Data to multiple Excel Sheets

Former Member — Tue, 17 Jun 2008 16:31:39 GMT

Thanks Naveed...