Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

dowunload data in different sheet of the same excel file

hi all,

i have three internal table and i want to download the data in three different work sheet of same excel file, i am able to down load only one colum of internal table but i want that the work sheet should contain all the colums with the record,

i mean i am havin internal table say it_mara now in this table i have various colums

i want that the hole of the colums should be down loaded in the work sheet.

and i also want to give the heading to ecah of the cell, and after heading data should appear, friends i am very new to OLE concepts please give some importent information regarding my problem,

full points will be allocated to all the useful help,

thanks in advance,

Tags:
Former Member
Former Member replied

hello,

I modified the same code to get 2 columns

search for the ** changed to * end of change comments in the code

that is where i have made the modifications

the logic is as follows..

We need to choose a cell and then write to it

In this case we have stored its position in the variable 'index'

We have a constant row_max which stores the maximum number of rows in the sheet (which is 256 for all the versions of excel i have used till no - though i think it has increased in office 2007)

the index value of the first row cell in the first column is 1

the index value of the first row cell in the second column is 2 and so on

the index value of the second row cell in the first column is 257 etc

the statement " index = row_max * (row - 1) + (col). " is used to calculate the position based on row and column number

therefore row = 1 and col = 1 will give

256 * 0 + 1

= 0 + 1

= 1

incase of internal tables, sy-tabix will give us the row number

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.
 

** changed
DATA: BEGIN OF itab1 OCCURS 0, 
first_name(10),
field_2(10),
END OF itab1.
** end of change

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.
 

** changed
  APPEND: 'surname1' TO itab2,
                              'worli' TO itab3,
            'surname2' TO itab2,
                              'chowpatty' TO itab3,
            'surname3' TO itab2,
                              'versova' TO itab3,
           'surname4' TO itab2,
                              'grant road' TO itab3,
           'surname5' TO itab2,
                              'gaon' TO itab3,
            'surname6' TO itab2,
                              'mahim' TO itab3.
 
itab1-first_name = 'name1'.
itab1-field_2 = 'other1'.
append itab1.

itab1-first_name = 'name2'.
itab1-field_2 = 'other2'.
append itab1.

itab1-first_name = 'name3'.
itab1-field_2 = 'other3'.
append itab1.

itab1-first_name = 'name4'.
itab1-field_2 = 'other4'.
append itab1.

itab1-first_name = 'name5'.
itab1-field_2 = 'other5'.
append itab1.

itab1-first_name = 'name6'.
itab1-field_2 = 'other6'.
append itab1.

* end of change
 
*  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 'Value' = itab1-first_name.
 
** changed
         index = index + 1.
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Value' = itab1-field_2.
* end of change

        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:tempexceldoc1.xls'     "filename
                             #2 = 1.                          "fileFormat

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question