We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Copy a range from sheet 1 to sheet 2 but only keep the values and formatting (no formulas)

Thread ID:

Created:

Updated:

Platform:

Replies:

148786 Nov 4,2019 03:29 PM UTC Nov 6,2019 12:29 PM UTC WinForms 3
loading
Tags: XlsIO
Dimitri
Asked On November 4, 2019 03:29 PM UTC

Dear Syncfusion,

I have 2 excel sheets and I am trying to copy all the data starting from row 8 from sheet 1 to sheet 2. But I only want to keep the values together with the formatting. So no formulas.

This is what I have up until now but it keeps all the formula's after its copied:

//Calculate the used range withing sheet 1.
IRange sheetRange = nonMasterDoc.Worksheets[tabName].UsedRange; //Range of non-master document

//Takes the used range of sheet 1 and copies it to sheet 2.
nonMasterDoc.Worksheets[tabName].Range[StartingIndexes[tabName], 1, sheetRange.LastRow - ((tabName == tab2Name) ? iTab2SkipRows : 0), sheetRange.LastColumn].CopyTo(MasterDocWorkbook.Worksheets[tabName].Range[MasterDocCounters[tabName], 1], ExcelCopyRangeOptions.All);

PS: I have tried to use "ExcelCopyRangeOptions.CopyValueAndSourceFormatting" because it sounded like something I need but it just made my whole file corrupt (still got backups so no worries there) and gave me an error "Value cannot be null. Parameter name hashNewParentIndexes". So apparently this is not what I thought it was.

Konduru Keerthi Konduru Ravichandra Raju [Syncfusion]
Replied On November 5, 2019 07:10 AM UTC

Hi Dimitri, 

Greetings from Syncfusion. 

ExcelCopyRangeOptions.CopyValueAndSourceFormatting works fine for you requirement, copying the values and formatting alone excluding the formulas. We have prepared a simple sample for you reference, which can be downloaded from the following link. 


We request you to modify and share us the issue reproducing sample, which will be helpful for us to investigate further and provide prompt solution at the earliest. 

Regards, 
Keerthi. 


Dimitri
Replied On November 5, 2019 10:44 AM UTC

The error occurs when I invoke "MasterDocWorkbook.Save();" While if I use "ExcelCopyRangeOptions.All" in place of "ExcelCopyRangeOptions.CopyValueAndSourceFormatting" I get no error at all.

Extra info:
Message = "Value cannot be null.\r\nParameter name: hashNewParentIndexes"
Source = "Syncfusion.XlsIO.Base"

Excel Version: 2016

Source Code: See zip file in attachments

StackTrace:
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeBlankCell(XmlWriter writer, Int32 iRowIndex, Int32 iColumnIndex, Int32 iXFIndex, Dictionary`2 hashNewParentIndexes)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeCells(XmlWriter writer, RowStorage row, CellRecordCollection cells, Dictionary`2 hashNewParentIndexes, String cellTag)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeRow(XmlWriter writer, RowStorage row, CellRecordCollection cells, Int32 iRowIndex, Dictionary`2 hashNewParentIndexes, String cellTag, Boolean isSpansNeeded)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeSheetData(XmlWriter writer, CellRecordCollection cells, Dictionary`2 hashNewParentIndexes, String cellTag, Dictionary`2 additionalAttributes, Boolean isSpansNeeded)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeSheetDataSet(XmlWriter writer, ExternWorkbookImpl book)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeExternalLink(XmlWriter writer, ExternWorkbookImpl book)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeLinkItem(XmlWriter writer, ExternWorkbookImpl book)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SerializeExternalLink(ExternWorkbookImpl externBook)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeLink(ExternWorkbookImpl externBook, XmlWriter writer, RelationCollection relations)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeBookExternalLinks(XmlWriter writer, RelationCollection relations)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeWorkbook(XmlWriter writer, Stream streamStart, Stream streamEnd, List`1 lstBookViews, RelationCollection relations, Dictionary`2 cacheFiles, Stream functionGroups, List`1 lstCustomBookViews)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveWorkbookPart(Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveWorkbook(ExcelSaveType saveAsType)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(ExcelSaveType saveType)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(Stream stream, ExcelSaveType saveType)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(String filename, ExcelSaveType saveType)
   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.Serialize(String fullName, WorkbookImpl book, ExcelSaveType saveType)
   at Syncfusion.XlsIO.Implementation.WorkbookImpl.SaveAs(String FileName, ExcelSaveType saveType, ExcelVersion version)
   at Syncfusion.XlsIO.Implementation.WorkbookImpl.SaveAs(String FileName, ExcelSaveType saveType)
   at Syncfusion.XlsIO.Implementation.WorkbookImpl.Save()
   at UpdateMasterExcelDocument.VerwerkingsInfoCollection.GenerateInfo() in ..\VerwerkingsInfoCollection.cs:line 153
   at UpdateMasterExcelDocument.VerwerkingsInfoCollection..ctor() in ..\VerwerkingsInfoCollection.cs:line 26
   at UpdateMasterExcelDocument.MainForm.btnProcessDocuments_Click(Object sender, EventArgs e) in ..\MainForm.cs:line 306

Attachment: UpdateMasterExcelDocument_2b24faa9.zip

Konduru Keerthi Konduru Ravichandra Raju [Syncfusion]
Replied On November 6, 2019 12:29 PM UTC

Hi Dimitri, 

We have tried to reproduce the reported exception through the provided stack trace but, we are unable to reproduce the issue. As this is document specific issue, we request you to share the MasterDocWorkbook and nonMasterDoc Excel documents used at your end, which will be helpful for us to investigate further and provide prompt solution at the earliest. 

Regards, 
Keerthi. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon