Help with odd behavior reading excel dates via ole db connection

I'm using the following code to import a simple excel spreadsheet into a dataset via an ole db connection. I'm seeing different behavior when reading dates from the spreadsheet depending on开发者_运维知识库 whether the spreadsheet is opened by another process or not.

The behavior can easily be re-produced. Create an xlsx file with a header row and one row of data with a date field. Try 1/1/2011. Leave the spreadsheet open and run the following code and inspect the xml output. You will see the value 40544 where 1/1/2011 should have been, if you close the spreadsheet and run the same code you will actually see 1/1/2011 in the xml output. Any ideas or suggestions on how to get consistent behavior?

var fileName = @"C:\Test.xlsx";
var connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;""", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds);

ds.WriteXml(@"c:\test.xml");

Create an xlsx file with a header row

Check.

var connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;""", fileName);

Huh? :)

Change HDR (header) to Yes and I think you'll see it fixes the problem.