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
close icon

Error when using "Range.CopyTo" between different files

Hello there,I am using XIsIO in my application,but I got an error with these steps(language is C#):
1,open "a.xlsx" and "b.xlsx" as:
ExcelEngine engine=new ExcelEngine();
IApplication excelApp=engine.Excel;
IWorkbook aBook=excelApp.Workbooks.Open("a.xlsx");//there is 1 sheet in a.xlsx
IWorkbook bBook=excelApp.Workbooks.Open("b.xlsx");//there is 1 sheet in b.xlsx
2,create a new sheet in "a.xlsx" as:
IWorksheet aNewSheet=aBook.Worksheets.Create();
3,copy data of the sheet in "b.xlsx" into "aNewSheet" as:
IWorksheet bSheet=bBook.Worksheets[0];
bSheet.UsedRange.CopyTo(aNewSheet.Range["A1"],ExcelCopyRangeOptions.All);
4,save and close items
bBook.Close();
aBook.SaveAs("newA.xlsx");//Error
aBook.Close();
 
In the step 4,I got an Exception(my computer is Japanese OS ,so the error message is Japanese):
指定された引数は、有効な値の範囲内にありません。
パラメータ名: reference
   場所 Syncfusion.XlsIO.Implementation.WorkbookImpl.IsExternalReference(Int32 reference)
   場所 Syncfusion.XlsIO.Implementation.FormulaUtil.HasExternalReference(Ptg[] ptg)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeSimpleFormula(XmlWriter writer, FormulaRecord formulaRecord, CellRecordCollection cells)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeCell(XmlWriter writer, BiffRecordRaw record, RowStorageEnumerator rowStorageEnumerator, CellRecordCollection cells, Dictionary`2 hashNewParentIndexes, String cellTag)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeCells(XmlWriter writer, RowStorage row, CellRecordCollection cells, Dictionary`2 hashNewParentIndexes, String cellTag)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeRow(XmlWriter writer, RowStorage row, CellRecordCollection cells, Int32 iRowIndex, Dictionary`2 hashNewParentIndexes, String cellTag, Boolean isSpansNeeded)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeSheetData(XmlWriter writer, CellRecordCollection cells, Dictionary`2 hashNewParentIndexes, String cellTag, Dictionary`2 additionalAttributes, Boolean isSpansNeeded)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.Excel2007Serializator.SerializeWorksheet(XmlWriter writer, WorksheetImpl sheet, Stream streamStart, Stream streamConFormats, Dictionary`2 hashXFIndexes)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.SerializeWorksheetPart(WorksheetImpl sheet, Dictionary`2 hashNewXFIndexes)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.SerializeWorksheet(WorksheetImpl sheet, Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveWorksheet(WorksheetImpl sheet, String itemName, Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveSheet(WorksheetBaseImpl sheet, String itemName, RelationCollection relations, String workbookPath, Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveSheets(RelationCollection relations, String workbookItemName, Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveWorkbookPart(Dictionary`2 hashNewXFIndexes, Dictionary`2 cacheFiles)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveWorkbook(ExcelSaveType saveAsType)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(ExcelSaveType saveType)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(Stream stream, ExcelSaveType saveType)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.SaveDocument(String filename, ExcelSaveType saveType)
   場所 Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.Serialize(String fullName, WorkbookImpl book, ExcelSaveType saveType)
   場所 Syncfusion.XlsIO.Implementation.WorkbookImpl.SaveAs(String FileName, ExcelSaveType saveType, ExcelVersion version)
   場所 Syncfusion.XlsIO.Implementation.WorkbookImpl.SaveAs(String FileName)
 
 
If only doing copy "UsedRange" to different sheets in same XLSX file,it will be ok.
Any ideas?

3 Replies

ZH zhangjunchen January 9, 2013 04:58 AM UTC

My Syncfusion version is 10.302.0.43


ZH zhangjunchen January 11, 2013 01:31 AM UTC

After some checking,I found that the real problem is:
"b.xlsx" has two sheets:b1,b2  ,and there's formula in b1's cell(B33) like this:
"=COUNTIF(B2:B32,"="&b2!$A$5)"
 
"a.xlsx" only has one sheet:a1
If doing "copyto" on b1's B33,then the error will come out.
Must I copy the sheet "b2" to "a.xlsx" if I want to copy "b1"?
 
Now I solve this problem by doing these steps:
1,get the real data of cell B33 as:
double b33Value=b1Sheet.Range["B33"].FormulaNumberValue;
2,set the data to "a.xlsx"
aSheet.Range["B33"].Number=b33Value;
 
Any other solution?


MM Manikandan M Syncfusion Team January 15, 2013 12:34 PM UTC

Hi Zhangjunchen,

Thanks for using Syncfusion products.

We are able to see the exception "Specified argument was out of the range of valid values" from our side.we suspect this could be a defect. Could you please create the new incident for further follow up on this query? So that we could proceed on this

Thank you very much for your patience.

Please let us know if you need any clarifications.

Thanks,
Manikandan.M

Loader.
Live Chat Icon For mobile
Up arrow icon