Pivot Table Hide and Sort data

Pivot Table Hide and Sort data

Aspose.Cells for Node.js via C++ supports hiding and sorting data in the pivot table. The following code snippet demonstrates this feature by sorting the Score column in descending order and then hiding the rows with a score less than 60.

const AsposeCells = require("aspose.cells.node");

//directories
var sourceDir = RunExamples.Get_SourceDirectory();
var outputDir = RunExamples.Get_OutputDirectory();
var workbook = new AsposeCells.Workbook(sourceDir + "PivotTableHideAndSortSample.xlsx");
var worksheet = workbook.getWorksheets().get(0);
var pivotTable = worksheet.getPivotTables().get(0);
var dataBodyRange = pivotTable.getDataBodyRange();
var currentRow = 3;
var rowsUsed = dataBodyRange.endRow;
//Sorting score in descending
var field = pivotTable.getRowFields().get(0);
field.setIsAutoSort(true);
field.setIsAscendSort(false);
field.setAutoSortField(0);
pivotTable.refreshData();
pivotTable.calculateData();
//Hiding rows with score less than 60
while (currentRow < rowsUsed)
{
    var cell = worksheet.getCells().get(currentRow, 1);
    var score = cell.getFloatValue();
    if (score < 60)
    {
        worksheet.getCells().hideRow(currentRow);
    }
        
    currentRow = currentRow + 1;
}
    
pivotTable.refreshData();
pivotTable.calculateData();
//Saving the Excel file
workbook.save(outputDir + "PivotTableHideAndSort_out.xlsx");

The source and output excel files used in the code snippet are attached for reference.

Source File

Output File