PivotTable Manual Sort and Filter

Aspose.Cells Product Family
1

Hi,


I’m currently re-evaluating Aspose Cells. There are two areas in which I still can’t find the answer.

1. Pivot Table Manual Sorting

Let say I have Pivot Table like this:

Sum of Total Sales (100K) Column Labels
Row Labels Brian Cathy John Grand Total
Indonesia 350 350
Malaysia 477 477
Singapore 500 500
Thailand 600 600
Vietnam 250 250
Grand Total 850 727 600 2177

I want to programatically manual sort the Pivot Table so it looks like this:

Sum of Total Sales (100K) Column Labels
Row Labels Brian Cathy John Grand Total
Indonesia 350 350
Singapore 500 500
Malaysia 477 477
Thailand 600 600
Vietnam 250 250
Grand Total 850 727 600 2177

I basically just manually assign the order to make it Singapore comes before Malaysia, can I do this with Aspose? Please be aware that the sorting is without any param (not alphabetically, not related to sales figures, etc), I just do it in excel via manually drag and drop.

Does Aspose Cells support this capability? If not, is there any idea to achieve what I want?

2. Pivot Table Filtering

I tried to made a Pivot Table that have a default filter. However, the generated excel will have a pivot table with a blank Filter. Whereas I need to click the filter, click ok, and then the Pivot will be generated properly.

I got this (I can't paste it properly).
United Square 50 503 268 195 1016
Rochester Mall 2 123 77 21 223
HomeTeamNS Bukit Batok 36 14 7 57
Tampines Point 61 18 9 88
Bedok Point 47 10 16 73
UE Square 43 22 7 72
Westgate 30 10 7 47
Grand Total 54 954 463 289 1760

Instead of this:

Class Request Status Withdrawal Requested
Count of Student Column Labels
Facility Chinese English Maths Science Grand Total
Mountbatten Square 2 111 44 27 184
United Square 50 503 268 195 1016
Rochester Mall 2 123 77 21 223
HomeTeamNS Bukit Batok 36 14 7 57
Tampines Point 61 18 9 88
Bedok Point 47 10 16 73
UE Square 43 22 7 72
Westgate 30 10 7 47
Grand Total 54 954 463 289 1760


Any help will be very appreciated. Thanks.


Anthony Steven





2

Hi Anthony,

Thanks for your posting and considering Aspose.Cells.

We need to check if manual sorting of the pivot table is supported or not. However, pivot table filtering should be supported. Please attach your sample excel file containing your pivot table as you have shown in your post so that we could start investigating this issue at our end. We will look into it and provide you a sample code to achieve the same functionality as Microsoft Excel using Aspose.Cells APIs. Thanks for your cooperation.

3

Thank you for your prompt response. However, I can’t attach the sample file that contains the pivot filtering issue, since it contains many sensitive data. I will try to make a mock sample if its really needed. To be more clear, when I try the PivotTable filtering functionality of Aspose Cells the resulting file contains a pivot table with a filtering field that is already pre-selected with the filter that I want (which is a good thing), however, I need to click on the filtering field to apply the filter so that is applied to my PivotTable.


Hence the one that I pasted on the thread above:

Class Request Status Withdrawal Requested



This is actually the filtering field. When I open the file generated, the “Withdrawal Requested” field is empty, I need to go to the field (in which I find the “Withdrawal Requested”, which is the filter I chose, is pre-selected) I still need to click OK to really filter the Pivot Table. This is unfortunately undesirable since my goal is to construct a Pivot Table that is already being filtered to my client’s folder. This situation made me need to open the file first, click OK, and saved.

Thank you for your kind help, I’m also waiting for your answer in regards of the manual sorting, apart from these two issues, everything else is already OK.


Regards,


Anthony Steven
4

Hi Anthony,


Thank you for writing back.

Actually we need a sample application along with the input & output spreadsheets to thoroughly investigate both matters on our side. By the way, what we understand is you are are populating the data for a pre-built Pivot Table, and filters are not selected correctly. This behavior could be caused due to some problem in the API as well as in your code. However, we are not sure because we currently do not have the code or the samples to look into. You may check if you are refreshing the Pivot Table before saving the spreadsheet. If not, please call PivotTable.CalculateData & RefreshData routines that should fix the problem related to the filtering. In case the problem persists, we definitely need a standalone sample application along with the spreadsheets to properly investigate the matter on our side.

Regarding the positioning of the Pivot Items, there are a few APIs that you may use for your requirement. Please check details as follow.

  • PivotItem.Position property can be used to specify the position index in all the Pivot Items regardless of the parent node.
  • PivotItem.PositionInSameParentNode property can be used to specify the position index for the Pivot Items under the same parent node.
  • PivotItem.Move(int count, bool isSameParent) method to move the item up or down based on the count value, where count is the number of position to move the Pivot Item up or down. If the count value is less than zero, the item will be moved up where as if the count value is larger than zero, the Pivot Item will move down, Boolean type isSameParent parameter specify whether the moving operation has to be performed in the same parent node or not.

In case you are not able to get the desired results, please provide a sample application (if are populating the data to refresh the Pivot Table) as well as the input spreadsheet and desired data representation. You may manually create the desired results using Excel application to show what you need. Please also note, we need standalone applications for both cases. That means, if you are importing the data onto the worksheet, you have to create the data source dynamically so that we do not need database connectivity while evaluating the cases.