Formula containing #REF! and displaying the same in Excel still has value in Aspose.Cells

Aspose.Cells Product Family
1

Hello,

I'm using Aspose.Cells 5.1.1.0.

I have an excel file with a reference problem in one cell. It contains #REF! inside the formula and is evaluated by Excel as #REF! (see attached file excel_error_displayed.png)

I thought the IsErrorValue of Aspose.Cells will help me to detect such kind of problems before reading the celle value.

But the IsErrorValue is false and the StringValue of the cell is "6". (see attached file error_eval_formula.png)

Can you explain if it is normal ?

Regards,

Dimitri

2

Hi Dimitri,

We are looking into this issue. Please send us the Excel template file which contains the error. This will help us in order to find out the root cause of the issue.

Thanks,

3

Hello,

Thanks for you fast reply

the file contains some sensible data, here is the file without other worksheets. I have not tested if it produces the same bug.

Regards

Dimitri

4

Hi,

I have tried your scenario with the latest version v5.1.3.3 using your template file with the following code and it works fine with it. Please try the attached version.

Sample code:
Workbook workbook = new Workbook(“e:\test\refe\test_file.xls”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

MessageBox.Show("String: " + cells[“A5”].StringValue); //#REF!
MessageBox.Show("String: " + cells[“A6”].StringValue); //#REF!
MessageBox.Show("IsErrorValue: " + cells[“A6”].IsErrorValue.ToString()); //True
MessageBox.Show("String: " + cells[“A7”].StringValue); //#REF!


If you still find any issue using the latest attached version, please do post your template (or original file - you may fill dummy data if you want) file with sample code here, so, that we can reproduce the issue on our and could fix it (if we found the issue) soon.

Thank you.

5

Thanks for your reply

I just tested with the full file with the version 5.1.3.3, i still have the problem

I'm attaching the file

6

Hi,

Well, it works fine with v5.1.3.3. I use the following code using your provided file, it gives #REF! for A6 cell in the second sheet. Also, Cell.IsErrorValue is true which it should be:

Sample code:
Workbook workbook = new Workbook(“e:\test\refe\new\test_fails.xls”);
Worksheet worksheet = workbook.Worksheets[1];
Cells cells = worksheet.Cells;
// workbook.CalculateFormula(); //you may also call this line if you want, the result would be same too.

MessageBox.Show("String: " + cells[“A6”].StringValue); //#REF! ----------OK
MessageBox.Show("IsErrorValue: " + cells[“A6”].IsErrorValue.ToString()); //True ------------OK




Kindly give me the sample code to reproduce the issue you have mentioned. Preferably, kindly do create a sample console application, zip it and post it here, we will check it soon.

Thank you.

7

well, that's a pity that you can not reproduce it

i'll try to extract the code from our solution to reproduce it

8

Here's the code that reproduces the bug:

Workbook workbook = new Workbook("test_fails.xls");
workbook.CalculateFormula();
workbook.Worksheets["Parameters"].Cells["D18"].PutValue(123);
workbook.CalculateFormula();

Cells cells = workbook.Worksheets["Verifications"].Cells;
Console.WriteLine("IsErrorValue: {0}", cells["A6"].IsErrorValue);
Console.WriteLine("StringValue: {0}", cells["A6"].StringValue);
Console.WriteLine("Formula: {0}", cells["A6"].Formula);

It outputs:

IsErrorValue: False
StringValue: 6
Formula: =IF(AND(Parameters!D18<1,Parameters!D11<>"Sacherie"&#REF!),"Bloquant","")

Aspose.Cells.dll version 5.1.3.3


9

Hi,

This time, using your code segment, I can find the issue you have talked about. If you could comment out the first call to “workbook.CalculateFormula();” line it works fine though.

Anyways, this is an issue and I have logged your issue into our issue tracking system with an id: CELLSNET-20549. We will figure it out soon.

Thank you.

10

thank you for fast reply

we can not comment the "workbook.CalculateFormula()" because we need the values to be recalculated after excel contents change

Please keep me informed about the update, we need it urgently

11

Hi,

Please try the attached version v5.1.3.4. We have fixed the issue now.

Thank you.

12

it works, thank you

13

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

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