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. For example, if you want to set a formula for a Range and you are in the French locale, Excel requires you to use the localized French formula names and formatting:

sheet.get_Range("A1", Type.Missing).Formula = "=SOMME(3; 4)";

This behavior differs from VBA and VSTO code behind solutions that work independent of locale. VBA and VSTO always tell Excel that the locale is US 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.get_Range("A1", Type.Missing).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.get_Range("A1", Type.Missing).Value2 = "03/11/02";

This value may be interpreted by Excel as March 11, 2002, November 3, 2002, or November 2, 2003 depending on the locale of the current thread.

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 Properly Passes a Date to Excel

private void Sheet1_Startup(object sender, System.EventArgs e)
{
  Excel.Range range1 = this.get_Range("$A$1", missing);

  // March 11, 2002
  System.DateTime date = new System.DateTime(2002, 3, 11);
  range1.Value2 = date.ToOADate();
}

Switching the Thread Locale to English and Back Is Not Recommended

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 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

static void Main(string[] args)
{
  Excel.Application application = new Excel.Application();
  application.Visible = true;
  object missing = Type.Missing;

  Excel.Workbook workbook = application.Workbooks.Add(missing);
  Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(missing,
missing, missing, missing);
  Excel.Range range1 = sheet.get_Range("$A$1", missing);

  // Set Formula in English (US) using reflection
  typeof(Excel.Range).InvokeMember("Formula",
    System.Reflection.BindingFlags.Public |
    System.Reflection.BindingFlags.Instance |
    System.Reflection.BindingFlags.SetProperty,
    null, range1,
    new object[] {"=SUM(12, 34)" },
    System.Globalization.CultureInfo.GetCultureInfo(1033));

  // Set NumberFormat in English (US) using reflection
  typeof(Excel.Range).InvokeMember("NumberFormat",
    System.Reflection.BindingFlags.Public |
    System.Reflection.BindingFlags.Instance |
    System.Reflection.BindingFlags.SetProperty,
    null, rangel,
    new object[] {"General" },
    System.Globalization.CultureInfo.GetCultureInfo(1033));
}

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 that it cannot find. The solution to this problem is to install the xllex.dll file or install the MUI language packs for Office. You can also make a copy of excel.exe, rename it to 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 US 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 down 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 to an unproxied Application object using the Equals operator, they 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.cs file to false to turn the transparent proxy off for the entire Excel solution.

If you navigate to objects from another PIA and then navigate back again to the Excel PIA, you can lose the transparent proxy. For example, if you get a CommandBar object from the Microsoft.Office.Core PIA namespace from the Application.CommandBars collection and then use the CommandBar.Application property to get back to the Excel Application object, you have now 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 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. It will be off by 4 years and 2 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, 1900, not January 1, 1904. So this code would give bad date-times if you are using the 1904 format in your workbook.

object excelDate = myRange.get_value(Type.Missing);
DateTime possiblyBadDateIfExcelIsIn1904Mode = (DateTime)excelDate;

To get a 1904 format date into a DateTime format, you must add to the 1904 format date 4 years and 2 leap days (to make up for the fact that the 1904 has its 0 in 1904 rather than 1900). So if you write this code instead and use the function Convert-ExcelDateToDate in Listing 5-38, you will get the right result if the 1904 date system is used.

object excelDate = myRange.get_value(Type.Missing);
DateTime goodDate = ConvertExcelDateToDate(excelDate);

Listing 5-38. Converting Excel Dates to DateTime and Back Again

static readonly DateTime march1st1900 = new DateTime(1900, 03, 01);
static readonly DateTime december31st1899 = new DateTime(1899, 12, 31);
static readonly DateTime january1st1904 = new DateTime(1904, 01, 01);
static readonly TimeSpan date1904adjustment = new TimeSpan(4 * 365 + 2, 0, 0,0, 0);
static readonly TimeSpan before1stMarchAdjustment = new TimeSpan(1, 0, 0, 0);
bool date1904 = ActiveWorkbook.Date1904;

object ConvertDateToExcelDate(DateTime date)
{
    LanguageSettings languageSettings = Application.LanguageSettings;
    int lcid = languageSettings.get_LanguageID(
      MsoAppLanguageID.msoLanguageIDUI);
    CultureInfo officeUICulture = new CultureInfo(lcid);
    DateTimeFormatInfo dateFormatProvider = officeUICulture.
      DateTimeFormat;
    string dateFormat = dateFormatProvider.ShortDatePattern;

    if (date1904)
    {
        if (date >= january1st1904)
            return date - date1904adjustment;
        else
            return date.ToString(dateFormat, dateFormatProvider);
    }
    if (date >= march1st1900)
        return date;
    if (date < march1st1900 && date > december31st1899)
        return date - before1stMarchAdjustment;
    return date.ToString(dateFormat, dateFormatProvider);
}

DateTime ConvertExcelDateToDate(object excelDate)
{
    DateTime date = (DateTime)excelDate;
    if (date1904)
        return date + date1904adjustment;
    if (date < march1st1900)
        return date + before1stMarchAdjustment;
    return date;
}

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, they wanted to make sure they kept compatibility with existing Lotus 1-2-3 spreadsheets by making it so that they 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, 1899they 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).