Insert Pivot Table

What is a Pivot Table?

A pivot table is an interactive summary report: it condenses a large block of raw, row-by-row data into a compact matrix without a single formula. You decide which columns become row labels, which become column labels and which are aggregated as values — the pivot table does the grouping and the math:

  • Summarize — total sales per product, per region or per quarter instead of long formula chains.
  • Compare — categories on the rows and periods on the columns turn a flat list into a cross report where trends are visible at a glance.
  • Drill down — double-click any summary value to inspect the source rows behind it.
  • Refresh — when the source data changes, one refresh recalculates the whole report. Typical scenarios are sales dashboards, expense breakdowns and inventory analysis — any question of the form “how do these numbers break down by category and time?”. Aspose.Cells for .NET builds the same pivot tables programmatically, so the workbooks your application generates can carry ready-made reports instead of raw data only.

Pivot Table Object Model

Aspose.Cells provides a special set of classes in the Aspose.Cells.Pivot namespace that are used to create and control pivot tables. These classes are used to create and set PivotTable objects, the building blocks of a pivot table. The objects are:

Creating a Simple Pivot Table Using Aspose.Cells

Creating a pivot table is a three-step recipe: write the source data into worksheet cells, add the pivot table at a destination position, and place the pivot fields into the row, column and data areas.

  1. Add data to a worksheet using the Cell object’s PutValue method. This data will be used as the pivot table’s data source.
  2. Add a pivot table to the worksheet by calling the PivotTables collection’s Add method: Add(sourceData, row, column). sourceData is the source range (for example, “A1:C8”); row and column are the 0-based coordinates of the destination cell at the pivot table’s upper-left corner (cell E3 means row 2, column 4). The method returns the new PivotTable object directly.
  3. Place the pivot fields with the PivotTable object’s AddFieldToArea method: a field name goes to the row area (PivotFieldType.Row), the column area (PivotFieldType.Column) or the data area (PivotFieldType.Data).
  4. Save the workbook — the pivot table is written into the saved file. After executing the example code, a pivot table is added to the worksheet.
using Aspose.Cells;
using Aspose.Cells.Pivot;
// Create a new workbook and get the first worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Write the source data: sales per sport and quarter
sheet.Cells["A1"].PutValue("Sport");
sheet.Cells["B1"].PutValue("Quarter");
sheet.Cells["C1"].PutValue("Sales");
sheet.Cells["A2"].PutValue("Golf");
sheet.Cells["B2"].PutValue("Qtr3");
sheet.Cells["C2"].PutValue(1500);
sheet.Cells["A3"].PutValue("Golf");
sheet.Cells["B3"].PutValue("Qtr4");
sheet.Cells["C3"].PutValue(2000);
sheet.Cells["A4"].PutValue("Tennis");
sheet.Cells["B4"].PutValue("Qtr3");
sheet.Cells["C4"].PutValue(600);
sheet.Cells["A5"].PutValue("Tennis");
sheet.Cells["B5"].PutValue("Qtr4");
sheet.Cells["C5"].PutValue(1500);
sheet.Cells["A6"].PutValue("Tennis");
sheet.Cells["B6"].PutValue("Qtr3");
sheet.Cells["C6"].PutValue(4070);
sheet.Cells["A7"].PutValue("Tennis");
sheet.Cells["B7"].PutValue("Qtr4");
sheet.Cells["C7"].PutValue(5000);
sheet.Cells["A8"].PutValue("Golf");
sheet.Cells["B8"].PutValue("Qtr3");
sheet.Cells["C8"].PutValue(6430);
// Add a pivot table at destination cell E3, sourced from A1:C8
PivotTable pivotTable = sheet.PivotTables.Add("A1:C8", 2, 4);
// Assign pivot fields: Sport to Rows, Quarter to Columns, Sales to Data
pivotTable.AddFieldToArea(PivotFieldType.Row, "Sport");
pivotTable.AddFieldToArea(PivotFieldType.Column, "Quarter");
pivotTable.AddFieldToArea(PivotFieldType.Data, "Sales");
// Save the workbook
workbook.Save("pivotTable_test_out.xlsx");