C# and Excel interop

Question

Hey guys. One of my users is having an issue when trying to open an Excel file through my C# app. Everything works ok when I run it from my machine and it works for other users. I am no Excel interop expert so hopefully you guys can help me out.

Here is how it is setup:

I added a reference to my app to Microsoft.Office.Interop.Excel.dll, version 10.0.4504.0 (which I believe is Excel 2002). On my machine I have installed Excel 2007. In my code I try to open a worksheet like so:

using Microsoft.Office.Interop
...
Microsoft.Office.Interop.Excel.ApplicationClass _excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = _excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(1);

I logged the users version as Excel 9.0 (which is 2000). The error that the user gets is:

Exception='System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
   at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)

It looks like the workbook cannot be opened. The file was confirmed to exist and was just loose on the user's PC at C:. I thought by using the PIA I wouldn't have to worry about Excel version issues. I know there are other users using Excel 2000, and it works on my dev machine.

Any ideas? Maybe my calls to open the excel file should be changed? The unfortunate thing is that I am unable to reproduce it.

Any help is greatly appreciated.

Answer 1

I'm using Microsoft.Office.Interop.Excel.dll: Runtime version: v1.1.4322 Version: 12.0.0.0

Could try this:

  private object mMissingValue = System.Reflection.Missing.Value;

  private void CreateWorkbook(string fileName)
  {
     if (File.Exists(fileName))
     {
        // Create the new excel application reference
        mExcelApplication = new Application();
        // Open the file
        Workbook excel_workbook = mExcelApplication.Workbooks.Open(templatePath,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue);
        Worksheet sheet = (Worksheet)mExcelWorkbook.Worksheets[1];
    }
 }

Answer 2

I've come to prefer saving Excel files as XML spreadsheets and then opening them up with System.Xml objects. XML I use a lot for all sorts of things, while Interop is something of a black art to me. It may not be good enough for your needs, but if it is, it is probably a lot easier than tangling with the Interop libraries.

Answer 3

"I thought by using the PIA I wouldn't have to worry about Excel version issues. I know there are other users using Excel 2000, and it works on my dev machine."

Almost true, but not quite.

By developing against the PIA for Excel 2002, your program will be compatible for all versions of Excel 2002 (10.0) and above. The failing machine, however, is running with Excel 2000 (9.0), which is a version below the version that you have developed against.

There is no officially supported PIA below Excel 2002, so if you need your program to run on Excel 2000 (9.0) then you will have to create your own custom interop assembly. You can use TlbImp.exe to create an interop assembly for Excel 9.0, as explained in the article Achieving Backward Compatibility with .NET Interop: Excel as Case Study.

If your assembly is strong-named, then you need to create a strong-named interop assembly. This can be done by providing the name of your .pfx or .snk file via the /keyfile switch, as demonstrated in the article How to create a Primary Interop Assembly (PIA). That article is also making use of the /primary switch in order to create a "primary interop assembly". Making the interop assembly primary is not really needed in your case, but does not hurt. What the article omits, however, is the use of the /sysarray switch, which you should use.

Making a strong-named interop assembly does have some complexities to consider, however. For some more details, have a read of Using TLBIMP.exe to create Strong Named Interop Assemblies. (Among other things, this article explains the need for the /sysarray switch.)

In short, making a custom interop assembly is very simple if your assembly is not strong named. It is more complicated if your assembly is strong-named and, therefore, you need to create a strong-named interop assembly. But hopefully these articles should get you going.

-- Mike