Pivot Table Sort Exception

Aspose.Cells Product Family
1

Hi

I am trying to sort a colum in a pivot table as follows:

int nDataField = pvtTable.AddFieldToArea(PivotFieldType.Data, "SG ID");
pvtTable.DataFields[nDataField].Number = 3; // #,##0
pvtTable.DataFields[nDataField].IsAutoSort = true;
pvtTable.DataFields[nDataField].IsAscendSort = true;

However I get an exception "Object reference not set to an instance of an object" when the 'IsAscendSort' line is executed.

Am I doing something wrong?

Thnaks

2

Hi,


Well, I think you should sort data in the Pivot table based on Column fields, so you may correct your sample code for sorting.

If you still find any issue, kindly provide us a sample console application, zip it and post us here to show the issue on our end, we will check it soon. Also provide your template files (e.g input/ source file, output file via Aspose.Cells APIs and your expected file with your desired pivot table with sorted data that you may manually create in Ms Excel) for our review and investigations.

And, please try our latest version/fix: Aspose.Cells for .NET v8.3.1.1


Thank you.


3

Hi

I have tried with the latest version but I still have the same issue. I'm not sure how to make it work with ColumnFields as I am not adding any ColumnFields. My code is below:

Workbook wb = new Workbook(@"F:\Debug\SortTest.xlsx");
Worksheet wsData = wb.Worksheets[0];
Worksheet wsPivot = wb.Worksheets.Add("pvt");

PivotTableCollection pivotTables = wsPivot.PivotTables;
int index = pivotTables.Add("='Values'!A1:B3000", "A3", "test_PivotTable");
PivotTable pvtTable = pivotTables[index];
pvtTable.IsAutoFormat = true;
pvtTable.AutoFormatType = PivotTableAutoFormatType.Report4;

// add Hubsite row field
pvtTable.AddFieldToArea(PivotFieldType.Row, "Hubsite");

// add data field
int nDataField = pvtTable.AddFieldToArea(PivotFieldType.Data, "Count");
pvtTable.DataFields[nDataField].Number = 3; // #,##0
pvtTable.DataFields[nDataField].IsAutoSort = true;
pvtTable.DataFields[nDataField].IsAscendSort = true;

// save & close file
wb.Save(@"F:\Debug\SortTestOutput.xlsx", SaveFormat.Xlsx);

The code throws an exception when the "IsAscendSort" line is executed. I have attached two files, one is the input data and the other contains the pivot if the "IsAscendSort" line is removed.

How do I sort the Count column in the pivot table?

Another question - in the pivot table how do I change the column A header from "Row Labels" to the name of the data in this case "Hubsite"?

Thanks

4

Hi,


Thanks for providing us template files and sample code.

We will evaluate your issue and get back to you soon.

Also, we appreciate if you could also provide us the sample file with your PivotTable having your desired sorted data and formatting, it may help a bit.

Thank you.
5

Hi

As requested I have attached a file with the pivot manually sorted as required. I performed a sort of the "Hubsite column Ascending (A to Z) by: Sum of Count".

Thanks

6

Hi,

Thanks for your expected Excel file and using Aspose.Cells.

Please see the following sample code, it achieves your desired results and does not cause any exception. I have also attached the output Excel file generated by it for your reference.

C#


String filePath = @“F:\Shak-Data-RW\Downloads\SortTest.xlsx”;


Workbook wb = new Workbook(filePath);

Worksheet wsData = wb.Worksheets[0];

Worksheet wsPivot = wb.Worksheets.Add(“pvt”);


PivotTableCollection pivotTables = wsPivot.PivotTables;

int index = pivotTables.Add("=‘Values’!A1:B3000", “A3”, “test_PivotTable”);

PivotTable pvtTable = pivotTables[index];

pvtTable.IsAutoFormat = true;

pvtTable.AutoFormatType = PivotTableAutoFormatType.Report4;


// add Hubsite row field

pvtTable.AddFieldToArea(PivotFieldType.Row, “Hubsite”);


// add data field

int nDataField = pvtTable.AddFieldToArea(PivotFieldType.Data, “Count”);

pvtTable.DataFields[nDataField].Number = 3; // #,##0

// pvtTable.DataFields[nDataField].IsAutoSort = true;

// pvtTable.DataFields[nDataField].IsAscendSort = true;


pvtTable.RowFields[0].IsAutoSort = true;

pvtTable.RowFields[0].IsAscendSort = true;


// save & close file

wb.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);


7

Hi,


Thanks for the template file.

We have evaluated your scenario/ case further. It looks the feature “Sort data in Data field with respect to / by Row pivot field in the PivotTable” is not supported at the moment as I tried the following sample code (I also tried with other options and combinations of the APIs) but it does not work as expected. It works fine if we sort the pivot field (Row field) itself.
e.g
Sample code:

Workbook wb = new Workbook(@“e:\test2\SortTest.xlsx”);
Worksheet wsData = wb.Worksheets[0];
Worksheet wsPivot = wb.Worksheets.Add(“pvt”);

PivotTableCollection pivotTables = wsPivot.PivotTables;
int index = pivotTables.Add("=‘Values’!A1:B3000", “A3”, “test_PivotTable”);
PivotTable pvtTable = pivotTables[index];
pvtTable.IsAutoFormat = true;
pvtTable.AutoFormatType = PivotTableAutoFormatType.Report4;

// add Hubsite row field
pvtTable.AddFieldToArea(PivotFieldType.Row, 0);

// add data field
int nDataField = pvtTable.AddFieldToArea(PivotFieldType.Data, “Count”);
pvtTable.DataFields[nDataField].Number = 3; // #,##0


pvtTable.RowFields[0].IsAutoSort = true;
pvtTable.RowFields[0].IsAscendSort = true;
//Sort by grand total (Sum of Count)
pvtTable.RowFields[0].AutoSortField = 1;

//Setting autoShow options.
//Setting the field auto show.
pvtTable.DataFields[0].IsAutoShow = true;
//Setting the field auto show ascend.
pvtTable.DataFields[0].IsAscendShow = true;
//Setting the auto show using field(data field).
pvtTable.DataFields[0].AutoShowField = 0;

// save & close file
wb.Save(@“e:\test2\outSortTestOutput1.xlsx”, SaveFormat.Xlsx);

I have logged a ticket with an id “CELLSNET-43257” for your issue/ requirements. We will look into it soon.

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

And for your other needs i.e., change the header text in the Row header to “Hubsite”, well, when the Row label area only contains one PivotField, it would show “Row Label”. If you implement such a table in Ms Excel manually, you will get the same results too. You should first have the Row label area that contains two or more Pivot fields.

Thank you.
8

Hi

I have tried your sample code but it actually seems to sort by the Hubsite names not by the count value. In the attached file (created using your sample code) if you look at cell A3 the sort icon appears and when hovered over shows "Hubsite: A to Z", this should show "Hubsite: Smallest to Largest by Sum of Count"

Can you please advise how to make this work?

I am using DLL version 8.3.1.1

Thanks

9

Hi,


Well, as we already told you that the feature “Sort data in Data field with respect to / by Row pivot field in the PivotTable” is not supported at the moment, see my reply with sample code in the previous post:
https://forum.aspose.com/t/65804
It works fine if we sort the pivot field (Row field) itself. We have already logged a ticket with an id “CELLSNET-43257” for your issue.

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

Thank you.

10

Hi

Your previous response was not clear to me. I thought you were offering a solution. Is there a web page to view the progress / status of open tickets (for example CELLSNET-43257)?

Thanks

11

Hi Brendan,


The ticket details are not publicly accessible, however, you can check the status of the ticket from left hand side pane of your first post. You will notice that the ticket (CELLSNET-43257) is currently unresolved. As soon as the requested feature is available for your testing, the status will be changed to resolved, and you will be automatically notified here.

Please feel free to write back in case you have further questions.