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");