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

Free Support Forum - aspose.com

Dynamic array formula does not populate spilled cells

Aspose.Cells Product Family
,
#1

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

#2

@varsanyi.marton,

Thanks for the sample files and details.

Could you please also zip and attach “Book1.xlsx” file, it will help us to evaluate your issue precisely on our end.

#3

@Amjad_Sahi

See here: Book1.zip (7.7 KB)

Thanks,
Marton

#4

@varsanyi.marton,

Thanks for the template file.

Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using your template file. I confirmed reading the value of the spilled cells return null values. Also, formulas using the spilled area just uses the first value of the array. I have logged a ticket with an id “CELLSNET-51716” for your issue. We will investigate and look into the details of the issue. Hopefully, we could sort it out soon.

Once we have an update on it, we will let you know.

1 Like
#5

@varsanyi.marton,

We have evaluated your issue in details.
Because the dynamic array formulas in the template file depend on the calculated results of other normal cell formulas, and those normal cell formulas also depend on the newly updated cells, so before refreshing the dynamic array formulas, other formulas need to be calculated at first. Currently, please change the sample code as:

...
wb.CalculateFormula();
wb.RefreshDynamicArrayFormulas(false);
wb.CalculateFormula();
...

And, later on, we will investigate whether we can make this process simpler for users or not.

#6

@Amjad_Sahi

Hi, thank you for the workaround. This indeed works for the attached spreadsheet, but if there are any cells that are trying to consume the value of calculated cells with other formulas, it stops working.

I have changed the sample code to the following:

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";
workbook.CalculateFormula();
workbook.RefreshDynamicArrayFormulas(false);
workbook.CalculateFormula();

Console.WriteLine($"Expected: {expectedValue}");
Console.WriteLine("Reading data from the Sheet2 sheet");
var ws2 = workbook.Worksheets.First(ws => ws.Name == "Sheet2");
Console.WriteLine($"Actual: {ws2.Cells["D1"].Value}");
Console.WriteLine($"C2: {ws2.Cells["C2"].Value}");

Console.WriteLine("Reading data from the output sheet");
var outputSheet = workbook.Worksheets.FirstOrDefault(ws => ws.Name == "Output");
if (outputSheet != null)
{
	var sum = outputSheet.Cells["C1"].IntValue;
	var count = outputSheet.Cells["B2"].IntValue;
	List<int?> output = new List<int?>(count);
	for (int i = 0; i < count; i++)
	{
		var cell = outputSheet.Cells[1, (int)('C' - 'A' + i)];
		output.Add(cell.IsNumericValue ? cell.IntValue : null);
	}

	Console.WriteLine($"Actual: {sum}");
	Console.WriteLine($"List: {string.Join(", ", output)}");
}

workbook.Save("output.xlsx");

If I remove the output tab from the Book1.xlsx, then it starts working again. Here are the spreadsheets compressed into a zip file: sample_excels.zip (54.1 KB)

I can reproduce this issue with 22.7.0 and 22.8.0 version of Aspose.Cells.

I’m looking forward to see the permanent fix of this issue.

Regards,
Marton

#7

@varsanyi.marton,

Thanks for the new test case with new file.

You are right it stopped working again. It does not work (as I tested) when there are some cells that are trying to consume the value of calculated cells with other formulas. I have logged your test case with template file to your existing ticket “CELLSNET-51716”. We will look into it soon.

Once we have an update on it, we will let you know.