Reading excel file using OLEDB Data Provider

Solution 1:

This worked for me

        using (OleDbConnection conn = new OleDbConnection())
        {
            DataTable dt = new DataTable();
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
            + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
            using (OleDbCommand comm = new OleDbCommand())
            {
                comm.CommandText = "Select * from [" + sheetName + "$]";
                comm.Connection = conn;
                using (OleDbDataAdapter da = new OleDbDataAdapter())
                {
                    da.SelectCommand = comm;
                    da.Fill(dt);
                    return dt;
                }
            }
        }

The MAXSCANROWS=0 overrides the registry default and scans all rows before determining types. IMEX=1 still needs to be included.

For example, given this table:

Header | Header
------ | ------
Cell1  | 2456354
Cell2  | 2456354
Cell3  | 2456354
Cell4  | 2456354
Cell5  | 2456354
Cell6  | 2456354
Cell7  | 2456354
Cell8  | 2456354
Cell9  | A5341

The following connection strings will lose A5341

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path 
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;MAXSCANROWS=0'"

But it works when it has both.

Solution 2:

You need to set value for TypeGuessRows Registry key to 0, this way driver will set data type based on all column values instead of first 8 (default).

The location of the key differs from version to version of driver, you can easily Google it based on your specific version. For example for Access Connectivity Engine 2007 it would be

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

By the way, you do not need Jet to read XLS files, ACE is perfectly capable of this as well.