Problems with filter function

Aspose.Cells Product Family
1

Hi
We have workbook were we have a custom filter that looks this:
=FILTER(PayrollProcessResult!P:T;PayrollProcessResult!R:R>0)

Data comes from a Unique formular that is calculated correctly.
I use RefreshDynamicArrayFormulas to ensure the calculation of the unique formular and that works like a charm.

The filter function is never calculated until we open it in excel and press enter in the cell.
Is there any function or similar I can try to use ?

We are use Aspose.cells 25.6.0 for .net.

3

@nic11
Would you like to provide your sample file and runnable test code? We will check it soon.

4

@nic11

We tested code like:

...
            cell.SetDynamicArrayFormula("=FILTER(PayrollProcessResult!P:T;PayrollProcessResult!R:R>0)", new FormulaParseOptions(), true);
            wb.Save("result.xlsx");

and found the generated file can work fine in ms excel, no matter the saved format is xls, xlsx or xlsb.
So we are afraid we need the executable code and template file in your case to reproduce the issue so we can investigate it.

5

error.png (42.4 KB)

Exceltest.zip (743.6 KB)

Setting the formular as you do gives me an error, as shown in the image.
I have tried updating aspose.cell to the newest version to see if that changes anything.
I also tried making a brand new document and copied data to is with the same result.
If I paste the =FILTER(PayrollProcessResult!P:T;PayrollProcessResult!R:R>0) to a new cell in the output_detaljer sheet it works as expected.
The formula in I7 is the original one that is not calculated.
Not sure if the error is in the workbook or I am lacking something.
Hope this is enough help.

6

@nic11,

Thanks for the template Excel file and sample code.

I am able to reproduce the issue as you mentioned by using your template Excel file and sample app. I found by setting Filter formula produces an exception: “Aspose.Cells.CellsException: ‘Invalid ‘;’ which has been taken as separator of array’”.

We require thorough evaluation of the issue. 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-59319

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.

7

@nic11
After further research, we found that your formula was set incorrectly by writing commas as semicolons. Please refer to the following example code and check the attachment. output.zip (501.0 KB)

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath + "filtertest.xlsx");

var cell = workbook.Worksheets["Output_detaljer"].Cells["s7"];
cell.Formula = "=FILTER(PayrollProcessResult!P:T,PayrollProcessResult!R:R>0)";
cell.SetDynamicArrayFormula("=FILTER(PayrollProcessResult!P:T,PayrollProcessResult!R:R>0)", new Aspose.Cells.FormulaParseOptions(), true);

workbook.Save(filePath + "output.xlsx");

Hope helps a bit.

8

@nic11

  1. For the error of setting the formula, I copied it from your original post(I think it should be copied by you from your ms excel and for you region the separator is ‘;’ instead of ‘,’) and forgot to modify the separator as ‘,’. For setting formulas, without special settings, we always use the standard(en-US) separators.

  2. For the formula of I7 that its formula is not calculated in ms excel, the reason should be that the calculation id defined in this template file is greater than the version of your ms excel. To make the file can be re-calculated when opening with ms excel, please use code like:

...
            wb.Settings.FormulaSettings.CalculateOnOpen = true;
            wb.Save("result.xlsx");
  1. Even if you have not changed the calculation settings, if you call wb.CalculateFormula() like:
...
            wb.CalculateFormula();
            wb.Save("result.xlsx");

We also found the expected result when open result.xlsx with ms excel.

  1. For refreshing dynamic array formulas, starting from 25.10 users do not need to call RefreshDynamicArrayFormulas explicitly any more before calculating the workbook. By default we will refresh all dynamic array formulas automatically when calculating the whole workbook.
9

Thanks formular change and wb.Settings.FormulaSettings.CalculateOnOpen = true; helped.

10

@nic11,

Thank you for your feedback.

We are glad to hear that the suggested formula and other settings meet your requirements. Please don’t hesitate to reach out to us if you have any additional questions or comments.