Введение

Представляю вашему вниманию пошаговое руководство по разработке add-in’а для Excel.
Excel-DNA это бесплатная открытая библиотека для создания Excel расширений. Сайт проекта excel-dna.net

На протяжении данного руководства мы разработаем add-in, который позволяет по нажатию кнопки загружать данные со стороннего сайта в текущую страницу. Итак, начнем.

Разработка add-in'а

Для начала создадим новый проект типа Class Library, назовем его cryptostar. Подключим библиотеку excel-dna:

Install-Package ExcelDna.AddIn
Install-Package ExcelDna.Integration
Install-Package ExcelDna.Interop

Теперь можем приступать к реализации. В качестве источника данных будем использовать API api.coinmarketcap.com/v1/ticker, запрос возвращает массив объектов содержащих информацию о различных цифровых валютах.

[
    {
        "id": "bitcoin", 
        "name": "Bitcoin", 
        "symbol": "BTC", 
        "rank": "1", 
        "price_usd": "4512.7", 
        "price_btc": "1.0", 
        "24h_volume_usd": "2711790000.0", 
        "market_cap_usd": "74640450605.0", 
        "available_supply": "16540087.0", 
        "total_supply": "16540087.0", 
        "percent_change_1h": "0.3", 
        "percent_change_24h": "-7.03", 
        "percent_change_7d": "3.95", 
        "last_updated": "1504391067"
    }, 
    {
        "id": "ethereum", 
        "name": "Ethereum", 
        "symbol": "ETH", 
        "rank": "2", 
        "price_usd": "336.689", 
        "price_btc": "0.0740905", 
        "24h_volume_usd": "1402470000.0", 
        "market_cap_usd": "31781255657.0", 
        "available_supply": "94393508.0", 
        "total_supply": "94393508.0", 
        "percent_change_1h": "2.36", 
        "percent_change_24h": "-13.01", 
        "percent_change_7d": "0.84", 
        "last_updated": "1504391070"
    }
]

Первым делом напишем загрузчик данных:

    public class Ticker
    {
        public string id { get; set; }
        public string name { get; set; }
        public string symbol { get; set; }        
        public decimal? rank { get; set; }
        public string price_usd { get; set; }
        public decimal? price_btc { get; set; }
        public string market_cap_usd { get; set; }
        public decimal? available_supply { get; set; }
        public decimal? total_supply { get; set; }
        public string percent_change_1h { get; set; }
        public string percent_change_24h { get; set; }
        public string percent_change_7d { get; set; }
        public long last_updated { get; set; }
    }

    public class DataLoader
    {
        public Ticker[] LoadTickers()
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://api.coinmarketcap.com/v1/ticker/");
            request.Method = "GET";
            request.ContentType = "application/json";

            using (var response = request.GetResponse())
            using (var stream = response.GetResponseStream())
            using (var responseReader = new StreamReader(stream))
            {
                string data = responseReader.ReadToEnd();

                using (var sr = new StringReader(data))
                using (var jsonReader = new JsonTextReader(sr))
                {
                    var items = JsonSerializer.CreateDefault()
                            .Deserialize<Ticker[]>(jsonReader);

                    return items;
                }
            }
        }
    }

Пояснять данный код я не буду, так как он довольно простой и к нашей теме отношение имеет довольно опосредованное.

Теперь мы умеем получать данные в виде массива объектов класса Ticker. Пришло время научиться отображать эти данные на текущей странице.

Что бы отобразить данные, нам понадобится экземпляр класса Microsoft.Office.Interop.Excel.Application. Он предоставляет доступ к объектной модели Excel, через него мы сможем получить объект-страницу(worksheet) и записать наши данные в нужные ячейки. Давайте напишем класс для записи данных на страницу.

    public class DataRender
    {
        public void RenderData(Ticker[] tickers)
        {    
            // используем dynamic что бы не привязываться к конкретной версии Excel
            dynamic xlApp = ExcelDnaUtil.Application;

            // получаем активную страницу
            var ws = xlApp.ActiveSheet;
		
	     // если страница не открыта ничего не делаем
            if (ws == null)
                return;

	     // очищаем содержимое страницы
            ws.Cells.Clear();

            // с использованием reflection заполняем страницу данными
            var props = typeof(Ticker).GetProperties();

            for (var j = 0; j < props.Length; j++)
            {
                var prop = props[j];
                var cell = ws.Cells[1, j + 1];
                cell.Value2 = prop.Name;
                cell.Font.Bold = true;
            }            

            // предварительно запишем данные в двумерный массив, а затем присвоим этот массив объекту Range. Это позволит значительно ускорить работу плагина по сравнению с вариантом, в котором каждое значение по отдельности устанавливается в отдельную ячейку.
            object[,] data = new object[tickers.Length, props.Length];

            for (var i = 0; i < tickers.Length; i++)
            {
                for (var j = 0; j < props.Length; j++)
                {
                    var val = props[j].GetValue(tickers[i], null);

                    data[i, j] = val;
                }
            }

            var startCell = ws.Cells[2, 1];
            var endCell = ws.Cells[1 + tickers.Length, props.Length];

            var range = ws.Range[startCell, endCell];
            range.Value2 = data;

            var firstCell = ws.Cells[1, 1];
            // выравниваем колонки, чтобы все данные были на виду
            ws.Range[firstCell, endCell].Columns.AutoFit();
        }
    }

При работе с объектной моделью надо помнить о том, что работаем со ссылками на COM объекты. В основном потоке Excel мы можем спокойно использовать эти объекты и не заботиться об освобождении ссылок (Marshal.ReleaseComObject), однако, если мы захотим использовать объектную модель из отдельного потока, у нас есть два варианта:

1. Самостоятельно отслеживать все используемые объекты и очищать ссылки на них. Этот подход чреват ошибками и я не рекомендую его использовать.
2. ExcelDna предоставляет возможность добавить задание на выполнение в основном потоке, для этого предназначен метод ExcelAsyncUtil.QueueAsMacro, пример использования:
   
   

   ExcelAsyncUtil.QueueAsMacro(() =>{ 
           Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Appplication;
           xlApp.StatusBar="Sending request...";
       });
   

Таким образом, мы научились отображать данные на странице. Приступим к работе с пользовательским интерфейсом. ExcelDna позволяет вносить изменения в стандартный Ribbon, добавлять в него новые вкладки и кнопки. Создадим собственную вкладку и разместим на ней кнопку. По нажатию на кнопку будет происходить загрузка данных на текущую страницу. Для этого мы должны отнаследоваться от класса ExcelRibbon и переопределить метод GetCustomUI, метод возвращает RibbonXML с описанием интерфейса нашего add-in'а.

    
    [ComVisible(true)]
    public class RibbonController : ExcelRibbon
    {        
	
        public override string GetCustomUI(string RibbonID)
        {
 
            return @"
      <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>
      <ribbon>
        <tabs>
          <tab id='tab1' label='Cryptostar'>
            <group id='group1' label='Cryptostar'>
              <button id='button1' image='bitcoin' label='Get Data' onAction='OnButtonPressed'/>
            </group >
          </tab>
        </tabs>
      </ribbon>
    </customUI>";
        }
        public void OnButtonPressed(IRibbonControl control)
        {
            try
            {
                var dataLoader = new DataLoader();

                var tickers = dataLoader.LoadTickers();

                
                var dataRender = new DataRender();

                dataRender.RenderData(xlApp, tickers);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
            }
        }
}

Мы объявили кнопку, располагающуюся на закладке и группе с названием cryptostar. У кнопки задан обработчик onAction=’OnButtonPressed’, при нажатии на кнопку будет вызван метод OnButtonPressed в классе RibbonController.

Помимо обработчика мы указали изображение для кнопки: image=’bitcoin’. Имя изображения задается в конфигурационном файле — Cryptostar-AddIn.dna. Данный файл автоматически добавляется в проект при подключении nuget’a. Пример:

<Image Name="bitcoin" Path="bitcoin.png" Pack="true" />

Сборка и Отладка

Наш плагин готов, давайте попробуем его собрать. Нажимаем F5. После чего получаем набор файлов *.xll:
Cryptostar-AddIn64-packed.xll, Cryptostar-AddIn-packed.xll, Cryptostar-AddIn.xll, Cryptostar-AddIn64.xll
Видим, что полученные файлы отличаются как по разрядности, так и по наличию слова packed. С разрядностью все понятно, выбирать нужно тот, который совпадает по разрядности с Excel. А чем же отличаются packed и не packed add-in'ы? ExcelDNA позволяет упаковывать зависимости плагина в .xll файл. Зависимостями могут являться любые файлы, используемые в проекте, например внешние библиотеки или картинки. Зависимости задаются в конфигурационном файле, выглядит это так:

<DnaLibrary Name="Cryptostar Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="Cryptostar.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" />
  <Reference Path="Newtonsoft.Json.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" />  
  <Image Name="bitcoin" Path="bitcoin.png" Pack="true" /> 
</DnaLibrary>

Обратите внимание на атрибут 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 до его отладки и создания установщика.

Исходный код проекта доступен по ссылке.