Setting an Excel cell value to a decimal in C#. Differences between .NET 2 and .NET 4

Question

I have this code (this is a simplified version of the real code) that opens excel and set the value of a cell. Please note that I am in Italy and we use , instead of . to separate the decimal digits. This means that I use 12.5 in the code, but the result of ToString() on this number is "12,5" and Excel shows 12,5, too.

using Xls = Microsoft.Office.Interop.Excel;

public class ExcelTest
{
    public static void Test
    {
        object hmissing = System.Reflection.Missing.Value;
        // create an instance of Excel and make it visible            
        Xls.Application anApp = new Xls.ApplicationClass();
        anApp.Visible = true;
        // add an empty workbook
        Xls.Workbooks wbks = anApp.Workbooks;
        wbks.Add(hmissing);
        Marshal.ReleaseComObject(wbks);
        // set the value of the first cell
        Decimal d = 12.5m;
        Xls.Range aRange = anApp.get_Range("A1", hmissing);
        aRange.set_Value(Xls.XlRangeValueDataType.xlRangeValueDefault, d.ToString());
        Marshal.ReleaseComObject(aRange);
    }
}

Everything has worked fine for years using .NET 2, but when I try to switch to .NET 4 Excel warns me that the number has been stored as text.

Please note that I know that simply avoiding to call ToString() fixes the problem, but in the real case I am dealing with a legacy application that wants to store several kinds of data in excel, both basetypes and user defined ones, and ToString() was the only method available on all these types that would give the desired result in Excel without having to check for the type and deciding what to pass to set_Value.

So I'm not asking how to modify my code in order to make it work. I'm just trying to understand why the behaviour is different in the two versions of the framework. It's quite important because I'd prefer to modify my code before the users find out that there's something wrong.

Answer 1

The output of the "ToString" methode isn't set in stone and depends on the current cultureinfo your thread is running. If you are running the program on a pc with another language pack the output can change. Another reason could be that the current thread is set to certain culturinfo somewhere else in your code.

Please try the following example.

static void Main(string[] args)
{
        decimal d = 12.56m;
        // Different thread cultureinfo
        Thread.CurrentThread.CurrentCulture = 
            new System.Globalization.CultureInfo("de-DE");
        Console.WriteLine(d);
        Thread.CurrentThread.CurrentCulture = 
            new System.Globalization.CultureInfo("en-US");
        Console.WriteLine(d);

        Console.WriteLine(d.ToString(
            new System.Globalization.CultureInfo("de-DE").NumberFormat));
        Console.WriteLine(d.ToString(
            new System.Globalization.CultureInfo("en-US").NumberFormat));
        Console.Read();
}