I am trying to read data from an excel file. It was successful, but I solved the problem. Whenever the cell format and the data entered into the cell do not match, I get empty data
eg
If the data cell is formatted as Date - dd/mm/yyyyand the user enters 13/17/2011, the date format and the entered date are inconsistent, so excel gives me a completely empty cell. Only if the cell format is text, do I get the data as input.
Why does the excel file give me an empty cell in case the date format entered does not match the set cell set?
This is the code that reads 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");
source
share