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