Read data from Excel

Question

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 enters 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");

some code excerpt on how you actually read the cell would be useful for finding the problem. — Fun Mun Pieng, Mar 22 '11 at 03:44
This is the limitation if you are reading using OleDbConnection. because it always returns strongly typed objects based on column format, if you want to read data as text check this http://stackoverflow.com/questions/657131/how-to-read-data-of-an-excel-file-using-c question. — Sanjeevakumar Hiremath, Mar 22 '11 at 04:43

score 2 · Answer 1 · answered Jun 07 '11 at 11:08

2

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.

answered Jun 07 '11 at 11:08

Andrew Kalashnikov

3,073
5
34
57

2

Actually, `IMEX=1` does that (it treats all columns as text, afaik). `HDR=Yes` is something unrelated: It states that the first row of the sheet contains the column headers rather than data. – Heinzi Jun 07 '11 at 11:11

score 0 · Answer 2 · answered Nov 03 '12 at 03:43

0

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

answered Nov 03 '12 at 03:43

dicemaster

1,203
10
22

Read data from Excel

2 Answers2