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 theproperty 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. Thecontrols 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.
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.
To unlock the object model, set the attribute ExcelLocale1033Attribute in the file AssemblyInfo.vb or AssemblyInfo.cs to false.
Unlocking Specific Excel Objects
Theclass provides two methods you can use to modify the locale ID behavior for individual Excel objects (that is, objects that are defined in the 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 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 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.
For example, the following code throws an exception if the ExcelLocale1033Attribute is true. Themethod has an out parameter.
string outString = null; Excel.XlXmlExportResult expResult = Globals.ThisWorkbook.XmlMaps.ExportXml(out outString);
To resolve this issue, you can pass theinto the Unwrap method before calling ExportXml.
string outString = null; Excel.XmlMap map = Microsoft.Office.Tools.Excel. ExcelLocale1033Proxy.Unwrap( Globals.ThisWorkbook.XmlMaps) 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.ExportXml(out outString);
Before calling the