Filtering by a formula column in a sorted table is not updating

Aspose.Cells Product Family
1

Hi,

We have come across an issue where our table is not updated properly when filtering by a formula column within a sorted table. If we tried filtering the table on a non-formula column within a sorted table, it works fine, however when we filter based on a formula column we end up with a table with all it’s rows hidden. As a result we have to reapply the filtering manually. This issue become more of a problem when we convert the workbook into a PDF file, because we wouldn’t have a way to reapply the filter when it’s converted.

I have attached a small project to demonstrate the issue. I have also attached an input file containing a table with a filtered formula column and a sorted column.

Running the project will output two files; an excel file called “generatedFile.xlsx” and a PDF file called “generatedFile.pdf”.
If you look at the generated Excel file you can see that the filter is still visible (the funnel is still present on the sort button), but no filtering is really applied.
In the PDF file, the table looks empty.

I the issue here is just the way I have used Aspose.Cells please let me know where I went wrong.

Thank you

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have looked into your issue and ran your project. It generates xlsx file and not the pdf file. Please check your project why it is not generating the pdf file.

Please also provide screenshots to elaborate your issue more precisely. It will help us sort out your issue easily.

Please also download and try the latest version: Aspose.Cells
for .NET v7.5.1.2.

3

Hi,

Sorry I have sent the wrong version of the mini project earlier. I have attached the up to date version.
I have also attached two screenshots to show the difference between table filtered with excel and when the filter is refreshed using Aspose.Cells’ ListObject.AutoFilter.Refresh.

4

Hi,


Thanks for the updated project.

After an initial test, I observed the issue as you mentioned by using your newly attached sample project with your template file. The generated Excel file still have the filters that are visible but without the filtered data where as in the PDF file, the table is empty too. I have logged a ticket with an id “CELLSNET-41863” for your issue. We will look into your issue soon.

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

Thank you.


5

Hi,

Thanks for using Aspose.Cells.

The value of the formula will be removed after sorting because the
formulas are changed too.

So please call Workbook.CalculateFormula() to
calculate formulas.

Please change your code as the following :

C#


foreach (Worksheet sheet in wb.Worksheets)

{

foreach (ListObject lo in sheet.ListObjects)

{


lo.AutoFilter.Sorter.Sort();

}

}

wb.CalculateFormula();

foreach (Worksheet sheet in wb.Worksheets)

{

foreach (ListObject lo in sheet.ListObjects)

{

lo.AutoFilter.Refresh();

}

}

6

Hi,

Thank you very much for the clarification. I have now set our code to recalculate the formulas after sorting.
Could I ask for more clarification though? does the sport affect the formulas on other tables or just the table being sorted? If it doesn’t affect other, would calling Worksheet.CalculateFormula() be enough?

i.e.

foreach (Aspose.Cells.Tables.ListObject lo in sheet.ListObjects)
{
lo.AutoFilter.Sorter.Sort();
sheet.CalculateFormula(true, false, null);
lo.AutoFilter.Refresh();
}
7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have logged your comment in our database. We will look into it and advise you. Once, there is some update for you, we will let you know asap.

8

Hi,

Thanks for using Aspose.Cells.

If the formulas of other tables refers to this table, the values should
be changed too.

So if the table is in other worksheet,
Worksheet.CalculateFormula() is not enough, workbook.CalculateFormula() is better.


C#


foreach (Aspose.Cells.Tables.ListObject lo in sheet.ListObjects)

{

lo.AutoFilter.Sorter.Sort();

workbook.CalculateFormula(); //if the data of the next table bases on this table, we have to Calculate Formula after sorting.

lo.AutoFilter.Refresh();

}

9

Hi.

Thank you, for the advice. However in the case where we’re going through all the worksheets anyway i.e. below, wouldn’t the results be the same?

foreach (Worksheet sheet in Workbook.Worksheets)
{
foreach (Aspose.Cells.Tables.ListObject lo in sheet.ListObjects)
{
lo.AutoFilter.Sorter.Sort();
sheet.CalculateFormula(true, false, null);
lo.AutoFilter.Refresh();
}
}
I’m just worried about the processing overhead when the whole workbook is recalculated every single time a table is sorted when the workbook has a large number of records, tables, and worksheets.
10

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have logged your comments in our database for consideration. We will look into it and advise you. Once, there is some update for you, we will let you know asap.

11

Hi,

Thanks for using Aspose.Cells.

There is an temp solution if you just call Worksheet.CalculateFormula, please see following code:

C#


foreach (Worksheet sheet in Workbook.Worksheets)

{

sheet.CalculateFormula(true, false, null);// if tables on this worksheet refers to other worksheet, it will calculated before sorting, then table could be sorted by correct values.

foreach (Aspose.Cells.Tables.ListObject lo in sheet.ListObjects)

{

lo.AutoFilter.Sorter.Sort();

sheet.CalculateFormula(true, false, null);

lo.AutoFilter.Refresh();

}


}