Hi,
I’m trying to use the dynamic arrays feature of Aspose.Cells for .NET, but reading the value of the spilled cells returning null values. Also, formulas using the spilled area just uses the first value of the array.
See the Excel files below that I populate with the following code snippet:
var workbook = new Workbook("./Book1.xlsx");
var worksheet = workbook.Worksheets.First(ws => ws.Name == "Sheet1");
var random = new Random((int)DateTimeOffset.UtcNow.Ticks);
var expectedValue = 0;
for (int i = 0; i < 1000; i++)
{
var value = random.Next(1, 1500);
worksheet.Cells[$"A{i + 1}"].Value = (char)('A' + (i % 4));
worksheet.Cells[$"B{i + 1}"].Value = value;
if (i % 4 == 0)
{
expectedValue += value;
}
}
worksheet.Cells["A1001"].Value = "End";
var ws2 = workbook.Worksheets.First(ws => ws.Name == "Sheet2");
workbook.RefreshDynamicArrayFormulas(false);
workbook.CalculateFormula();
Console.WriteLine($"Expected: {expectedValue}");
Console.WriteLine($"Actual: {ws2.Cells["D1"].Value}");
Console.WriteLine($"{ws2.Cells["C2"].Value}");
workbook.Save("output.xlsx");
Sheet2 checks the content of Sheet1, and creates a dynamic reference to an array based on where the End value is in column A, and collects those numbers that are next to “A”, and sums them.
When opening the output, I see the same result that I get from the program as well:
image.png (2.0 KB)
However, when I put the refresh the formula in cell C1 to the same value as before, I get the following:
image.png (8.8 KB)
Output.xlsx is the original output of the above code snippet, and output_fixed.xlsx is the one that should be the expected result.
sample_excels.zip (43.0 KB)
Can you please advise?
Regards,
Marton