Creating an XLL add-in using Excel-DNA

On a previous post I walked through creating an XLL add-in using C# and ManagedXLL for Excel. This is a commercial library which offers many options for interacting and integrating with Excel. But there’s a nice, open source, alternative called Excel-DNA, if you prefer to go this way.

So let’s take a look at creating a Excel add-in using Excel-Dna.

  1. Let’s start off by creating a class library in C# (I’m going to call mine ExcelDNATest, I know it’s not very original)
  2. Using NuGet, add the Excel-DNA package to your solution
  3. Copy and rename packages\Excel-DNA.0.30.3\tools\ExcelDna.xll to projectname-AddIn.xll, so in my case that’s ExcelDNATest-AddIn.xll
  4. We need to copy this file to the output folder so either create a post build step to copy this or I simply added it to the project and marked it’s “Copy to Output Directory” to “Copy if newer”
  5. You’ll notice that the NuGet package added a file named projectname-AddIn.dna to the project. This is the config file for the XLL add-in we’re creating. If you open it you should see an ExternalLibrary element which has the name of the DLL we’re going to build.

At this point we’ve created the barebones for our DLL which the information which allows the XLL file to call into our DLL which we shall use to contain the Excel functions.

You should have found the NuGet package added a file ExcelDna.Integration.dll to our project. This contains the attributes and other code which allows us to expose Excel functions from our code and for us to talk to Excel.

So at this point, to recap, we’ve created a DLL, we’ve added Excel-DNA to the project. We’ve copied/renamed the XLL file to match out project and ensure the .dna configuration file points to our DLL name. Now it’s time to have a little more fun…

Let’s add a function to our class and usable from Excel

  1. Rename your Class1.cs file to MyFunctions.cs (or whatever you prefer)
  2. Open the class file make the class static
  3. Add a static method as follows 
    [ExcelFunction(Name="MyFunctions.Hello", Description="Hello X", 
Category="My Excel Functions"]
public static string Hello([ExcelArgument("Enter your name")] string name)
{
   return "Hello " + name;
}

    We’ve now got a function which will appear in the Excel function wizard in the category “My Excel Functions”. The function will be named MyFunction.Hello and has the description “Hello X”.

  4. Simply type

    =MyFunctions.Hello(“Mark”)

    in your spreadsheet cell to see the function in action.

Alternatively create the code in the DNA file

I’m not sure how useful this is, but you can also create UDF (user-defined functions) inside the .DNA file.

For example

<DnaLibrary Name="ExcelDNATest Add-In" RuntimeVersion="v4.0" Language="CS">
  <![CDATA[
  using ExcelDna.Integration;
  
  public class SomeMoreFunctions
  {
     [ExcelFunction(Description="Test Function")]
     public static string Test()
     {
        return "This is a test function";
     }
  }
  ]]>
</DnaLibrary>

And the rest is as per the compiled source code, i.e. it’s available in the function wizard etc.