How to integrate excel in a Windows form application using the webbrowser

From http://www.codeproject.com/KB/office/Embedding_Excel.aspx

• Download Demo project-28.4 KB

 

Introduction

With automation, you are able to drive any office application from your. NET application. this is really powerful. it may happen that one time, you wowould like to integrate such an application (Excel, for example) in your own application, and handle it like a control. A first approach has already been published on the Code project (see the background section in this article ). the other method I will describe here uses the MicrosoftWebbrowserControl as a host for the document.

Background

You can study Anup Shinde's article. His method works fine. Instead ofWebbrowserControl, it is based on Windows Win32 API.

If you are interested in the original publication ofWebbrowserMethod, you can see the Microsoft KB.

Starting from scratch

Create a new form myform, and add a newWebbrowserControl, namedWebbrowser1. AddM_excelfilenameField:

Collapse | Copy code

 
//Contains the path to the workbook FilePrivate StringM_excelfilename ="Test.xls";//Replace here with an existing file
 

Then create a functionOpenfileLike this:

Collapse | Copy code

Public VoidOpenfile (StringFilename ){//Check the file existsIf(! System. Io. file. exists (filename ))Throw NewException (); m_excelfilename = filename;//Load the workbook in the webbrowser ControlThis. Webbrowser1.navigate (filename,False);}
 

You can try and run your application, giving the filename an existing Excel file path. You'll see that it works perfectly. Really easy, don't you think?

In fact, you will quickly get into trouble if you try to run the application a second time with the same Excel file. an error message tells you that your file is already in use. this may be strange because you think that you closed your application, and you did. so where is the problem?

Let's see what happened in the background. WhileWebbrowserWas navigating, it opened an invisible Excel application, and loaded the workbook inside it. And when you closed your application,WebbrowserDidn't close either its Excel application or the workbook. So we must do it, and this is the most difficult part of our job.

Solving the problem step by step

Before further reading, you have to load the following office com library references for office automation:

• Microsoft Excel 11.0 Object Library
• Microsoft Office 11.0 Object Library

And use them in your file:

Collapse | Copy code

 
UsingMicrosoft. Office. core;UsingMicrosoft. Office. InterOP. Excel;
 

You'll need these assemblies too:

Collapse | Copy code

 
UsingSystem. runtime. interopservices;UsingSystem. reflection;UsingSystem. runtime. interopservices. comtypes;
 

Declare these two Excel fields:

Collapse | Copy code

 
//Contains a reference to the hosting applicationPrivateMicrosoft. Office. InterOP. Excel. Application m_xlapplication = NULL;//Contains a reference to the active workbookPrivateWorkbook m_workbook = NULL;
 

Before trying to close the workbook, we need a handle on it. For convenience, the best moment to do this is just after the document has been loaded inWebbrowser. So we have to generateWebbrowser1_navigatedEvent handler and its matching function, like this:

Collapse | Copy code

 
Private VoidWebbrowser1_navigated (ObjectSender, webbrowsernavigatedeventargs e ){//Creation of the workbook objectIf(M_workbook = retrieveworkbook (m_excelfilename) = NULL)Return;//Create the Excel. ApplicationM_xlapplication = (Microsoft. Office. InterOP. Excel. Application) m_workbook.application ;}
 

Then we defineRetrieveworkbookFunction. it is based on two imported Win32 API functions, that retrieve all the programs that are running on our computer. our job is to search among them the one that is working with the workbook that namesXlfile. The code is like this:

Collapse | Copy code

[Dllimport ( "  Ole32.dll" )] Static   Extern   Int Getrunningobjecttable ( Uint Reserved, Out Irunningobjecttable pprot); [dllimport ( "  Ole32.dll" )] Static   Extern   Int Createbindctx ( Uint Reserved, Out Ibindctx pctx ); Public Workbook retrieveworkbook ( String Xlfile) {irunningobjecttable prot = NULL; ienummoniker pmonkenum = NULL; Try { Intptr Pfetched = intptr. zero;//  Query the running object table (ROT)              If (Getrunningobjecttable ( 0 , Out Prot )! = 0 | prot = NULL) Return   Null ; Prot. enumrunning ( Out Pmonkenum); pmonkenum. Reset (); imoniker [] monikers = new imoniker [ 1 ]; While (Pmonkenum. Next ( 1 , Monikers, pfetched) = 0) {ibindctx pctx;String Filepathname; createbindctx ( 0 , Out Pctx ); //  Get the name of the file Monikers [ 0 ]. Getdisplayname (pctx, Null , Out Filepathname ); //  Clean up Marshal. releasecomobject (pctx ); // Search for the workbook                   If (Filepathname. indexof (xlfile )! =-1 ){ Object Roval; //  Get a handle on the workbook Prot. GetObject (monikers [ 0 ], Out Roval ); Return Roval As Workbook ;}}} Catch {Return   Null ;} Finally { //  Clean up               If (Prot! = NULL) Marshal. releasecomobject (prot ); If (Pmonkenum! = NULL) Marshal. releasecomobject (pmonkenum );} Return   Null ;}

Now we can write the code involved to close the background Excel application, while overridingOnclose ()Event:

Collapse | Copy code

 Protected   Override   Void Onclosed ( Object Sender, eventargs e ){ Try { //  Quit Excel and clean up.          If (M_workbook! = NULL) {m_workbook.close ( True , Missing. Value, missing. Value); system. runtime. interopservices. Marshal. releasecomobject (m_workbook); m_workbook = NULL ;} If (M_xlapplication! = NULL) {m_xlapplication.quit (); system. runtime. interopservices. Marshal. releasecomobject (m_xlapplication); m_xlapplication = NULL; system. gc. Collect ();}} Catch {MessageBox. Show ( "  Failed to close the application" );}}

Using the code

You can use the code as written upper. Otherwise, it may be interesting to embed all the stuff in a. Net Control. You'll be able to manageCommandbars,Menus, Etc. Inside the control. You will find some code in the downloadable package section.