While reading Excel using OLEDB, if any formatting has been done on the excel sheets, the OLEDB driver is unable to read some values which results in getting null values.
OLEDB connection string in code:
var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + fileNameWithPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
IMEX=1 reads the excel.
you can google out for more details on IMEX and HDR values.
This connection string is prefect when there is no formatting done on the excel sheet.
For reading the whole excel with any formatting on excel, one registry entry needs to be changed:
Run->RegEdit->Computer->HKEY_LOCAL_MACHINE->SOFTWARE->WOW6432Node->Microsoft->Office->12.0->Access Connectivity Engine->Engines->Excel
Change the settings TypeGuessRows=0, by default this is set as 8.
NOTE: if the connection states Excel12.0, then the reg entry of "Office->12.0" needs to be changed, else it will not work
After googling for 2 days, finally found the solution here:
http://righthelp.xyz/question/3726720/values-changing-when-importing-from-excel-with-c-oledb
who found the answer here
Thanks to this anon blog poster and to Andrew Garrison who saved my day!!
