Skip to Content
avatar image
Former Member

ABAP write data into Excel

I used to write data into excel in range area, which is using the following method:

CALL METHOD OF sheet

'CELLS' = cell

EXPORTING

#1 = p_row

#2 = p_column.

But I 'd like to know if there is a way to write data into excel using a cell's name.

PS:

how to define a cell's name?

insert---- names ---definition , input your name and we can find the cell using this name.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Mar 26, 2009 at 08:29 AM

    Refer below code....

    REPORT  YKEXL1                                  .
    
    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), END OF itab1.
    DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
    DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
    
    START-OF-SELECTION.
    
    APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,
    *'=Sheet1!A1 & u201D u201D & Sheet2!A1' TO itab3,
    'John' TO itab1, 'Smith' TO itab2.
    *'=Sheet1!A2 & u201D u201D & Sheet2!A2' 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 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.
    ENDLOOP.
    
    * Create second Excel sheet
    CALL METHOD OF application 'Worksheets' = sheet
    EXPORTING #1 = 2.
    SET PROPERTY OF sheet 'Name' = 'Sheet2'.
    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 'Value' = itab2-last_name.
    ENDLOOP.
    
    * Create third Excel sheet
    CALL METHOD OF application 'Worksheets' = sheet
    EXPORTING #1 = 3.
    SET PROPERTY OF sheet 'Name' = 'Sheet3'.
    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-formula.
    SET PROPERTY OF cells 'Value' = itab3-formula.
    ENDLOOP.
    
    * Save excel speadsheet to particular filename
    CALL METHOD OF sheet 'SaveAs'
    EXPORTING #1 = 'c:\temp\exceldoc1.xls'     "filename
    #2 = 1.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 26, 2009 at 08:20 AM

    By the way, if any way to check all properties and methods about ole2 object.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 26, 2009 at 08:24 AM

    Hi,

    Refer to this link..http://209.85.153.132/translate_c?hl=en&sl=ru&u=http://www.sapnet.ru/viewforum.php%3Ff%3D9&usg=ALkJrhif7LrpHXw1uSgJQYca6VO8hVMdGg

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 26, 2009 at 08:50 AM

    Hi,

    Refer this sample code below:

    
    
    
    
    INCLUDE ole2incl.               "include used for providing classes used for using 
                                               create object for creating application and worksheets
    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), END OF itab1.
    *DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
    DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
    *
    
    
    TABLES: vbap,mara.
    
    
    TYPES: BEGIN OF itab,
           lifnr TYPE lfa1-lifnr,
           land1 TYPE lfa1-land1,
    *       name1 TYPE lfa1-name1,
    *       ort01 TYPE lfa1-ort01,
           END OF itab.
    
    
    
    DATA: BEGIN OF itab2 OCCURS 0,
    matnr TYPE mara-matnr,
    ersda TYPE mara-ersda,
    ernam TYPE mara-ernam,
    END OF itab2.
     data : v_row type sy-tabix.
    
    DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.
    
    DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
          WA_XLSTAB LIKE LINE OF IT_XLSTAB.
    
    START-OF-SELECTION.
    
      SELECT lifnr land1 fROM lfa1  INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.
    
    
      SELECT matnr
      ersda
      ernam
      FROM mara
      INTO CORRESPONDING FIELDS OF
      TABLE itab2 UP TO 5 ROWS.
    
    
    
    ************************************************************************
    *START-OF-SELECTION
    START-OF-SELECTION.
    
      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'.
      clear v_row.
      LOOP AT itab1.
         v_row = sy-tabix.
          perform fill_cell  using  v_row 1  itab1-lifnr.
          perform fill_cell  using  v_row 2 itab1-land1.
      ENDLOOP.
    
    * Create second Excel sheet
      CALL METHOD OF application 'Worksheets' = sheet
                                   EXPORTING #1 = 2.
      SET PROPERTY OF sheet 'Name' = 'Sheet2'.
      CALL METHOD OF sheet 'Activate'.
      clear v_row.
      LOOP AT itab2.
         v_row = sy-tabix.
    
          perform fill_cell using  v_row 1 itab2-matnr.
          perform fill_cell using  v_row 2 itab2-ersda.
          perform fill_cell using  v_row 3 itab2-ernam.
    
      ENDLOOP.
    
    
    * Save excel speadsheet to particular filename
      CALL METHOD OF sheet 'SaveAs'
                      EXPORTING #1 = 'c:\temp\excelgeet.xls'     "filename
                                #2 = 1.                          "fileFormat
    
    *  Closes excel window, data is lost if not saved
      SET PROPERTY OF application 'visible' = 0.
    
    *  call method of sheet 'CLOSE'
    *
    *  EXPORTING #1 = 'YES'.
    
    
    *&---------------------------------------------------------------------*
    *& both the below coding closes the apllication permanently from the task manager also.
    *&---------------------------------------------------------------------*
    
    SET PROPERTY OF application 'DisplayAlerts' = 0.
       free OBJECT application.
    
    
    **  call method of application 'QUIT'.
    **
    **  FREE OBJECT: APPLICATION,
    **               SHEET.
    
    *&---------------------------------------------------------------------*
    *&      Form  fill_cell
    *&---------------------------------------------------------------------*
    *       text
    *----------------------------------------------------------------------*
    *      -->P_1      text
    *      -->P_V_COL  text
    *      -->P_ITAB1_LIFNR  text
    *----------------------------------------------------------------------*
    form fill_cell  using row  col val.
    
        CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row  #2 = col.
        SET PROPERTY OF cells 'Value' = val.
    
    
    endform.                    " fill_cell
    
    
    

    In this 2 internal tables data is getting downloaded into 1 excel files 2 sheets.

    Hope it helps

    Regards

    Mansi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 26, 2009 at 09:40 AM

    I got the idea finally.

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

    can make my idea to real. and p_rc is the range name.

    Add comment
    10|10000 characters needed characters exceeded