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.
Unfortunately, activation email could not send to your email. Please try again.

Error when using "Range.CopyTo" between different files

Thread ID:

Created:

Updated:

Platform:

Replies:

106464 Jan 8,2013 11:56 PM Jan 15,2013 07:34 AM ASP.NET Web Forms (Classic) 3
loading
Tags: XlsIO
zhangjunchen
Asked On January 8, 2013 11:56 PM

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?

zhangjunchen
Replied On January 8, 2013 11:58 PM

My Syncfusion version is 10.302.0.43

zhangjunchen
Replied On January 10, 2013 08:31 PM

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?

Manikandan M [Syncfusion]
Replied On January 15, 2013 07:34 AM

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

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.

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.

;