Formula are not resolving

Aspose.Cells Product Family
1

Hi,

When I write formula in code, the formulae sometimes do not resolve to the result in the worksheet. They display as the formula.

For example, if I use:

cells(1,1).formula = "=A5", the value "=A5" appears in the cell, rather than the result, which is the value of cell A5

cells(1,1).formula = "=(A5+A10+A15)", same thing happens.

However, when I can see the spreadsheet, and cut the formula and paste it straight back in, it resolves properly.

Do I have to tell it to recalculate or something? What am I doing wrong?

2

Hi,

After setting all formulas by code, you need to call the WebWorksheets.RunAllFormulas method to caculate all the formula cells.

3

Thankyou, but what object do I run this method against? Excel, worksheet, cells? I can’t see .RunAllFormula as a method for any of the objects I’m using.

4

Hi,

For example:

...

// Assumes your control on the page is named "ExcelWeb1"

ExcelWeb1.WebWorksheets.RunAllFormulas();

5

Which product are you using, Aspose.Excel or Aspose.Excel.Web? If Aspose.Excel.Web, please follow Henry's guide to use WebWorksheets.RunAllFormula method.

If you use Aspose.Excel, which version of Aspose.Excel are you using? You just need to use Cell.Formula to assign the formula. RunAllFormula method needn't to be called.

6

I'm using Aspose.Excel, although it's for a web project. There's certainly nothing like the WebWorksheets object.

I am building a spreadsheet entirely in code, with a variable number of worksheets, so I have no form controls to work from.

The whole thing is based on creating an excel object, creating worksheets, populating cells with numbers and formula.

Some formula seem to resolve ok, eg =Sum(A1:A5), but if I use a formula like =A5 or =A1+A10+A20 then it does not resolve.

Any ideas?

7

Sorry I realise that I should have posted this in the Aspose.Excel forum instead of this one. I still need the help though…

8

I move you post here.

For your problem, which version are you using? I don't find the problem. Please try this attached fix.

Please remove reference to the old dll in your project and re-add reference to this new dll. Then rebuild your application.

If you still meet problem, please post your output file here.

9

Thankyou. I have applied the new dll but it still happens.

I have attached the resulting spreadsheet. Note the formulae in cells E42 and C58. If you cut the formulae, then paste it straight back to the same cell, it resolves itself.

10

I checked your file and I think you didn't use the correct method to set the formula.

In Cell E42, it's a string "=E23+E31+E39", not a formula "=E23+E31+E39".

So you must use the following code:

cells("E42").PutValue("=E23+E31+E39")

but you should use

cells("E42").Formula = "=E23+E31+E39"

To verify this, please try the following code:

Dim ExcelObj as Excel = new Excel()

Dim cells as Cells = ExcelObj.Worksheets(0).Cells

cells("E42").Formula = "=E23+E31+E39"

ExcelObj.Save("C:\\book1.xls")

11

I am definitely already using the method cells("E42").Formula = "=E23+E31+E39" for this.

The problem seems to be the type of formula. As I said, when I use the form =Sum(A3:A5), it works fine, but when I use the form =E23+E31+E39 or =E23 it doesn't.

Also, I debugged the code, and used the .IsFormula property to check whether Aspose thinks it is a formula, and it returns true.

So I can only conclude that it doesn't like this style of formula.

What to do now?

12

Please create a new project to try my sample code with this new dll I posted here and see what happens.

13

Thankyou very much for your prompt and friendly help. This is now working fine.