Excel file created with OleDbConnection uses invalid CultureInfo

Question

I'm using an OleDbConnection to create an Excel file:

  String bewegungenDateiname = System.IO.Path.ChangeExtension(System.IO.Path.GetTempFileName(), ".xls");
  string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
  + System.IO.Path.GetDirectoryName(bewegungenDateiname) + @"\" + System.IO.Path.GetFileName(bewegungenDateiname)
  + @";Extended Properties='Excel 8.0;HDR=YES'";
  using (System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(strConnectionString))
  using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("", objConn))
  {
    objConn.Open();

    cmd.CommandText = "CREATE TABLE [Test] ([MyDecimal] DECIMAL NULL)";
    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
    Decimal value = 12.34m;
    cmd.Parameters.AddWithValue("@P01", value);
    cmd.CommandText = "INSERT INTO [Test$] ([MyDecimal]) VALUES (@P01)";
    cmd.ExecuteNonQuery();
  }
  System.Diagnostics.Process.Start(bewegungenDateiname);

Now when Excel 2013 opens the Excel file it will Show:

MyDecimal
1234

So in my case Excel is losing the dot. Now I'm running a german Version of Windows/Office and if I use the following line to add the Parameter it will work:

    cmd.Parameters.AddWithValue("@P01", value.ToString());

German localization of numbers uses a colon instead of the dot to separate the fractions from the number value (meaning 12,34 instead of 12.34). So it seems the OleDbConnection uses the wrong culture variant to write the Excel file?

I fear my Version might break with a different Version of Excel or a different locale - is there a way to fix this and get decimal values to Excel without such risks? I would use some other way to create Excel files, if it is without this flaw.

score 1 · Answer 1 · edited Oct 03 '13 at 18:23

1

With Excel 2013 try using the following:

strConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=No;IMEX=1""", _filePath)

I have no idea if it will solve the problem.

edited Oct 03 '13 at 18:23

bland

1,968
1
15
22

answered Oct 03 '13 at 17:54

Pierre Gauvin

11
1

If I use that I get an Exception when ob objConn.Open(): Error E_FAIL(0x80004005) in IErrorInfo.GetDescription. – Sam Oct 04 '13 at 07:59
I tried to use parts of your Connection string (ACE instead of Jet, 12 instead of 4 or 8, both, Imex=1 - some crashed and those which did not Crash did not Change my Problem :/ – Sam Oct 04 '13 at 08:05

Excel file created with OleDbConnection uses invalid CultureInfo

1 Answers1