ArgumentOutOfRangeException

[Jerry Shaver]

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]

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]

>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]

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]

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]

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 >