Pivot table: update data area

Aspose.Cells Product Family
1

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
2

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.
3

Great! thanks a lot :slight_smile:

Valter