3

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?

JoeyDiaz
  • 137
  • 1
  • 2
  • 12

2 Answers2

3

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.

C. Augusto Proiete
  • 24,684
  • 2
  • 63
  • 91
  • hi I understood and maybe my question wasn't clear, but what I was asking if I use excel dna in hopes to have a ribbon and a button to create an excel workbook/sheet do I still need to use the Excel.Interop to write to excel or does Excel dna have its own way to work with excel? – JoeyDiaz Jul 12 '18 at 20:32
  • @JoeyDiaz You don't "**need**" to use the `Excel.Interop`, but I'd recommend you use it. You can just use the `ExcelDnaUtil.Application` directly via a `dynamic` variable, without the need for `Excel.Interop` as @Govert explains in detail in his answer... However, using the `Excel.Interop` makes development much easier because you get IntelliSense... – C. Augusto Proiete Jul 12 '18 at 20:38
1

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.)

Govert
  • 16,387
  • 4
  • 60
  • 70