Formatting Data in Excel with Various Regional Settings
Applies to |
|---|
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type
Microsoft Office version
For more information, see Features Available by Application and Project Type. |
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 Visual Studio Tools for Office project.
By default, the Visual Studio Tools for Office runtime causes the Excel object model to expect 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 Visual Studio Tools for Office solutions.
Programming the Locked Excel Object Model
By default, document-level customizations and application-level add-ins that you create by using Visual Studio Tools for Office 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.
Unlocking the Excel Object Model
Visual Studio Tools for Office enables you to unlock the locale ID behavior for the entire Excel object model. The ExcelLocale1033Attribute controls the Excel object model behavior in your Visual Studio Tools for Office solution. 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 Tools for Office 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.
Note
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. You can write your code so that it works in any locale setting. For more information, see How to: Make String Literals Region-safe in Excel Using Reflection.
To unlock the object model, set the ExcelLocale1033Attribute in the AssemblyInfo.vb or AssemblyInfo.cs file in your project to false.
<Assembly: ExcelLocale1033(False)>
[assembly: ExcelLocale1033(false)]
Note
Because the Visual Studio Tools for Office runtime wraps all native Excel objects in a proxy object when the 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 Unwrap 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 Unwrap uses the locale ID of the current thread, not locale ID 1033.
Unlocking Specific Excel Objects in Excel 2003 Projects
If you are developing solutions for Excel 2003, there are some cases in which you need to modify a native Excel object (that is, an instance of a class that is defined in the Microsoft.Office.Interop.Excel namespace) so that it uses the locale ID of the current thread instead of locale ID 1033. For example, you must do this if you want to pass null as an out parameter of a method that belongs to an Excel object.
To change the locale ID behavior for individual Excel objects in Excel 2003 projects, use methods of the ExcelLocale1033Proxy class. Call the Unwrap method to modify an Excel object so that it uses the locale ID of the current thread instead of locale ID 1033.
After you call Unwrap to modify an Excel object, you might want to use the object again, but in a situation where you want to use locale ID 1033 instead of the locale ID of the current thread. Call the Wrap method to wrap the Excel object in a proxy object that it uses locale ID 1033 instead of the locale ID of the current thread.
Note
The Unwrap and Wrap methods are obsolete in Visual Studio Tools for Office projects for Excel 2007. The Visual Studio Tools for Office runtime that is used with Excel 2007 projects contains improvements that make native Excel objects work as expected when the ExcelLocale1033Attribute is true. For more information about the Visual Studio Tools for Office runtime, see Visual Studio Tools for Office Runtime Overview.
Calling Methods That Have out Parameters While the Excel Object Model Is Locked
If the ExcelLocale1033Attribute is true in an Excel 2003 solution, you will receive an exception if you pass null as an out parameter of a method in the Excel object model. To pass null as an out parameter, you must call Unwrap and pass in the object that contains the method before you call the method.
Alternatively, you can assign the parameter to a value before calling the method. For more information about out parameters, see out (C# Reference).
For example, the following code throws an exception if the ExcelLocale1033Attribute is true. The ExportXml(String) method has an out parameter.
Dim outString As String = Nothing
Dim result As Excel.XlXmlExportResult = _
Globals.ThisWorkbook.XmlMaps(1).ExportXml(outString)
string outString = null;
Excel.XlXmlExportResult expResult =
Globals.ThisWorkbook.XmlMaps[1].ExportXml(out outString);
To resolve this issue, you can pass the XmlMap into the Unwrap method before calling ExportXml(String).
Dim outString As String = Nothing
Dim map As Excel.XmlMap = TryCast( _
Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Unwrap( _
Globals.ThisWorkbook.XmlMaps(1)), Excel.XmlMap)
Dim result As Excel.XlXmlExportResult = map.ExportXml(outString)
string outString = null;
Excel.XmlMap map = Microsoft.Office.Tools.Excel.
ExcelLocale1033Proxy.Unwrap(
Globals.ThisWorkbook.XmlMaps[1]) as Excel.XmlMap;
Excel.XlXmlExportResult expResult = map.ExportXml(out outString);
Alternatively, you can assign the outString variable to a value before passing it to ExportXml(String).
Dim outString As String = String.Empty
Dim result As Excel.XlXmlExportResult = _
Globals.ThisWorkbook.XmlMaps(1).ExportXml(outString)
string outString = String.Empty;
Excel.XlXmlExportResult expResult =
Globals.ThisWorkbook.XmlMaps[1].ExportXml(out outString);
See Also
Tasks
How to: Localize Excel Solutions
How to: Make String Literals Region-safe in Excel Using Reflection
Concepts
Creating Office Solutions in Visual Studio
Visual Studio Tools for Office Runtime Overview