Hi,
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
Hi Scott,
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.
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
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
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.
Hi,
Try opening it in Excel 2007 which is what I'm using.
Scott
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:
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();
}
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
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;
}
Hi,
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
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;
}
Confirmed that the issue is now fixed. Thanks very much for your efforts in fixing this problem.
Scott
The issues you have found earlier (filed as CELLSNET-40605) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.