How to prevent ADO.NET from altering double values when it reads from Excel files
- by Khnle
I have the following rows in my Excel input file:
Column1 Column2
0-5 3.040
6 2.957
7 2.876
and the following code which uses ADO.NET to read it:
string fileName = "input.xls";
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
try
{
var dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
var dbReader = dbCommand.ExecuteReader ();
while (dbReader.Read())
{
string col1 = dbReader.GetValue(0).ToString();
string col2 = dbReader.GetValue(1).ToString();
}
}
finally
{
dbConnection.Close();
}
The results are very disturbing. Here's why:
The values of each column in the first time through the loop:
col1 is empty (blank)
col2 is 3.04016411633586
Second time:
col1 is 6
col2 is 2.95722928448829
Third time:
col1 is 7
col2 is 2.8763272933077
The first problem happens with col1 in the first iteration. I expect 0-5. The second problem happens at every iteration with col2 where ADO.NET obviously alters the values as it reads them. How to stop this mal-behavior?
