UNIQUE Function in Aspsoe

Hi,

We are having an issue with UNIQUE function (which declared as supported by Aspose)

The issue is related to changes in the excel structure which are not well reflected in the UNIQUE function result when re-calculating the Excel.

I tried to create a very simple and lean examples to simulate it.
We have two operations, adding and removing cells from the area which is being used by the UNIQUE function

Hereby is the code and also attaching the source and output Excel files unique.zip (26.3 KB)

Please adivce
Thanks
Ori

        internal static void TestUniqueDeleteRow()
        {
            Workbook workbook = null;
            using var ms = new FileStream(@"C:\Users\OriKoren\Documents\Aspose issues\UNIQE\unique.xlsx", FileMode.Open);
            workbook = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));

            var sheet = workbook.Worksheets["Sheet1"];
            var range = sheet.Cells.CreateRange("A3:A5");
            

            var cellArea = new CellArea
            {
                StartRow = range.FirstRow ,
                StartColumn = range.FirstColumn,
                EndRow = range.FirstRow ,
                EndColumn = range.FirstColumn + range.ColumnCount - 1
            };

            sheet.Cells.DeleteRange(cellArea.StartRow,cellArea.StartColumn, cellArea.EndRow, cellArea.EndColumn, ShiftType.Up);
            var fileName = @"C:\Users\OriKoren\Documents\Aspose issues\UNIQE\unique-delete-out.xlsx";

            if (File.Exists(fileName)) { File.Delete(fileName); }
            workbook.CalculateFormula();
            workbook.Save(fileName);
        }

        internal static void TestUniqueAddRow()
        {
            Workbook workbook = null;
            using var ms = new FileStream(@"C:\Users\OriKoren\Documents\Aspose issues\UNIQE\unique.xlsx", FileMode.Open);
            workbook = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));

            var sheet = workbook.Worksheets["Sheet1"];
            var range = sheet.Cells.CreateRange("A3:A3");


            var cellArea = new CellArea
            {
                StartRow = range.FirstRow,
                StartColumn = range.FirstColumn,
                EndRow = range.FirstRow,
                EndColumn = range.FirstColumn + range.ColumnCount - 1
            };

            sheet.Cells.InsertRange(cellArea, ShiftType.Down);
            var fileName = @"C:\Users\OriKoren\Documents\Aspose issues\UNIQE\unique-add-out.xlsx";

            range = sheet.Cells.CreateRange("A3:A3");
            range.Value = 21;


            if (File.Exists(fileName)) { File.Delete(fileName); }
            workbook.CalculateFormula();
            workbook.Save(fileName);
        }

@orik
Please note that you can get the expected result by resetting the formula in the cell, for example:

...
sheet.Cells.DeleteRange(cellArea.StartRow, cellArea.StartColumn, cellArea.EndRow, cellArea.EndColumn, ShiftType.Up);

var d1 = sheet.Cells["D1"];
if (d1.IsDynamicArrayFormula)
{
    d1.SetDynamicArrayFormula(d1.Formula, new FormulaParseOptions() { Parse = true }, true);
}
...

Additionally, we have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53442

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

@orik,

For performance consideration, we cannot refresh dynamic array formulas for all operations such as inserting/deleting ranges. Api RefreshDynamicArrayFormulas is provided for such kind of situation. By calling this method user may refresh dynamic array formulas manually after those operations which may affect formulas finished.

So, for code:

            workbook.CalculateFormula();
            workbook.Save(fileName);

please change it to:

            workbook.RefreshDynamicArrayFormulas(true);
            workbook.Save(fileName);

then the generated results should be the expected ones. Thank you.

Thanks for the replay !

Does the RefreshDynamicArrayFormulas is including the CalculateFormula functionality in it?

What kind of performance issue using the RefreshDynamicArrayFormulas may lead to?

I there a way to know if the file includes dynamic formulas in advance to avoid the performance issue?

@orik

As you know, the dimension of one dynamic array formula commonly depends on the calculated result, so when spilling it to range, formula calculation is required. However, the calculation is mainly about the dynamic array formula itself, not for other formulas existing in current workbook. For our APIs of refreshing dynamic array formulas, RefreshDynamicArrayFormulas(bool) will not calculate other formulas even if the dynamic array formula references to them. For example, for the dynamic array formula “=A1:A3+1”, when refreshing it by this method those formulas of A1, A2, A3 will not be re-calculated. To make sure those formulas will be calculated recursively, please use method RefreshDynamicArrayFormulas(bool, CalculationOptions) where you may provide a CalculationOptions with Recursive property as true.

The performance issue I said is for the requirement of refreshing dynamic array formulas automatically. For example, every insert/delete operation may cause change for formulas, and it is common situation for users to perform insert/delete operation many times such as in one loop. If for every operation we need to check and refresh dynamic array formula, it is sure to cause performance issue for users’ application. So we cannot make the refresh of dynamic array formula as one automatic operation. Instead, we provide those two methods for users to call it manually when needed.

When you call those methods, we will check whether there are dynamic formulas automatically. If yes, then they will be re-calculated and refreshed. We think such kind of check and calculation, and corresponding influence of performance is necessary.

@johnson.shi thanks again for the detailed response!

I have another clarification, just to make sure I fully understand your response.

you said " for the dynamic array formula “=A1:A3+1”, when refreshing it by this method those formulas of A1, A2, A3 will not be re-calculated. To make sure those formulas will be calculated recursively, please use method RefreshDynamicArrayFormulas(bool, CalculationOptions) where you may provide a CalculationOptions with Recursive property as true"

Which is completely understood, the only open question here related to other formulas in the workbook which are not effect the dynamic range calc.
Formulas that for some reasons also needs to be calculated (i.e. another worksheet etc.) will they be calculated when we do RefreshDynamicArrayFormulas with the recursive option?

OR we need to run CalculateFormula as well?

Thanks

@orik,

Apparently, they will be taken care of and calculated as well.

We will get back to you soon to provide you with more details.

From a short test I performed, independent formulas are not calculated.

Thanks!

@orik,

You mean non-array (dynamic) formulas? If so, you may use Workbook.CalculateFormula() method.

Yes.
Both are needed to get full calculation of the file

@orik,

Your understanding is correct.