CODEWITHSUNDEEP
c#asp.netexceloledb
RedEagle
RedEagle

Reputation: 21

White spaces and oledb

I'm reading an excel file using OleDb on ASP. NET (C#). All the information is returned ok and I was surprised to see that even the cell type defined on the Excel file is returned to my code. The problem is that I have column where all the cells "general" types and since the values are only numbers Excel assumes it to be Number. If there are no spaces olebd driver returns the right value to my code but if there are space it returns ""...

Here's how I'm getting the information:

OleDbConnection oleDbConn = new OleDbConnection(connString);
oleDbConn.Open();
OleDbCommand oleDbComm = new OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConn);
OleDbDataAdapter oleDbDtAdapter = new OleDbDataAdapter();
oleDbDtAdapter.SelectCommand = oleDbComm;

DataSet dtSet = new DataSet();
oleDbDtAdapter.Fill(dtSet, "SMSs");
Object testZeroZero = dtSet.Tables[0].Rows[0][0];

I can't go to the Excel and change the cell type to "text" because the end user must not worry on changing this so how can I overcome this?

Regards!

Upvotes: 1

Views: 1354

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

Have you considered your connection string?

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

-- http://www.connectionstrings.com/excel

Upvotes: 3

Related Questions