Read data from Excel

I have been trying to read data from an excel file. It has been successful, but I enountered a problem. Whenever the format of the cell and the data entered in the cell is not matching then I get empty data

e.g

If the data cell is formatted as Date - dd/mm/yyyy, and the user ente开发者_JAVA百科rs 13/17/2011, the as the date format and the date entered is contradictory so the excel gives me entirely empty cell. Only if the cell format is text I get the data as entered.

Why is the excel file giving me empty cell in case the entered date format is not complying with the cell format set?

This is the code that reads the excel data

if(fileEXT.Equals(".xls"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0");
}
else if(fileEXT.Equals(".xlsx"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=\"Excel 12.0;HDR=YES;\"");
}
else if(fileEXT.Equals(".xlsm"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 12.0 Macro");
}

oledbConn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds,"LocationDetails");

You can change your connection string to

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1"

HDR=Yes;IMEX=1

tells OldDb driver that data at columns in defferent format.

Instead of OLEDB, I would suggest using EPPLus library to handle your excel files.It is very easy and comprehensive. Nuget package is also available for the same. http://nuget.org/packages/EPPlus