CODEWITHSUNDEEP
c#asp.net
aruni
aruni

Reputation: 2752

Read excel sheet cell in C#

I try to read the .xls flie using c#.

This is my code

OleDbCommand command = new OleDbCommand("Select [Id], [Name], [Email] from [sheet$]", connection);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter .SelectCommand = command;

DataSet objDataset = new DataSet();

objAdapter .Fill(objDataset1);
DataView dv = objDataset .Tables[0].DefaultView;

for (int i = 0; i < dv.Count; i++)
{
    if (dv[i][0] != DBNull.Value ) 
    {
    }
}

but in the excel sheet cell has a green color mark it does not read.

enter image description here

It say it is null. So how can i read Like this cells?

thank you.

Upvotes: 0

Views: 1325

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

When reading Excel sheets from an OleDbCommand your worksheet must be formatted as a table, with column headers in row 1 and data underneath (your tiny screenshot doesn't seem to follow this). I don't know what kind of reading you get when the worksheet isn't set up like this, but DBNull.Value sounds like a reasonable possibility.

As for the green mark, the cell is probably formatted as text - from memory I'd say if you mouse-hover over that green mark Excel is saying "Number value is formatted as text"; if you only have a couple one of those you could try Ctrl+F1 to fix the formatting (make it General) and F2+Enter (enter cell / confirm entry) to fix the values. Of course if there are 5,000 rows of data it's not something you want to be doing - you could just copy and paste the values (i.e. not with the formats) to a new worksheet instead.

If your data starts at $A$1 and still all you get is DBNull.Value, then I'd try something like this, assuming all you're getting from the OLE driver is object:

for (int i = 0; i < dv.Count; i++)
{
    var value = dv[i][0].ToString();
    if (!string.IsNullOrEmpty(value)) 
    {
        var intValue = Convert.ToInt32(value);
        ...
    }
}

Also make sure the worksheet you want to read from, is active when the workbook is saved & closed. Or make it the only worksheet in the workbook, if that's possible.

Upvotes: 0

David Cummins
David Cummins

Reputation: 988

Based on my past experience, Excel is probably making a best guess about each column's data type, and sometimes is gets it wrong. Anything that doesn't match the presumed data type is returned as null. Look at the below, particularly with regards to the IMEX setting.

http://www.connectionstrings.com/excel#microsoft-jet-ole-db-4-0

Upvotes: 1

Related Questions