Excel DNA


Recently I was looking at technologies for “Excel Addins” and come across “Excel DNA” (used by some of my colleagues). It is a free framework with a very permissive open-source license.

My need was to be able to consume a WCF service (.NET 3.0) from some VBA code in Excel 2007. Excel-DNA helped to make the final solution very easily. I did a prototype in little time, thanks to ExcelDNA (my Visual Studio 2008 solution sample RndExcelDna.zipRenamed, just renamed the file with a “.zip” extension).

Here is the overview of my prototype:

From the overview picture above, we have got:

  • 2 host processes:
    • Excel.exe. It will host
      • The ExcelDna.xll plug-in (provided by ExcelDna framework).
      • The assembly MyWcfClient.dll, i.e. my add-in for Excel providing the UDF (User Defined Function) to be used by VBA in Excel.
      • The assembly MyWcfService.dll.
    • MyServiceHost.exe. It will host MyWcfService.dll containing my WCF service (contract definition + implementation).

Here, for simplicity, I packaged the WCF contract with its implementation in a single assembly, MyWcfService.dll . Usually, you will split the contract definition from the implementation, so deployment and distribution packages to client machines do not include the service code. In the end, the service will be executed and hosted by MyServiceHost.exe.

Regarding the WCF-proxy generated assembly (either by Visual StudioAdd Service Reference” feature or a tool such as svcutil.exe), the WCF proxy-boiled code is inside MyWcfClient.exe.

One key thing with .NET DLLs being hosted in a process is that the configuration file by default is the one of the host process, here “excel.exe.config” (if you installed Office 2007 using the default path, that file is located under “C:\Program Files\Microsoft Office\Office12”).  Excel-DNA will create an AppDomain to run your add-in and will configure it to look for “<add-in>.xll.config” file (See Govert’s comment further down – thank you). So, you have 3 options:

  1. Edit Excel config file to have a section about the WCF client endpoint(s) that you want to consume.
  2. Edit a config file with the name of your add-in (e.g. for my sample below, that would be HelloWcfAddin.xll.config)
  3. Have another configuration file with the WCF information in it and load it yourself (you still need to know where this file is).  However, you will need to create the WCF objects programmatically (Endpoints, channels, …) using the custom configuration file (you will need to provide a name to your endpoint and use it in the constructor).

For simplicity for my prototype (at the time I was not aware of option 2), I used the former (edit “excel.exe.config):

I edited it and added the <system.serviceModel> section:

As documented by the ExcelDNA framework, I copied and renamed ExcelDna.xll to HelloWcfAddin.xll . I also added an xml file called HelloWcfAddin.dna (same name as for .xll file). The content of that file will have a reference to my add-in DLL, i.e. MyWcfClient.dll:

<DnaLibrary>
<ExternalLibrary Path=MyWcfClient.dll />
</DnaLibrary>

I put the DLLs in a folder:

I double-clicked the HelloWcfAddin.xll. Excel was launched and I enabled this add-in. Here are a few screenshot from Excel if you ever have to enable/disable an Excel add-in:

In a cell, I tried my new UDF function, =GetMsgFromSvc(“Hello”) , and got an error: fine, at this stage I have not yet launch the service.

From a command line window, I launched my WCF service, MyServiceHost.exe:

I tried again the call from Excel; this time is was a success:

Here are the useful links about Excel-DNA I used to do my prototype:

Advertisements
Next Post
Leave a comment

6 Comments

  1. Hi Joao – thanks for writing up the nice example.
    You might be able to put the configuration info into a file called HelloWcfAddin.xll.config. Excel-DNA will set this as the config file for the add-in’s AppDomain, so it should work the same as putting your configuration into excel.exe.config, but it puts all your add-in’s files together for easy distribution. (The ExcelDnaPack packing tool will also pack the .xll.config file.)
    -Govert

    Reply
  2. Anant

     /  November 17, 2011

    Hi Joao,

    Thanks for this nice article. But, when I try your steps, and click on ‘HelloWcfAddin.xll’, Excel says, “There was an error during processing of HelloWcfAddin.dna. There is an error in XML document (0,0). The type initializer for XMLSerializationReader’ threw an exception”.

    I wrote following lines in ‘HelloWcfAddin.xll.config’,

    MyWcfClient.dll, MyWcfService.dll and MyServiceHost.exe are created in release-mode using VS2010. And, All files are in same folder including ‘HelloWcfAddin.xll.config’.

    Please let me know where I am wrong. Thanks.

    Reply
    • Anant

       /  November 18, 2011

      Hello Joao,

      Finally, I have resolved the problem. The problem was in ‘HelloWcfAddin.xll.config’.

      Thanks & Regards,
      Anant Tripathi.

      Reply
      • Hello Anant,

        Sorry to come too late. Just saw both of your messages. I am glad to hear that you resolved it.
        # In your first comment, you mentioned you added a few lines, but I could not see the details.
        # What was wrong? How did you resolved the issue?

        Regards,
        Joao

  3. Prasad

     /  December 27, 2013

    Hello,
    Thanks for the nice article. I landed here through thanks to google. We are trying this Excel DNA for one of our projects and the objective is to provide a function in Excel through which a table will be resulting – i.e. instead of a single value (a number or a string like ‘Hello World’ !), we need to display a table (say student marks with subjects in coulmns and names in rows – given the student id). Can you please help solve this?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: