How to sort Pivot Table in Aspose by row and column field values?

[hitesh.dholaria]

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

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 attached 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)

[amjad.sahi]

Hi,

Thanks for providing us sample code snippet, details and screen shots.

Well, since your requirement is to customize sorting based on some specific values in the Pivot Table report, so you cannot achieve it 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 render the PivotTable report into the worksheet cells. Now use MS Excel’s data sorting feature (see the document for your reference: 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 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 here (with steps details), we will check and help you soon.

Thank you.

Save Edit

[hitesh.dholaria]

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!

[amjad.sahi]

Hi,

Well, saving workbook to streams or file should not make any difference. Anyways, I did evaluate your issue a bit using a simple Excel file (that contains your mentioned dataset used as a datasource for the PivotTable). I can create the PivotTable and could search the row items after that using Aspose.Cells APIs to further perform customized data sorting on specific set of values only. Here, it looks like the data sorting is performed (by Aspose.Cells APIs) upon a specific area/range of values in the calculated PivotTable report. I might need to perform further tests to evaluate your issue and get back to you. In the meanwhile, could you also provide us a simple JAVA code snippet (runnable) on how do you find out your desired area and apply data sorting upon it. We will also check it.

Thank you.

[hitesh.dholaria]

Here is my code for your reference. Also, attached the Workbook being used in the code.

  // Generate Excel with Pivot Table sorted on column and row attributes

public byte[] generate() throws ExcelGenerationException{

LOG.entry();

// 3. Create pivot table.

Integer noOfMeasures = getRequest().getFields().getMeasures().size();

Integer noOfColumnAttributes = getRequest().getFields().getColumnAttributes().size();

Integer noOfRowLabels = getRequest().getFields().getRowAttributes().size();

Integer noOfColumns =

noOfMeasures.intValue() + noOfColumnAttributes.intValue() + noOfRowLabels.intValue();

Integer noOfRows = getRequest().getDataset().size() + 1;
int pivotSheetIndex = getWorkbook().getWorksheets().add();
Worksheet sheetPivotTable = getWorkbook().getWorksheets().get(pivotSheetIndex);
LOG.debug("Pivot sheet created for Report - '"+getRequest().getReportName()+"'");
sheetPivotTable.setName("Pivot Table");
if(noOfColumns.intValue()>0)
{
  PivotTableCollection ptCollection = sheetPivotTable.getPivotTables();
  Integer pivotTableIndex =
      ptCollection.add("="+PieMakerConstants.VIZ_EXCEL_EXPORT_DATA_SHEETNAME+"!A1:" + Metadata.getColumnLabel(noOfColumns - 1) + "" + noOfRows,
                       Metadata.getColumnLabel(1) + "2", 
                       getRequest().getReportName() +"-PivotTable");
  PivotTable pivotTable = ptCollection.get(pivotTableIndex);

  for(Map.Entry<Integer, Field> entry: getAttributesIndexMap().get().entrySet()){
    pivotTable.addFieldToArea(getPivotFieldType(entry.getValue().getType()), entry.getKey());
  }
//move sum values to column to make it identical with ui
  PivotFieldCollection columFields = pivotTable.getColumnFields();
  if(columFields !=null && pivotTable.getDataField()!=null)
  {
     columFields.add(pivotTable.getDataField());
  }
  
  // Set column and row grand summary flags
  pivotTable.setColumnGrand(getRequest().isColGrandSmry());
  pivotTable.setRowGrand(getRequest().isRowGrandSmry());
  
  // Sort pivot table by column and row attributes
  String sortByColName = "OrderDate";
  String sortByColOrder = "asc";
  String sortByColValue = "28/07/2000";

  String sortByRowName = "CategoryName";
  String sortByRowOrder = "desc";
  String sortByRowValue = "Seafood";
  
  PivotFieldCollection colFields = pivotTable.getColumnFields();
  PivotFieldCollection rowFields = pivotTable.getRowFields();

  PivotField sortByColField = colFields.get(sortByColName);
  PivotField sortByRowField = rowFields.get(sortByRowName);

  pivotTable.refreshData();
  try {
    pivotTable.calculateData();
  } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  
  // Get the cells collection in the sheet
  Cells cells = sheetPivotTable.getCells();

  // Obtain the DataSorter object in the workbook
  DataSorter sorter = getWorkbook().getDataSorter();
  sorter.setHasHeaders(true);

  // Create a cells area (range).
  CellArea ca = new CellArea();

  // Search for the column field value index
  int colFieldValueIdx = indexOf(sortByColField.getItems(), sortByColValue);
  if (colFieldValueIdx >= 0) {
    colFieldValueIdx += sortByColField.getBaseItemPosition();
  }

  // Search for the row field value index
  int rowFieldValueIdx = indexOf(sortByRowField.getItems(), sortByRowValue);
  if (rowFieldValueIdx >= 0) {
    rowFieldValueIdx += sortByRowField.getBaseItemPosition();
  }

  // Sort pivot table vertically, i.e, top to bottom
  if (colFieldValueIdx >= 0) {

    // Define the first key
    sorter.setKey1(colFieldValueIdx);

    // Set the first order
    sorter.setOrder1(sortByColOrder.equalsIgnoreCase("asc")
        ? SortOrder.ASCENDING
        : SortOrder.DESCENDING);

    // Specify the start row index.
    ca.StartRow = 3;

    // Specify the start column index.
    ca.StartColumn = 1;

    // Specify the last row index.
    ca.EndRow = ca.StartRow + sortByRowField.getItemCount() - 1;

    // Specify the last column index.
    ca.EndColumn = ca.StartColumn + sortByColField.getItemCount() - 1;

    // Sort data in the specified data range
    sorter.sort(cells, ca);
  }

  pivotTable.refreshData();
  try {
    pivotTable.calculateData();
  } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  
  // Sort pivot table horizontally, i.e, left to right
  sorter.setSortLeftToRight(true);
  
  if (rowFieldValueIdx >= 0) {

    // Define the first key
    sorter.setKey1(rowFieldValueIdx);

    // Set the first order
    sorter.setOrder1(sortByRowOrder.equalsIgnoreCase("asc")
        ? SortOrder.ASCENDING
        : SortOrder.DESCENDING);

    // Specify the start row index.
    ca.StartRow = 2;

  // Specify the start column index.
    ca.StartColumn = 2;

    // Specify the last row index.
    ca.EndRow = ca.StartRow + sortByRowField.getItemCount() - 1;

    // Specify the last column index.
    ca.EndColumn = ca.StartColumn + sortByColField.getItemCount() - 1;

    // Sort data in the specified data range
    sorter.sort(cells, ca);
  }

  pivotTable.refreshData();
  try {
    pivotTable.calculateData();
  } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  
}
else
{
  LOG.warn("There are no columns (row attribute/column attribute/measures) in the report.");
  LOG.info("Cannot create Pivot table for empty report.");
}

// Making pivot table sheet as default.
getWorkbook().getWorksheets().setActiveSheetIndex(pivotSheetIndex);

ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
  getWorkbook().save(os, SaveFormat.XLSX);
} catch (Exception e) {
  LOG.error("Cannot save the excel data to byte stream.",e);
  throw new ExcelGenerationException("Pivot table Generator failed - " + e.getMessage());
}
byte fileBytes[] = os.toByteArray();
LOG.info("Pivot table created successfully. Returning the bytes back to caller.");
LOG.exit();
return fileBytes;

}
public static int indexOf(String[] a, String s) {

if (a == null || s == null) {

return -1;

}
for (int i = 0; i < a.length; i++) {
  if (s.equalsIgnoreCase(a[i])) {
    return i;
  }
}
return -1;

}

[amjad.sahi]

Hi,

Thanks for the sample code snippet and template Excel file.

I have performed further tests to evaluate your issue and tried to accomplish your task as per your screenshots. I also discussed the issue with concerned developer from product team. Well, I am afraid, currently, apply custom data sorting for a specific area in PivotTable report in not supported by Aspose.Cells APIs. I have logged a separate ticket with and id “CELLSJAVA-41814” with all the details, samples and template file into our database for your desired feature. Our concerned developer will look into the details of your task soon.

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

Thank you.

[hitesh.dholaria]

Just wanted to check if we have any update on the ticket CELLSJAVA-41814.

[babar.raza]

Hi Hitesh,

I am afraid, we haven’t yet received updates regarding the ticket logged earlier as CELLSJAVA-41814. However, we have requested the concerned member of the product team to share the analysis results along with an ETA for the fix, if possible. As soon as we get any news in this regard, we will post here for your kind reference.

[babar.raza]

Hi again,

This is to update you that we have investigated the matter logged earlier as CELLSJAVA-41814. Please note, the required feature should be implemented by sorting the PivotField by DataField, and not the custom data sorting of PivotField. We will bring this feature on our road map of Aspose.Cells APIs after further analysis, and inform you regarding the estimated time required for the implementation.

[hitesh.dholaria]

Hi,

It seems that "pivotField.setAutoSortField(-1)" works fine while sorting on the field itself, but does not work as expected in case of "pivotField.setAutoSortField(n), where n>=0" (sorting on data field).

[babar.raza]

Hi Hitesh,

Thank you for your feedback. We have logged your comments to the aforementioned ticket for product team’s consideration. As soon as we get further updates in this regard, we will post here for your kind reference.

[hitesh.dholaria]

Please refer to the example given in my first comment.

https://forum.aspose.com/t/32962

So, I want to sort my pivot table basis the column labels (e.g., 01/01/1990, 07/04/1995, 28/07/2000) using the below code segment. But, the problem is that Aspose determines the data format of those column headers (e.g., dd/mm/yyyy) on its own and we developers don't have control over them. Hence, if we had column labels with different data format (e.g., mm/dd/yyyy), Aspose may get confused and cannot sort the pivot table properly.

pivotColumnField.setAutoSort(true);

pivotColumnField.setAscendSort(true);

pivotColumnField.setAutoSortField(-1);

Can you please guide me achieve my objective of sorting the pivot table on pivot fields (not the data fields) that are in different Date formats?

[babar.raza]

Hi Hitesh,

In certain situations when auto sort of the Pivot Fields isn’t applicable, there are APIs available that allows you to position the Pivot Items as per your requirements. For instance, you may use the PivotItemCollection.changeItemsOrder method to sort the Pivot Table according to manual order. Please disable the auto sort for the Pivot Field in order to work with aforementioned approach. Moreover, you can position Pivot Item using the PivotItem.Position and PivotItem.PositionInSameParentNode attributes and can move the Pivot Item (up or down) using the PivotItem.move method.

Here are a few details for your reference.

• 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.
• Please call PivotTable.calculateData method before and after moving an individual Pivot Item.

In case you still face any difficulty, please provide us the source spreadsheet containing the raw Pivot Table as well as your desired results, that you may create manually in Excel, and share it here to show your exact requirements.

[rajendrak]

Hi,
If I want to have custom sort order in pivot table and maintain it (without Aspose reordering in alphabetical order) how do I do that? I mean what excel option I should use and what should the Code using Aspose cell should be like?

[amjad.sahi]

@rajendrak,

I am not entirely certain about your requirements. Please elaborate on how could you achieve your custom data sorting on PivotTable report via PivotTable options in MS Excel manually? If you can achieve it via PivotTable options in MS Excel manually, please create a sample file containing your desired PivotTable in it, save the file and provide us here with steps details, we will check it soon. Alternatively, you may apply MS Excel’s data sorting feature (see the document for your reference: Data Sorting) to sort your desired area via Aspose.Cells APIs. but you got to first refresh and calculate Pivot Table.

[ahsaniqbalsidiqui]

@rajendrak,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-41814”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

[amjad.sahi]

@rajendrak,

Please try our latest version/fix (.NET and Java): Aspose.Cells v19.9.3 (attached)
(Please choose/download the appropriate fix for your underlying platform/language)

Your issue should be fixed in it. See the sample code for your reference:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "Bk_PvtSort.xlsx");

        //Obtaining the reference of the newly added worksheet
    Worksheet sheet = wb.getWorksheets().get(0);

    PivotTableCollection pivotTables = sheet.getPivotTables();

    // soruce PivotTable
    //Adding a PivotTable to the worksheet
    int index = pivotTables.add("=Sheet1!A1:C10", "E3", "PivotTable2");

    //Accessing the instance of the newly added PivotTable
    PivotTable pivotTable = pivotTables.get(index);

    //Unshowing grand totals for rows.
    pivotTable.setRowGrand(false);
    pivotTable.setColumnGrand(false);

    //Dragging the first field to the row area.
    pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
    PivotField rowField = pivotTable.getRowFields().get(0);
    rowField.setAutoSort(true);
    rowField.setAscendSort(true);

    //Dragging the second field to the column area.
    pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
    PivotField colField = pivotTable.getColumnFields().get(0);
    colField.setNumberFormat("dd/mm/yyyy");
    colField.setAutoSort(true);
    colField.setAscendSort(true);

    //Dragging the third field to the data area.
    pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

    pivotTable.refreshData();
    pivotTable.calculateData();
    //end of soruce PivotTable





    // sort the PivotTable on "SeaFood" row field values
    //Adding a PivotTable to the worksheet
    index = pivotTables.add("=Sheet1!A1:C10", "E10", "PivotTable2");

    //Accessing the instance of the newly added PivotTable
    pivotTable = pivotTables.get(index);

    //Unshowing grand totals for rows.
    pivotTable.setRowGrand(false);
    pivotTable.setColumnGrand(false);

    //Dragging the first field to the row area.
    pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
    rowField = pivotTable.getRowFields().get(0);
    rowField.setAutoSort(true);
    rowField.setAscendSort(true);

    //Dragging the second field to the column area.
    pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
    colField = pivotTable.getColumnFields().get(0);
    colField.setNumberFormat("dd/mm/yyyy");
    colField.setAutoSort(true);
    colField.setAscendSort(true);    
    colField.setAutoSortField(0);
    

    //Dragging the third field to the data area.
    pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

    pivotTable.refreshData();
    pivotTable.calculateData();
    //end of sort the PivotTable on "SeaFood" row field values





    // sort the PivotTable on "28/07/2000" column field values
    //Adding a PivotTable to the worksheet
    index = pivotTables.add("=Sheet1!A1:C10", "E18", "PivotTable2");

   //Accessing the instance of the newly added PivotTable
    pivotTable = pivotTables.get(index);

    //Unshowing grand totals for rows.
    pivotTable.setRowGrand(false);
    pivotTable.setColumnGrand(false);
   //Dragging the first field to the row area.
    pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
    rowField = pivotTable.getRowFields().get(0);
    rowField.setAutoSort(true);
    rowField.setAscendSort(true);
    rowField.setAutoSortField(0);

    //Dragging the second field to the column area.
    pivotTable.addFieldToArea(PivotFieldType.COLUMN, 0);
    colField = pivotTable.getColumnFields().get(0);
    colField.setNumberFormat("dd/mm/yyyy");
    colField.setAutoSort(true);
    colField.setAscendSort(true);
    

    //Dragging the third field to the data area.
    pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

    pivotTable.refreshData();
    pivotTable.calculateData();   
    //end of sort the PivotTable on "28/07/2000" column field values


    //Saving the Excel file
    wb.save(filePath + "out_java.xlsx");
    PdfSaveOptions options = new PdfSaveOptions();
    options.setOnePagePerSheet(true);
    wb.save(filePath + "out_java.pdf", options);

Let us know your feedback.
[.NET]
Aspose.Cells19.9.3 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.9.3 For .Net4.0.Zip (4.9 MB)
Aspose.Cells19.9.3 For .NetStandard20.Zip (4.1 MB)

[Java]
Aspose_Cells_Java_v19.9.3.zip (6.6 MB)

[aspose.notifier]

The issues you have found earlier (filed as CELLSJAVA-41814) have been fixed in Aspose.Cells for Java v19.10. This message was posted using Bugs notification tool by ahsaniqbalsidiqui