Hello,
I have a very weird problem that affects some Excel spreadsheets.
Description:
We have a webapplication running were users need to upload excel spreadsheets to. The data contained in it is first checked, and if found correct saved to a database. The most excel spreadsheets work fine, some cause the web application to hang (page does not return, not even after a few minutes) This is not related to the size of those excel files as some big files with 3000 rows x 60 columns roun smoothly, others don't. The problem also occured with a excel file with only 2 rows x 60 columns.
Details:
To load the data from the spreadsheets worksheet into a typed dataset we need to check the datatype of the cell in excel. To do this we run this routine:
switch (expectedType) {
case"int":
if (worksheet.Cells[j, i].Type != CellValueType.IsNull) {
if(worksheet.Cells[j, i].Type == CellValueType.IsNumeric) {
Double tempDouble = worksheet.Cells[j, i].DoubleValue;
String tempString = tempDouble.ToString(new CultureInfo(""));
if (tempString.IndexOf(".") != -1 || tempString.Length > 9) {
formalErrorFound = true;
Response.Write("FormalError[Pos, " + (j+1) +", "+ columnName +"]: Type mismatch (Expected: integer number)");
}
} else {
formalErrorFound = true;
Response.Write("FormalError[Pos, " + (j+1) +", "+ columnName +"]: Type mismatch (Expected: integer number)");
}
}
break;
case"double":
if (worksheet.Cells[j, i].Type != CellValueType.IsNumeric &&
worksheet.Cells[j, i].Type != CellValueType.IsNull) {
formalErrorFound = true;
Response.Write("FormalError[Pos, "+ (j+1) +", "+ columnName +"]: Type mismatch (Expected: real number)");
}
break;
case"string":
/*
* No Type check when column should be of type string
*/
break;
case"date":
if (worksheet.Cells[j, i].Type != CellValueType.IsDateTime &&
worksheet.Cells[j, i].Type != CellValueType.IsNull) {
formalErrorFound = true;
Response.Write("FormalError[Pos, "+ (j+1) +", "+ columnName +"]: Type mismatch (Expected: date)");
}
break;
default:
formalErrorFound = true;
Response.Write("An internal error occurred at [Pos: Check types of values in cells] Please contact a system administrator!");
break;
}
This routine normally runs ok, but in some excel spreadsheets, it just does not finish.
I wasn't able to find any differences in excel files that worked and excel files that didn't work.
What is your suggestion with this? Is there a known problem with the Cell.Type function? Is there a better way to do what we need to do?
Regards,
Andrea Ravasi