5

I have the following C# code:

static void Main(string[] args)
{
    double a = 1.2d;
    double b = 3.1d;
    double c = 0.17241379310344829;
    double d = 0.25d;

    Console.WriteLine("{0:G17}", a * b * c * d);

    string CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
    using(var conn = new OleDbConnection(string.Format(CONNECTION_STRING, @"C:\TEMP\anyExcelFile.xlsx")))
    {
        conn.Open();

        using(var sqlCmd = conn.CreateCommand())
        {
            sqlCmd.CommandType = System.Data.CommandType.Text;
            sqlCmd.CommandText = "INSERT INTO [Sheet1$A1:A] VALUES (1)";
            sqlCmd.ExecuteNonQuery();
        }
    }

    Console.WriteLine("{0:G17}", a * b * c * d);

}

When I run it, the Console.WriteLine gives me the following results:

0.16034482758620688
0.16034482758620691

You need to have a excel file on C:\TEMP\anyExcelFile.xlsx (with a sheet named Sheet1) to be able to run it.

If I comment the ExecuteNonQuery line, and run again, I get:

0.16034482758620688
0.16034482758620688

Why executing excel causes double operation to return different results?

ecsousa
  • 158
  • 6
  • 1
    @Bjørn-RogerKringsjå there is nothing calculated in excel. Just "1" written to a cell. – dognose Jul 08 '15 at 15:31
  • The value written to Excel is not important. The weird is that, after using OleDB, the double operation in .NET environment have its results changed. You should look to values written to console. – ecsousa Jul 08 '15 at 15:36
  • I think Excel OleDB driver must be changing some configuration to current process or the AppDomain, that causes double operations to have different precision. But I wasn't able to figure out what. – ecsousa Jul 08 '15 at 15:43
  • 1
    @ecsousa Reproduced. I stepped through the code and it seems like it's the precision and not the formatting that has changed. This is very weird. Kudos to you for a great question! :) – Bjørn-Roger Kringsjå Jul 08 '15 at 15:51
  • @ecsousa It seems to be *"thread specific"*. Run the last line in a new thread, like `Task.Run(() => { Console.WriteLine("{0:G17}", a * b * c * d); });`, and it will calculate the "correct" value. – Bjørn-Roger Kringsjå Jul 08 '15 at 16:01
  • I've just noticed that too. And also, if I run the Ole command in a different thread, the second double calculation does not have its results changed. I will go with this work around to fix my issue. Thanks!! – ecsousa Jul 08 '15 at 16:09
  • 4
    Maybe also look at this post: same Issue it seems: http://stackoverflow.com/questions/16916073/the-microsoft-ace-driver-changes-the-floating-point-precision-in-the-rest-of-my – dognose Jul 08 '15 at 16:10

0 Answers0