We’ve covered a lot of ground in the last two blog posts, now its time to start looking at Excel DNA in more detail. In this post we are going to look at how Excel DNA works. It’s important to have an idea of how the product works as this will help when we start writing solutions based on the technology.
Solutions written with Excel DNA work in the following way. The dot Net code is called by a standard C XLL, which is called by Excel. That’s all there is to it. It looks like this:
There are two ways that the bridge addin (the XLL) can use your dot Net code. In the simplest implementation you write your code in a basic XML file, this is why you can use Excel DNA with out even having an IDE installed. The DNA XLL will then pick up the XML file read out the code and compile it. It does this when the addin is first loaded, so there is a small amount of overhead here. The complier is part of the dot Net framework, so as long as you have the framework installed on the PC the code will run on, it will always work. The other method is where you provide a complied assembly* to the XLL. This methods requires you to write the code in some sort of IDE, compile it to a DLL and then you set a reference to that DLL in the XLM/DNA file, so there is one more file with this method. One of the many nice features about Excel DNA is that it provides a packing tool, so you can budel all these bits, and any other support file you need into a single XLL – which is of course super easy to deploy!
There one other very important component with Excel DNA and that’s the integration library (it’s called ExcelDna.Integration.dll). This is the brains of Excel DNA. It about 30 or so C# classes that deal with loading you managed code, implementing any attributes you have specified, creates RTD services if you have coded that, handles errors, sets up the ribbon/menu interfaces, gives up an Excel Application object/Instance etc… When you write a complied library you need to reference this library in that code, this step is not needed for the text file method. There’s a lot going on in the integration library, and its worth having a poke around in the source code once you get up and running with the product.
Well that it for now, next week, we’ll actually write some code and make an addin – exciting times!
*An assembly in dot Net is either a complied EXE or DLL, it basically another name for a library.
anxious to see it, I’ve been trying with no luck to make it work. some questions I have / things I’d like to see covered are:
1. how to properly set up VS2010 (w/ exceldna’s version 0.27).
2. how do you code outside a CDATA block, so that you can actually use VS2010 features.
3. how to do stuff beyond UDFs.
but in any case, thanks for directing me to exceldna in the first place!
Hi cmenem,
I’ll be using SharpDevelop for these post for reasons I’ll detail at the time, but VS2010 should be fine with 0.27 as it support version 4 of the framework – what problems are you having?
>>CDATA block, so that you can actually use VS2010 features.
Not sure what you mean by this? CDATA is used in the XML file when you are not writing the code in a IDE – maybe the next post will help show folks what you need to do…
We will be looking at stuff beyond UDF in later posts so stay tuned.
Thanks for reading, and please keep the feedback coming!
cheers
Ross
Ross,
Nice article but I believe we may need more details here.
How secure is a Excel DNA solution?
Can we choose between having the Excel DNA solution loaded in its own appdomain (isolated) and not?
I’m not sure why it uses ExcelDna.Integration.dll in the first? If it rely on and use .NET Framework what role does the external play? My interpretation is that it’s a stripped version of an Interop.DLL.
Keep up the good work,
Dennis
Hi Dennis,
Not to sure about the security side of a DNA solution, obviously the XLL obeys the same rules as any other unmanaged Excel Addin, but I’m not sure what is required for the managed side of thing, and if this gets stopped if running in partial trust etc. – Maybe Govert or Simon could post a comment here? From what I’ve seen the major issue is in trying to call a DNA solution from another addin.
The Integration.dll , is not a stripped version of the Interop.dll. It’s doing a lot of the work for the developer, for example, we can write a method in a .Net file and compile it to a dll, if we decorate that dll will see these and add the methods to a new menu (for example). Likewise it will take some XML and create (implement that xml) in to a ribbon or menu. It gives an easy to use wrapper for the XL4 call function of the C API(not all of them at the moment I think), and lot of other useful things – it’s more than the interop.dll, and its specific to Excel.
Thanks for the feedback Dennis,
Cheers
Ross
Hi,
Firstly, I’m the developer of Excel-Dna. Thank you to Ross for this great series.
Secondly, It’s going to be pretty hard keeping track of all the questions here, so I suggest you post any questions about Excel-Dna to the Google group, http://groups.google.com/group/exceldna, which is the primary support site. With more than a 1000 posts, it is also a great source of information about Excel-Dna. And I’m quite good at checking and answering there.
cmenem:
- The .Net 3.5 and .Net 4 language features can be used in the .dna file – just add a CompilerVersion or RuntimeVersion attribute. There are some examples in the Distribution.
- There are examples of macros in the Distribution – look through the MoreSamples.dna file for some inspiration.
Dennis:
- Excel-Dna add-ins always load into their own AppDomain – there is no option to load into the default AppDomain. Excel-Dna RTD servers load into the same AppDomain as the rest of the add-in.
- Ross is right about the security aspects. No additional VSTO-like security model is imposed.
- The ExcelDna.Integration.dll library is the heart of the runtime, it uses reflection to look at your assembly and expose the functions and macros to Excel. It contains no COM Interop aspects. It is embedded in the .xll for use at runtime, so need not be separately shipped. But at compile-time you can reference it in your project if you want to use the ExcelDna attributes or helper functions.
Govert
Ross and Govert,
Thanks for the feedback. And also for the link to the Q&A for Excel DNA.
The “picture” is getting better with the explanation of the role the Integration DLL actually plays.
As for the security aspect; although the VSTO model is complex the intention with it is good. Third-party tools can handle it quite painless if we don’t want to spend time to solve it.
The lack of any security is, at least for me, not always good. Sure, it’s nice for a developer but not always good for the clients.
Is it possible to digitally sign the solution with a commercial certificate (Comodo/Veritas/Thawte et al), also when using a XML file?
Kind regards,
Dennis
Hi Dennis,
Excel-Dna allows you to pack everything (the .dna file, your compiled .dll and any dependencies) into a single .xll file, which can then be signed and treated like any binary .xll file. Allowing this was one of the motivations for the packing feature, though I have no experience with the signing aspects myself.
Packing into a single .xll also makes the deployment of Excel-Dna solutions much more ‘add-in’-like, as opposed to having a full-blown installer that makes your add-in seem like a new ‘program’ that the user installs. I find the idea of a single-file add-in that you can copy and either open or add to the Excel add-ins list extremely appealing. The Ribbon and RTD support in Excel-Dna is designed so that no installation or registration step is needed to make them work.
Regards,
Govert
Govert,
It sounds better and better ;)
I will take a closer look and create an example to test signing and other things as well.
The single-file-add-in should be a highly appreciated approach. Excel developers are used to only handle one file and a simple installation process.
Now I need to test for myself. Thanks for the information and I’m looking forward to the next chapter about Excel DNA.
Kind regards,
Dennis
Hi again,
In my previously comment I forgot to ask about the installation process.
Since it’s a XLL file it needs to be activated. Is this automatically done or does it require any action from the developer or Excel user?
Kind regards,
Dennis
Hi Dennis,
There is no installer in XL DNA, either the user can install the addin via the Excel interface or the dev can use a .MSI, or some other installer and script the COM object or go direct to the registry.
Thanks
Ross
Hi Dennis,
Not sure what you mean by ‘activated’? The .xll can be loaded by double-clicking on it, or by File->Open or by adding it to the Excel Add-ins list. If the file is unsigned, you might need to deal with the Trust Center of course ….
If you want to make a light-weight ‘installer’, I think a nice plan is to prompt the user when the .xll is first opened, and then add it to the add-ins list programmatically (using Application.AddIns.Add…) so that Excel will automatically load it in future. Nothing like this is built in, but I think it would be quite easy to do, and a nice example for Ross’s series.
Otherwise, installation would just entail creating the OPENx registry key, as for any .xll add-in.
If you need to do a proper install for ‘All Users’ on a machine, with support for installing under Windows 7 UAC, you will have to do more work, and probably making an installer is a good idea. I’ve not yet tried (or seen the need) to jump through those hoops…
Regards,
Govert
I remembered now that Chris Spicer had a posting about signing an .xll: http://www.technicana.com/index.php?option=com_content&view=article&id=53&Itemid=61
Nice to get a name-check from Govert! In my experience I have found that adding the XLL to the addin-list in the registry during installation is a good approach.
In my opinion, deploying ExcelDna is no harder than deploying a normal XLL, easier than deploying a COM add-in and simple compared to deploying a VSTO add-in (for Excel 2003, at least).
I like this article and I look foward to seeing more. Will you be going in to more depth as to how ExcelDna addresses the .Net functions? All those thunks make my head spin…
All the best,
Chris
Hi Chris,
Thanks for your comments.
The plan is to give the basics of how to get started with DNA, in a reasonably logical set of steps, so far I have introduced the product, taked about the options and the reasons to use it, then a simple function next how to use the IDE and DLL, etc…
After this I’ll switch to doing more random post about what ever I like at the time, so maybe some of them will have useful content.
If there is anyone out there who has done something interesting and wants to write it up I would be happy to post it here, to help the wider community etc.
thanks
Ross
Ross and Govert,
Thanks for the feedback.
[quote]Nothing like this is built in, but I think it would be quite easy to do, and a nice example for Ross’s series.[/quote]
Yes, it was that I meant. Agreed, looks like Ross has some more input for coming articles.
I have a rather strong opinion that end user should not be involved to load and activate any add-ins. In other words, they may click on a msi/exe file to start the installation and next they start to use the add-ins.
Now I will read what Chris has to say.
Kind regards,
Dennis
This looks very interesting. Thanks for sharing!
[...] How does Excel DNA Work? [...]