Skip to Content
avatar image
Former Member

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,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 21, 2007 at 06:54 AM

    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:tempexceldoc1.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.
    

    Message was edited by:

    Kris Donald

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Kris Donald

      hi krish ,

      thanks a lot for all your valuable help,

      every thing is working fine, thanks for all your early response,

      thanks again frnd.

      god bless you,

      null

  • Aug 21, 2007 at 06:42 AM

    Sudeep,

    MS_EXCEL_OLE_STANDARD_OLE-To download the data in different sheets of the same excel file.

    K.Kiran.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 24, 2007 at 05:09 AM

    Use XML.

    Create a very simple excel workbook with three sheets, save as XML, and look at the result in Notepad. You can create the same structure using ABAP.

    I have written a wili about this, or search for Excel XML on the forum.

    Michael

    Add comment
    10|10000 characters needed characters exceeded