Formatting Data in Excel with Various Regional Settings

You must format all data that has locale-sensitive formatting, such as dates and currency, using the English (United States) data format (locale ID 1033) before you pass it to Microsoft Office Excel or read the data from code in your Office project.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

By default, when you develop an Office solution in Visual Studio, the Excel object model expects locale ID 1033 data formatting (this is also called locking the object model to locale ID 1033). This behavior matches the way that Visual Basic for Applications works. However, you can modify this behavior in your Office solutions.

By default, Office solutions that you create by using Visual Studio are not affected by the end user's locale settings, and always behave as though the locale is English (United States). For example, if you get or set the Value2 property in Excel, the data must be formatted the way that locale ID 1033 expects. If you use a different data format, you might get unexpected results.

Even though you use the English (United States) format for data that is passed or manipulated by managed code, Excel interprets and displays the data correctly according to the end user's locale setting. Excel can format the data correctly because the managed code passes locale ID 1033 along with the data, which indicates that the data is in English (United States) format and therefore must be reformatted to match the user's locale setting.

For example, if end users have their regional options set to the German (Germany) locale, they expect the date June 29, 2005, to be formatted this way: 29.06.2005. However, if your solution passes the date to Excel as a string, you must format the date according to English (United States) format: 6/29/2005. If the cell is formatted as a Date cell, Excel will display the date in German (Germany) format.

In projects that target the .NET Framework 4, the common language runtime (CLR) automatically passes locale ID 1033 to all methods and properties in the Excel object model that accept locale-sensitive data. There is no way to change this behavior automatically for all calls into the object model. However, you can pass a different locale ID to a specific method by using InvokeMember to call the method and by passing the locale ID to the culture parameter of the method.

In projects that target the .NET Framework 3.5, the Visual Studio Tools for Office runtime passes locale ID 1033 to the Excel object model by default. However, you can modify this behavior by using the Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute

You can unlock the locale ID behavior for the entire Excel object model in Office projects that target the .NET Framework 3.5. The Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute controls the Excel object model behavior in these projects. By default the attribute is set to true, which locks the Excel object model to expect locale ID 1033 formatting. When this attribute is true, Visual Studio wraps every Excel object that is instantiated by the solution in a proxy object that always passes the locale ID 1033 to Excel. When you set the attribute to false, these proxy objects are not used, and the Excel object model uses the end user's locale settings.

If you choose to set the attribute to false, you can still use reflection to force specific calls to Excel properties and methods to use English (United States) data format (locale ID 1033). For more information, see How to: Make String Literals Region-safe in Excel Using Reflection.

NoteNote

If you set the attribute to false and any end users have a locale setting other than English (United States), they might see unexpected behavior in your solution.

To unlock the object model, set the Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute in the AssemblyInfo.vb or AssemblyInfo.cs file in your project to false.

[assembly: ExcelLocale1033(false)]
NoteNote

Because the Visual Studio Tools for Office runtime wraps all native Excel objects in a proxy object when the Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute is true, you might notice a slowdown in some operations that involve native Excel objects. In these cases, you can improve the performance by using the Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Unwrap(System.Object) method to get the Excel object without the proxy, and then perform the operation using that object. If you do this, be aware that the object returned by Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Unwrap(System.Object) uses the locale ID of the current thread, not locale ID 1033.

Community Additions

ADD
Show: