Create Pivot Tables and Pivot Charts

A pivot table is an interactive summary of records. For example, you may have hundreds of invoice entries in a list in a worksheet. A pivot table can total the invoices by customer, product or date. With Microsoft Excel it is possible to quickly re-arrange the information in the pivot table by dragging buttons to a new position.

A pivot chart is an interactive graphical representation of the data in a pivot table. Pivot charts were introduced in Excel 2000. Using a pivot chart makes it even easier to understand the data since the pivot table creates subtotals and totals automatically.

Aspose.Cells supports pivot tables and pivot charts.

Adding Pivot Tables and Charts

Aspose.Cells provides a special set of classes used to create pivot tables. These classes are used to create and set PivotTable objects, which act as a PivotTable object’s basic building blocks:

• PivotField, a field in a pivot table report.
• PivotFields, a collection of all the PivotField objects in a pivot table.
• PivotTable, a PivotTable report on a worksheet.
• PivotTables, a collection of all the PivotTable objects on the worksheet.

Preparing to use Aspose.Cells

1. Download and install Aspose.Cells:
   1. Download Aspose.Cells.
   2. Install it on your development computer. All Aspose components, when installed, work in evaluation mode. The evaluation mode has no time limit and it only injects watermarks into produced documents. To work with the component in its full capacity you do need to have a valid license.
2. Create a project:
   1. Start Visual Studio.Net.
   2. Create a new console application.
3. Add references: Add reference to the Aspose.Cells component into your project, for example …\Program Files\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll

Adding a Pivot Table

To create a pivot table using Aspose.Cells:

1. Add some data to a worksheet cells using a Cell object’s PutValue/setValue method. You also use a template file already filled with data. The 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 (encapsulated in the Worksheet object).
3. Access the new PivotTable object from the PivotTables collection by passing its index. # Use any of the pivot table objects encapsulated in the PivotTable object to manage the table.

Code examples are given below.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiating an Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Name the sheet
sheet.Name = "Data";
Cells cells = sheet.Cells;

// Setting the values to the cells
Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");

cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James");
cell = cells["A7"];
cell.PutValue("James");
cell = cells["A8"];
cell.PutValue("James");
cell = cells["A9"];
cell.PutValue("James");
cell = cells["A10"];
cell.PutValue("James");
cell = cells["A11"];
cell.PutValue("Miya");
cell = cells["A12"];
cell.PutValue("Miya");
cell = cells["A13"];
cell.PutValue("Miya");
cell = cells["A14"];
cell.PutValue("Miya");
cell = cells["A15"];
cell.PutValue("Miya");
cell = cells["A16"];
cell.PutValue("Miya");
cell = cells["A17"];
cell.PutValue("Miya");
cell = cells["A18"];
cell.PutValue("Elvis");
cell = cells["A19"];
cell.PutValue("Elvis");
cell = cells["A20"];
cell.PutValue("Elvis");
cell = cells["A21"];
cell.PutValue("Elvis");
cell = cells["A22"];
cell.PutValue("Elvis");
cell = cells["A23"];
cell.PutValue("Elvis");
cell = cells["A24"];
cell.PutValue("Elvis");
cell = cells["A25"];
cell.PutValue("Jean");
cell = cells["A26"];
cell.PutValue("Jean");
cell = cells["A27"];
cell.PutValue("Jean");
cell = cells["A28"];
cell.PutValue("Ada");
cell = cells["A29"];
cell.PutValue("Ada");
cell = cells["A30"];
cell.PutValue("Ada");

cell = cells["B2"];
cell.PutValue("1");
cell = cells["B3"];
cell.PutValue("2");
cell = cells["B4"];
cell.PutValue("3");
cell = cells["B5"];
cell.PutValue("4");
cell = cells["B6"];
cell.PutValue("1");
cell = cells["B7"];
cell.PutValue("2");
cell = cells["B8"];
cell.PutValue("3");
cell = cells["B9"];
cell.PutValue("4");
cell = cells["B10"];
cell.PutValue("4");
cell = cells["B11"];
cell.PutValue("1");
cell = cells["B12"];
cell.PutValue("1");
cell = cells["B13"];
cell.PutValue("2");
cell = cells["B14"];
cell.PutValue("2");
cell = cells["B15"];
cell.PutValue("3");
cell = cells["B16"];
cell.PutValue("4");
cell = cells["B17"];
cell.PutValue("4");
cell = cells["B18"];
cell.PutValue("1");
cell = cells["B19"];
cell.PutValue("1");
cell = cells["B20"];
cell.PutValue("2");
cell = cells["B21"];
cell.PutValue("3");
cell = cells["B22"];
cell.PutValue("3");
cell = cells["B23"];
cell.PutValue("4");
cell = cells["B24"];
cell.PutValue("4");
cell = cells["B25"];
cell.PutValue("1");
cell = cells["B26"];
cell.PutValue("2");
cell = cells["B27"];
cell.PutValue("3");
cell = cells["B28"];
cell.PutValue("1");
cell = cells["B29"];
cell.PutValue("2");
cell = cells["B30"];
cell.PutValue("3");

cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang");
cell = cells["C7"];
cell.PutValue("Chang");
cell = cells["C8"];
cell.PutValue("Chang");
cell = cells["C9"];
cell.PutValue("Chang");
cell = cells["C10"];
cell.PutValue("Chang");
cell = cells["C11"];
cell.PutValue("Geitost");
cell = cells["C12"];
cell.PutValue("Chai");
cell = cells["C13"];
cell.PutValue("Geitost");
cell = cells["C14"];
cell.PutValue("Geitost");
cell = cells["C15"];
cell.PutValue("Maxilaku");
cell = cells["C16"];
cell.PutValue("Geitost");
cell = cells["C17"];
cell.PutValue("Geitost");
cell = cells["C18"];
cell.PutValue("Ikuru");
cell = cells["C19"];
cell.PutValue("Ikuru");
cell = cells["C20"];
cell.PutValue("Ikuru");
cell = cells["C21"];
cell.PutValue("Ikuru");
cell = cells["C22"];
cell.PutValue("Ipoh Coffee");
cell = cells["C23"];
cell.PutValue("Ipoh Coffee");
cell = cells["C24"];
cell.PutValue("Ipoh Coffee");
cell = cells["C25"];
cell.PutValue("Chocolade");
cell = cells["C26"];
cell.PutValue("Chocolade");
cell = cells["C27"];
cell.PutValue("Chocolade");
cell = cells["C28"];
cell.PutValue("Chocolade");
cell = cells["C29"];
cell.PutValue("Chocolade");
cell = cells["C30"];
cell.PutValue("Chocolade");

cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe");
cell = cells["D7"];
cell.PutValue("Europe");
cell = cells["D8"];
cell.PutValue("Europe");
cell = cells["D9"];
cell.PutValue("Europe");
cell = cells["D10"];
cell.PutValue("Europe");
cell = cells["D11"];
cell.PutValue("America");
cell = cells["D12"];
cell.PutValue("America");
cell = cells["D13"];
cell.PutValue("America");
cell = cells["D14"];
cell.PutValue("America");
cell = cells["D15"];
cell.PutValue("America");
cell = cells["D16"];
cell.PutValue("America");
cell = cells["D17"];
cell.PutValue("America");
cell = cells["D18"];
cell.PutValue("Europe");
cell = cells["D19"];
cell.PutValue("Europe");
cell = cells["D20"];
cell.PutValue("Europe");
cell = cells["D21"];
cell.PutValue("Oceania");
cell = cells["D22"];
cell.PutValue("Oceania");
cell = cells["D23"];
cell.PutValue("Oceania");
cell = cells["D24"];
cell.PutValue("Oceania");
cell = cells["D25"];
cell.PutValue("Africa");
cell = cells["D26"];
cell.PutValue("Africa");
cell = cells["D27"];
cell.PutValue("Africa");
cell = cells["D28"];
cell.PutValue("Africa");
cell = cells["D29"];
cell.PutValue("Africa");
cell = cells["D30"];
cell.PutValue("Africa");

cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France");
cell = cells["E7"];
cell.PutValue("France");
cell = cells["E8"];
cell.PutValue("Germany");
cell = cells["E9"];
cell.PutValue("Italy");
cell = cells["E10"];
cell.PutValue("France");
cell = cells["E11"];
cell.PutValue("U.S.");
cell = cells["E12"];
cell.PutValue("U.S.");
cell = cells["E13"];
cell.PutValue("Brazil");
cell = cells["E14"];
cell.PutValue("U.S.");
cell = cells["E15"];
cell.PutValue("U.S.");
cell = cells["E16"];
cell.PutValue("Canada");
cell = cells["E17"];
cell.PutValue("U.S.");
cell = cells["E18"];
cell.PutValue("Italy");
cell = cells["E19"];
cell.PutValue("France");
cell = cells["E20"];
cell.PutValue("Italy");
cell = cells["E21"];
cell.PutValue("New Zealand");
cell = cells["E22"];
cell.PutValue("Australia");
cell = cells["E23"];
cell.PutValue("Australia");
cell = cells["E24"];
cell.PutValue("New Zealand");
cell = cells["E25"];
cell.PutValue("S.Africa");
cell = cells["E26"];
cell.PutValue("S.Africa");
cell = cells["E27"];
cell.PutValue("S.Africa");
cell = cells["E28"];
cell.PutValue("Egypt");
cell = cells["E29"];
cell.PutValue("Egypt");
cell = cells["E30"];
cell.PutValue("Egypt");

cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500);
cell = cells["F7"];
cell.PutValue(1500);
cell = cells["F8"];
cell.PutValue(800);
cell = cells["F9"];
cell.PutValue(900);
cell = cells["F10"];
cell.PutValue(500);
cell = cells["F11"];
cell.PutValue(1600);
cell = cells["F12"];
cell.PutValue(600);
cell = cells["F13"];
cell.PutValue(2000);
cell = cells["F14"];
cell.PutValue(500);
cell = cells["F15"];
cell.PutValue(900);
cell = cells["F16"];
cell.PutValue(700);
cell = cells["F17"];
cell.PutValue(1400);
cell = cells["F18"];
cell.PutValue(1350);
cell = cells["F19"];
cell.PutValue(300);
cell = cells["F20"];
cell.PutValue(500);
cell = cells["F21"];
cell.PutValue(1000);
cell = cells["F22"];
cell.PutValue(1500);
cell = cells["F23"];
cell.PutValue(1500);
cell = cells["F24"];
cell.PutValue(1600);
cell = cells["F25"];
cell.PutValue(1000);
cell = cells["F26"];
cell.PutValue(1200);
cell = cells["F27"];
cell.PutValue(1300);
cell = cells["F28"];
cell.PutValue(1500);
cell = cells["F29"];
cell.PutValue(1400);
cell = cells["F30"];
cell.PutValue(1000);

// Adding a new sheet
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
// Naming the sheet
sheet2.Name = "PivotTable";
// Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1");
// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
// Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
// Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
// Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
// Draging the third field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);
// Draging the second field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
// Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);
// Draging the fifth field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);
// Setting the number format of the first data field
pivotTable.DataFields[0].NumberFormat = "$#,##0.00";
// Saving the Excel file
workbook.Save(dataDir+ "pivotTable_test.out.xlsx");

Adding a Pivot Chart

To create a PivotChart using Aspose.Cells:

1. Add a chart.
2. Set the PivotSource of the chart to refer to an existing pivot table in the spreadsheet.
3. Set other attributes.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiating an Workbook object
// Opening the excel file
Workbook workbook = new Workbook(dataDir+ "pivotTable_test.xlsx");
// Adding a new sheet
Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)];
// Naming the sheet
sheet3.Name = "PivotChart";
// Adding a column chart
int index = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16);
// Setting the pivot chart data source
sheet3.Charts[index].PivotSource = "PivotTable!PivotTable1";
sheet3.Charts[index].HidePivotFieldButtons = false;
// Saving the Excel file
workbook.Save(dataDir+ "pivotChart_test_out.xlsx");