Use ExcelDNA to create Add-In to Excel

[Lee Zeitz]

Hi,

I am attempting to do the following using C#:

Create an Excel Add-In complete with ribbon and forms that can call my RESTful web service methods.  In addition, I want to create Excel UDFs of these.

My service has the ability to login, and use a session id to access other methods. My thought would be that the user could use the add-in ribbon bar button to log-in.  A separate button could allow the user access to a form that would allow them to specify their setup.  Excel UDFs would allow them to call these directly into cells.

I originally started the route of using the Visual Studio Templates (in Visual Studio 2010) and used the Excel 2007 Add-In template.  This worked great except that I could not create UDFs that Excel recognizes.  According to most of the documentation that I have found on the web, VSTO cannot create UDFs.

I then went the XLL route and used ExcelDNA.  I can create static methods that Excel can see.  I then wanted to go about creating my ribbon functionality.  I see that with DNA, I must go the hard way and create a ribbon class.  There seem to be no C# examples of how to actually do this.

I have the following questions:

- how can I persist the RESTful web service connections and session ID info using ExcelDNA?

- can I have an all in one approach where a UDF can popup a form to get login info for the RESTful service call?

- where are C# examples of how to create a ribbon with a button that would be hosted under the Add-Ins menu in Excel?

- the samples that I have found include XML.  Does the XML go into the .dna file?  If not, where does it go?

- do I need to add a reference to System.Runtime.InteropServices or does ExcelDNA provide this?  I am asking this question because I cannot specifically add a ComVisible attribute to a class.  Is there an ExcelDNA attribute that is the same?

Thanks in advance,

Lee

[Govert van Drimmelen]

Hi Lee,

Putting everything in a single Excel-DNA add-in should be a good solution.

I'll try to give some pointers to your questions. You're welcome to follow up with more specific issues.

> how can I persist the RESTful web service connections and session ID info using ExcelDNA?

Easiest is probably to write this into the Windows registry. .NET has some classes to make that easy - see the Microsoft.Win32.Registry class as a starting point: http://msdn.microsoft.com/en-us/library/Microsoft.Win32.Registry.aspx

> can I have an all in one approach where a UDF can popup a form to get login info for the RESTful service call?

You could, but your plan with a ribbon and form sounds better. Popping up a form from within a UDF can be problematic, since you'd be starting on Excel's calculation thread.

> where are C# examples of how to create a ribbon with a button that would be hosted under the Add-Ins menu in Excel?

> the samples that I have found include XML.  Does the XML go into the .dna file?  If not, where does it go?

There are some samples in Excel-DNA under Distribution\Samples\Ribbon and lots more links here; https://exceldna.codeplex.com/wikipage?title=Ribbon%20Customization&referringTitle=Documentation

For a custom ribbon you need to provide the ribbon .xml, either in a .dna file like in the examples, or return it from a GetCustomUI() override in your ExcelRibbon-derived class.

If you've made the ribbon in a VSTO project, you can export the ribbon xml from there - some right-click button, I can't remember exactly how, but that gives you a good start. However the event handlers need to be in your ExcelRibbon-derived class explicitly - Excel-DNA does not yet provide the friendly wrappers that VSTO has.

> do I need to add a reference to System.Runtime.InteropServices or does ExcelDNA provide this?  I am asking this question because I cannot specifically add a ComVisible attribute to a class.  Is there an ExcelDNA attribute that is the same?

No. The ComVisible attribute is defined in the standard mscorlib library. So you just need to add a "using System.Runtime.InteropServices;" as the top of your file.

Cheers,

Govert

[Lee Zeitz]

Hi Govert,

I successfully got all of my functionality to work in VSTO with automation.  I am also investigating the XLL approach.

In VSTO, when I add a ribbon it appears under the Add-Ins menu in Excel.  I attempted to use the sample where the ribbon was created in the .dna file for the project but do not see it under the Add-In menu.  Where does this menu appear? 

The contents of my .dna file are provided below.

Thanks,

Lee

<DnaLibrary Name="ExcelXLL Add-In" RuntimeVersion="v4.0">

  <ExternalLibrary Path="ExcelXLL.dll" LoadFromBytes="true" Pack="true" />

  <Reference AssemblyPath="System.Windows.Forms.dll" />

  <!-- 

       The RuntimeVersion attribute above allows two settings:

       * RuntimeVersion="v2.0" - for .NET 2.0, 3.0 and 3.5

       * RuntimeVersion="v4.0" - for .NET 4 and 4.5

       Additional referenced assemblies can be specified by adding 'Reference' tags. 

       These libraries will not be examined and registered with Excel as add-in libraries, 

       but will be packed into the -packed.xll file and loaded at runtime as needed.

       For example:

       

       <Reference Path="Another.Library.dll" Pack="true" />

  

       Excel-DNA also allows the xml for ribbon UI extensions to be specified in the .dna file.

       See the main Excel-DNA site at http://excel-dna.net for downloads of the full distribution.

  -->

<![CDATA[

using System;

using System.Runtime.InteropServices;

using System.Windows.Forms;

using ExcelDna.Integration.CustomUI;

[ComVisible(true)]

public class MyRibbon : ExcelRibbon

{

public void OnButtonPressed(IRibbonControl control)

{

MessageBox.Show("Hello from control " + control.Id);

}

public static void ShowHelloMessage()

{

MessageBox.Show("Hello from 'ShowHelloMessage'.");

}

}

 

public static class MyFunctions

{

public static string TestFunction()

{

return "Testing...OK";

}

]]>

  <CustomUI>

    <!-- Inside here is the exact RibbonX xml passed to Excel -->

    <!-- This will only be loaded for Excel 2010 because of the namespace -->

    <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui'>

      <ribbon>

        <tabs>

          <tab id='CustomTab' label='XLL POC Ribbon'>

            <group id='SampleGroup' label='My Sample Group'>

              <button id='btnXLLPOCRibbon' label='XLL POC' onAction='RunTagMacro' tag='ShowHelloMessage' />

            </group >

          </tab>

        </tabs>

      </ribbon>

    </customUI>

  </CustomUI>

</DnaLibrary>

[Govert van Drimmelen]

Hi Lee,

I tried your example, and found the following:

1. I took out the <ExternalLibrary ... />, since I don't have that library.

2. If the file is called TestRibbon.dna / TestRibbon.xll, it failed to load on my machine with no error message. This is probably because I already have an add-in called TestRibbon.xll that loads automatically (though from a different path). I was surprised that no error is shown, and will have to have a look.

3. I then rename to TextRibbonX.dna / TestRibbonX.xll and load again. This time I get a whole bunch of error, starting with:

    There were errors when compiling project: 

    S:\Temp\4p0bv03c.0.vb(1,0) : error BC30689: Statement cannot appear outside of a method body/multiline lambda.

This is the VB.NET compiler trying to compile the C# code in your .dna file. The default language for code in the .dna file is VB.NET. You can change it to C# by adding a Language attribute at the top:

    <DnaLibrary Name="ExcelXLL Add-In" RuntimeVersion="v4.0" Language="C#" >

4. Having fixed that and reloading, I get this error:

   There were errors when compiling project: 

   Error while loading assemblies. Exception: Input string was not in a correct format.

This is a catastrophic error of the C# parser, normally indicating that there are some brackets missing. Indeed, you need an extra { at the end of your code:

     ...

     public static class MyFunctions

     {

     public static string TestFunction()

     {

     return "Testing...OK";

     }

>> }

Then everything loads happily, and I see a tab called: XLL POC Ribbon, with a button that works as expected.

If you really want your group to be integrated into the built-in "Add-Ins" tab, you need to find the "idMso" name for that built-in tab. To do this, I downloaded the list of Excel 2010 ribbon ids from here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=6627 It turns out the idMso name is "TabAddIns". So I modify the ribbon xml like this:

        <tabs>

          <tab idMso='TabAddIns'>

            <group id='SampleGroup' label='My Sample Group'>

              <button id='btnXLLPOCRibbon' label='XLL POC' onAction='RunTagMacro' tag='ShowHelloMessage' />

            </group >

          </tab>

        </tabs>

And not the buttons are part of the built-in Add-Ins tab.

You'd skip some of the issues I mentioned above by having your ribbon implementation code as part of your add-in library, instead of directly in the .dna file.

Regards,

Govert

[Lee Zeitz]

Thanks Govert,

I think the problem was that I needed to specify the language.  Once I did that, I was able to separate the ribbon xml from the ribbon code and everything works now.

I have a question regarding the threading model.  Does ExcelDNA have an equivalent to the VSTO ThisAddIn class?  I have information that I am looking to cache for the add-in and am trying to figure out where to place the info.

Thanks,

Lee

[Lee Zeitz]

Edited question to be more specific

On Monday, July 29, 2013 11:20:35 AM UTC-4, Lee Zeitz wrote:

Thanks Govert,

I think the problem was that I needed to specify the language.  Once I did that, I was able to separate the ribbon xml from the ribbon code and everything works now.

I have a question regarding the threading model.  Does ExcelDNA have an equivalent to the VSTO ThisAddIn class?  I have information that I am looking to cache for the add-in and am trying to figure out where to place the info.  I would like to have static properties to pull information from the sheet (ActiveSheet) and workbook (ActiveWorkbook) as well as determine whether a new workbook was created.  In a VSTO, I can create an event in the following way:

            this.Application.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);

Is there an equivalent in ExcelDNA? I have seen that I can inherit from IExcelAddIn, but that runs before workbooks have been opened.  In VSTO, the ThisAddIn runs after the workbook has been opened but it seems like classes that inherit from IExcelAddIn run before the Workbook has been opened.

[Govert van Drimmelen]

Hi Lee,

In an Excel-DNA add-in you can get hold of the root Application COM object with a call to ExcelDnaUtil.Application.

If you have a reference to the Primary Interop Assemblies for Excel, you can cast the result to an Excel Application.

The IExcelAddIn.AutoOpen should be a good place to set up an event handler.

I'm not sure about the exact sequence on startup.

Regards,

Govert