Hi team, I am using below code for performing the task mentioned. The problem is, code is working fine if there are NO graphical objects (charts etc) in the sheet. And if I run the same code on excels having charts, destination file is getting corrupted. Like, when I open the saved file in Excel, it throws an error - 'We found a problem with some content ‘destination file - name’ Do you want us to try to recover as much as we can? Yes or No. If we say Yes, MS Excel is throwing another error ‘Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)’ - there after, opening the file, but with no graphs.
My objective is move copy the sheet into destination as values. And the formattings, borders, colors everything should stay as is. Can you please advise. Thanks.
public void MoveCopySheetFromSourceToDestWorkBook(Workbook srcBook, Worksheet srcSheet, Workbook destBook, bool deleteIfAlreadyExists = true)
{
if (deleteIfAlreadyExists && destBook.ContainsSheet(srcSheet.Name))
destBook.Worksheets.RemoveAt(srcSheet.Name);
CopyOptions copyOptions = new CopyOptions();
copyOptions.CopyInvalidFormulasAsValues = true;
copyOptions.CopyNames = false;
copyOptions.ReferToSheetWithSameName = false;
Worksheet destSheet = destBook.Worksheets.Add(srcSheet.Name);
destSheet.Copy(srcSheet,copyOptions);
// Copy the page setup from source to destination
destSheet.PageSetup.Copy(srcSheet.PageSetup, copyOptions);
destSheet.PageSetup.PrintArea = srcSheet.PageSetup.PrintArea;
destSheet.TabColor = srcSheet.TabColor;
if (destBook.ContainsSheet("Sheet1"))
destBook.Worksheets.RemoveAt("Sheet1");
//srcBook.Save(@"C:\Users\sv41\Desktop\New folder\source.xlsm");
destBook.Save(@"C:\Users\sv41\Desktop\New folder\destination.xlsx");
}
@SubbuatAspose,
Thank you for your query.
I have tested this issue using my own samples file with latest version Aspose.Cells for .NET 18.8 but observed no issue. Please try this scenario using latest version, and if issue is still there, share your sample source and output destination files here for our testing.
Thanks for the reply. I will upload the file once I reach office. Can you let me know whether code is correct for doing ‘values only’ export of sheet from one book to another.
@SubbuatAspose,
I have used same code as given above except destBook.ContainsSheet() function which is not found in the latest library. However your code copied text and charts fine without any error. Please share you sample files so that your issue can be recreated here and our feedback can be provided afterwards.
Hi, What I observed is, when we use the extended settings of CopyOptions object, then its creating problem. I have changed the code as below and I have now NO issues. I am attaching the template file I used. My process fetches data from SQL and updates raw data sheet. Then refresh blue tabs having lot of formulas. Finally, move the Equity tab out as a new book (report) and save. Problem was with old code while moving out the tab. Ideally setting the property values on CopyOptions object should not create issues but it is.
public void MoveCopySheetFromSourceToDestWorkBook(Workbook srcBook, Worksheet srcSheet, Workbook destBook, bool deleteIfAlreadyExists = true)
{
if (deleteIfAlreadyExists && destBook.ContainsSheet(srcSheet.Name))
destBook.Worksheets.RemoveAt(srcSheet.Name);
CopyOptions copyOptions = new CopyOptions();
// copyOptions.CopyInvalidFormulasAsValues = true;
// copyOptions.CopyNames = false;
// copyOptions.ReferToSheetWithSameName = false;
Worksheet destSheet = destBook.Worksheets.Add(srcSheet.Name);
destSheet.Copy(srcSheet,copyOptions);
// Copy the page setup from source to destination
destSheet.PageSetup.Copy(srcSheet.PageSetup, copyOptions);
destSheet.PageSetup.PrintArea = srcSheet.PageSetup.PrintArea;
destSheet.TabColor = srcSheet.TabColor;
destBook.RemoveExternalLinks();
if (destBook.ContainsSheet("Sheet1"))
destBook.Worksheets.RemoveAt("Sheet1");
destBook.Save(@"C:\Users\sv41\Desktop\New folder\destination.xlsx");
}
Another problem with Aspose - moving tabs to another book is, tab color is getting changed. Its not coming as original even if we use destSheet.TabColor = srcSheet.TabColorLiquidity - Risk Report Template.zip (2.6 MB)
@SubbuatAspose,
I have tested the issue using latest version Aspose.Cells for .NET 18.8.4 and observed no issue with and without the extended properties of CopyOptions. The output file is opened fine in Excel 2016. Please give it a try and share the feedback. If issue persists, send us complete running code, output file created using Aspose.Cells and also a file which is expected output and created using Excel. We will analyze this issue by comparing the output file with the expected output and provide our feedback. Please ensure that latest version is used and code is complete and compilable without any error.
I dont have access to download from DropBox, would you please attach the files. Thanks.
Sorry for the late reply. Thanks for giving the latest verion of DLLs. My problem is still not resolved.
Strange that same code is working on 1 excel file - to move copy a particular sheet but not working on a different excel to perform the same operation. Herewith giving you both working and non-working templates I am working on. Please help me to fix the issue. I tested the below code on both 18.8.3 and 18.8.4 versions. This is really frustrating as I am building a model framework in order to prove that I can continue building the solution with a valid procured Aspose.Cells licence.Templates.zip (2.9 MB)
Code I have for move copy operation:
public static void MoveCopySheetFromSourceToDestWorkBook(this Workbook srcBook, Worksheet srcSheet, Workbook destBook, bool deleteIfAlreadyExists = true, bool removeFormulas = true)
{
if (deleteIfAlreadyExists)
destBook.Worksheets.RemoveAt(srcSheet.Name);
CopyOptions copyOptions = new CopyOptions();
Worksheet destSheet = destBook.Worksheets.Add(srcSheet.Name);
destSheet.Copy(srcSheet,copyOptions);
// Copy the page setup from source to destination
destSheet.PageSetup.Copy(srcSheet.PageSetup, copyOptions);
destSheet.PageSetup.PrintArea = srcSheet.PageSetup.PrintArea;
destSheet.TabColor = srcSheet.TabColor;
destBook.RemoveExternalLinks();
if (removeFormulas)
destSheet.Cells.RemoveFormulas();
if (destBook.ContainsSheet("Sheet1") && destBook.Worksheets.Count > 1)
destBook.Worksheets.RemoveAt("Sheet1");
destBook.Save(@"C:\Users\sv41\Desktop\New folder\destination.xlsm");
}
Code that works on 1 excel - Liquidity - Risk Report Template.xlsm
Workbook src = new Workbook(@“C:\Users\sv41\Desktop\New folder\Liquidity - Risk Report Template.xlsm”);
Workbook dest = new Workbook();
Worksheet sht = src.WorkSheets(“Liquidity - Report”);
src.MoveCopySheetFromSourceToDestWorkBook(sht, dest);
Code that does NOT works on 1 excel - Time Series - Risk Report Template.xlsm
Workbook src = new Workbook(@“C:\Users\sv41\Desktop\New folder\Time Series - Risk Report Template.xlsm”);
Workbook dest = new Workbook();
Worksheet sht = src.WorkSheets(“FI Time Series Analysis”);
src.MoveCopySheetFromSourceToDestWorkBook(sht, dest);
What is the problem I am facing:
As i explained earlier in this ticket, destination book is get corrupted with all the graphs deleted. And sometimes if graphs comes, all the references of the graphs will go as #REF .
@SubbuatAspose,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46325 - Graphs lost while copying Worksheet from one workbook to another
**Thanks team. Will wait for your update. Just to narrow down the underlying issue, I am sharing my analysis, to help you fix the problem quickly. **
--------------------------------------------------------------------------------------------------------------------------------------------
**If the charts in sheet we are moving away, have reference data sitting in a different sheet, then the trouble. **
--------------------------------------------------------------------------------------------------------------------------------------------
@SubbuatAspose,
Thank you for the information. We have logged it with the ticket for our later reference.
@SubbuatAspose,
We have investigated the issue and have found that it is not a bug. If Aspose.Cells copy worksheet from the workbook to another workbook and the referred worksheet does not exist in the destination workbook, it will create an external link to the worksheet in the source workbook.
If you call Workbook.RemoveExternalLinks(), all external links will be removed, then the data source of the chart will be a constant array.
Hi, please note that the code we tested does include workbook.RemoveExternalLinks() but still the destination workbook is getting corrupted.
@SubbuatAspose,
Thank you for providing the feedback. We are looking into it and will provide our feedback soon.
@SubbuatAspose,
Please try our latest version/fix: Aspose.Cells For .NET v18.8.7:
Your issue should be fixed in it.
Let us know your feedback.
@SubbuatAspose,
We recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v18.8.8 (attached)
It fixes your issue.
Let us know your feedback.
Aspose.Cells18.8.8 For .Net2_AuthenticodeSigned.Zip (4.6 MB)
Aspose.Cells18.8.8 For .Net4.0.Zip (4.6 MB)
The issues you have found earlier (filed as CELLSNET-46325) have been fixed in Aspose.Cells for .NET v18.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi