WorkSheetCollection.AddCopy not updating references

Aspose.Cells Product Family
1

Hi,

I have a workbook set up with one worksheet. On that worksheet i have an area where i have raw data, another area where i build a pivot table from that data and a Chart that sources it's data from the pivot table.

I use Aspose Cells to 'paste' over the raw data.

It works great if I'm 'pasting' over the data in the same worksheet.

However, what i actually need to do is to copy the worksheet (lots of times into the same workbook) and paste different data into each new worksheet. I use AddCopy to perform the copy. It does create a new worksheet and it is a copy of the original and the new raw data does paste into the right spot, but the Pivot Table is still sourcing its data from the original worksheet and not from the new worksheet.

What methods could i use to update the references in the new worksheets to source data from themselves. I don't seem to be able to set the original worksheets pivot table to remove the worksheet name from the datasource.

Cheers,

Scott

2
Hi,

I think you have to change your data source for the pivot tables and pivot charts(if you have). See the following sample code for your reference:

Sample code:

foreach (PivotTable pt in sht.PivotTables)
{
string[] t = new string[] { "=data!A1:C5", null };

pt.ChangeDataSource(t);

pt.RefreshDataOnOpeningFile = true;
}

foreach (Chart cht in sht.Charts)
{
cht.PivotSource = "tables!PivotTable1";

cht.RefreshPivotData();
}

We will refresh the pivot source of chart accordingly as well when you change the worksheet's name.

Thank you.
3

Thanks for that Amjad.

Unfortunatley it doesn't seem to be working.

When i look at the DataSource property prior to trying to change it i found that there is only a single element in the array. It shows as [0] = "Base!$A$84:$G$108".

In this instance all i need to do is change "Base" to "ALL" ("ALL" being the name of the new worksheet).

I've tried setting my new datasource using all the methods below. In debug i have seen that the value of pt.DataSource has not changed immediately after i call pt.ChangeDataSource(t).

Under what circumatances would pt.ChangeDataSource(t) not change the value ?

I even tried pt.DataSource = mystringarray and that doesn't work either.

1. Single element string array, value "ALL!$A$84:$G$108".

2. Single element string array, value "=ALL!$A$84:$G$108".

3. Single element string array, value "=ALL!A84:G108".

4. Double element string array, values "ALL!$A$84:$G$108" and null.

5. Double element string array, values "=ALL!$A$84:$G$108" and null.

6. Double element string array, values "=ALL!A84:G108" and null.

Cheers Scott

4

Hi,


Please create a sample console demo application, zip it and post it here with your template files to show the issue, we will check thoroughly to figure your issue out soon. Aspose.Cells should work the same way as MS Excel does. Could you kindly also confirm when you do an add copy of the sheet (having pivot tables in it) in MS Excel manually, does Excel behaves as you expected.

Also, by the way, we recommend you to kindly use our latest fix: Aspose.Cells for .NET v7.2.0.1


Thank you.

5

Hi,

Console App attached.

When using Excel if i copy a worksheet that has a PivotTable in it, the PivotTable still has its DataSource as the original worksheet. I have to click the PivotTable and change the DataSource. So Aspose.Cells is doing the same the Excel does except I'm having this problem trying to change the data source.

Cheers,

Scott

6

Hi Scott,


Thanks for the sample project with template files.

After an initial test, it looks like an issue. I can notice the issue using your template file and sample code, it does not update the data source of the pivot table in the copied worksheet.

I have logged a ticket with an id: CELLSNET-40605. We will look into your issue soon.

Thank you.
7

Hi,

Please download and try this fix: Aspose.Cells for .NET 7.2.0.3

8

Although this change did allow me to update the DataSource of the PivotTable in the NEW worksheet it also updated the DataSource of the PivotTable in the Copied worksheet as well. This is different to the behaviour of Excel.

If you run the console App i sent you and look at the output Template.xlsx workbook you'll notice that in the Base worksheet's PivotTable, the source data has changed to CopiedWS.

9

Hi,

Thanks for your feedback.

I was able to observe the problem mentioned by you. I have logged your comment in our database. Development team will look into this issue and once the issue is fixed or we have some update for it, we will let you know asap.

The comment has been logged against the issue id: CELLSNET-40605

10

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.2.0.4

11

Thankyou. This has fixed the issue in the Console App i sent you but when i applied this fix to the real workbook I've encountered another problem. I've updated the Base workbook and the console app main source to demonstrate this.

The problem i get is when i open the output workbook Excel gives me the error "Excel found unreadable content in 'Template.xslx'. Do you want to recover the contents of this workbook? If you trust ......."

I've attached updated source code and sample workbook.

Confirming that this did not occur in 7.2.0.3 version.

Scott

12

Hi,

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.4
it is working fine.

I have attached the output file generated by your code, when you open it in Ms-Excel 2010, you find there is no error it opens fine.

13

Hi,

Try opening it in Excel 2007 which is what I'm using.

Scott

14

Hi,

Thanks for your input.

I was able to reproduce this issue with Ms-Excel 2007.

I have attached the screenshot for a reference.

We have logged your comment in our database. Development team will look into this issue and fix the problems and we will update you asap.

The comment has been logged against the issue id: CELLSNET-40605

Screenshot:

15

Hi,

We have fixed this issue.

Please download and try the latest version: Aspose.Cells for .NET v7.2.0.6

Please try adding the following sample code.

C#


// Update Charts Sources
foreach (Chart cht in wb.Worksheets[wb.Worksheets.ActiveSheetIndex].Charts)
{
cht.PivotSource = cht.PivotSource.Replace(“Base”, newWSName);

cht.RefreshPivotData();
}

foreach (Chart cht in wb.Worksheets[0].Charts)
{
cht.RefreshPivotData();
}

16

Thankyou. This has fixed the issue in the Console App i sent you but when i applied this fix to my actual program I'm still getting the problem. I have managed to recreate the problem and have update the Console App (StartRun.cs file zipped attached).

The difference between the original Console App and this one is that this one Saves and Re-Opens the Workbook between each Copy (that's what my actual application does). Note it doesn't occur if there's just one Copy done, there has to be at least 3.

We are getting close. Thanks for all your help.

Reminder. I'm using Excel 2007

17

Hi,

Thanks for your feedback.

I was able to get the corrupted output file using your code and running it on the latest version:
Aspose.Cells
for .NET v7.2.0.6

I have attached both the source and output files and the screenshot for a reference.

Source File: Base.xlsx
Output File: Template.xlsx

We have reopened this issue and we will look into it and get back to you asap.

The comment has been logged against the issue id: CELLSNET-40605

C#


static int Main()

{

string outputFile = “Template.xlsx”;


File.Copy(“Base.xlsx”, outputFile, true);


for (int i = 1; i < 5; i++)

{

Workbook wb = new Workbook(outputFile);

Worksheet baseWS = wb.Worksheets[0];

CopyWorkSheet(wb, “CopiedWS” + i);

wb.Save(outputFile);

}


Console.WriteLine(“Press any key to continue…”);

Console.ReadKey(true);


return 0;


}

static private void CopyWorkSheet(Workbook wb, string newWSName)

{

Worksheet newWorksheet = wb.Worksheets[wb.Worksheets.AddCopy(0)];


newWorksheet.Name = newWSName;

newWorksheet.VisibilityType = VisibilityType.Visible;

wb.Worksheets.ActiveSheetIndex = wb.Worksheets.Count - 1;


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

}


// Update Charts Sources

foreach (Chart cht in wb.Worksheets[wb.Worksheets.ActiveSheetIndex].Charts)

{

cht.PivotSource = cht.PivotSource.Replace(“Base”, newWSName);

cht.RefreshPivotData();

}

foreach (Chart cht in wb.Worksheets[0].Charts)

{

cht.RefreshPivotData();

}


return;

}



18

Hi,


We have fixed this issue. Please download and use the latest fix: Aspose.Cells for .Net V 7.2.0.8 and let us know your feedback.

Thank you for your patience
19

Thankyou. As before this fixed the problem in the Console App and Base.xslx i sent you but still fails in my application with the same error. I haven't been able to work out why but if i use the Base.xslx from my application with the the Console app it produces the erroneous output.

I've attached a new Base.xslx

Can you run the console app with this file and you should see that the output Template.xslx displays that error when you open it in Excel.

It's possible this error is not occurring all the time. I'm sure that the first time i ran my application with the new Aspose.dll 7.2.0.8 that it worked, but only the once... It never worked again. I even tried rebooting my machine to see if i could get it to work a second time and i couldn't

Good luck, Scott

20

Hi,

Thanks for your feedback.

After running the following code, I can replicate the bug using your newer file:

Attachment: Base.xlsx (new file)

However, it worked with with your older file.

Attachment: Base.xlsx (older file)

I am attaching all the files and the code alongwith screenshot for a reference.

We have logged the comment in our database. Once this issue is fixed, we will update you asap.

The comment has been logged against the issue id: CELLSNET-40605

C#


static int Main()

{

string outputFile = “Template.xlsx”;


File.Copy(“Base.xlsx”, outputFile, true);


for (int i = 1; i < 5; i++)

{

Workbook wb = new Workbook(outputFile);

Worksheet baseWS = wb.Worksheets[0];

CopyWorkSheet(wb, “CopiedWS” + i);

wb.Save(outputFile);

}


Console.WriteLine(“Press any key to continue…”);

Console.ReadKey(true);


return 0;


}

static private void CopyWorkSheet(Workbook wb, string newWSName)

{

Worksheet newWorksheet = wb.Worksheets[wb.Worksheets.AddCopy(0)];


newWorksheet.Name = newWSName;

newWorksheet.VisibilityType = VisibilityType.Visible;

wb.Worksheets.ActiveSheetIndex = wb.Worksheets.Count - 1;


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

}


// Update Charts Sources

foreach (Chart cht in wb.Worksheets[wb.Worksheets.ActiveSheetIndex].Charts)

{

cht.PivotSource = cht.PivotSource.Replace(“Base”, newWSName);

cht.RefreshPivotData();

}

foreach (Chart cht in wb.Worksheets[0].Charts)

{

cht.RefreshPivotData();

}


return;

}



Screenshot: