数据透视表隐藏和排序数据

数据透视表隐藏和排序数据

Aspose.Cells支持在数据透视表中隐藏和排序数据。以下代码片段演示了通过按得分列按降序排序,然后隐藏得分低于60的行的功能。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// directories
string sourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "PivotTableHideAndSortSample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var pivotTable = worksheet.PivotTables[0];
var dataBodyRange = pivotTable.DataBodyRange;
int currentRow = 3;
int rowsUsed = dataBodyRange.EndRow;
// Sorting score in descending
PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 0;
pivotTable.RefreshData();
pivotTable.CalculateData();
// Hiding rows with score less than 60
while (currentRow < rowsUsed)
{
Cell cell = worksheet.Cells[currentRow, 1];
double score = Convert.ToDouble(cell.Value);
if (score < 60)
{
worksheet.Cells.HideRow(currentRow);
}
currentRow++;
}
pivotTable.RefreshData();
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(outputDir + "PivotTableHideAndSort_out.xlsx");

示例代码中使用的源和输出Excel文件已附上供参考。

源文件

输出文件