0

Please find the below image demonstrating what exactly I want to do.

Pivot Table Use-Case Demo

I have tried to do so in the below code, but it does not produce correct/readable Excel file. Alternatively, if I use the commented code, it produces the correct Excel file but does not sort correctly as expected.

Can you please help me achieve the objective mentioned in the above image.

  String sortByColName = "OrderDate";
  String sortByRowName = "CategoryName";

  PivotFieldCollection colFields = pivotTable.getColumnFields();
  PivotFieldCollection rowFields = pivotTable.getRowFields();

  PivotField sortByColField = colFields.get(sortByColName);

  sortByColField.setAutoSort(true);
  sortByColField.setAscendSort(true);
  sortByColField.setAutoSortField(2); /* sortByColField.setAutoSortField(-1); */
  sortByColField.setAutoShow(true);
  sortByColField.setAscendShow(true);
  sortByColField.setAutoShowField(0);

  PivotField sortByRowField = rowFields.get(sortByRowName);
  sortByRowField.setAutoSort(true);
  sortByRowField.setAscendSort(true);
  sortByRowField.setAutoSortField(2); /* sortByRowField.setAutoSortField(-1); */
  sortByRowField.setAutoShow(true);
  sortByRowField.setAscendShow(true);
  sortByRowField.setAutoShowField(0)
Hitesh Dholaria
  • 157
  • 3
  • 13

1 Answers1

2

Since your requirement is to customize sorting based on some specific values/area in the Pivot Table report, so you cannot achieve it directly via PivotTable options, you may confirm this in MS Excel manually. I am afraid, you have to first create your PivotTable via Aspose.Cells APIs, then you got to refresh PivotTable with its data (i.e., use PivoTable.refreshData() and PivotTable.calculateData(), etc.), so Aspose.Cells should paste/render the PivotTable report into the worksheet cells. Now use general data sorting feature (see the document for your reference: http://www.aspose.com/docs/display/cellsjava/Data+Sorting) to sort your desired area (using top to bottom or left to right options) accordingly via Aspose.Cells APIs. It is not so simple because you first need to find out which area or part of area (row or column) to be sorted out. Here, you may use Find/Search options provided by Aspose.Cells API.

If you still think such a custom sorting can be done directly via PivotTable options in MS Excel, kindly do create your desired PivotTable manually in MS Excel, save the Excel file and provide us in your thread, we will check and help you soon there.

I am working as Support developer/ Evangelist at Aspose.

Amjad Sahi
  • 1,813
  • 1
  • 10
  • 15
  • Thanks @Amjad for the right direction! I have made relevant changes as you suggested, but still it is not producing the expected Excel. Actually, I am saving my current workbook to ByteArrayOutputStream object rather than File (I cannot change the code's API). Moreover, I am sorting the same workbook twice (vertically and horizontally). Since, this is the part of the same request, I am not sure, I can achieve what I wanted in single request/thread. Please guide me further! – Hitesh Dholaria Apr 12 '16 at 12:38
  • Well, saving workbook to streams or file path should not make any difference. We recommend you to kindly follow up your thread (http://www.aspose.com/community/forums/thread/712040/how-to-sort-pivot-table-in-aspose-by-row-and-column-field-values.aspx) for your reference. – Amjad Sahi Apr 12 '16 at 18:01