Exporting excel formulas

This post has 6 Replies | 3 Followers

JHH
Points 70
Replied On: Mon, Dec 22 2008 5:21 AM

Hi, I'm trying to export to one excel document some data in a DataTable using Infragistics.Excel, and I would like to export some formulas (for example for the summaries..).

Example:

------------------------------------------------------------------------------------------------------------------------------------------------------

Workbook wb = new Infragistics.Excel.Workbook(Infragistics.Excel.WorkbookPaletteMode.CustomPalette);
Worksheet ws = wb.Worksheets.Add("WorkSheet");

int i = 0;

 for(i=0; i<10; i++) {

      ws.Rows[1].Cells[i].Value = "NIGHT";

}

Infragistics.Excel.Formula formula = Infragistics.Excel.Formula.Parse("=COUNT.IF($A12:$E12;\"NIGHT\")", CellReferenceMode.R1C1);
formula.ApplyTo(ws.Rows[1].Cells[i+1]);

----------------------------------------------------------------------------------------------------------------------------------------------

Thowing an exception when try to apply formula: "The formula has extra expressions after the end of it.Portion with error: ($A12:$E12;"NIGHT")"

 What is wrong?

 Thanks.

 Jacobo.

  • Post Points: 20
Mike Saltzman
Points 444,935
Infragistics Employee
Replied On: Mon, Dec 22 2008 10:19 AM

Hi Jacobo,

For one thing, your formula is using A1 notation and you are specifying R1C1 as your CellReferenceMode. So that's a contradication. 

Also, you are using a semi-colon instead of a comma to separate the arguments of your function. 

 

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

  • Post Points: 55
JHH
Points 70
Replied On: Mon, Dec 22 2008 10:35 AM

 Thanks for your help Mike. Now it works well. 

------------------------------------------------------------------------------------------------------------------------------------------------------

Workbook wb = new Infragistics.Excel.Workbook(Infragistics.Excel.WorkbookPaletteMode.CustomPalette);
Worksheet ws = wb.Worksheets.Add("WorkSheet");

int i = 0;

 for(i=0; i<10; i++) {

      ws.Rows[1].Cells[i].Value = "NIGHT";

}

Infragistics.Excel.Formula formula = Infragistics.Excel.Formula.Parse("=Countif(A12:E12,\"NIGHT\")", CellReferenceMode.A1);
formula.ApplyTo(ws.Rows[1].Cells[i+1]);

----------------------------------------------------------------------------------------------------------------------------------------------

  • Post Points: 5
rashd60
Points 45
Replied On: Tue, Feb 2 2010 5:40 PM

When i use the "=IF" formula in the code during export to excel apply formula, it throws an error "=#N/A"

Example:

=IF(P10 - Q10 + R10  < 0, 0, P10 - Q10 + R10)

 

It use to work in the previous version of infragistics (v7.3). We recently upgraded to the newer version(9.2 for .net3.5).

Is this a known bug?

  • Post Points: 20
Mike Saltzman
Points 444,935
Infragistics Employee
Replied On: Wed, Feb 3 2010 10:34 AM

It's difficult to answer your question without more information. How are you using this formula? To what is it being applied? Are you using a WinGrid or create an Excel file on your own using the Infragistics.Excel assembly?

How is this related to the other posts in this thread?

 

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

  • Post Points: 20
jroconnor
Points 180
Replied On: Wed, Feb 3 2010 10:47 AM

I'm Having this same issue. I'm creating the the excel file on my own using the Infragistics.Excel assembly. I've even simplified the formula to

dim f as Formula = Formula.Parse("=if(1>1, 0, 1)", CellReferenceMode.R1C1)
f.ApplyTo(worksheet.rows(1).cells(1))

And it gives me the "=N/A" error

  • Post Points: 20
Mike Saltzman
Points 444,935
Infragistics Employee
Replied On: Wed, Feb 3 2010 11:21 AM

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

  • Post Points: 5
Page 1 of 1 (7 items) | RSS