Export (0) Print
Expand All

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 Visual Studio Tools for Office project. By default, document-level customizations and application-level add-ins that you create by using Visual Studio Tools for Office cause the Excel object model to expect locale ID 1033 data formatting. Setting the object model to expect only one locale ID is called locking the object model to that locale ID. 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.

Locking the Excel Object Model

For customizations created by using Visual Studio Tools for Office, Version 2003, the locale settings on the end user's computer could affect the behavior of the Excel object model. These locale settings could cause unexpected behavior, such as displaying dates or numbers in the wrong format.

Customizations and add-ins that you create by using Visual Studio 2005 Tools for Office are not affected by the end user's locale settings, and always behave as though the locale is English (United States). The Excel object model is locked to expect locale ID 1033 data formatting. This behavior matches the way that Visual Basic for Applications works.

Unlocking the Excel Object Model

Visual Studio 2005 Tools for Office enables you to unlock the locale ID behavior for the entire Excel object model, or for individual Excel objects. The ExcelLocale1033Attribute controls the Excel object model behavior in your Visual Studio 2005 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 you set the attribute to false, the Excel object model uses the end user's locale settings, which is the way that Visual Studio Tools for Office, Version 2003, works.

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. 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 attribute ExcelLocale1033Attribute in the file AssemblyInfo.vb or AssemblyInfo.cs to false.

[assembly: ExcelLocale1033(false)]

Unlocking Specific Excel Objects

The ExcelLocale1033Proxy class provides two methods you can use to modify the locale ID behavior for individual Excel objects (that is, objects that are defined in the Microsoft.Office.Interop.Excel namespace). If you want to modify an Excel object so that it uses the locale ID of the current thread instead of locale ID 1033, call the Unwrap method. 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 modify the Excel object so that it uses locale ID 1033 instead of the locale ID of the current thread.

Calling Methods That Have out Parameters While the Excel Object Model is Locked

If the ExcelLocale1033Attribute is true, you cannot 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 method has an out parameter.

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 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 outString = String.Empty;
Excel.XlXmlExportResult expResult =
    Globals.ThisWorkbook.XmlMaps[1].ExportXml(out outString);

NoteNote

Before calling the XmlImport and XmlImportXml methods of the Microsoft.Office.Tools.Excel.Workbook and Microsoft.Office.Interop.Excel.Workbook classes, you must use Unwrap to unwrap the object that contains the method and the parameters that are Excel objects. For more information, see XmlImport and XmlImportXml.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft