Free Support Forum - aspose.com

Copying worksheet containing PivotTables

Hi, I have a worksheet containing a ListObject which serves as source for a Pivot table which lies in the same worksheet.

Then I need to copy this worksheet into a new one (Copy or AddCopy methods, the result is the same), but when I open the generated file the PivotTable in the new worksheet is still referencing data from the original sheet from which this was copied.
Afterwards I tried invoking the “ChangeDataSource” method on the newly copied pivottable passing the name of the ListObject which should have served, locally, as new datasource (I’ve also called all the RefreshData and CalculateData methods as mentioned by you in other threads).
The result is that when I try to open the generated file, I get a recovery error by Excel and if I choose to repair, the new PivotTable is still pointing to the original datasource.

Below the example code I used to reproduce the problem

void Main()
{
Workbook wb = new Workbook(“test.xlsx”);
CopyWorkSheet(wb, “NewSheet”);
wb.Save(“output.xlsx”);
}

public static void CopyWorkSheet(Workbook wb, string newWSName)
{
Worksheet sheet = wb.Worksheets[0];
Worksheet newWorksheet = wb.Worksheets.Add(newWSName);
newWorksheet.MoveTo(sheet.Index + 1);
newWorksheet.Copy(sheet);
// Update PivotTable Sources
foreach (PivotTable pt in newWorksheet.PivotTables)
{
string[] newDS = new string[] { “Table2” };
pt.ChangeDataSource(newDS);
pt.RefreshDataOnOpeningFile = true;
}
}

Hi,


Thanks for providing us template file and sample code.

Please try our latest version/fix: Aspose.Cells for .NET v17.2.0.
I have evaluated your scenario/ case using your template file and sample code, it works fine and as expected.

Let us know if you still have any issue with v17.2.0.

Thank you.
You are right, after updating the Aspose.Cells dll to the latest version the problem I've reported does not seem to be present anymore.
Unfortunately my scenario includes also the usage of a PivotChart which still presents the issue.
I've attached another excel template (actually the same where I simply added a pivot chart connected to the same source as the PivotTable).
Running the previous code over this new template, you can notice the following:
  1. The pivot chart is correctly copied togheter with the sheet;
  2. The pivot chart in the new sheet is still pointing to the Table1 in the original sheet (the same problem that affected the PivotTable);
  3. Whatever modification you do to any pivot chart, it affects the other. It seems to me that the copied pivot chart is not just a clone of the original one, but the same instance.
Do you have any suggestion on how can I address this problem?
Thanks in advance for your answers and professionality.

Hi,


Thanks for the new template file.

Please update the pivot source for the chart in the new sheet accordingly, see the updated code segment for your reference:
e.g
Sample code:

// Update PivotTable Sources
foreach (PivotTable pt in newWorksheet.PivotTables)
{
string[] newDS = new string[] { “Table2” };
pt.ChangeDataSource(newDS);
newWorksheet.Charts[0].PivotSource = “NewSheet!PivotTable1”;
pt.RefreshDataOnOpeningFile = true;
}


Hope, this helps a bit.

Thank you.

Thank you very much.

It worked fine for my use cases.

Hi,


Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.