Excel SEQUENCE function does not work properly

[jurjendegroot]

Hi,
I have an excel file where I want to use a variable to generate a sequence of a certain length, cocatenate the full sequence in a string and give the output back to the user.

Unfortunately the sequence function does not work properly with Aspose.cells as soon as a variable is used to indicate its length.
To illustrate,
=SEQUENCE(5) work fine.
but if you try =SEQUENCE(B2) (where B2 is the cell with a user specified value), the result is only the first entry of the sequence, regardless of the value for length.

I attached a sample excel file to demonstrate the problem.

Can you have a look at this issue?

Jurjen
sequencetest.zip (10.8 KB)

[ahsaniqbalsidiqui]

@jurjendegroot,
We have observed this issue and logged it in our database for further analysis. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48647 - Excel SEQUENCE function does not work properly

[amjad.sahi]

@jurjendegroot,

We evaluated your issue further. The formula of calculation!C2 is dynamic array formula. When you change the value of the formula’s parameter and the change may cause the formula’s calculated results (matrix) changed too, then the formula will be re-spilled and adjacent cells will be updated accordingly. In MS Excel, such operation is automatic when you modify corresponding values.

However, for performance considerations, we cannot perform such kind of operation automatically for our component. Instead we provide API for such kind of situation so you can trigger the update when needed (for example, after update all required values you may refresh those dynamic array formulas at once). For this case, please change the code to:

Workbook workbook = new Workbook(@"sequencetest.xlsx");
workbook.Worksheets["input"].Cells["D3"].Value = 5; 
workbook.RefreshDynamicArrayFormulas(false); 
workbook.CalculateFormula(); 
workbook.Save(@"sequencetestOut.xlsx");

Let us know your results.