Pivot tables with Dynamic data

Aspose.Cells Product Family
1

I have been trying to get a pivot table example to work and not having much luck. I have looked through the forums and found that several other people have had issues with pivot tables as well, but none of the posts seemed to answer my question (and may of them were over a year old).

I have a designer excel file that has a pivot table. The data source for the pivot table is a named Range called "PlaceHolder". At run time I need to load data in the Named range and have it be the base data for the pivot table. The problem seems to be that I don't know the size of my data until run time (the query could pull back n number of rows).

Here is what I need to accomplish:

1) Have a designer that contains a pivot table.
2) Load a dynamic set of data (always same columns, just a variable number of rows) into the designer at run time.
3) Have the pivot table be based of that dynamic set of data.

So far I have not been able to accomplish what I'm attempting. Can anyone provide any pointers or an example of pivot table use with Aspose.Excel? I'm suprised with as many questions as I saw in the Forums that there is not an example of this functionality.

Thanks.

2

I created a template with the following steps:

1. Create a designer with a pivot table.
2. The data source of this pivot table is a named range "Range"
3. Set the table options with "Refresh on Open".
4. Use the following code to update the data.

Excel excel = new Excel();
excel.Open("c:\\book1.xls");
Range range = excel.Worksheets[0].Cells.CreateRange(3, 3, 6, 2);
range.Name = "Range";

excel.Worksheets[0].Cells["D9"].PutValue(5);
excel.Worksheets[0].Cells["E9"].PutValue(9);

excel.Save("abc.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);

I attached my designer file. Please download

and try the above code.

3

That worked great. Thanks for the example!

4

@ShermContractor,

Please note, Aspose.Excel has been renamed to Aspose.Cells. The new API is more robust and feature rich and includes many advanced level features.

You do not even need to use designer spreadsheet for Pivot tables or pivot charts. You can create Pivot tables and pivot charts from the scratch via the APIs. Aspose.Cells supports to create, manipulate, refresh and render Pivot Tables and Pivot Charts in Excel files (MS Excel 2007 - 2019). See the article with example code for your reference:
How to create Pivot Tables and Pivot Charts in Excel files

Also, See the sample code on how to create pivot table in Excel spreadsheet for your reference:
e.g
Sample code:

// 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);

Aspose.Cells.Pivot.PivotTableCollection 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
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;

// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

// Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
         
// Saving the Excel file
workbook.Save(dataDir + "pivotTable_test_out1.xlsx");