Hi,
I’m trying to create pivottable with ‘show report filter pages’ option using aspose cells for .net. I have no problems with creating pivottable or adding report filters but I can’t find any reference how to enable ‘show report filter pages’ option on created pivottable. Is it possible?
I’m attaching screen of location where I can find this option in Excel.
image.png (28.8 KB)
Thanks in advice.
Thanks for your query.
Please send us two sample files with and without “Show Report Filter Pages” which are created using Excel for our reference. We will analyze these files and provide our feedback to create similar files using Aspose.Cells.
Sure thing.
The examples I’m providing may not have much sense because I’ve filled them with mock data but certainly they are showing what functionality I’m intrested in.
exapmles_show_report_filter_pages.zip (26.8 KB)
I have analyzed both the files but am afraid to share that I could not mark difference between the two files. When I open both the files in Excel and follow the steps mentioned in the image, I get same report filter page. Please explain in detail what is the difference between the two files and what is your expected behavior of the file which you want to create using Aspose.Cells.
Provided workbooks should differ in a number of worksheets.
What this options do is split every possible value of report filter to be in separate worksheet.
So for example if I had filter on ‘Position’ and in my data I would have three distinct positions (A,B,C) then as a result I would get 3 addinational worksheets (named A,B,C) which are the same pivot table but with preselected (A, B or C respectively).
For some further reading you could go here: https://www.excel-exercise.com/generate-multi-worksheets/
We have logged the issue in our database for further investigations. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46429 - PivotTable with Show Report Filter Pages optionHello,
Any update on this topic?
We are working over your issue and hopefully we will support this feature before December 31, 2018.
Keep in touch.
Please try our latest version/fix: Aspose.Cells for .NET v18.12.1:
Aspose.Cells18.12.1 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells18.12.1 For .Net4.0.Zip (4.7 MB)
Aspose.Cells18.12.1 For .NetStandard20.Zip (3.8 MB)
Your issue should be fixed in it.
Moreover, there are some tips for you:
1.Adds PivotTable.ShowReportFilterPage(PivotField pageField), PivotTable.ShowReportFilterPageByIndex(int posIndex) and PivotTable.ShowReportFilterPageByName(string fieldName) methods.
2.The attachments are source file and result file. The sample code in .NET as follows:
Workbook wb = new Workbook(filePath + "a.xlsx";);
PivotTable pt = wb.Worksheets[1].PivotTables[0];
pt.ShowReportFilterPage(pt.PageFields[0]);
pt.ShowReportFilterPageByIndex(pt.PageFields[0].Position);
pt.ShowReportFilterPageByName(pt.PageFields[0].Name);
wb.Save(filePath + "out.xlsx");
Files.zip (27.0 KB)
Let us know your feedback.
Good to know that your issue is sorted out by the new fix/version with newer API. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
Well, we publish official releases only on Nuget/maven repos., we do not publish fixes over there. We publish official release of the product once in a month. So, either you may use the fix for now (it will behave like and official release and fully tested for production server) or wait for our next official release of the product (Aspose.Cells for .NET v19.1 - it will include all previous fixes and other enhancements). Our next official release is due in the third or fourth week of January 2019.
The issues you have found earlier (filed as CELLSNET-46429) have been fixed in Aspose.Cells for .NET v19.1. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi