CODEWITHSUNDEEP
c#excel-addinsexcel-dna
JoeyDiaz
JoeyDiaz

Reputation: 157

Excel DNA and Excel Add In COM

I have had previous experience working with an Excel Add-In vsto COM object. Specifically using the Excel.Interop dll to create a worksheet and populate it with data. As well as, interact with the worksheet by using the worksheet change events to update data, thru c#. I wanted to explore Excel DNA and possibly use it. I am completely new to excel dna, but while researching it I only found examples of creating functions and a ribbon with buttons, but I haven't seen a way to create a worksheet and populate it from a datatable.

Is Excel DNA only used for the creation of the excel add in and if I wanted to create a worksheet, populate it with data and have events I either need to use excel.interop dll or OpenXML?

Upvotes: 3

Views: 2936

Answers (2)

C. Augusto Proiete
C. Augusto Proiete

Reputation: 27818

Excel-DNA is meant to allow you to run .NET code from within Excel. If you want, for example, to have a custom Ribbon in Excel, with a button that, when clicked, will run some .NET code to create a new Workbook and populate some data, etc. then yes... Excel-DNA is a great tool for that.

If you want to create Excel files outside of Excel, for example, in a Console App or Windows Service, then Excel-DNA is not the right tool for that, and you should look at using the Excel.Interop if you know your app will run on a machine with Excel installed, or other alternatives such as ClosedXml and other OpenXml-compatible tools, that will generate Excel files without requiring Excel installed on the machine.

Upvotes: 3

Govert
Govert

Reputation: 16907

You have full access to the Excel COM Object Model from within your Excel-DNA add-in. One important step is that you have to get hold of the correct Application root object for the Excel instance that is hosting your add-in. (Just calling new Application() might get hold of another Excel instance.) To get hold of the Application object you call ExcelDnaUtil.Application - that return the COM object.

From there you can use the dynamic support in C# to talk to the object model. But better is to reference the Interop assemblies, giving you IntelliSense and early-binding.

A convenient way of referencing a set of interop assemblies (corresponding to the Excel 2010 object model) is to install the ExcelDna.Interop package from NuGet. With the 'Embed Interop Types' feature in .NET 4 (which is set true by default), you need not redistribute anything special and your code will be compatible with all Excel versions, as long as the object model parts you use are supported there.

As an entry point into running the COM code, you can make a macro, shortcut ribbon or context menu. From the object model you could also hook COM events.

A simple example with detailed instructions for making a Ribbon button that then runs some COM code is available on GitHub.

Note that the VSTO wrappers on top of the COM object model (everything in a Microsoft.Office.Tools.Excel namespace) are not compatible with your Excel-DNA add-in, so you'd have to implement that functionality yourself based on the native COM object model (the types in the Microsoft.Office.Interop.Excel namespace.)

Upvotes: 1

Related Questions