Locale / Culture



Visual Studio 2010 and Later



Visual Studio 2008 and Earlier

When programming against Excel using managed code you could get certain methods and properties behaving differently depending on the locale of the current thread.
This problem only occurs in COM Add-ins, Automation add-ins and Automation executables.
This problem did not occur in document level solutions or add-ins built using VSTO.
If you want to define a formula for a Range and you are in the french locale you must use localised french formula names.

Range["A1"].Formula = "SOMME(3;4)"; 

Excel expects localized strings for date formats, number formats and formula names.


You could get around this issue by accessing these properties using reflection.
Reflection enables you to specify an English locale and write code that will work regardless.

Excel.Range range1 = sheet.Range["A1"]; 
typeof(Excel.Range).InvokeMember("Formula",
                System.Reflection.BindingFlags.Public |
                System.Reflection.BindingFlags.Instance |
                System.Reflection.BindingFlags.SetProperty,
                null, range1,
                new object[]{"=SUM(10,20)"},
                System.Globalization.CultureInfo.GetCultureInfo(1033));



VSTO Solutions

VSTO solutions solve this locale problem by using a transparent proxy that sits between Excel and VSTO.
This proxy forces a US English locale which matches the behaviour in VBA.
A new attribute was added to the AssemblyInfo.cs file to resolve this issue.
The default value for this attribute is true.
This transparent proxy can:

  • slow the code slightly

  • cause objects to look slightly different in the debugger

This proxy was removed in .NET 4.0
You can bypass this proxy by setting the assenbly level attribute ExcelLocale1033 to false in AssemblyInfo.cs

[assembly: ExcelLocale1033(false)] 

Or alternatively if you only want to bypass for a particular method you can use:
Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.UnWrap method
This method returns the raw PIA object which exposes you to the locale issue.



Handle Culture Issues

CultureInfo baseCulture = System.Threading.Thread.CurrentThread.CurrentCulture; 
Thread.CurrentThread.CurrentCulture = new CultureInfo(xlapp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI));
// do something
System.Threading.Thread.CurrentThread.CurrentCulture = baseCulture;




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext