FM for excel sheet download

Former Member · ‎05-08-2006

Is there any function module which can help me download data from an internal table into mutiple sheets on an excel workbook ?

My internal table contains more than 65536 records.

Former Member · ‎05-08-2006

Hi Gaurav,

One way is to split the number of records into several tables and download them indpendently.

Thanks and Regards,

Bharat Kumar Reddy.V

Former Member · ‎05-08-2006

Hi Bharat,

I have already written the logic for splitting the data into 2+ internal tables ,but my requirement is a single excel sheet and so i want to know if there is any method except OLE code by which i can download the data into multiple sheets of the same excel workbook.

Gaurav.

former_member181966 · ‎05-08-2006

Check it out !!

&----

*& Report Y_EXCEL_MULTIPLE *

*& *

&----

*& *

&----

REPORT Y_EXCEL_MULTIPLE.

parameters: p_fname like RLGRAP-FILENAME

default 'C:\temp\testNN.xls'.

data: fname like p_fname,

kn like sy-repid.

data: cnt type i value 0.

data: sheetname(10) value 'TEST ',c_row type i,

scnt type i,

val(20), wb(2).

parameters: p_exvis as checkbox default 'X',

p_workbk(2) type p default '01',

p_wsheet(2) type p default '01'.

CONSTANTS: OK TYPE I VALUE 0.

INCLUDE OLE2INCL.

DATA: EXCEL TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL TYPE OLE2_OBJECT,

CELL1 TYPE OLE2_OBJECT,

COLUMN TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT,

BORDERS TYPE OLE2_OBJECT,

button TYPE OLE2_OBJECT,

int TYPE OLE2_OBJECT,

FONT TYPE OLE2_OBJECT,

ROW TYPE OLE2_OBJECT.

data: application type ole2_object,

book type ole2_object,

books type ole2_object.

data: ole_book TYPE ole2_object.

do p_workbk times.

move p_fname to fname.

unpack sy-index to wb.

replace 'NN' with wb into fname.

*

perform create_EXCEL.

create sheets and save

perform sheet.

perform save_book.

enddo.

write: ' Done'.

----

FORM create_excel *

----

........ *

----

form create_excel.

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 .

Put Excel in background

if p_exvis eq 'X'.

SET PROPERTY OF EXCEL 'VISIBLE' = 1.

else.

SET PROPERTY OF EXCEL 'VISIBLE' = 0.

endif.

Create worksheet

set property of excel 'SheetsInNewWorkbook' = 1.

call method of workbook 'ADD'.

endform.

----

FORM save_book *

----

........ *

----

form save_book.

get property of excel 'ActiveSheet' = sheet.

free object sheet.

free object workbook.

GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.

call method of workbook 'SAVEAS' exporting #1 = p_fname #2 = 1.

call method of workbook 'CLOSE'.

call method of excel 'QUIT'.

free object sheet.

free object workbook.

free object excel.

endform.

----

FORM sheet *

----

........ *

----

form sheet.

do p_wsheet times.

unpack sy-index to sheetname+5(2).

if sy-index gt 1.

CALL METHOD OF EXCEL 'WORKSHEETS' = sheet.

call method of sheet 'ADD'.

free object sheet.

endif.

scnt = sy-index.

call method of excel 'WORKSHEETS' = SHEET EXPORTING #1 = scnt.

call method of sheet 'ACTIVATE'.

SET PROPERTY OF SHEET 'NAME' = sheetname.

free object sheet. "OK

perform fill_sheet.

*

CALL METHOD OF EXCEL 'Columns' = COLUMN.

CALL METHOD OF COLUMN 'Autofit'.

free object COLUMN.

*

call method of sheet 'BUTTON' = button.
call method of button 'ADD'.
set property of button 'fmButtonStyle' = 0.
exporting #1 = '96.75' #2 = '372' #3 = '123.75' #4 = '12'.
set property of button 'Characters' = 'ButtonTest'.

free object button.

free object font.

free object int.

free object cell.

free object: cell1.

free object range.

free object borders.

free object: column, row.

enddo.

free object font.

free object int.

free object cell.

free object cell1.

free object range.

free object borders.

free object column.

free object row.

free object sheet.

endform.

----

FORM border *

----

........ *

----

--> we *

----

form border using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '1'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '2'.

set property of borders 'WEIGHT' = we.

free object borders.

top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '3'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '4'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM border2 *

----

........ *

----

--> we *

----

form border2 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '5'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '6'.

set property of borders 'WEIGHT' = we.

free object borders.

top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '7'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '8'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM border3 *

----

........ *

----

--> we *

----

form border3 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '9'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '10'.

set property of borders 'WEIGHT' = we.

free object borders.

top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '11'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '12'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM fill_cell *

----

........ *

----

--> color *
--> pattern *

----

form fill_cell using color pattern.

call method of cell 'INTERIOR' = int.

set property of int 'ColorIndex' = color.

set property of int 'Pattern' = pattern.

free object int.

endform.

----

FORM font *

----

........ *

----

--> bold *
--> size *

----

form font using bold size.

call method of CELL 'FONT' = font.

set property of font 'BOLD' = bold.

set property of font 'SIZE' = size.

free object font.

endform.

----

FORM fill_sheet *

----

........ *

----

form fill_sheet.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'.

perform font using 1 '14'.

SET PROPERTY OF CELL 'VALUE' = 'Counter'.

perform fill_cell using '15' '1'.

perform border using '2'.

free object cell.

val = 'Workbook-Count'.

move wb to val+16.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '14' '1'.

perform border using '4'.

free object cell.

val = 'Sheet-Count'.

unpack sy-index to val+12.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'C1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '12' '1'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E3'.

perform border using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E5'.

perform border using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E7'.

perform border using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E9'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F3'.

perform border2 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F5'.

perform border2 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F7'.

perform border2 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F9'.

perform border2 using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G3'.

perform border3 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G5'.

perform border3 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G7'.

perform border3 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G9'.

perform border3 using '4'.

free object cell.

val = 'ROW-Count'.

do 19 times.

c_row = sy-index + 1.

unpack c_row to val+12(4).

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

enddo.

endform.

----

You find SAP OLE programs under development Class 'SOLE' *
*
MSTAPPL Table Maintenance APPL *
RSOLEDOC Document list *
RSOLEIN0 OLE Load Type Information *
RSOLEINT Type Info Loaded *
RSOLETI0 OLE Object Browser *
RSOLETI1 OLE Object Browser *
RSOLETI2 OLE Object Browser *
RSOLETI3 F4 Help For OLE Objects *
RSOLETT1 OLE 2.0 Automation Demo Program *
*
Transactions: *
SOLE *
SOLO - List of OLE applcations with loaded type info *
*
*
You will find the decription of possible objects and methods in the *
windows help file for excel. *

----

Hope this’ll give you idea!!

<b>P.S award the points.!!! !!!</b>

Good luck

Thanks

Saquib Khan

"Some are wise and some are otherwise"

Former Member · ‎05-08-2006

Hi Gaurav,

Then I am afraid, I don't have the answer. You can try the solutions given by our friends.

Thanks and Regards,

Bharat Kumar Reddy.V

Former Member · ‎05-08-2006

hi

use fm GUI_DOWNLOAD( FILETYPE AS DAT )OR FM SAP_CONVERT_TO_XLS_FORMAT

HOPE THIS HELPS,

PRIYA.

Former Member · ‎05-08-2006

Hi,

I think FM 'XXL_FULL_API' may be useful in this since it has input parameters for starting data and ending data.

Refer this link for more. It gives a nice example.

<a href="http://www.thespot4sap.com/Articles/Download_to_excel.asp">http://www.thespot4sap.com/Articles/Download_to_excel.asp</a>

Hope it will help you !!!

Jignesh.

- Reward if useful otherwise throw away !!

Former Member · ‎05-08-2006

Gavrav,

u can check the below links

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c1d54348-0601-0010-3e98-bd2...

/people/sap.user72/blog/2006/02/07/downloading-data-into-excel-with-format-options

Regards,

Naveen