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.
Summary:
Data retrieved from an access database. Read by c# application and written to an Excel spreadsheet.
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.
Observed behaviour:
English setup with default Options --> International - as expected
German setup with default Options --> International- as expected
English setup with decimal separator changed to ",", thousands separator set to "." and System Separators unticked in Options --> International - Excel data incorrect. See asterisked rows.
Data Excel
3706888.0300 3706888.03
2587033.8000 2587033.8
2081071.1800 2081071.18
9030160.3333 90.301.603.333**
42470.9842 424.709.842**
4465546.2800 4465546.28
1436037.3200 1436037.32
111650.0000 111650
2567007.0833 25.670.070.833**
I have attcahed sample code to demonstrate this behaviour. If anybody can show me what I am doing wrong, it would be much appreciated.
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.
Many Thanks
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;
}
}
}