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

Question

I have this code (this is a simplified version of real code) that opens excel and sets the cell value. Please note that I am in Italy and we use , instead . to separate 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 also shows 12.5.

 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 worked fine for many years using .NET 2, but when I try to switch to .NET 4, Excel warns me that the number was saved as text.

Please note that I know that just avoiding the call to ToString () fixes the problem, but in the real case I am dealing with an outdated application that wants to store several types of data in excel, both in banquets and in user-defined ones, and ToString ( ) was the only method available for all of these types that would give the desired result in Excel without having to check the type and decide whether to pass set_Value.

So I am not asking how to change my code to make it work. I'm just trying to understand why the behavior is different in the two versions of the framework. This is very important because I would prefer to change my code before users find out that something is wrong.

Answer 1

The output of the "ToString" method is not set in the stone and depends on the current cultinfo in which your stream works. If you run the program on a computer with a different language pack, the output may change. Another reason may be that the current thread is set for a specific culturinfo elsewhere 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(); } 

Answer 2

Try the following:

  Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Open(@"c:\test.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; decimal dec = 12.5m; worksheet.get_Range("A1").Value = dec; DateTime date = DateTime.Now.Date; worksheet.get_Range("A2").Value = date; string str = "Hello"; worksheet.get_Range("A3").Value = str; Marshal.FinalReleaseComObject(worksheet); workbook.Save(); workbook.Close(false, Type.Missing, Type.Missing); Marshal.FinalReleaseComObject(workbook); excel.Quit(); Marshal.FinalReleaseComObject(excel);