We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pivot table: update data area

Hi all,

I have a question about functionality of Aspose.Cells! :slight_smile:
Probably i must manipulate a XLS model predefined with pivot table. I must put new data in hidden sheet e then change the data area of predefinded pivot table.
Is it prossibile using Aspose.Cells?
Thanks you very much,
Valter

Hi,


Please use PivotTable.ChangeDataSource() method for your requirements. See the sample line of code below:
pivotTable.ChangeDataSource(new[] { “=Sheet3!$A$1:$B$11” });


Here is another sample code snippet for your needs, here wb is a Workbook object:

// Update PivotTable Sources

foreach (PivotTable pt in wb.Worksheets[wb.Worksheets.ActiveSheetIndex].PivotTables)
{

Console.WriteLine("\nDataSource Before Change:" + pt.DataSource[0]);


string[] newDS = new string[] { pt.DataSource[0].Replace(“Base”, newWSName) };

Console.WriteLine(“New DataSource Value to Set:” + newDS[0]);


pt.ChangeDataSource(newDS);

pt.RefreshDataOnOpeningFile = true;


Console.WriteLine(“DataSource After Change:” + pt.DataSource[0]);

}



Thank you.

Great! thanks a lot :slight_smile:

Valter