永利最大(官方)网站

Aspose.Cells for .NET是Excel电子表格编程API，可加快电子表格管理和处理任务，同时支持构建具有生成，修改，转换，呈现和打印电子表格功能的跨平台应用程序。

用于生成和操作Excel电子表格的自动化解决方案如今已被广泛使用。Excel中的数据透视表被广泛用于数据的汇总和分析。而对数据透视表中的数据进行排序对于检查Excel电子表格中的大量数据非常有用。

数据透视表中的数据排序可用于按字母顺序（AZ或ZA）排列文本值的项目，或在数字的情况下从最高值到最低值或从最低值到最高值。基于Excel数据透视表的重要性，本文旨在向您展示如何：

• 在Excel中创建数据透视表
• 按行字段值对数据透视表进行排序
• 按列字段值对数据透视表进行排序
• 并隐藏数据透视表行

如果你还没有使用过Aspose.Cells，可以点击此处下载最新版体验。

为了进行演示，下面图示的Excel电子表格将在整个示例中使用。

使用C＃在Excel中创建数据透视表

首先让我们看看如何使用Aspose.Cells for .NET在C＃中创建Excel数据透视表。创建数据透视表后，我们将隐藏行并根据其列或行字段对数据进行排序。下面的代码示例演示如何创建Excel数据透视表。

Workbook wb = new Workbook("SampleExcel.xlsx");

// Obtaining the reference of the newly added worksheet
Worksheet sheet = wb.Worksheets[0];

PivotTableCollection pivotTables = sheet.PivotTables;

// source 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[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;

// Dragging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
PivotField rowField = pivotTable.RowFields[0];
rowField.IsAutoSort = true;
rowField.IsAscendSort = true;

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 0);
PivotField colField = pivotTable.ColumnFields[0];
colField.NumberFormat = "dd/mm/yyyy";
colField.IsAutoSort = true;
colField.IsAscendSort = true;

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

pivotTable.RefreshData();
pivotTable.CalculateData();
// end of source PivotTable

//Saving the Excel file
wb.Save("output.xlsx");

输出结果

按C＃中的行字段值对数据透视表进行排序

现在，我们将创建另一个数据透视表，并对数据进行排序。下面的代码示例创建并按“ SeaFood”行字段值对数据透视表进行排序。

Workbook wb = new Workbook("SampleExcel.xlsx");

// Obtaining the reference of the Excel worksheet.
Worksheet sheet = wb.Worksheets[0];

PivotTableCollection pivotTables = sheet.PivotTables;

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

// Accessing the instance of the newly added PivotTable.
PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;

// Dragging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
PivotField rowField = pivotTable.RowFields[0];
rowField.IsAutoSort = true;
rowField.IsAscendSort = true;

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 0);
PivotField colField = pivotTable.ColumnFields[0];
colField.NumberFormat = "dd/mm/yyyy";
colField.IsAutoSort = true;
colField.IsAscendSort = true;
colField.AutoSortField = 0;

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

pivotTable.RefreshData();
pivotTable.CalculateData();

// Saving the Excel file.
wb.Save("output.xlsx");

输出结果

在C＃中按列字段值对数据透视表进行排序

以下C＃代码示例对“ 28/07/2000”列的字段值进行排序。

Workbook wb = new Workbook("SampleExcel.xlsx");

// Obtaining the reference of the Excel worksheet.
Worksheet sheet = wb.Worksheets[0];

PivotTableCollection pivotTables = sheet.PivotTables;

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

// Accessing the instance of the newly added PivotTable.
PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;

// Dragging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
PivotField rowField = pivotTable.RowFields[0];
rowField.IsAutoSort = true;
rowField.IsAscendSort = true;
colField.AutoSortField = 0;

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 0);
PivotField colField = pivotTable.ColumnFields[0];
colField.NumberFormat = "dd/mm/yyyy";
colField.IsAutoSort = true;
colField.IsAscendSort = true;

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

pivotTable.RefreshData();
pivotTable.CalculateData();

// Saving the Excel file.
wb.Save("output.xlsx");

输出结果

在c#中隐藏数据透视表行

根据希望应用的某些条件隐藏Excel数据透视表中的行。下面的代码示例展示了如何使用c#隐藏数据透视表中的特定行。

Workbook workbook = new Workbook("output.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

var pivotTable = worksheet.PivotTables[0];
var dataBodyRange = pivotTable.DataBodyRange;
int currentRow = 1;
int rowsUsed = dataBodyRange.EndRow;

// Sorting values in descending
PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 0;

pivotTable.RefreshData();
pivotTable.CalculateData();

// Hiding rows with value less than 15
while (currentRow < rowsUsed) { Cell cell = worksheet.Cells[currentRow, 2]; double score = Convert.ToDouble(cell.Value); if (score < 15) { worksheet.Cells.HideRow(currentRow); } currentRow++; } pivotTable.RefreshData(); pivotTable.CalculateData(); // Saving the Excel file workbook.Save("PivotTableHideAndSort.xlsx");