Aspose.Cells for .NET

CreatePivotTableInExcel.cs

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

HideRowsInPivotTable.cs

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

SortPivotTableInExcelByColumn.cs

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

SortPivotTableInExcelByRow.cs

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