topic Re: ABAP write data into Excel in Application Development and Automation Discussions

ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:13:14 GMT

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.

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:20:15 GMT

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

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:24:02 GMT

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

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:29:23 GMT

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.

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:36:41 GMT

I don't think it is useful just because it is using row number and column number

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 08:50:46 GMT

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

Re: ABAP write data into Excel

Former Member — Thu, 26 Mar 2009 09:40:22 GMT

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.