I faced such a problem. I trying to read Excel file data, all are as a string. I used code bellow.
try
{
var connectionString = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"", session["FilePath"] );
using (var adapter = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM [Sheet1$]", connectionString ))
{
var ds = new DataSet();
adapter.Fill( ds, "workBook" );
workBook = ds.Tables["workBook"];
}
if (workBook == null)
throw new Exception( "Could not load imported spreadsheet!" );
if (workBook.Rows.Count <= 0)
throw new Exception( "You are use an empty spreadsheet!" );
foreach (DataColumn column in workBook.Columns)
column.ColumnName = column.ColumnName.Trim();
}
catch (Exception exc)
{
}
All worked fine, I was getting a datatable with data as a string data type and was parsing them on program level (I just have a mixed data types in one column). But when the cell have a Number format and value of this cell, for example, is 0589, I need to add a leading apostrophe in a cell because 0 must be present in 4-digit number. When I tried read such excel file using a IMEX parameter 1, I have got NULL value from this cell. I don't understand why, I read all data as a string data type.
