title: C# - Excel Number Formatting Issue with International settings
body: <p>I am trying to write to an Excel 2003 spreadsheet using c# 3.5. However I am unable to get this to function correctly across different country settings. The country settings are either English or German. These two settings have different decimal and thousands settings. Everything works fine unless a user has changed the decimal and thousands separators in the International settings of the Options screen. Can anybody help as I feel I can no longer see the wood for the trees and am missing something obvious.</p>
  
  <p>Summary:</p>
  
  <p>Data retrieved from an access database.
  Read by c# application and written to an Excel spreadsheet.</p>
  
  <p>Excel Version 2003
  Machines are either English or German.
  It is possible that decimal and thousands separators have been changed within the International Settings options in Excel -- This is where the problem occurs.</p>
  
  <p>Observed behaviour:<br>
  English setup with default Options --> International - as expected<br>
  German setup with default Options --> International- as expected  </p>
  
  <p>English setup with decimal separator changed to ",", thousands separator set to "." and System Separators unticked in Options --> International - Excel data incorrect. See asterisked rows.</p>
  
  <p>Data                    Excel </p>
  
  <p>3706888.0300            3706888.03<br>
  2587033.8000            2587033.8<br>
  2081071.1800            2081071.18<br>
  9030160.3333            90.301.603.333**<br>
  42470.9842              424.709.842**<br>
  4465546.2800            4465546.28<br>
  1436037.3200            1436037.32<br>
  111650.0000             111650<br>
  2567007.0833            25.670.070.833**  </p>
  
  <p>I have attcahed sample code to demonstrate this behaviour. If anybody can show me what I am doing wrong, it would be much appreciated. </p>
  
  <p>To run this code sample, just create a new Windows forms application and post the code below into Form1.cs. You will also need to add a reference to Microsoft.Office.Interop.Excel.</p>
  
  <p>Many Thanks</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Windows.Forms;
  using Microsoft.Office.Interop.Excel;
  using System.Runtime.InteropServices;
  using System.Threading; 
  using System.Globalization;
  
  
  namespace WindowsFormsApplication1
  {
      public partial class Form1 : Form
      {
          public Form1()
          {
              InitializeComponent();
          }
  
          public void ExportDTToExcel()
          {
  
              Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
              app.Visible = true;
              Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
              Worksheet ws = (Worksheet)wb.ActiveSheet;
  
  
              string culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();//"en-GB";
              CultureInfo ci = new CultureInfo(culture);
  
              string excelGroupSeparator = app.ThousandsSeparator.ToString();
              string excelDecimalSeparator = app.DecimalSeparator.ToString();
              bool systemseparators = app.UseSystemSeparators  ;
              if (app.UseSystemSeparators == false)
              {
                  app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
                  app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
                  //ci.NumberFormat.NumberDecimalSeparator = app.DecimalSeparator;
                  //ci.NumberFormat.NumberGroupSeparator = app.ThousandsSeparator;
              }
              //app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
              //app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
  
  
              app.UseSystemSeparators = true;
  
  
              // Content.   
  
              try
              {
                  SetCellValue("3706888.0300", ws, 0, 0, ci);
                  SetCellValue("2587033.8000", ws, 1, 0, ci);
                  SetCellValue("2081071.1800", ws, 2, 0, ci);
                  SetCellValue("9030160.3333", ws, 3, 0, ci);
                  SetCellValue("42470.9842", ws, 4, 0, ci);
                  SetCellValue("4465546.2800", ws, 5, 0, ci);
                  SetCellValue("1436037.3200", ws, 6, 0, ci);
                  SetCellValue("111650.0000", ws, 7, 0, ci);
                  SetCellValue("2567007.0833", ws, 8, 0, ci);
  
              }
              catch (Exception e)
              {
  
  
                      MessageBox.Show(e.Message);
  
              }
  
              //wb.SaveAs(Filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
              //wb.Close(false, Type.Missing, false);
              app.DecimalSeparator = excelDecimalSeparator;
              app.ThousandsSeparator = excelGroupSeparator;
              app.UseSystemSeparators = systemseparators;
              //app.Quit();
              Marshal.ReleaseComObject(app);
              Marshal.ReleaseComObject(wb);
              Marshal.ReleaseComObject(ws);
              app = null;
              wb = null;
              ws = null;
  
  
          }
  
          private static void SetCellValue(string data, Worksheet ws,int row, int col, CultureInfo ci)
          {
  
  
                  double val;
                  try
                  {
                      val = Convert.ToDouble(data);
                      Console.WriteLine(val);
  
                  }
                  catch (Exception e)
                  {
  
                      //Util.Log("Null Value ignored.", LogType.ERROR);
                      return;
                  }
  
                  try
                  {
                      string s = val.ToString();
                      ws.Cells[row + 2 , col + 1] = s;
  
                      //Util.Log("S:" + s, LogType.ERROR);
                  }
                  catch
                  {
                      //Util.Log("Null Value ignored.", LogType.ERROR);
                  }
              }
  
          private void button1_Click(object sender, EventArgs e)
          {
              this.Cursor = Cursors.WaitCursor;
              ExportDTToExcel();
              this.Cursor = Cursors.Default;
          }
          }
      }
  </code></pre>