In version 24.11 this was fine, but in 24.12 if I add a Label Filter to a Pivot using the “Does not contain” option, then add “(blank)” as the text to filter on, and refresh the pivot, and save, the saved xlsx file does not have the filter applied, even though I can see it is there.
If I go into the filter and click Ok manually it filters fine.
The screenshots show it is not applied, I go in and reapply it and it works in Excel, just not coming out of Aspose.
image.png (9.6 KB)
image.png (69.1 KB)
image.png (19.4 KB)
image.png (6.6 KB)
We have this on several different xlsx templates so it is a general issue. Let me know if you need me to come up with some code to reproduce it.
Thanks for the screenshots.
We appreciate if you could provide your sample (executable) code or, ideally, a complete console application (full source code without any compilation errors) along with sample files (such as an Excel file or other types) to help us replicate the issue on our side. We will evaluate and investigate your issue soon.
@catnoise
We can obtain the correct results by testing on the latest version v24.12 using the following sample code. Please refer to the attachment. CaptionNotContains.zip (9.8 KB)
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
cells[0, 0].Value = "fruit";
cells[1, 0].Value = "grape";
cells[2, 0].Value = "blueberry";
cells[3, 0].Value = "kiwi";
cells[4, 0].Value = "cherry";
cells[5, 0].Value = "grape";
cells[6, 0].Value = "blueberry";
cells[7, 0].Value = "kiwi";
cells[8, 0].Value = "cherry";
cells[0, 1].Value = "year";
cells[1, 1].Value = 2020;
cells[2, 1].Value = 2020;
cells[3, 1].Value = 2020;
cells[4, 1].Value = null;
cells[5, 1].Value = 2021;
cells[6, 1].Value = 2021;
cells[7, 1].Value = 2021;
cells[8, 1].Value = 2021;
cells[0, 2].Value = "amount";
cells[1, 2].Value = 50;
cells[2, 2].Value = 60;
cells[3, 2].Value = 70;
cells[4, 2].Value = 80;
cells[5, 2].Value = 90;
cells[6, 2].Value = 100;
cells[7, 2].Value = 110;
cells[8, 2].Value = 120;
PivotTableCollection pivots = sheet.PivotTables;
int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
PivotTable pivot = pivots[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
pivot.AddFieldToArea(PivotFieldType.Column, "year");
pivot.AddFieldToArea(PivotFieldType.Data, "amount");
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;
//Add PivotFilter
PivotField field = pivot.ColumnFields[0];
field.FilterByLabel(PivotFilterType.CaptionNotContains, "(blank)", "");
pivot.RefreshData();
pivot.CalculateData();
book.Save(filePath + "CaptionNotContains.xlsx");
If you still have questions, please provide us with your sample files and runnable test code, and we will check it soon.
Thank you for your speedy response. If you change 1 line you will be able to reproduce the problem. Just change one of those 2021 scores to “(blank)” and you will see the problem:
cells[3, 1].Value = 2020;
cells[4, 1].Value = null;
cells[5, 1].Value = 2021;
cells[6, 1].Value = “(blank)”;
cells[7, 1].Value = 2021;
image.png (19.9 KB)
So this is more about (blank) being a special keyword in Excel to denote there is no score, but deep in our system and across hundreds of templates we are sending back the string “(blank)” to take advantage of that and hide both blank cells and cells that are populated with the string “(blank)”.
I hope you are able to help us here as you made another fix for us in 24.12 but we have had to go back to 24.11 due to this new bug.
Many thanks for your continued support over the years.
I have evaluated your scenario/case after changing the values in the code snippet as per your suggestions in your reply and found the issue as you mentioned. To workaround it or cope with the issue, kindly do change the lines of code:
i.e.,
//Add PivotFilter
PivotField field = pivot.ColumnFields[0];
field.FilterByLabel(PivotFilterType.CaptionNotContains, "(blank)", "");
to:
//Add PivotFilter
PivotField field = pivot.ColumnFields[0];
field.FilterByLabel(PivotFilterType.CaptionNotContains, "blank", "");
and it will fix the issue.
Let us know if you still have any issue.
That was not my code, that was your team wrote that code.
My problem is that we have hundreds of xlsx template files where our clients have entered (blank) as the filter already, and it works ok for them in Excel, but as soon as they pass the template to us it breaks. We really need Aspose to have the same functionality as Excel here if it is possible for you please?
@catnoise
We can reproduce the issue by testing on the latest version v24.12 using the following sample code. Found that PivotFilter is not being applied when adding filter to pivot table to filter “(blank)” value. In pivot tables, null values are treated as “(blank)” values. This leads to errors in filtering null values and “(blank)” values in the original data. Please review the screenshot and result file. result.zip (44.5 KB)
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
cells[0, 0].Value = "fruit";
cells[1, 0].Value = "grape";
cells[2, 0].Value = "blueberry";
cells[3, 0].Value = "kiwi";
cells[4, 0].Value = "cherry";
cells[5, 0].Value = "grape";
cells[6, 0].Value = "blueberry";
cells[7, 0].Value = "kiwi";
cells[8, 0].Value = "cherry";
cells[0, 1].Value = "year";
cells[1, 1].Value = 2020;
cells[2, 1].Value = 2020;
cells[3, 1].Value = 2020;
cells[4, 1].Value = null;
cells[5, 1].Value = 2021;
cells[6, 1].Value = "(blank)";
cells[7, 1].Value = 2021;
cells[8, 1].Value = 2021;
cells[0, 2].Value = "amount";
cells[1, 2].Value = 50;
cells[2, 2].Value = 60;
cells[3, 2].Value = 70;
cells[4, 2].Value = 80;
cells[5, 2].Value = 90;
cells[6, 2].Value = 100;
cells[7, 2].Value = 110;
cells[8, 2].Value = 120;
PivotTableCollection pivots = sheet.PivotTables;
int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
PivotTable pivot = pivots[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
pivot.AddFieldToArea(PivotFieldType.Column, "year");
pivot.AddFieldToArea(PivotFieldType.Data, "amount");
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;
//Add PivotFilter
PivotField field = pivot.ColumnFields[0];
field.FilterByLabel(PivotFilterType.CaptionNotContains, "(blank)", "");
pivot.RefreshData();
pivot.CalculateData();
book.Save(filePath + "CaptionNotContains.xlsx");
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-57472
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.
This is to inform you that your issue (Ticket ID: “CELLSNET-57472”) has been resolved. The enhancement/fix will be included in the next release (Aspose.Cells v25.1) scheduled for release in the first half of January 2025. We will notify you when the new version is available.