How to embed Excel spreadsheet in Windows forms application

  • Thread starter Thread starter John O'Neill
  • Start date Start date
J

John O'Neill

Hi

I would like to know how to go about embedding an Excel spreadsheet into a
c# Windows forms application. Any tips or pointers to some articles to get
me started would be much appreciated.

John
 
Hello again!

I have since found out that an Excel document cannot be directly embedded
into a c# windows forms application. I have read the following article that
illustrates a workaround using a WebBrowser control to host the Excel
document instead.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;304662

What I would like to know now is how to manipulate and read the contents of
the Excel document that is embedded in the WebBrowserControl from my c#
windows forms application. For example, I might add a menu option to my c#
windows form application that retrieves a dataset of product details using a
web service. I would then want to populate an Excel spreadsheet with the
product data and display this within my windows form application and allow
the user to edit the product prices column or apply a markup %age or
discount to all product and see how the prices are affected. I would then
want to enable the user to select another menu item from my c# windows form
application that would be able to read any changes made to the product
prices and update the master product database using a web service.

I hope this makes a bit of sense and again, any help or pointers to existing
articles would be appreciated.

Thanks.

John
 
Hi,

Basically, you may use GetActiveObject to get an instance of the excel
application.
However , if there are multiple instances of Excel running at the same
time, the
GetActiveObject() API function returns the instance startup first.

Theoretically, you can iterate the ROT for each individual instance, but
Office applications do not register themselves if another instance is
already in the ROT because the moniker for itself is always the same, and
cannot be distinguished. This means that you cannot attach to any instance
except for the first. However, because Office applications also register
their documents in the ROT, you can successfully attach to other instances
by iterating the ROT looking for a specific document, attaching to this
document, and then getting the Application object from this document.

The following two KB Articles describes the above steps in detail:

<HOWTO: Attach to a Running Instance of an Office Application>
http://support.microsoft.com/default.aspx?scid=kb;en-us;238975
<HOWTO: Get IDispatch of an Excel or Word Document from an OCX>
http://support.microsoft.com/default.aspx?scid=kb;EN-US;190985

On .NET, you need do some P/Invoke to enumerate the ROT,
the following code snippet may help. You may try casting the returned RCW
object of COM interface into the managed Interface defined in Office PIA to
execute methods.
<code>
using System.Runtime.InteropService;
[DllImport("ole32.dll")]
public static extern int GetRunningObjectTable(int reserved, out
UCOMIRunningObjectTable prot);

[DllImport("ole32.dll")]
public object[] ActiveObjectList(string moniker)
{
UCOMIRunningObjectTable prot;
UCOMIEnumMoniker pMonkEnum;
ArrayList list = new ArrayList();
int Fetched = 0;

Win32.GetRunningObjectTable(0, out prot);
prot.EnumRunning(out pMonkEnum);
pMonkEnum.Reset();

UCOMIMoniker[] pmon = new UCOMIMoniker[1];

while (pMonkEnum.Next(1, pmon, out Fetched) == 0)
{
UCOMIBindCtx pCtx;
Win32.CreateBindCtx(0, out pCtx);
string str;
pmon[0].GetDisplayName(pCtx, null, out str);
if (str.IndexOf(moniker) != -1)
list.Add(str);
Marshal.ReleaseComObject(pCtx);
} return list.ToArray();
}
</code>

Does it resolve problem?
Let me know if you have anything unclear or meet some problem on it.

Thanks!

Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.
 
Hi Ying-Shen

Thank you for your reply which was quite helpful.

I have managed to use your first example successfully to bind to an
Excel.Application object by calling
Marshal.GetActiveObject("Excel.Application") which is ok as long as only a
single instance of Excel is running.

However, I am having trouble using the 2nd example in sample code you
provided. The ActiveObjectList() method returns an object array containing a
list of strings that match the target document I pass in as the moniker
argument. So, lets say I have 2 instances of Excel running, the 1st instance
has "MySpreadsheet.xls" open and the 2nd has "MyProducts.xls" open and I
call the following code:

object[] excelDocuments = ActiveObjectList("MyProducts.xls");

excelDocuments will now contain a single string entry of "MyProducts.xls".
How do I now attach to the Excel instance of "MyProducts.xls"?

Thanks for your help.

John


"Ying-Shen Yu[MSFT]" said:
Hi,

Basically, you may use GetActiveObject to get an instance of the excel
application.
However , if there are multiple instances of Excel running at the same
time, the
GetActiveObject() API function returns the instance startup first.

Theoretically, you can iterate the ROT for each individual instance, but
Office applications do not register themselves if another instance is
already in the ROT because the moniker for itself is always the same, and
cannot be distinguished. This means that you cannot attach to any instance
except for the first. However, because Office applications also register
their documents in the ROT, you can successfully attach to other instances
by iterating the ROT looking for a specific document, attaching to this
document, and then getting the Application object from this document.

The following two KB Articles describes the above steps in detail:

<HOWTO: Attach to a Running Instance of an Office Application>
http://support.microsoft.com/default.aspx?scid=kb;en-us;238975
<HOWTO: Get IDispatch of an Excel or Word Document from an OCX>
http://support.microsoft.com/default.aspx?scid=kb;EN-US;190985

On .NET, you need do some P/Invoke to enumerate the ROT,
the following code snippet may help. You may try casting the returned RCW
object of COM interface into the managed Interface defined in Office PIA to
execute methods.
<code>
using System.Runtime.InteropService;
[DllImport("ole32.dll")]
public static extern int GetRunningObjectTable(int reserved, out
UCOMIRunningObjectTable prot);

[DllImport("ole32.dll")]
public object[] ActiveObjectList(string moniker)
{
UCOMIRunningObjectTable prot;
UCOMIEnumMoniker pMonkEnum;
ArrayList list = new ArrayList();
int Fetched = 0;

Win32.GetRunningObjectTable(0, out prot);
prot.EnumRunning(out pMonkEnum);
pMonkEnum.Reset();

UCOMIMoniker[] pmon = new UCOMIMoniker[1];

while (pMonkEnum.Next(1, pmon, out Fetched) == 0)
{
UCOMIBindCtx pCtx;
Win32.CreateBindCtx(0, out pCtx);
string str;
pmon[0].GetDisplayName(pCtx, null, out str);
if (str.IndexOf(moniker) != -1)
list.Add(str);
Marshal.ReleaseComObject(pCtx);
} return list.ToArray();
}
</code>

Does it resolve problem?
Let me know if you have anything unclear or meet some problem on it.

Thanks!

Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.
Click to expand...
 
Hi John,

Sorry, I should return the object, not the string. You may cast the
corresponding object to WorkBook object, then get the corresponding
Excel.Application by its Application property.
Here is the modified code snippet:
<code>
public object GetActiveObject(string moniker)
{
UCOMIRunningObjectTable prot = null;
UCOMIEnumMoniker pMonkEnum = null;
try
{

int Fetched = 0;

Win32.GetRunningObjectTable(0, out prot);
prot.EnumRunning(out pMonkEnum);
pMonkEnum.Reset();

UCOMIMoniker[] pmon = new UCOMIMoniker[1];

while (pMonkEnum.Next(1, pmon, out Fetched) == 0)
{
UCOMIBindCtx pCtx;

Win32.CreateBindCtx(0, out pCtx);

string str;
pmon[0].GetDisplayName(pCtx, null, out str);
Marshal.ReleaseComObject(pCtx);
if (str.IndexOf(moniker) != -1)
{
object objReturnObject;
prot.GetObject(pmon[0], out objReturnObject);
return objReturnObject;
}
}
return null;
}
finally
{
if (prot != null )
Marshal.ReleaseComObject( prot);
if (pMonkEnum != null )
Marshal.ReleaseComObject ( pMonkEnum );
}
}
</code>

If you still have problem on this issue, please feel free to reply this
thread to let me know.
Thanks!

Best regards,

Ying-Shen Yu [MSFT]
Microsoft Community Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
This mail should not be replied directly, please remove the word "online"
before sending mail.
 
Hi Ying-Shen

Thank you for your help. Your last post has solved my problem.

Best regards

John
 
Back
Top