Calculated Cell is #VALUE! after CalculateFormula()

Aspose.Cells Product Family
1

Hello,

I'm trying to find a better solution for handling excel-files. I'm now using Office Windows Components for this. It works, but it's very slow.I did some testing with Aspose.Cells and after loading an excel-file and calling the CalculateFormula-method I got some #VALUE! -values in some cells. When I look at the file it looks fine.

I attached the excel-file. And below is what I did.

FileStream stream = File.Open("tiox_ws_rekenmodel_0.xls", FileMode.Open);
Workbook workbook = new Workbook(stream);
workbook.CalculateFormula();
Worksheet uitvoerWorksheet = workbook.Worksheets["uitvoer"];
string value = uitvoerWorksheet.Cells["B2"].StringValue;

Kind regards,

Jan Kok

2

Hi,

I have tested it with latest Aspose.Cells
for .NET v5.3.2.6. I was able to reproduce this issue.

This issue has been logged as CELLSNET-26711.

3

Hello Shakeel Faiz,

Thank you for the fast response! Did you already find the problem?

Kind regards,

Jan Kok

4

Hi,

Yes, I did find the problem and reported it. But I am afraid, it has not been fixed yet. Once, it will be fixed, we will post here to let you know.

5

Hi,

We have fixed this issue. Please download Aspose.Cells
for .NET v5.3.2.7.

6

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

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

Hello Shakeel Faiz,

I've been running to the same kind of problem again. This time with another excel-file (see attachment). I used the latest version of Aspose.Cells for this test.

I have tested it with the following code:

FileStream stream = File.Open(@"E:\tiox_ws_rekenmodel_4.xls", FileMode.Open);
Workbook workbook = new Workbook(stream);
workbook.CalculateFormula();
Worksheet uitvoerWorksheet = workbook.Worksheets["uitvoer"];
string value2 = uitvoerWorksheet.Cells["B2"].StringValue;
string value5 = uitvoerWorksheet.Cells["B5"].StringValue;
string value10 = uitvoerWorksheet.Cells["B10"].StringValue;

In the three string-variables I get '#VALUE'.

Kind regards,

Jan Kok

8

Hi,

Thanks for reporting the issue.

I can replicate it with the latest version Aspose.Cells
for .NET v5.3.3.4. I have reopened the ticket.

9

I’m also seeing the same issue with similar setup.

I’ve noticed that if I saved the workbook to stream and does a calculateFormula, then the workbook wouldn’t calculate correctly.

10

Hi,


Although we have already logged this issue and we will let you know here when its is fixed. But if you provide your template file. We can look into too.
Thank you for reporting.
11

Here is the template file for testing.

here is what I did to reproduce the issue:

Workbook wb = new Workbook(fileNameHere);
wb.CalculateFormula(false);

DataTable dt = wb.Worksheets.GetRangeByName(“Rank”).ExportToDataTable();
//shows correct values

var fs = wb.SaveToStream();
wb.CalculateFormula(false);

dt = wb.Worksheets.GetRangeByName(“Rank”).ExportToDataTable();
//all the items in the datatable now returns the value “2” which is not correct.

//If i re-instantiate the workbook from the stream then i can get the correct workbook but can’t
//run calculateFormula()
wb = new Workbook(fs);
wb.CalculateFormula(false);
dt = wb.Worksheets.GetRangeByName(“Rank”).ExportToDataTable();
//same thing, returns all 2’s.


12

Hi,


Thanks for your source code and sample file.
I have attached your comments and excel file with the Ticket associated with this thread. Hopefully soon we will figure this out.
13

Hi,

We have fixed this issue. Please download Aspose.Cells for .NET v5.3.3.5.

By the way, the Workbook.SaveToStream method will save workbook as Excel97-2003 xls file, so all data will be converted.

If you don’t want to change the file type, please try the following codes:

C#


MemoryStream ms = new MemoryStream();

wb.Save(ms, SaveFormat.Xlsx);

ms.Seek(0, SeekOrigin.Begin);

14

Hello Shakeel,

I found the problem. It has to do with the following calculation:

=ROW(INDIRECT(TypeEenheid))

The ROW-function returns a different value in Aspose.Cells. It looks like Aspose has a zero-based ROW-function.

Kind regards,

Jan Kok

15

Hello Shakeel,

Thanks for the fast fix!

I see you've changed the ROW-function.

Kind regards,

Jan