We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Incorrect calculation in named range when CreateCalcChain is false

Hi,

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

If you run the code below against the Test.xlsx file in my attachment, the output for t5.ToString() is:

Aspose.Cells.Cell [ T5; ValueType : IsNumeric; Value : 0; Formula:=COUNTIF(data_preDefined_type_list,S5) ]

However, if you uncomment the 2nd line //workbook.Settings.CreateCalcChain = true; and run the same code again, the output for t5.ToString() is:

Aspose.Cells.Cell [ T5; ValueType : IsNumeric; Value : 4; Formula:=COUNTIF(data_preDefined_type_list,S5) ]

I believe the correct value is “4”, I’m not sure why setting workbook.Settings.CreateCalcChain to false calculates the value as “0”. It seems like it has something to do with the named range having being changed.

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();

// Copy data from source to this target.
Name targetName = workbook.Worksheets.Names.First(n => n.Text == "source_preDefined");
Range targetRange = targetName.GetRange();
int firstRow = targetRange.FirstRow;
int firstCol = targetRange.FirstColumn;

// Copy from this source range Sheet1!BB20:BL32.
Worksheet wksheet = workbook.Worksheets["Sheet1"];
Range srcRange = wksheet.Cells.CreateRange("BB20", "BL32");

for (int rowOffset = 0; rowOffset < srcRange.RowCount; ++rowOffset)
{
    for (int colOffset = 0; colOffset < srcRange.ColumnCount; ++colOffset)
    {
        Cell srcCell = srcRange[rowOffset, colOffset];
        Cell targetCell = wksheet.Cells[firstRow + rowOffset, firstCol + colOffset];

        string value = srcCell.StringValue;
        if (string.IsNullOrEmpty(value))
        {
            value = null;
        }

        targetCell.PutValue(value, true, false);
    }
}

// Adjust targetName to match srcRange dimensions.
targetName.RefersTo = "=Sheet1!$P$87:$Z$99";

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

// Read the value of Sheet1!T5
Cell t5 = wksheet.Cells["T5"];
Console.WriteLine(t5.ToString());

@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-47199 – Difference in calculation for named range while setting CreateCalcChain true and false

@llawryy,
Please give a try to the following fix and share your feedback.

Aspose.Cells20.2.6 For .Net4.0.Zip (5.2 MB)
Aspose.Cells20.2.6 For .Net2_AuthenticodeSigned.Zip (5.2 MB)

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

Great thanks! I’ve tested it’s fixed now.

@llawryy,

Good to know that your issue is resolved by the latest version.