Problem with CalculateFormula on Array Formulas

Aspose.Cells Product Family
1

Hi,

I am using 4.7.1 Cells.

When I run workbook.calculateformula() any array formula's I have return a Value. When I open in excel it is fine.

I have recreated the issue on a simple workbook which I attach.

The code I am using for this test is:

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Total.lic")

Dim wbk As Workbook = New Workbook

wbk.Open("c:\arrtest.xls")

wbk.CalculateFormula()

MsgBox(wbk.Worksheets(0).Cells("g120").Value)

It returns #VALUE.

If I comment out the CalculateFormula line it returns the correct value.

I hope you can help,
Oli

2

Hi,

Thank you for sharing the template file and code sample.

After an initial test we have found your mentioned issue, we will look into it and get back to you soon.

Thank You & Best Regards,

3

Hi, do you know of any work around I can use while you investigage as this is causing a bit of a problem for us at the moment.

Thanks for all your help,
Oli

4

Hi,

Thank you for considering Aspose.

Well, we are working on your issue and hopefully we will provide you with a fix in 2~3 days time.

Thank you for understanding.

5

Hi,

Please try the attached version, we have fixed your issue.

Thank you.

6

Hi,

So far it seems to work perfectly.

Thanks for your help,
Oli

7

Hi Guys,

That fixed the previous problem perfectly, I now have another one for also with CalculateFormula with an Array Formula.

The problem is how Cells evaluates >0 against blank cells within IF statements in array formulas.

I have attached the example workbook and find my sample code below.

Thanks again,

Oli

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Total.lic")

Dim wbkTest As New Workbook()

wbkTest.Open("c:\testing\test10.xls")

MsgBox(wbkTest.Worksheets(0).Cells("B5").Value)

wbkTest.CalculateFormula()

MsgBox(wbkTest.Worksheets(0).Cells("B5").Value

8

Hi,

Thanks for providing us the template file with sample code.

Yes, we found the issue after an initial test, we will provide a fix soon.

Thank you.

9

Hi,

Please try the attached version.We have fixed this bug. If you still have any problem, please feel free to contact us.

Thank you.

10

Many thanks,
Oli

11

The issues you have found earlier (filed as 8588) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Note: Just for your knowledge. In the new release v4.8.0, we have merged Aspose.Grid suite to Aspose.Cells for .NET msi installer as Aspose.Grid would be no longer offered as a separate product now. You need to install the installer (uninstall older one first if you have) and use only Aspose.Cells.dll library in your project for your need. You may also take advantage using Aspose.Cells Grid suite though.

12

I am not totally sure if this is the correct thread to respond in, but I think this issue may have popped up again. Please see the attached document that is giving me a problem.


When I open the file in Excel (14.0.7116.5000), it correctly evaluates the formulas on the “OUT_Suggestions” worksheet…
However, when I load the sheet, and retrieve the values of the named region ‘OUTPUT_Suggestions’ all the cells in column ‘A’ have the correct values, but the cells in ‘B’ all just say ‘#value’ indicating that they have not been evaluated (correctly).

I’m currently using version: 7.7.2.0 C#

var readValuesDocument = new Aspose.Cells.Workbook(“f:\BadEvaluation.xlsx”);
readValuesDocument.CalculateFormula(false);
var tableRange = readValuesDocument.Worksheets.GetRangeByName(“OUTPUT_Suggestions”);

Please advice,
Bas.
13

Hi Van de Loo,


Thanks for the template file.

After an initial test, I observed the issue as you mentioned by using your template file with your sample code. MS Excel correctly calculates the formulas on the “OUT_Suggestions” worksheet. However, Aspose.Cells evaluates the formulas as “#VALUE!” when I opened the output file into MS Excel and check the sheet.
e.g
Sample code:

var readValuesDocument = new Aspose.Cells.Workbook(“e:\test2\BadEvaluation.xlsx”);
readValuesDocument.CalculateFormula();
readValuesDocument.Save(“e:\test2\outBadEvaluation1.xlsx”);

I have logged a ticket with an id “CELLSNET-42507” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
14

Hello,

I have some additional information and a question.


Information
We have run into the same issues even when we are not using array functions.
So the problem may be related to calculations in general, not arrays specifically.
We are using your ‘dynamic functions’ in the original template, I am not sure if this is a factor.

Question
We have intentions to use the excel-sheets as part of the new release of our platform, which in was planned for the upcoming weeks.
Any ideas on whether this will take long to be resolved?

Greetings.
Bas
15

Hi,


It is to update you that the issue “CELLSNET-42507” is fixed now. Hopefully, we will provide the fix here before the end of this week or so after some extensive testing and adding other enhancements.

Keep in touch.

Thank you.
16

Hi,


That time-frame is workable for us.

Thank you,
Bas.
17

Hi,

Please download and try our latest fix/version: Aspose.Cells for .NET 8.0.0.1

We have fixed the issue now.

Let us know your feedback.

Thank you.

18
Kind gentleman,

We have updated our version and we'll be running some tests after the weekend.
We will let you know our results.

Thank you very much.
Bas.
19

Hi,


Sure, you may evaluate the fix and conduct more tests. Hopefully, the fix would work fine for your scenarios. If you find any issue, let us know here with all the details, sample code and template files, we will look into it further.

Thank you.
20
Kind gentlemen,

I am happy to report that the issue seems to be resolved for our scenarios.
A small office party has been thrown in your honor!

Kind regards,
Bas.