Introduction

Developers can add PivotTables to their spreadsheets using Aspose.Cells. Aspose.Cells provides some special set of classes that are used to create and set the PivotTables. These classes are used to create and set PivotTable Objects, which act as the building blocks of a PivotTable. These PivotTable objects are listed below:

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

Creating a Simple PivotTable

To create a PivotTable using Aspose.Cells, please follow the steps below:

• Add some data to worksheet cells by using the setValue method of a Cell object. This data will be used as a data source for the PivotTable.
• Add a PivotTable to the worksheet by calling the add method of PivotTables collection,which is encapsulated in the Worksheet object.
• Access the newly added PivotTable object from the PivotTables collection by passing the PivotTable index. Use any of the PivotTable Objects (explained in the above section) encapsulated in the PivotTable object to manage your PivotTable.

 

NoteWhen you assign a range of cells as data source, you can only set the range from top left to bottom right. For example, "A1:C3" is valid while "C3:A1" is invalid.

 

Using the above general steps, developers can create a simple PivotTable.

[C#]

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Obtaining the reference of the newly added worksheet

Worksheet sheet = workbook.Worksheets[0];

 

 

Cells cells = sheet.Cells;

 

 

//Setting the value to the cells

Cell cell = cells["A1"];

cell.PutValue("Sport");

cell = cells["B1"];

cell.PutValue("Quarter");

cell = cells["C1"];

cell.PutValue("Sales");

 

 

cell = cells["A2"];

cell.PutValue("Golf");

cell = cells["A3"];

cell.PutValue("Golf");

cell = cells["A4"];

cell.PutValue("Tennis");

cell = cells["A5"];

cell.PutValue("Tennis");

cell = cells["A6"];

cell.PutValue("Tennis");

cell = cells["A7"];

cell.PutValue("Tennis");

cell = cells["A8"];

cell.PutValue("Golf");

 

 

cell = cells["B2"];

cell.PutValue("Qtr3");

cell = cells["B3"];

cell.PutValue("Qtr4");

cell = cells["B4"];

cell.PutValue("Qtr3");

cell = cells["B5"];

cell.PutValue("Qtr4");

cell = cells["B6"];

cell.PutValue("Qtr3");

cell = cells["B7"];

cell.PutValue("Qtr4");

cell = cells["B8"];

cell.PutValue("Qtr3");

 

 

cell = cells["C2"];

cell.PutValue(1500);

cell = cells["C3"];

cell.PutValue(2000);

cell = cells["C4"];

cell.PutValue(600);

cell = cells["C5"];

cell.PutValue(1500);

cell = cells["C6"];

cell.PutValue(4070);

cell = cells["C7"];

cell.PutValue(5000);

cell = cells["C8"];

cell.PutValue(6430);

 

 

PivotTables pivotTables = sheet.PivotTables;

 

 

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=A1:C8","E3","PivotTable2");

 

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

 

 

//Unshowing grand totals for rows.

pivotTable.RowGrand = false;

 

 

//Draging the first field to the row area.

pivotTable.AddFieldToArea(PivotFieldType.Row,0);

 

 

//Draging the second field to the column area.

pivotTable.AddFieldToArea(PivotFieldType.Column,1);

 

 

//Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Data,2);

 

 

//Saving the Excel file

workbook.Save("C:\\book1.xls");

 

 

 

[VB]

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Obtaining the reference of the newly added worksheet

Dim sheet As Worksheet = workbook.Worksheets(0)

 

 

Dim cells As Cells  = sheet.Cells

 

 

'Setting the value to the cells

Dim cell As Cell = cells("A1")

cell.PutValue("Sport")

cell = cells("B1")

cell.PutValue("Quarter")

cell = cells("C1")

cell.PutValue("Sales")

 

 

cell = cells("A2")

cell.PutValue("Golf")

cell = cells("A3")

cell.PutValue("Golf")

cell = cells("A4")

cell.PutValue("Tennis")

cell = cells("A5")

cell.PutValue("Tennis")

cell = cells("A6")

cell.PutValue("Tennis")

cell = cells("A7")

cell.PutValue("Tennis")

cell = cells("A8")

cell.PutValue("Golf")

 

 

cell = cells("B2")

cell.PutValue("Qtr3")

cell = cells("B3")

cell.PutValue("Qtr4")

cell = cells("B4")

cell.PutValue("Qtr3")

cell = cells("B5")

cell.PutValue("Qtr4")

cell = cells("B6")

cell.PutValue("Qtr3")

cell = cells("B7")

cell.PutValue("Qtr4")

cell = cells("B8")

cell.PutValue("Qtr3")

 

 

cell = cells("C2")

cell.PutValue(1500)

cell = cells("C3")

cell.PutValue(2000)

cell = cells("C4")

cell.PutValue(600)

cell = cells("C5")

cell.PutValue(1500)

cell = cells("C6")

cell.PutValue(4070)

cell = cells("C7")

cell.PutValue(5000)

cell = cells("C8")

cell.PutValue(6430)

 

 

Dim pivotTables As PivotTables = sheet.PivotTables

 

 

'Adding a PivotTable to the worksheet

Dim index As Integer = pivotTables.Add("=A1:C8","E3","PivotTable2")

 

'Accessing the instance of the newly added PivotTable

Dim pivotTable As PivotTable = pivotTables(index)

 

 

'Unshowing grand totals for rows.

pivotTable.RowGrand = False

 

 

'Draging the first field to the row area.

pivotTable.AddFieldToArea(PivotFieldType.Row,0)

 

 

'Draging the second field to the column area.

pivotTable.AddFieldToArea(PivotFieldType.Column,1)

 

 

'Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Data,2)

 

 

'Saving the Excel file

workbook.Save("C:\book1.xls")

 

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Obtaining the reference of the newly added worksheet

Worksheet sheet = workbook.getWorksheets().addSheet();

Cells cells = sheet.getCells();

 

 

//Setting the value to the cells

Cell cell = cells.getCell("A1");

cell.setValue("Sport");

cell = cells.getCell("B1");

cell.setValue("Quarter");

cell = cells.getCell("C1");

cell.setValue("Sales");

 

 

cell = cells.getCell("A2");

cell.setValue("Golf");

cell = cells.getCell("A3");

cell.setValue("Golf");

cell = cells.getCell("A4");

cell.setValue("Tennis");

cell = cells.getCell("A5");

cell.setValue("Tennis");

cell = cells.getCell("A6");

cell.setValue("Tennis");

cell = cells.getCell("A7");

cell.setValue("Tennis");

cell = cells.getCell("A8");

cell.setValue("Golf");

 

 

cell = cells.getCell("B2");

cell.setValue("Qtr3");

cell = cells.getCell("B3");

cell.setValue("Qtr4");

cell = cells.getCell("B4");

cell.setValue("Qtr3");

cell = cells.getCell("B5");

cell.setValue("Qtr4");

cell = cells.getCell("B6");

cell.setValue("Qtr3");

cell = cells.getCell("B7");

cell.setValue("Qtr4");

cell = cells.getCell("B8");

cell.setValue("Qtr3");

 

 

cell = cells.getCell("C2");

cell.setValue(1500);

cell = cells.getCell("C3");

cell.setValue(2000);

cell = cells.getCell("C4");

cell.setValue(600);

cell = cells.getCell("C5");

cell.setValue(1500);

cell = cells.getCell("C6");

cell.setValue(4070);

cell = cells.getCell("C7");

cell.setValue(5000);

cell = cells.getCell("C8");

cell.setValue(6430);

 

 

PivotTables pivotTables = sheet.getPivotTables();

 

 

//Adding a PivotTable to the worksheet

int index = pivotTables.add("=A1:C8","E3","PivotTable2");

 

 

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables.get(index);

 

 

//Unshowing grand totals for rows.

pivotTable.setRowGrand(false);

 

 

//Draging the first field to the row area.

pivotTable.addFieldToArea(PivotFieldType.ROW,0);

 

 

//Draging the second field to the column area.

pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);

 

 

//Draging the third field to the data area.

pivotTable.addFieldToArea(PivotFieldType.DATA,2);

 

 

//Saving the Excel file

workbook.save("C:\\book1.xls",FileFormatType.DEFAULT);

 

After executing the above example code, a PivotTable will be added to the worksheet as shown below:

 

 

Figure: Creating a Pivot Table based on a corresponding field