Skip to end of metadata
Go to start of metadata
Contents Summary
 

Create Pivot Table

It is possible to use Aspose.Cells to add pivot tables to spreadsheets programmatically.

Create Pivot Table Using Microsoft Excel

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:

  • PivotField represents a field in a PivotTable.
  • PivotFieldCollection represents a collection of all the PivotField objects in the PivotTable.
  • PivotTable represents a PivotTable on a worksheet.
  • PivotTableCollection represents a collection of all the PivotTable objects on a worksheet.

Create Pivot Table Using Aspose.Cells

  1. Add data to a worksheet using the Cell object's setValue method.
    This data will be used as te pivot table's data source.
  2. Add a pivot table to the worksheet by calling the PivotTables collection's add method, which is encapsulated in the Worksheet object.
  3. Access the new PivotTable object from the PivotTables collection by passing the PivotTable index.
  4. Use any of the PivotTable objects (explained above) to manage the pivot table.

After executing the example code, a pivot table is added to the worksheet.

 

When assigning a range of cells as the data source, the range must go from top left to bottom right. For example, "A1:C3" is valid but "C3:A1" is not.

Pivot Table's Source Data

There are times when you want to create Microsoft Excel reports with pivot tables that takes data from different data sources (such as a database) that are not known at design time. This article provides an approach to dynamically change a pivot table's data source.

Changing a Pivot Table's Source Data

  1. Creating a new designer template.
    1. Create a new designer template file as in the screenshot below.
    2. Then define a named range, DataSource, which refers to this range of cells.
  2. Creating a Pivot Table Based on this named range.
    1. In Microsoft Excel, choose Data, then PivotTable and PivotChart Report.
    2. Create a pivot table based on the named range created in the first step.
    3. Drag the corresponding field to pivot table row and column, then create the resulting pivot table as in the screenshot below.
  3. Right-click the pivot table and select Table Options.
    1. Check Refresh on open in Data options settings.

Now, you can save this file as your designer template file.

  1. Populating new data and changing source data of a pivot table.
    1. Once the designer template is created, use the following code to change the source data of the pivot table.

Executing the example code below changes the source data of the pivot table will be changed.

 

Labels
  • No labels