Excel object model - C#

[Attila]

Dear All,
I am a newbie in Excel-DNA (which seems to be a wonderful product, though!), and have problems with accessing the Excel object model in C# -- because most examples are written in VB. Could you please send me a link to a tutorial which shows, how to use the EOM in C#, or just copy a very simple code which demonstrates it?
Thank you so much.
BR,
Attila

[Govert van Drimmelen]

Hi Atilla,

Interop with Excel through the COM object model can be done in a few
ways.
* Call Excel late-bound via reflection. The answer I just posted here
is an example: http://groups.google.com/group/exceldna/browse_frm/thread/ceacc4228b3b99ad.
* Call Excel late-bound using the C# 4 'dynamic' types. This makes
calling Excel late-bound from C# as easy as it is from VB.NET. An
example is Distribution\Samples\DynamicMethodSample.dna.
* Call Excel through a version-independent COM wrapper like NetOffice
(http://netoffice.codeplex.com). We're just starting to learn about
this - see http://groups.google.com/group/exceldna/browse_frm/thread/f356a30adf9130d0.
* Call Excel through the primary interop assembly (PIA). This requires
the (version-dependent) PIA from Microsoft to be pre-installed on the
machine, or shipped as part of your add-in. I paste an example below.

If you have a particular VBA or VB.NET example in mind, or need more
information about any of the about or choosing, please write back with
some more questions.

-Govert

<DnaLibrary Language="C#">
<Reference Name="Microsoft.Office.Interop.Excel" />
<Reference Name="System.Windows.Forms" />

<![CDATA[
using WinForms = System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

public static class TestInterop
{
[ExcelCommand(MenuText="Say Hello")]
public static void ShowMessageBox()
{
Application app;
Range r;
app = (Application)ExcelDnaUtil.Application;
r = (Range)app.Cells[1,1];

WinForms.MessageBox.Show("Hello from Excel version " +
app.Version +
". Cell A1 contains: " + r.Value2);
}
}
]]>
</DnaLibrary>

[Attila]

Hi Govert,

thank you for your answer. I try the late-bound reflection example,
but I must miss a point here.
So, my question is: how could I re-code this VB example in C#?

----- VB example ------
Public Shared Function UserName (ByVal sString As String) As String
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
Return XLApp.Username
End Function
-----------------------

And my wrong code is:

----- SOMETHING IS MISSING HERE -----
<DnaLibrary Language="CS">
<![CDATA[
using System.Reflection;
using ExcelDna.Integration;
public static class NiceTry
{
[ExcelFunction(IsMacroType=true, Description="Does
something", Category="MyFirstPlugin")]
public static object DoesSomething()
{
ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);

// What is missing here???
return caller.Username;
}
}
]]>
</DnaLibrary>
--------------------------------

Thank you
Attila

On júl. 8, 00:46, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Atilla,
>
> Interop with Excel through the COM object model can be done in a few
> ways.
> * Call Excel late-bound via reflection. The answer I just posted here

> is an example:http://groups.google.com/group/exceldna/browse_frm/thread/ceacc4228b3....

> * Call Excel late-bound using the C# 4 'dynamic' types. This makes
> calling Excel late-bound from C# as easy as it is from VB.NET. An
> example is Distribution\Samples\DynamicMethodSample.dna.
> * Call Excel through a version-independent COM wrapper like NetOffice
> (http://netoffice.codeplex.com). We're just starting to learn about

> this - seehttp://groups.google.com/group/exceldna/browse_frm/thread/f356a30adf9....

[Govert van Drimmelen]

Hi Attila,

The ExcelReference type is related to the C API, and used with the
XlCall.Excel calls.
XlCall.xlfCaller is the C API equivalent of Application.Caller.

I translate your UserName function to late-bound C# below.

-Govert

<DnaLibrary Language="C#">
<![CDATA[
using ExcelDna.Integration;
using System.Reflection;

public class MyFunctions
{
[ExcelFunction(IsMacroType=true)]
public static string UserName(string unused)
{
object xlApp = ExcelDnaUtil.Application;
object userName = xlApp.GetType().InvokeMember("UserName",
BindingFlags.GetProperty, null, xlApp, null);
return (string)userName;
}
}
]]>
</DnaLibrary>

[Attila]

Govert, thank you for your support. :)
Is "ExcelDnaUtil.Application" documented somewhere, so that I can
learn how to start with it?
(Sorry I am really a newbie, have only an ASP.NET background.) Is it
documented somewhere?
It seems that using it from C# is rather tricky as compared to VB.

--
VB: XLApp.Username
vs.
C#: xlApp.GetType().InvokeMember("UserName", BindingFlags.GetProperty,
null, xlApp, null);
--

There are dozens of VB examples and also good books about the Excel
object model, how
can I learn how to use them but in C#?
Thank you and sorry for asking such basic questions.
Best,
Attila

[Govert van Drimmelen]

Hi Attila,

Calling the Excel automation object model late-bound from C# is the
most cumbersome way to do it. VB.NET is much better than C# for this,
although C# 4 is a great improvement. The nicer-looking C# example
uses .NET 4 and the 'dynamic' type, and is at is Distribution\Samples
\DynamicMethodSample.dna.

All other ways I suggested in my first reply are more elegant than C#
late-bound via reflection. Which of those alternatives are best for
you depends on what tools you are using, what you know and who will be
using your add-in. For just getting started, if you definitely want to
use C# instead of VB.NET, I'd suggest you install the Primary Interop
Assemblies (PIAs) for your Office version (download from Microsoft,
and add a reference in your project). Then you don't need the late-
bound reflection-based InvokeMember stuff, and your code will look
like the VB code.

ExcelDnaUtil.Application is just a way of getting the the correct
Excel Application object from within your add in. The returned COM
object is the same object you would have in VBA when you put in
'Application', and is your entry point from Excel-DNA into the Excel
COM object model. The Application object is the entry into a huge
library that is part of Excel and not part of or particular to Excel-
DNA. Some documentation entry points are here:
http://msdn.microsoft.com/en-us/library/wss56bz7(v=VS.100).aspx, and
http://msdn.microsoft.com/en-us/library/aa168292(v=office.11).aspx.
The object model used from Excel-DNA via C# or VB.NET is the same
object model used from VBA, so all those VB examples should be fairly
easy to translate once you get the hang of it.

If you have never worked with Excel programming, you'll have to get
used to translating those VBA examples to .NET. Certainly VB.NET is a
bit easier than C#, but the object model you're coding against is the
same.

-Govert