Writing an Excel add-in in .NET using the Excel-DNA library
Excel-DNA
Excel-DNA – a library for .NET, with which you can write and build a completely self-contained add-in file for Excel with the extension .xll
It is enough to put this file in a user folder, without administrator rights, and simply enable it in the Excel settings.
Such an add-in will have access to the Excel COM model, the Excel C API – allows you to interact with the program interface and workbooks. Will be able to implement custom formulas and add your own menu to the Ribbon UI interface ribbon.
In the article I will tell you how to write your own formula for Excel in C#. Build and install your first add-on.
Preparation
Create a new class library project for .NET 6
dotnet new classlib --framework net6.0 -o ExcelAddIn
In file .csproj you need to change the value of the framework version to
<TargetFramework>net6.0-windows</TargetFramework>
Now let’s install the base package ExcelDna.AddIn
dotnet add package ExcelDna.AddIn
You can write in both Visual Studio and VSCode.
To debug code in VSCode you need to create a file launch.json and change two lines in it, "program" And "args":
{
"version": "0.2.0",
"configurations": [
{
"name": ".NET Core Launch (console)",
"type": "coreclr",
"request": "launch",
"preLaunchTask": "build",
"program": "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE",
"args": [
"${workspaceFolder}\\bin\\Debug\\net6.0-windows\\ExcelAddIn-AddIn64.xll"
],
"cwd": "${workspaceFolder}",
"console": "internalConsole",
"stopAtEntry": false
},
{
"name": ".NET Core Attach",
"type": "coreclr",
"request": "attach"
}
]
}
You need to put your own paths to the Excel program file and the assembled add-in.
First formula
Let’s write a simple formula that adds two numbers:
global using ExcelDna.Integration;
namespace ExcelAddIn;
public static class ExcelFunctions
{
[ExcelFunction]
public static double DNASUM(double a, double b)
{
return a + b;
}
}
All static methods marked with the attribute [ExcelFunction] are now accepted by Excel as custom formulas.
Press F5, Excel will open, which will already open our add-in file. Since we do not have a digital signature, a warning will appear.
Click “Enable” and check the work of our formula
IExcelAddIn Interface
For our add-in to work, it may need to do some useful things when it opens and closes. For example, I read settings from the registry, connected databases.
Let’s create a class MyAddInwhich implements the interface IExcelAddIn
public class MyAddIn : IExcelAddIn
{
public void AutoClose()
{
throw new NotImplementedException();
}
public void AutoOpen()
{
throw new NotImplementedException();
}
}
When the add-in is opened, an instance of the class will be created MyAddIn and executed method AutoOpen()
We make a formula for requesting the exchange rate from the Central Bank website
Now let’s write a formula that does something useful, for example, requests the exchange rate from the Central Bank website.
Client interface:
namespace ExcelAddIn.Services;
public interface ICurrencyClient
{
public Task<decimal?> GetExchangeRate(DateTime date);
}
Central Bank website API client class. Parse XML with rates for the desired date:
using System.Diagnostics;
using System.Text;
using System.Xml.Linq;
namespace ExcelAddIn.Services;
public class CurrencyClient : ICurrencyClient
{
private readonly HttpClient _httpClient;
private const string _requestAddress = @"https://www.cbr.ru/scripts/XML_daily.asp?date_req=";
public CurrencyClient(HttpClient httpClient)
{
_httpClient = httpClient;
}
public async Task<decimal?> GetExchangeRate(DateTime date)
{
string request = $"{_requestAddress}{date.Date:dd/MM/yyyy}";
HttpResponseMessage response = await _httpClient.GetAsync(request);
try
{
response.EnsureSuccessStatusCode();
var bytes = await response.Content.ReadAsByteArrayAsync();
var xml = Encoding.GetEncoding(1251).GetString(bytes);
XElement valCourses = XElement.Parse(xml);
decimal? exchangeRate = decimal.Parse(valCourses.Elements("Valute")
.Where(e => e.Element("Name").Value == "Евро")
.FirstOrDefault()
.Element("Value").Value);
return exchangeRate;
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return null;
}
}
}
Add the necessary services and create a service provider
using Microsoft.Extensions.DependencyInjection;
using ExcelAddIn.Services;
using System.Text;
public class MyAddIn : IExcelAddIn
{
public static ServiceProvider ServiceProvider { get; private set; }
public void AutoOpen()
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
IServiceCollection Services = new ServiceCollection();
Services.AddHttpClient()
.AddSingleton<ICurrencyClient, CurrencyClient>();
ServiceProvider = Services.BuildServiceProvider();
}
public void AutoClose()
{
}
}
And finally, we define a method for the formula:
using ExcelAddIn.Services;
using Microsoft.Extensions.DependencyInjection;
namespace ExcelAddIn;
public static class ExcelFunctions
{
[ExcelFunction]
public static object ExchangeRate(double dateField)
{
ICurrencyClient currencyClient = MyAddIn.ServiceProvider.GetService<ICurrencyClient>();
DateTime date = dateField == 0 ? DateTime.Today : DateTime.FromOADate(dateField);
if (ExcelAsyncUtil.Run(nameof(ExchangeRate), dateField, delegate
{
return currencyClient.GetExchangeRate(date)
.GetAwaiter()
.GetResult() ?? -1m;
}) is not decimal requestResult)
{
return "Загрузка...";
}
else if (requestResult < 0)
{
return ExcelError.ExcelErrorNA;
}
else
{
return Math.Round(requestResult, 2);
}
}
}
Now the formula =ExchangeRate( ) returns today’s euro exchange rate, and if you refer to a cell with a date in Excel format, to the desired date.
Assembly and installation
To assemble our add-in into one file with all dependencies, we need to add to the file .csproj lines
<Target Name="PackedReferences" AfterTargets="AfterBuild" BeforeTargets="ExcelDnaBuild">
<ItemGroup>
<References Include="$(OutDir)*.dll" Exclude="$(OutDir)$(TargetFileName)" />
</ItemGroup>
<PropertyGroup>
<ExcelAddInInclude>@(References)</ExcelAddInInclude>
</PropertyGroup>
</Target>
Now we do
dotnet build
and in folder \bin\Debug\net6.0-windows pick up two collected files for 32 and 64-bit versions of Excel
These files must be placed in a folder %AppData%\Microsoft\AddIns and in the Excel settings, check the box next to our file. Ready!
Conclusion
This article describes how to write and build a simple Excel add-in using .NET.
The code can be viewed at the link https://gitea.cebotari.ru/chebser/ExcelAddIn/
