Hosting an Excel Application in an MDI Form

• Download demo - 29.3 KB

Introduction

Warning: Microsoft took out the ability to host Excel in a WebBrowser in IE10. This article was written before that happened.

Excel Interop can be a nice thing for anyone wanting to embed Excel into their applications. In particular, using an MDI parent to host multiple instances of Excel Workbooks. You may say why would I need that? The answer is this: you have developed an application that contains many different kinds of opened windows and/or web browsers that, in essence, automate all UNC paths, URL navigating, and opening of windows without cluttering the desktop. This is really nice for customers that would rather do their job than keeping track of a zillion windows and UNCs or URLs.

Background

First off, I don't want to lead anyone to believe that hosting Excel workbooks in a web browser is easy. There are lots of problems, some of which will be discussed here. First off, thanks goes to this CodeProject article which started me off into thinking about how to enhance what was there. Thanks bsargos for all of your excellent work. This project only adds to what you've already done. Note there is a new problem with this work as indicated by Problem 4 in the comments section below. I've had to abandon this idea because of the problems, but will continue to work on it as time permits. The bottom line is that working with a COM object such as Excel leaves us blind because we can't see the code and can't determine why simple things such as clicking a menuitem in the excel toolbar doesn't work. If you can lend to our understanding of the Excel internals, please comment.

Using the Code

From the article above, most of the methods are intact; however, there were some additional changes, refactoring etc., to move this project a bit further down the road. One of them was to add to the list of Excel menu items (needing removal) that just plain don't work when hosted in a web browser.

Problem 1

The need to remove things in the Excel menu that just don't work when a workbook is hosted in a WebBrowser. I can find no documentation anywhere regarding why these commands just don't work from the Excel toolbar when Excel is hosted in a .NET WebBrowser or even an AxWebBrowser. The string removeList contains the names of commands that don't work.

private void removeExcelButtons() {
    try{
        //None of these work in a hosted excel instance via a webbrowser 
        //So we'll get rid of them
        string removeList = "New Open Mail Recipient Print Preview";
        foreach (Office.CommandBarControl control in _StandardCommandBar.Controls){
            string name = control.get_accName(Missing.Value);
            if (removeList.Contains(name)) {
                removeCommandBar(control);
            }
        }
    }
    catch (Exception iox) {
        MessageBox.Show(iox.Message, 
          "REX1001-An error occurred in RemoveExcelButtons"); 
    }
}

Points of Interest

One of the tricks one learns regarding Excel automation within a WebBrowser is that there are two parts to this:

1. The WebBrowser's work which is 100% independent of any Excel automation, and
2. Learning how to take the work performed by the WebBrowser and gain addressability to the Excel object using Excel Interop.

When a WebBrowser navigates to a file that it knows to be an Excel file, it does all the work of opening the file via the logic contained within the WebBrowser. The .NET WebBrowser is just a wrapper for IE8, which is a COM object. In fact, many developers use the COM object instead of the .NET WebBrowser as it exposes a lot more functionality. It is more difficult to use as a result and the chief reason this tip sticks with the .NET WebBrowser.

The IE8 engine knows how to handle different document types, and does a great job of it, but the .NET side is oblivious to the work done by the asynchronous unmanaged side of the IE8 engine. That is, until the .NET side gains visibility to the resultant Excel object contained in the WebBrowser after it has completed the navigation to that file. In the method below, addressability happens on NavigateComplete, and in particular, in the call to initExcelObjects().

private void AttachApplication() {
    try {
        if(_ExcelFileName==null||_ExcelFileName.Length==0) return;
        // Creation of the workbook object
        if((_Workbook=GetActiveWorkbook(_ExcelFileName))==null)return;
        // Get addressability to the Excel object via interop.
        initExcelObjects();
        // Get rid of buttons that don't work 
        removeExcelButtons();
    } catch (Exception iox){
        MessageBox.Show(iox.Message,"EXW1001-Error in Excel Wrapper");
        return;
    }
}

Let's examine the initExcelObjects() method below. The first line of code is the beginning of what is called "Excel Interop". The value of _Workbook is set prior to entry to this method upon the NavaigateComplete supporting code. It is merely an instance/pointer to the current workbook item. The workbook item has a property Application which gets us started. Notice the cast to one of the Excel Interop types. This is how we move from the COM side to the .NET side. Once we have the application layer ready, we now have the entire "world" of Excel opened up to .NET. The reason the code goes after the CommandBars is because of the second set of "problems" you'll encounter when hosting Excel objects in a .NET WebBrowser.

Problem 2

You have to explicitly set the toolbar position. If you don't do this, you won't get a toolbar.

private void initExcelObjects() {
     // Create the Excel.Application object using interop
     // Get .NET visibility
     _XlApplication = (Excel.Application)_Workbook.Application;
     // Creation of the standard toolbar NOTE there are many tool bars which
     // you can iterate through to see. But Standard is the big Kahuna
     _StandardCommandBar = _XlApplication.CommandBars["Standard"];
     //This is the fix for Problem 2 above
     _StandardCommandBar.Position = Office.MsoBarPosition.msoBarTop;
     _StandardCommandBar.Visible = _ToolBarVisible;    
}

Problem 3

Exiting the form containing the WebBrowser opened to an Excel workbook leaves Excel running along with the workbook left in a bad state. Bsargos' article includes this fix in the form Dispose() method.

It is worth pointing out the importance of this code. If it's not done, you will be faced with the absolutely maddening problem of half-baked closes of Excel. This shows up trying to reopen the same workbook and getting some ridiculous Excel message saying it's already open, of which you can only recover by taking down the Excel process. When this happens, you have lost control of that workbook. In fact, upon attempts to reopen the same workbook, you will get a request to attach a debugger (for runtime debugging). If you attach the debugger and attempt to see what's going on, you'll only see a bunch of machine code in the disassembly and a vague callstack pointing to Excel internal calls, which almost no one but MSFT engineers have any knowledge of. Bottom line, this code is essential in cleaning up after using Excel from the .NET side.

protected override void Dispose(bool disposing) {
  if(disposing&&(components!=null)) {
    components.Dispose();
  }
 //this.WebBrowserExcel.Dispose();
 try {
      // Quit Excel and clean up.
      if(_Workbook!=null) {
    _Workbook.Close(true,MISS,MISS);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(_Workbook);
    _Workbook=null;
       }
      if(_XlApplication!=null) {
    _XlApplication.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(_XlApplication);
    _XlApplication=null;
    System.GC.Collect();
      }
  } catch (Exception iox){
        MessageBox.Show(iox.Message(),"BAD NEWS in dispose");
  }
 base.Dispose(disposing);
}

The thing to note in the code above is the workbook close as well as the application quit. Both are important to bringing this all down, but even more important is Marshal.ReleaseComObject. Your code must "cross the COM" divide and tell COM to clean up. Finally, the GC collect , seems to help, but is questionable to me because CLR documentation says that no matter what you attempt to do with the Garbage Collector, it always has a mind of its own. I advise leaving the code as-is because without it, you are only half-baked, and everyone knows how irritating it is to be that way.

I hope this helps you to avoid weeks of learning curves (which lead me to this article) with COM and Excel Interop. Feel free to add to it, subtract and comment. In the future, for the MDIParent, I'll be adding other "normal" functions you would expect from running Excel directly.

History

• 08 December, 2009 - Added missing DLLS and disclaimer on "How difficult this is". Excel has a mind of its own which runs at COM layer, a totally invisible realm for the managed side. Many times, the Excel application will hang or menu items just won't respond.
• 25 November, 2009 - Edited
• 24 November, 2009 - Initial post