Free Support Forum - aspose.com

Pivot tables with Dynamic data

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.

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 v2.9.1 to try the above code.

That worked great. Thanks for the example!