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 would 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 Microsoft WebBrowser
control as a host for the document.
Background
You can study Anup Shinde's article. His method works fine. Instead of the WebBrowser
control, it is based on Windows Win32 API.
If you are interested in the original publication of the WebBrowser
method, you can see the Microsoft KB.
Starting from scratch
Create a new form MyForm, and add a new WebBrowser
control, named webBrowser1
. Add the m_ExcelFileName
field :
private string m_ExcelFileName="test.xls";
Then create a function OpenFile
like this :
public void OpenFile(string filename)
{
if(!System.IO.File.Exists(filename)) throw new Exception();
m_ExcelFileName=filename;
this.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. While the WebBrowser
was navigating, it opened an invisible Excel application, and loaded the workbook inside it. And when you closed your application, the WebBrowser
didn'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 :
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
You'll need these assemblies too :
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.InteropServices.ComTypes;
Declare these two Excel fields :
private Microsoft.Office.Interop.Excel.Application m_XlApplication=null;
private Workbook 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 in the WebBrowser
. So we have to generate a webBrowser1_Navigated
event handler and its matching function, like this :
private void webBrowser1_Navigated(object sender,WebBrowserNavigatedEventArgs e)
{
if((m_Workbook=RetrieveWorkbook(m_ExcelFileName))==null)return;
m_XlApplication=(Microsoft.Office.Interop.Excel.Application)m_Workbook.Application;
}
Then we define the RetrieveWorkbook
function. 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 names xlfile
. The code is like this :
[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;
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);
monikers[0].GetDisplayName(pctx,null,out filepathname);
Marshal.ReleaseComObject(pctx);
if(filepathname.IndexOf(xlfile)!=-1)
{
object roval;
prot.GetObject(monikers[0],out roval);
return roval as Workbook;
}
}
}
catch
{
return null;
}
finally
{
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 overriding the OnClose()
event :
protected override void OnClosed(object sender, EventArgs e)
{
try
{
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 manage CommandBars
, Menus
, etc. inside the control. You will find some code in the downloadable package section.