Что такое excel dna
Those of you who are veteran Excel users are likely aware of a multitude of ways to build complex spreadsheets. By stacking formulas even someone who is non-technical can perform complex tasks. However, there are also costs to this way of doing things. Building these spreadsheets is a very manual process and it’s hard to debug (like finding where your row formula is off by a single row). It would sure be good to be able to automate spreadsheet tasks.
Then in 1993 Microsoft launched VBA. Now we had a way to automate our spreadsheets and it was great! However, over the years VBA failed to keep up with other programming languages. The development environment had a comparatively poor editor and debugging environment. Using the full power of VBA could also compromise security in business environments. In addition, quality control is difficult. It is non trivial to integrate VBA code with common source control systems like git or svn. The code is embedded in a document so viewing changes or identifying what code changed between spreadsheet versions requires a huge effort. Unit testing is also hard.
The screenshots here are from Visual Studio 2019. Everything should look pretty much the same with some cosmetic differences in Visual Studio 2017.
The first step is to set up a class library project. If we initialize a project with the name ExampleClassLibrary we should see
Now to turn this into an Excel-DNA project we’ll use NuGet. To get there look under the Tools menu
In the NuGet package manager we can search for ExcelDna. When we find the package, we can click the project ExampleClassLibrary and push the Install button. While you’re at it also install ExcelDna.Intellisense
The installer will set up necessary references and add a file named ExampleClassLibrary-AddIn.dna. After renaming Class1.cs to RibbonActions.cs our project looks like
The project will generate an .xll file that can be opened as an add-in within Excel.
In this section we’ll see how to create a tab on Excel’s ribbon. We need to define the user interface in the ExampleClassLibrary-AddIn.dna file. We’ll insert the following code before the final element
In order to run the example code below you will have to add references to the project. The required system references are Microsoft.Office.Interop.Excel, PresentationFramework and PresentationCore. The code is
This code will get a Yes or No from the user and insert it into the selected cell on the current Excel spreadsheet. One quick note is that the project will build 2 add-ins, one for 64 bit Excel and one for 32 bit Excel (This is a property of the Office installation not the operation system. A 64 bit Windows system can run 32 bit Office). If we want to debug our add-in we only need to right click on the project and select Debug. This will open an instance of Excel and load the add-in. Excel will ask you for permission to load the add-in. Go ahead since it is our own development project. If you are running a 64 bit version of Office you have one additional task before you can debug. Go to the Debug tab of the project settings and add 64 to the end of the add-in name
Since we haven’t done anything fancy the Example Ribbon Tab will look like
Click the button and try it out!
Using this ribbon functionality we can add rich GUI interfaces and wizards to interact with our spreadsheets.
With Excel-DNA it is dead easy to write UDF functions to run from cells in Excel. We’ll add a file to our project titled ExcelFunctionCalls.cs and add the following code
We now have a function that gives us last week and another that reverses a string. The Attributes ExcelFunction and ExcelArgument give Excel-DNA additional information about how to display help and process the arguments. To enable Intellisense we’ll create a file named COMIntegration.cs and add the code
This is a special class where the AutoOpen() function is run when the add-in is loaded and the AutoClose() function is run when the add-in is closed. Here we have added code for using Intellisense with our UDF functions. Let’s compile and debug this now. We can see what our Intellisense looks like
The directives COMVisible and ClassInterface are pretty much all you need to make this a COM visible class. In order to be able to use this from VBA there is another step necessary. We need to generate a typelib for the project. We’ll use the tool TlbExp.exe as a post build step to automatically generate a typelib for the project. We’ll also need to update the COMIntegration.cs file
We can now edit VBA in a sample spreadsheet. Before we can run this code we have to add a reference to the typelib. In the Excel VBA editor under the Tools menu select References… Select browse and navigate to the typelib ExampleClassLibrary.tlb and select it. You should then see something like
Let’s create a module and insert the following code (remember to name the workbook with extension .xlsm to allow macros)
Running this macro will create the output
Et Voila! With little effort a full COM interface for VBA.
The Excel-DNA Runtime is free for all use, and distributed under a permissive open-source license that also allows commercial use.
Excel versions ‘97 through 2016 can be targeted with a single add-in. Advanced Excel features are supported, including multi-threaded recalculation (Excel 2007 and later), registration-free RTD servers (Excel 2002 and later) and customized Ribbon interfaces (Excel 2007 and 2010). There is support for integrated Custom Task Panes (Excel 2007 and later), offloading UDF computations to a Windows HPC cluster (Excel 2010 and later), and for the 64-bit versions of Excel 2010 and 2013.
Most managed UDF assemblies developed for Excel Services can be exposed to the Excel client with no modification. (Please contact us if you are interested in this feature)
Important Links
You are also welcome to contact us with questions, comments or suggestions.
Getting Started
Get going with some first steps by following the Getting Started page.
If you are using a version of Visual Studio that supports the NuGet Package Manager (including Visual Studio 2019 Community), the easiest way to make an Excel-DNA add-in is to:
The Excel-DNA NuGet Package installs the required files and configures your project to build an Excel-DNA add-in.
How To’s
-
The packing utility allow you to pack your add-in into a single .xll file for easy distribution. and running generally.
- Accepting Range Parameters in UDFs.
Samples
Various sample projects and snippets related to Excel-DNA are available in the Samples repository.
Additional samples are available in the Distribution/Samples folder and contains various .dna files, each of which is a self-contained add-in that exhibit some Excel-DNA functionality.
The .dna files are .xml files that can be edited with a regular text editor.
To run any of the sample .dna files, make a copy of the Distribution\ExcelDna.xll file, place it next to the .dna file, and rename to have the same prefix. E.g. to run Optional.dna , make a copy of ExcelDna.xll called Optional.xll , and double-click, or File->Open to load in Excel.
Power Tools
-
provides tools to make help generation easier. allows the automatic generation of parameter and function conversions, removing boiler-plate code for optional parameters, async functions etc. add in-sheet IntelliSense for Excel UDFs.
Community Projects
-
is a command-line utility to extract the contents of Excel-DNA add-ins packed with ExcelDnaPack facilitates mocking & unit testing of Excel-DNA Add-Ins is a user-contributed template (thank you very much to Lee Zeitz!) for making a WiX-based installer for an Excel-DNA add-in. provides strong name key pair used to sign Excel-DNA assemblies. integrates Excel-DNA Diagnostic Logging with your Serilog logging pipeling within your Excel-DNA Add-In is a Serilog sink that writes events to Excel-DNA LogDisplay is a Serilog Enricher with properties from Excel-DNA Add-Ins
External Links
Various Samples and Tutorials
External projects using Excel-DNA
FinAnSu uses a ribbon interface to make the various functions and macros easy to find. The RTD server support is used to implement asynchronous data update functions, providing a live quote feed from Bloomberg, Google or Yahoo! And then there is a bunch of useful-looking financial functions. Here’s a little preview:
Support
There is a searchable record of more than 5000 messages on the Excel-DNA Google Group.
There are many questions answered on Stack Overflow under the tag excel-dna .
Please don’t hesitate to ask. If you are stuck or need some help using Excel-DNA your questions really are very welcome - whether you are just getting started, or an Excel-DNA expert.
And if you could help put together some proper documentation, please contact me. We’d be happy to add you as an editor in this repository.
Related Projects
Performance
Information about the performance of Excel-DNA user-defined functions can be found on the Excel-DNA Performance page.
Formal Support Agreements
Corporate users of Excel-DNA, using the library as part of their mission critical infrastructure, are encouraged to enter into a formal support arrangement. We offer an annual subscription-based technical support agreement, providing direct support, priority bug-fixes and feature development and ensuring that Excel-DNA will continue to be updated and developed.
Donations
Financial support for the Excel-DNA project encourages future development and is greatly appreciated. Transactions are processed by PayPal.
More Details
Excel versions ‘97 through 2016 can be targeted with a single add-in. Advanced Excel features are supported, including multi-threaded recalculation (Excel 2007 and later), registration-free RTD servers (Excel 2002 and later) and customized Ribbon interfaces (Excel 2007 and 2010). There is support for integrated Custom Task Panes (Excel 2007 and later), offloading UDF computations to a Windows HPC cluster (Excel 2010 and later), and for the 64-bit versions of Excel 2010 and 2013.
Most managed UDF assemblies developed for Excel Services can be exposed to the Excel client with no modification. (Please contact me if you are interested in this feature.)
Since Excel-DNA uses the Excel C API, porting C/C++ add-in code based on the Excel XLL SDK is very easy. (No more XLOPER s!)
The Excel-DNA Runtime is free for all use, and distributed under a permissive open-source license that also allows commercial use.
Latest Releases
The current version is Excel-DNA v1.1, released in June 2020 and includes numerous improvements and bug-fixes.
Для начала создадим новый проект типа Class Library, назовем его cryptostar. Подключим библиотеку excel-dna:
Install-Package ExcelDna.AddIn
Install-Package ExcelDna.Integration
Install-Package ExcelDna.Interop
Первым делом напишем загрузчик данных:
Пояснять данный код я не буду, так как он довольно простой и к нашей теме отношение имеет довольно опосредованное.
Теперь мы умеем получать данные в виде массива объектов класса Ticker. Пришло время научиться отображать эти данные на текущей странице.
Что бы отобразить данные, нам понадобится экземпляр класса Microsoft.Office.Interop.Excel.Application. Он предоставляет доступ к объектной модели Excel, через него мы сможем получить объект-страницу(worksheet) и записать наши данные в нужные ячейки. Давайте напишем класс для записи данных на страницу.
При работе с объектной моделью надо помнить о том, что работаем со ссылками на COM объекты. В основном потоке Excel мы можем спокойно использовать эти объекты и не заботиться об освобождении ссылок (Marshal.ReleaseComObject), однако, если мы захотим использовать объектную модель из отдельного потока, у нас есть два варианта:
- Самостоятельно отслеживать все используемые объекты и очищать ссылки на них. Этот подход чреват ошибками и я не рекомендую его использовать.
- ExcelDna предоставляет возможность добавить задание на выполнение в основном потоке, для этого предназначен метод ExcelAsyncUtil.QueueAsMacro, пример использования:
Мы объявили кнопку, располагающуюся на закладке и группе с названием cryptostar. У кнопки задан обработчик onAction=’OnButtonPressed’, при нажатии на кнопку будет вызван метод OnButtonPressed в классе RibbonController.
Помимо обработчика мы указали изображение для кнопки: image=’bitcoin’. Имя изображения задается в конфигурационном файле — Cryptostar-AddIn.dna. Данный файл автоматически добавляется в проект при подключении nuget’a. Пример:
Сборка и Отладка
Наш плагин готов, давайте попробуем его собрать. Нажимаем F5. После чего получаем набор файлов *.xll:
Cryptostar-AddIn64-packed.xll, Cryptostar-AddIn-packed.xll, Cryptostar-AddIn.xll, Cryptostar-AddIn64.xll
Видим, что полученные файлы отличаются как по разрядности, так и по наличию слова packed. С разрядностью все понятно, выбирать нужно тот, который совпадает по разрядности с Excel. А чем же отличаются packed и не packed add-in'ы? ExcelDNA позволяет упаковывать зависимости плагина в .xll файл. Зависимостями могут являться любые файлы, используемые в проекте, например внешние библиотеки или картинки. Зависимости задаются в конфигурационном файле, выглядит это так:
Обратите внимание на атрибут Pack=”true”, он указывает, что данный файл должен быть упакован.
Если мы используем неупакованный add-in, то в одной директории с ним должны находиться и все его зависимости.
Теперь выбираем подходящий .xll файл и запускаем его. Если вы все сделали правильно, то после открытия Excel увидите новую вкладку Cryptostart и кнопку Get Data, а по нажатию на нее страница наполнится данными по валютам:
К сожалению, программы редко работают с первого раза, поэтому нам может потребоваться отладчик. Настроить отладку ExcelDna add-in'а просто. Для этого в свойствах проекта на закладке Debug выбираем Start External Program и прописываем путь к Excel.exe, в моем случае это G:\Program Files\Microsoft Office\Office14\Excel.exe. В start options пишем название упакованного файла add-in'a с учетом разрядности Excel. Например, Cryptostar-AddIn64-packed.xll. Все, теперь мы можем нажать F5 и полноценно отлаживать add-in.
Делаем установщик
Итак, add-in сделан, отлажен, протестирован и готов к работе. Вопрос в том, в каком виде его распространять. Один из вариантов доверить установку add-in'a пользователю. Делается это через интерфейс Excel, на закладке developer tab->Add-ins->Browse указываем путь к .xll файлу. Данный способ будет работать, только если .xll файл подписан сертификатом и сертификат присутствует в trusted root certification authorities store. Как создать сертификат и подписать им файл хорошо описано здесь.
Альтернативный способ – написать свою программу для установки add-in'a, которая бы прописывала необходимые ключи в реестре и таким образом регистрировала наш add-in. Задача эта не из легких, т.к. необходимо учитывать различные версии Excel у которых пути и ключи в реестре различаются. Но к счастью эта задача уже решена и существует шаблон проекта — инсталлятора, выполняющего необходимые действия. Шаблон можно взять здесь.
Заключение
В результате мы познакомились с библиотекой Excel-DNA и прошли полный путь от разработки add-in'a до его отладки и создания установщика.
Excel-DNA Registration Helper
This library implements helper functions to assist and modify the Excel-DNA function registration, by applying various transformations before the functions are registered.
The following transformations have been implemented:
Generation of wrapper functions for:
Examples of general function transformations:
- Logging / Caching / Timing handlers
- Suppress in Function Arguments dialog
If you've previously used the CustomRegistration library, note that I've renamed and rearranged the project source, and renamed the output assembly from ExcelDna.CustomRegistration to ExcelDna.Registration. The last state of the project before the large-scale rearrangement is marked by the git tag CustomRegistration_Before_Rename, and can be retrieved from the release tab on GitHub.
To make a simple add-in that uses the Excel-DNA Registration extension to dynamically update the HelpTopic information for function registrations:
- Press F5 to compile and start in Excel.
- Start typing =SayHello( in a cell and press the Fx button to open the function wizard. Check that the HelpTopic has been updated during registration to open Bing instead of Google.
See the add-ins in the Samples directory to see various registration update extensions.
Step-by-step for Visual Basic
Once you have a basic Visual Basic add-in working.
From the NuGet Package Manager Console, (or the Manage NuGet Packages dialog): PM> Install-Package ExcelDna.Registration.VisualBasic
Fix up your .dna file by changing to ExplicitRegistration for your library registration, and packing the extra ExcelDna.Registration libraries:
3.Perform the explicit registration in your AutoOpen by calling ExcelDna.Registration.VisualBasic.PerformDefaultRegistration():
Registration [Error] Repeated function name.
If you receive this error when opening your Excel addin, you need to add ExplicitRegistration="true" to the .
- 184 053 total downloads
- last updated 22.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dna
This NuGet package is deprecated, and used for compatibility only. The basic Excel-DNA NuGet package is now called ExcelDna.AddIn.
ExcelDna. Interop by: excel-dna
- 112 357 total downloads
- last updated 06.08.2015
- Latest version: 14.0.1
- ExcelInteropPIAExcelDna
Provides a local copy of the following assemblies from the Microsoft Office 2010: Primary Interop Assemblies Redistributable - Microsoft.Office.Interop.Excel.dll, Office.dll, Microsoft.Vbe.Interop.dll.
ExcelDna. IntelliSense by: excel-dna
- 96 516 total downloads
- last updated 23.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dnaintellisense
ExcelDna. Registration by: excel-dna
- 78 716 total downloads
- last updated 22.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dna
- 56 678 total downloads
- last updated 22.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dna
Deprecated reference library package for Excel-DNA. Use the package ExcelDna.AddIn to create a new add-in. Use the package ExcelDna.Integration to only reference the integration library.
- 40 395 total downloads
- last updated 23.12.2021
- Latest version: 1.5.1
- Excel-DNAExcel
ExcelDna. Registration. FSharp by: excel-dna
- 10 729 total downloads
- last updated 22.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dna
ExcelDna. Registration. VisualBasic by: excel-dna
- 9 892 total downloads
- last updated 22.12.2021
- Latest version: 1.5.1
- excelexceldnaudfexcel-dna
Excel-DNA Registration is an extension package for Excel-DNA, adding custom registration processing. This package adds additional transformations appropriate to Visual Basic projects, enhancing VBA compatibility.
ExcelDna. XmlSchemas by: excel-dna
- 4 978 total downloads
- last updated 27.04.2019
- Latest version: 1.0.0
- excelexceldnaintellisensexsddnaexcel-dna
This package contains XSD files that enables IntelliSense and validation when editing `.dna` files, for example, in Visual Studio.
ExcelDna. Interop. Dao by: excel-dna
- 1 746 total downloads
- last updated 06.08.2015
- Latest version: 14.0.2
- ExcelInteropPIAExcelDna
Provides a local copy of the Microsoft.Office.Interop.Access.Dao.dll assembly from the Microsoft Office 2010: Primary Interop Assemblies Redistributable. Can only be used in the 322-bit version of Excel.
ExcelDna. Testing by: excel-dna
- 667 total downloads
- last updated 11.11.2021
- Latest version: 1.5.0-rc (prerelease)
- excelexceldnaexcel-dnaxunit
Got questions about NuGet or the NuGet Gallery?
Read the Frequently Asked Questions about NuGet and see if your question made the list.
Читайте также: