Introduction

Sometimes, developers may need to create Excel reports with Pivot Tables where data is coming from different data sources (such as a database) and not known at design time. Following sample provides an approach to dynamically change soure data of a pivot table.

Steps to Change Source Data of a Pivot Table

 

1. Creating a New Designer Template

Please create a new designer template file as folowing chart. Then define a named range, DataSource, which refers to this range of cells.

 

 

Figure: Creating a designer template & defining a named range, DataSource

 

2. Creating a Pivot Table Based on this Named Range

In Microsoft Excel, choose Data | PivotTable and PivotChart Report.... Then create a pivot table based on the named range (DataSource) that we created in first step.

 

 

Figure: Creating a Pivot Table based on the named range, DataSource

 

Drag the corresponding field to pivot table row and column, then create the resulting pivot table as following:

 

 

Figure: Creating a Pivot Table based on a corresponding field

 

Right click the pivot table, select Table Options. Check Refresh on open in Data options settings.

 

 

Figure: Setting the Pivot Table options

 

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

 

3. Populating New Data and Changing Source Data of Pivot Table

After the designer template is created, you can use the following code to change the source data of the pivot table.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream=new FileStream("C:\\pivot.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Populating new data to the worksheet cells

worksheet.Cells["A9"].PutValue("Golf");

worksheet.Cells["B9"].PutValue("Qtr4");

worksheet.Cells["C9"].PutValue(7000);

 

 

//Changing named range "DataSource"

Range range = worksheet.Cells.CreateRange(0, 0, 9, 3);

range.Name = "DataSource";

 

 

//Saving the modified Excel file in default format

workbook.Save("C:\\output.xls",FileFormatType.Default);

 

 

//Closing the file stream to free all resources

fstream.Close();

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\pivot.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Accessing the first worksheet in the Excel file

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Populating new data to the worksheet cells

worksheet.Cells("A9").PutValue("Golf")

worksheet.Cells("B9").PutValue("Qtr4")

worksheet.Cells("C9").PutValue(7000)

 

 

'Changing named range "DataSource"

Dim range As Range =  worksheet.Cells.CreateRange(0,0,9,3)

range.Name = "DataSource"

 

 

'Saving the modified Excel file in default format

workbook.Save("C:\\output.xls",FileFormatType.Default)

 

 

'Closing the file stream to free all resources

fstream.Close()

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook=new Workbook();

 

 

//Opening the Excel file

workbook.open("C:\\pivot.xls");

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);

 

 

//Populating new data to the worksheet cells

Cells cells = worksheet.getCells();

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

cell.setValue("Golf");

cell = cells.getCell("B9");

cell.setValue("Qtr4");

cell = cells.getCell("C9");

cell.setValue(7000);

 

 

//Changing named range "DataSource"

cells.createNamedRange("DataSource",0, 0, 8, 2);

 

 

//Saving the modified Excel file in default format

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

 

After executing the above example code, source data of the pivot table will be changed and hence the pivot table will look like as follows:

 

 

Figure: Dynamically changed Pivot Table