Excel Template

[Former Member]

Hi all,

1- I want using Excel Template in my Abap program. How can I send data to Excel Template in my Abap program?

2- I am creating values dynamically in my program. How can I modify my Excel template for formulas? An example

a b c d e ... (dynamic fields)

1 =a2/c 2 =c2/e 1 ...

2 ....

3 ....

=Sum(a2-an)....

[RichHeilman]

Have you checked out OLE yet? Make sure that you check out the link to the article. This should get you started.

/people/rich.heilman2/blog/2005/09/12/manipulate-excel-with-ole-abap

Regards,

Rich Heilman

[Former Member]

Hi,

Dear Guru. I don't want using OLE in this time. But thanks for article. I will try it.

[Former Member]

Hi,

U can download the data into Excel sheet from ur program. Also, for the formulas, initiall u have to calculate and store them via in a dynamic internal table. Finally, u can download this data into excel. There are function modules for this. I dont remember exactly what it is, but try in SE37 with ALSM* and u will find one.

Pl. award points if helpful.

[Former Member]

Hi,

Ramesh I think so you talk about ALSM_EXCEL_TO_INTERNAL_TABLE function. But it doesn't my request. I want export my Internal Table records To Excel application. Do you have any idea? Thanks for your quick answer.

[RichHeilman]

Mehmet, please check out my weblog on OLE. This is how you will put your values into the specific fields of the excel template.

Regards,

Rich Heilman

[Former Member]

Hi Mehmet Avsar,

You can refer programs BC_ALVEXCEL and GREXCEL0 to learn about Excel template.

Regards,

Raj

[Former Member]

Hi,

You can pass data to an excel file using OLE Programming.

Sample Code:

***********

CREATE OBJECT w_excel 'EXCEL.APPLICATION'.

ole_check_error sy-subrc w_valid.

• SET PROPERTY OF w_excel 'Visible' = 1.

ole_check_error sy-subrc w_valid.

CALL METHOD OF w_excel 'Workbooks' = w_wbooks.

ole_check_error sy-subrc w_valid.

CALL METHOD OF w_wbooks 'Add' = w_wbook.

ole_check_error sy-subrc w_valid.

PERFORM fill_roles.

CHECK w_valid IS INITIAL.

Once excel file is ready for the first time you can give some formulas for corresponding cells, for next time you can simply mopdify the values.

If am answered to your problem,let me know i can give you some more help on this.

Thanks,

Suman

[RichHeilman]

Check it out, this is how easy it is .....

Create an xls file in C:\ and name it as RichTest.xls

The program will do the rest. It will put a value of 10 into A1 and a value of 20 into A2, then it will put a formulat in A3 that sums the two values.

report zole_example.

include ole2incl.

data: e_sheet type ole2_object.
data: e_appl  type ole2_object.
data: e_work  type ole2_object.
data: e_cell  type ole2_object.

data: field_value(30) type c.

parameters: p_file type localfile default 'C:RichTest.xls'.

start-of-selection.

* Start the application
  create object e_appl 'EXCEL.APPLICATION'.
  set property of e_appl 'VISIBLE' = 1.

* Open the file
  call method of e_appl 'WORKBOOKS' = e_work.

  call method of e_work 'OPEN'
          exporting
               #1 = p_file.


* Position to specific cell  in  Column 1
  call method of e_appl 'Cells' = e_cell
         exporting
              #1 = 1
              #2 = 1.
* Set the value
  set property of e_cell 'Value' = '10'.


* Position to specific cell  in  Column 2
  call method of e_appl 'Cells' = e_cell
         exporting
              #1 = 2
              #2 = 1.
* Set the value
  set property of e_cell 'Value' = '20'.


* position to specific cell  in  column 2
     call method of e_appl 'Cells' = e_cell
            exporting
                 #1 = 3
                 #2 = 1.
* Set the value
  set property of e_cell 'Value' = '=SUM(R[-2]C:R[-1]C)' .




** Close the file
*  call method of e_work 'close'.
*
** Quit the file
*  call method of  e_appl  'QUIT'.
*
*  free object e_appl.

[Former Member]

Hi Suman Kumar Reddy Kalavakuri

You are making via OLE but I don't want using OLE. So if you give me any idea for using excel template for this process (OLE) . I will try it.

PS. Special question for you. What is Kumar. Is that name or prefix? Because I see Kumar nearly all Indian persons name. Thanks for your interest

[Former Member]

Hello Mehmet,

Maybe you should describe your requirement in more detail, because OLE definitely seems like the best solution for what you are doing. Can you describe your scenario in more detail?

Regards,

Martin

[Former Member]

Hello Martin,

I am creating a dynamic internal table using QALS,QAMV tables for its field names.

Second step. Using QAMR table (with conditions) and select data to my dynamic internal table. For example its name <DynTable> . Ok

But my hard work is calculating some values from this internal tables. This table structure like this.

DateNoVal1PVal1Val2PVal2Val3--PVal3...

... 011235?????4333?????9867--????

...............................................

Totals : 12121 54454 4564564

my Pvalx values formulas are :

Valx-Sum of valx * %12

So I think I will do it via Excel template. Define formulas in Excel template. And export my internal to Excel. It will calculated from excel.

So my real problem is that. I wish I can explain my problem.

[RichHeilman]

Ok, so you are going to put all the formulas in Excel. Then you just want to put values into those fields, right? In order to put values in specific cells of the spreadsheet, you need to use OLE. There is no other way. You can do a straight internal table dump to XLS using GUI_DOWNLOAD, but I don't think that is what you want. You want to put values into specific fields of an existing Excel sheet, right?

Regards,

Rich Heilman

[Former Member]

Dear Rich you are right. But I don't know how many fields I have. May be 3 may be 5 but all field have a percentage column. Because it is dynamic.

|Col1|PercentofCol1|Col2|PercentofCol2|Coln|PercentofColn

I am filling ColX fields in internal table but don't fill PercentofColX fields. I want fill them in Excel.

I need your suggestion Thanks.

[RichHeilman]

I see, you will have to keep track of the number of columns in your program and then manipulate the excel sheet accordingly.

Regards,

Rich Heilman

[Former Member]

What is your suggestion about this?