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.
Syncfusion Feedback

ArgumentOutOfRangeException

Thread ID:

Created:

Updated:

Platform:

Replies:

44858 Jun 5,2006 05:55 PM Jun 9,2006 05:09 PM Windows Forms 5
loading
Tags: XlsIO
Jerry Shaver
Asked On June 5, 2006 05:55 PM

I am using C# to open spreadsheet and export to datatable. This works for a basic spreadsheet I create by hand, but fails when I attempt to open a spreadsheet downloaded from another program. When it fails, the squawk is "Syste.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. Parameter name: index..." I am using the statement in the syncfusion example of how to open a spreadsheet: this.dataGrid1.DataSource=sheet.ExportDataTable(sheet.UsedRange,ExcelExportDataTableOptions.ColumnNames) I''m undoubtadly violating a best practice by not first checking on a legitimate used range. Basically, I will be processing spreadsheets in which the columns are constant, but the number of rows will vary. Please indicate how to make bullet proof code to do this so I don''t throw errors at users. Thanks! Here is actual code: private void btn_OpenFile_Click(object sender, EventArgs e) { //Get Spreadsheet File Name string spreadsheetName; ofd.ShowDialog(); spreadsheetName = ofd.FileName; //MessageBox.Show(spreadsheetName); try { //Instantiate the Excel Engine ExcelEngine excelEngine = new ExcelEngine(); //Instantiate the excel application object IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(spreadsheetName); IWorksheet sheet = workbook.Worksheets[0]; this.dataGridView1.DataSource = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); } catch(System.Exception caught) { MessageBox.Show(caught.ToString()); } }

Administrator [Syncfusion]
Replied On June 6, 2006 07:50 AM

Hi Jerry, The following code would process spreadsheets in which the columns are constant, but the number of rows varies which is exported to a datagrid. sheet.ExportDataTable(1,1,sheet.UsedRange.End.Row,4,ExcelExportDataTableOptions.ColumnNames); and here is a sample which does the above:DataTableExport.zip Regarding the exception thrown when using a template for exporting, I am afraid.I was unable to reproduce the problem.Could you please provide me with a template file or modify the attached sample to show your problem so that I can further investigate on this issue. Thanks, Bharath.

Jerry Shaver
Replied On June 7, 2006 07:49 AM

>Hi Jerry, > >The following code would process spreadsheets in which the columns are constant, but the number of rows varies which is exported to a datagrid. > > > sheet.ExportDataTable(1,1,sheet.UsedRange.End.Row,4,ExcelExportDataTableOptions.ColumnNames); > > >and here is a sample which does the above:DataTableExport.zip > > >Regarding the exception thrown when using a template for exporting, I am afraid.I was unable to reproduce the problem.Could you please provide me with a template file or modify the attached > >sample to show your problem so that I can further investigate on this issue. > >Thanks, >Bharath. > Bharath, Here is the spreadsheet and the code. I actually recreated the C# project from scratch just to confirm that there wasn''t some special problem with the original.

Jerry Shaver
Replied On June 7, 2006 07:52 AM

Barath, Not sure if the attachment made it on the previous post. Just in case it did not, here it is. (I included two spreadsheets. Opening either one produces the error on my system. Thanks in advance for your good help. We have high hopes for placing XLsIO in service here. Jerry > >>Hi Jerry, >> >>The following code would process spreadsheets in which the columns are constant, but the number of rows varies which is exported to a datagrid. >> >> >> sheet.ExportDataTable(1,1,sheet.UsedRange.End.Row,4,ExcelExportDataTableOptions.ColumnNames); >> >> >>and here is a sample which does the above:DataTableExport.zip >> >> >>Regarding the exception thrown when using a template for exporting, I am afraid.I was unable to reproduce the problem.Could you please provide me with a template file or modify the attached >> >>sample to show your problem so that I can further investigate on this issue. >> >>Thanks, >>Bharath. >> > >Bharath, > >Here is the spreadsheet and the code. I actually recreated the C# project from scratch just to confirm that there wasn''t some special problem with the original.

CRCXLSTest.zip

Administrator [Syncfusion]
Replied On June 8, 2006 06:23 AM

Hi Jerry, Thanks for sending the sample to show your problem.I was able to reproduce the problem. Please use the following code to preserve the styles in the template worksheet: IWorkbook workbook = application.Workbooks.Open(spreadsheetName,ExcelParseOptions.SkipStyles); Here is a sample for your reference: CRCXLSTest.zip Please take a look at the modified sample and let me know if have any other queries. BestRegards, Bharath

Jerry Shaver
Replied On June 9, 2006 05:09 PM

Thanks so much. This works great. I want to buy this product! You guys rock. >Hi Jerry, > >Thanks for sending the sample to show your problem.I was able to reproduce the problem. Please use the following code to preserve the styles in the template worksheet: > > >IWorkbook workbook = application.Workbooks.Open(spreadsheetName,ExcelParseOptions.SkipStyles); > > > >Here is a sample for your reference: CRCXLSTest.zip > >Please take a look at the modified sample and let me know if have any other queries. > >BestRegards, > >Bharath >

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.

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

;