topic Re: Hi Experts,, in Application Development and Automation Discussions

Hi Experts,,

Former Member — Tue, 23 Oct 2007 09:31:51 GMT

I have requirement For Data to Down load from Application Server to Excel sheet.

But , here the requirement is to pass the same data into the Different sheets of Same Excel file .( It means Each Excel file haveing mulltiple Excel sheets ).

If any body knows ,please post the Logic... It's Very urgent....

Warm regards,

Tiru...........

Re: Hi Experts,,

Former Member — Tue, 23 Oct 2007 11:26:02 GMT

hi,

REPORT DWNLEXCEL .

INCLUDE ole2incl.

DATA: application TYPE ole2_object,

workbook 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),

last_name(10),

END OF itab1.

START-OF-SELECTION.

itab1-first_name = '123445'.

itab1-last_name = 'tesst'.

append itab1.

clear itab1.

itab1-first_name = '123446'.

itab1-last_name = 'tesst'.

append itab1.

clear itab1.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Add'.

Create first Excel Sheet

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = 1.

CALL METHOD OF sheet 'Activate'.

SET PROPERTY OF sheet 'Name' = 'Sheet1'.

LOOP AT itab1.

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

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

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

index = index + 1. " 1 - column name

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

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

ENDLOOP.

Save excel speadsheet to particular filename

CALL METHOD OF sheet 'SaveAs'

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

#2 = 1. "fileFormat

<b>Hope this is helpful, Do reward.</b>

Re: Hi Experts,,

Former Member — Tue, 23 Oct 2007 11:36:29 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.

Thanks

Re: Hi Experts,,

Former Member — Tue, 23 Oct 2007 14:04:08 GMT

HI Naveen,

Thanks for Quick Responce.

But , I need to Down load to Excel Sheet ,In such way that Each Record in the File has read should be sent to Different sheets of same Excel file .

For Ex..I have records( Row) need to Dowm load following way :

1st record should be in the 1st sheet of Excel,

2nd record should be in the 2nd sheet of Excel,

3rd record should be in the 3rd sheet of Excel,

4th record should be in the 4th sheet of Excel,

5th record should be in the 5th sheet of Excel,

...

So on.

Warm Regards,

..........TIru..........