Главная

Пишем надстройку для Excel на .NET с помощью библиотеки Excel-DNA

2023-06-10 в 19:07, admin, рубрики: .net, C#, Excel, плагины

Excel-DNA

Excel-DNA - библиотека для .NET, с помощью которой можно написать и собрать полностью самодостаточный файл надстройки для Excel с расширением .xll
Этот файл достаточно положить в пользовательскую папку, без прав администратора, и просто включить в настройках Excel.

Такая надстройка будет иметь доступ к COM-модели Excel, C API Excel - позволяет взаимодействовать с интерфейсом программы и рабочими книгами. Сможет реализовать пользовательские формулы и добавить свое меню на интерфейсную ленту Ribbon UI.

В статье я расскажу как написать свою формулу для Excel на C#. Собрать и установить свою первую надстройку.

Подготовка

Создадим новый проект библиотеки классов для версии .NET 6

dotnet new classlib --framework net6.0 -o ExcelAddIn

В файле .csproj нужно изменить значение версии фреймворка на

    <TargetFramework>net6.0-windows</TargetFramework>

Теперь установим базовый пакет ExcelDna.AddIn

dotnet add package ExcelDna.AddIn

Писать можно и в Visual Studio, и в VSCode.

Чтобы дебажить код в VSCode нужно создать файл launch.json и в нем изменить две строки, "program" и "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"
        }
    ]
}

Пути к файлу программы Excel и собранной надстройки нужно поставить свои.

Первая формула

Напишем простую формулу, которая складывает два числа:

global using ExcelDna.Integration; 

namespace ExcelAddIn;  

public static class ExcelFunctions
{
    [ExcelFunction]
    public static double DNASUM(double a, double b)
    {
        return a + b;
    }
}

Все статические методы помеченные атрибутом [ExcelFunction] теперь принимаются Excel как пользовательские формулы.

Нажмем F5, откроется Excel, который уже откроет файл нашей надстройки. Поскольку у нас нет цифровой подписи появится предупреждение.

Нажимаем "Включить" и проверяем работу нашей формулы

Интерфейс IExcelAddIn

Для работы нашей надстройки может понадобится, чтобы она при открытии и закрытии выполняла какие-либо полезные вещи. Например читала настройки из реестра, подключала базы данных.

Cоздадим класс MyAddIn, реализующий интерфейс IExcelAddIn

public class MyAddIn : IExcelAddIn
{
    public void AutoClose()
    {
        throw new NotImplementedException();
    }  

    public void AutoOpen()
    {
        throw new NotImplementedException();
    }
}

При открытии надстройки будет создан экземпляр класса MyAddIn и выполнен метод AutoOpen()

Делаем формулу запроса курса валют с сайта ЦБ

Напишем теперь формулу, которая делает что-то полезное, например запрашивает курс валют с сайта ЦБ.

Интерфейс для клиента:

namespace ExcelAddIn.Services; 

public interface ICurrencyClient
{
    public Task<decimal?> GetExchangeRate(DateTime date);
}

Класс клиента API сайта ЦБ. Парсим XML с курсами на нужную дату:

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;
        }
    }
}

Добавляем нужные сервисы и создаем провайдер сервисов

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()
    {
    }
}

И, наконец, определяем метод для формулы:

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);
        }
    }
}

Теперь формула =ExchangeRate( ) возвращает сегодняшний курс евро, а если сослаться на ячейку с датой в формате Excel, на нужную дату.

Сборка и установка

Чтобы собрать нашу надстройку в один файл со всеми зависимостями, нужно добавить в файл .csproj строки

  <Target Name="PackedReferences" AfterTargets="AfterBuild" BeforeTargets="ExcelDnaBuild">
    <ItemGroup>
      <References Include="$(OutDir)*.dll" Exclude="$(OutDir)$(TargetFileName)" />
    </ItemGroup>
    <PropertyGroup>
      <ExcelAddInInclude>@(References)</ExcelAddInInclude>
    </PropertyGroup>
  </Target>

Теперь делаем