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

[Dimitri]

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]

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. 

Sample Link: https://www.syncfusion.com/downloads/support/forum/148786/ze/F1487861973956735.zip  

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]

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]

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.