Cell value not updating when CreateCalcChain is false

Aspose.Cells Product Family
1

Hi,

I have a test case using this Excel workbook. Test.zip (11.7 KB)

If you run the code below against the Test.xlsx file in my attachment, the value of cell F3 as printed to console is “50”.

However, if you uncomment the 2nd line //workbook.Settings.CreateCalcChain = true; and run the same code again, the output value for cell F3 is “24”.

If you can please have a look at this issue that would be great. Thanks.

var workbook = new Workbook("Test.xlsx");
//workbook.Settings.CreateCalcChain = true;
workbook.CalculateFormula();

Worksheet wksheet = workbook.Worksheets["Sheet1"];

Cell q2 = wksheet.Cells["Q2"];
q2.PutValue(3);

// Calculate formula again.
workbook.CalculateFormula();

Cell f3 = wksheet.Cells["F3"];
Console.WriteLine(f3.ToString());
2

@llawryy,

Thanks for the template file and sample code segment.

I have evaluated your issue a bit. I found you are using Workbook.CalculateFormula() call two times. The first call is not necessary. Please remove the first call and it will work whether you comment or or uncomment the line regarding calculating chain. See the updated sample code segment for your reference:
e.g
Sample code:

var workbook = new Workbook("Test.xlsx");
//workbook.Settings.CreateCalcChain = true;

Worksheet wksheet = workbook.Worksheets["Sheet1"];

Cell q2 = wksheet.Cells["Q2"];
q2.PutValue(3);

// Calculate formula again.
workbook.CalculateFormula();

Cell f3 = wksheet.Cells["F3"];
Console.WriteLine(f3.ToString());

Hope, this helps a bit.

3

Hi @Amjad_Sahi

Thanks for your help. A problem with your proposed solution is that the cell Q2 is actually a user input. So let’s say the user initially wants to input 3 into Q2, then later wants to input 4 into Q2:

var workbook = new Workbook("Test.xlsx");
//workbook.Settings.CreateCalcChain = true;

Worksheet wksheet = workbook.Worksheets["Sheet1"];

Cell q2 = wksheet.Cells["Q2"];
q2.PutValue(3);  // Value from user input.

// Calculate formula again.
workbook.CalculateFormula();

Cell f3 = wksheet.Cells["F3"];
Console.WriteLine(f3.ToString()); // Tell user the result: 24

q2.PutValue(4); // New value from user input

f3 = wksheet.Cells["F3"];
Console.WriteLine(f3.ToString()); // Tel user the result: 24

In my tests against version 20.4.0, both times the Console.WriteLine reports the cell value as 24. But if you open the Test.xlsx in Excel and input 4 into cell Q2, you’ll get 50 as the value of F3. So it looks like this is still a bug?

4

@llawryy,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47322 – Wrong value calculated by Aspose.Cells while using OFFSET function

5

@llawryy,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

6

@llawryy,

Please try our latest version/fix: Aspose.Cells for .NET v20.4.5 (attached)

Your issue should be fixed in it. See the sample code which works fine as I tested.
e.g
Sample code:

var workbook = new Workbook("e:\\test2\\Test.xlsx");

            Worksheet wksheet = workbook.Worksheets["Sheet1"];

            Cell q2 = wksheet.Cells["Q2"];
            q2.PutValue(3);  // Value from user input.

            // Calculate formula.
            workbook.CalculateFormula();

            Cell f3 = wksheet.Cells["F3"];
            Console.WriteLine(f3.ToString()); // Tell user the result: 24

            q2.PutValue(4); // New value from user input

            // Calculate formula again. Whenever you change any cell value, you need to call Workbook.CalculateFormula method.
            workbook.CalculateFormula();

            f3 = wksheet.Cells["F3"];
            Console.WriteLine(f3.ToString()); // Tel user the result: 50

Let us know your feedback.
Aspose.Cells20.4.5 For .Net2_AuthenticodeSigned.Zip (5.3 MB)
Aspose.Cells20.4.5 For .Net4.0.Zip (5.4 MB)

7

The issues you have found earlier (filed as CELLSNET-47322) have been fixed in Aspose.Cells for .NET v20.5. This message was posted using Bugs notification tool by Amjad_Sahi

8

Hi, I’ve tested v20.5 and the issue has been fixed. Thanks!

9

@llawryy,
You are welcome.