I can not read an Excel cell having a leading apos

Question

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.

Answer 1

As I fixed that, before loading spreadsheet I set the registry key TypeGuessRows to zero on the program level and after loading back to 8 (in case other programs will use it).

Answer 2

Change the number format of the cells to "0000" for a number that will always be 4 digits and retain the leading zeros.