Skip to Content
avatar image
Former Member

excel multiple tab sheets

how to download data into multiple tab sheets in a single file

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 08, 2007 at 09:19 AM
    
    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: 'roshani' TO itab1, 'jain' TO itab2,
                                  'worli' TO itab3,
                'nehal' TO itab1, 'shah' TO itab2,
                                  'chowpatty' TO itab3,
               'saad' TO itab1, 'sheikh' TO itab2,
                                  'versova' TO itab3,
                'sushilnath' TO itab1, 'shukla' TO itab2,
                                  'grant road' TO itab3,
                'ajju' TO itab1, 'ratna' TO itab2,
                                  'gaon' TO itab3,
    
                'sanant' TO itab1, 'R.' 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'.
            itab1-first_name = 'FIRST NAME'.
    
            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 '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'.
            LOOP AT itab2.
              index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
              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'.
            LOOP AT itab3.
              index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
              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
    
    Add comment
    10|10000 characters needed characters exceeded