Special Excel Issues

You need to be aware of several special considerations when using the Excel object model with .NET. This section examines two of the most important: working with multiple locales and working with Excel dates.

The Excel Locale Issue for Automation Executables and COM Add-Ins

When you program against the Excel object model using managed code in an automation executable or a COM add-in, Excel methods and properties can behave differently, depending on the locale of the current thread. Note that this problem does not occur in code-behind-the-document solutions built with VSTO. If you want to set a formula for a Range, for example, and you are in the French locale, Excel requires you to use the localized French formula names and formatting:

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

This behavior differs from Visual Basic for Applications (VBA) and VSTO code-behind solutions that work independently of locale. VBA and VSTO always tell Excel that the locale is U.S. English (locale ID 1033). In VBA and VSTO code-behind solutions, you do not have to think about locale when talking to Excel. You can write this code and have it work even in a French locale:

sheet.Range("A1").Formula = "=SUM(3, 4)"

When managed code calls into the Excel object model, it tells Excel the locale it is running under (the locale of the current thread), which causes Excel to expect that you will provide formulas and other values in the localized format of that locale. Excel will also return formulas and other values in the localized format of that locale. Excel expects localized strings for such things as date formats, NumberFormat strings associated with a Range, color names associated with NumberFormat strings, and formula names.

Using DateTime for Dates

As an example of the badness that can ensue if you do not think about this issue, consider what the following code does:

sheet.Range("A1").Value2 = "03/11/02"

Depending on the locale of the current thread, Excel may interpret this value as March 11, 2002; November 3, 2002; or November 2, 2003.

For dates, you have a clear workaround. Do not pass dates as literal strings to Excel. Instead, construct a date using the System.DateTime object, and pass it to Excel using DateTime's ToOADate method, as shown in Listing 5.36. The ToOADate method converts a DateTime to an OLE Automation date, which is the kind of date format that the Excel object model expects.

Listing 5.36. A VSTO Customization That Passes a Date Properly to Excel

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range("$A$1")     ' March 11, 2002     Dim date1 As System.DateTime     date1 = New System.DateTime(2002, 3, 11)     range1.Value2 = date1.ToOADate()   End Sub End Class 

Switching the Thread Locale to English and Back

You might think that a solution to the problems associated with setting or getting Range.NumberFormat and Range.Formula is to save the locale of the thread; temporarily switch the locale of the thread to English (locale ID 1033); execute code that sets or gets a locale-affected property, such as NumberFormat or Formula; and then switch back to the saved locale. This approach is not recommended because it affects other add-ins that will not be expecting the locale switch.

Consider the following example. Your add-in is running on a French machine. Your add-in switches the locale to 1033 and sets a formula value. Another add-in is handling the Change event and displays a dialog box. That dialog box displays in English rather than French. So by changing the thread locale, you have changed the behavior of another add-in and have been a bad Office citizen in general.

Using Reflection to Work Around the Locale Issue

The recommended workaround for COM add-ins or automation executables encountering the locale issue (when they access properties affected by the current locale, such as the NumberFormat or Formula property) is to access these properties via reflection. Reflection enables you to specify an English locale to Excel and write code that will work regardless of the current thread locale. Listing 5.37 illustrates how to use reflection to set the NumberFormat and Formula properties.

Listing 5.37. Using Reflection to Work Around the Locale Issue in Excel

Imports Excel = Microsoft.Office.Interop.Excel Module Module1   Sub Main()     Dim application As Excel.Application     application = New Excel.Application()     application.Visible = True     Dim workbook As Excel.Workbook = application.Workbooks.Add()     Dim sheet As Excel.Worksheet = workbook.Worksheets.Add()     Dim range1 As Excel.Range = sheet.Range("$A$1")     ' Set Formula in English (US) using reflection     GetType(Excel.Range).InvokeMember("Formula", _       System.Reflection.BindingFlags.Public Or _       System.Reflection.BindingFlags.Instance Or _       System.Reflection.BindingFlags.SetProperty, _       Nothing, range1, New Object() {"=SUM(12, 34)"}, _       System.Globalization.CultureInfo.GetCultureInfo(1033))     ' Set NumberFormat in English (US) using reflection     GetType(Excel.Range).InvokeMember("NumberFormat", _       System.Reflection.BindingFlags.Public Or _       System.Reflection.BindingFlags.Instance Or _       System.Reflection.BindingFlags.SetProperty, _       Nothing, range1, _       New Object() {"General"}, _       System.Globalization.CultureInfo.GetCultureInfo(1033))   End Sub End Module 

Old Format or Invalid Type Library Error

A second issue that further complicates the Excel locale issue is that you can get an "Old format or invalid type library" error when using the Excel object model in an English Excel installation on a machine where the locale is set to a non-English locale. Excel is looking for a file called xllex.dll in Program Files\Microsoft Office\OFFICE11\1033 and cannot find it. The solution to this problem is to install the xllex.dll file or to install the MUI language packs for Office. You can also make a copy of excel.exe, rename it xllex.dll, and copy it to the 1033 directory.

VSTO and the Excel Locale Issue

VSTO code-behind-the-document solutions solve the Excel locale issue by using a transparent proxy object that sits between you and the Excel object model. This proxy always tells Excel that the locale is U.S. English (locale ID 1033), which effectively makes VSTO match VBA behavior. If you are using VSTO code-behind-the-document solutions, the Excel locale issue is solved for you, and you do not have to worry about it further. If you are building a managed COM add-in for Excel or an automation executable, the issue still exists.

There are some caveats to VSTO's solution to the Excel locale issue. The VSTO transparent proxy can slow your code slightly. It also causes Excel objects to display slightly differently when inspected in the debugger. Finally, if you compare a proxied Excel object such as Application with an unproxied Application object using the Equals operator, the objects will not evaluate to be equal.

If you want to bypass VSTO's transparent proxy for a particular object, you can use the Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Unwrap method and pass the Excel object that you want to bypass the proxy for. This method removes the proxy and returns the raw PIA object, which exposes you once again to the locale issue. You can also set the assembly-level attribute ExcelLocale1033 in a VSTO project's AssemblyInfo.vb file to False to turn the transparent proxy off for the entire Excel solution. To do this, you must show hidden files in Solution Explorer by clicking the Show Hidden Files button at the top of the Solution Explorer window. Then expand the My Project folder, and double-click the AssemblyInfo.vb file. At the bottom of this file, you will find the ExcelLocale1033 attribute. Change the value of this attribute from TRue to False.

If you navigate to objects from another PIA and then navigate back to the Excel PIA, you can lose the transparent proxy. If you get a CommandBar object from the Microsoft.Office.Core PIA namespace from the Application.CommandBars collection, for example, and then use the CommandBar.Application property to get back to the Excel Application object, you have lost the proxy, and the locale issue will occur again.

Finally, if you create a new instance of Excel from a Word VSTO code-behind solution, you are talking directly to the Excel PIA with no transparent proxy object, and the locale issue will continue to be in effect.

Converting Excel Dates to DateTime

Excel can represent dates in either of two formats: the 1900 format or the 1904 format. The 1900 format is based on a system where, when converted to a number, it represents the number of elapsed days since January 1, 1900. The 1904 format is based on a system where, when converted to a number, it represents the number of elapsed days since January 1, 1904. The 1904 format was introduced by early Macintosh computers because of a problem with the 1900 format that we describe later. You can determine which format a workbook is using by checking the Workbook.Date1904 property, which returns TRue if the workbook is using the 1904 format.

If an Excel workbook is using the 1904 format, and you convert a date from that workbook into a DateTime directly, you will get the wrong value. The date will be off by four years and two leap days, because DateTime is expecting the 1900 format, where the value of the Excel date represented by a number is the number of elapsed days since January 1, 1900not January 1, 1904. So this code would give a bad DateTime if you are using the 1904 format in your workbook.

Dim excelDate As Object = myRange.Value Dim possiblyBadDateIfExcelIsIn1904Mode As DateTime = _   CType(excelDate, DateTime) 

To get a 1904-format date into a DateTime format, you must add to the 1904-format date four years and two leap days (to make up for the fact that the 1904 has its zero in 1904 rather than 1900). So if you write this code instead, and use the function ConvertExcelDateToDate in Listing 5.38, you will get the right result if you use the 1904 date system.

Dim excelDate As Object = myRange.Value Dim goodDate As DateTime = ConvertDateToExcelDate(excelDate) 

Listing 5.38. Converting Excel Dates to DateTime and Back Again

Private ReadOnly march1st1900 As DateTime march1st1900 = New DateTime(1900, 3, 1) Private ReadOnly december31st1899 As DateTime = _   New DateTime(1899, 12, 31) Private ReadOnly january1st1904 As DateTime january1st1904 = New DateTime(1904, 1, 1) Private ReadOnly date1904adjustment As TimeSpan = _   New TimeSpan(4 * 365 + 2, 0, 0, 0, 0) Private ReadOnly before1stMarchAdjustment As TimeSpan = _   New TimeSpan(1, 0, 0, 0) Private isDate1904 As Boolean = ActiveWorkbook.Date1904 Private Function ConvertDateToExcelDate( _   ByVal date1 As DateTime) As Object   Dim languageSettings As Office.LanguageSettings = _     Application.LanguageSettings   Dim lcid As Integer = _     languageSettings.LanguageID( _       Office.MsoAppLanguageID.msoLanguageIDUI)   Dim officeUICulture As Globalization.CultureInfo = _     New Globalization.CultureInfo(lcid)   Dim dateFormatProvider As Globalization.DateTimeFormatInfo   dateFormatProvider = officeUICulture.DateTimeFormat()   Dim dateFormat As String   dateFormat = dateFormatProvider.ShortDatePattern   If isDate1904 = True Then     If date1 >= january1st1904 Then       Return date1 - date1904adjustment     Else       Return date1.ToString(dateFormat, dateFormatProvider)     End If   End If   If date1 >= march1st1900 Then     Return date1   End If   If (date1 < march1st1900 And _     date1 > december31st1899) Then     Return date1 - before1stMarchAdjustment   End If   Return date1.ToString(dateFormat, dateFormatProvider) End Function Private Function ConvertExcelDateToDate( _   ByVal excelDate As Object) _   As DateTime   Dim date1 As DateTime = CType(excelDate, DateTime)   If isDate1904 Then     Return date1 + date1904adjustment   End If   If date1 < march1st1900 Then     Return date1 + before1stMarchAdjustment   End If   Return date1 End Function 

Listing 5.38 also has a correction for 1900-format dates. It turns out that when Lotus 1-2-3 was written, the programmers incorrectly thought that 1900 was a leap year. When Microsoft wrote Excel, Microsoft wanted to make sure it kept compatibility with existing Lotus 1-2-3 spreadsheets by making it so that Excel calculated the number of days elapsed since December 31, 1899, rather than January 1, 1900. When DateTime was written, its creators did not try to back up to December 31, 1899; they calculated from January 1, 1900. So to get an Excel date in 1900 format that is before March 1, 1900, into a DateTime properly, you have to add one day.

Finally, Excel cannot represent days before January 1, 1900, when in 1900 format, or days before January 1, 1904, when in 1904 format. Therefore, when you are converting a DateTime to an Excel date, you have to pass a string rather than a number representing the datebecause these dates cannot be represented as dates in Excel (only as strings).