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. Image for the cookie policy date

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

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.

3 Replies

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team 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. 



DI Dimitri 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.\rParameter 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


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team 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. 


Loader.
Live Chat Icon For mobile
Up arrow icon