Microsoft Excel add-in development using Excel-DNA library

Introduction

I present to your attention a step-by-step guide for developing an add-in for Excel.
Excel-DNA is a free open-source library for creating Excel extensions. Project site excel-dna.net

Throughout this tutorial, we will develop an add-in that allows you to download data from a third-party site to the current page by pressing a button. So, let's begin.

Add-in development

To begin, create a new project of the Class Library type, let's call it cryptostar. Let's connect the excel-dna library:

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

Now we can start implementation. We will use api.coinmarketcap.com/v1/ticker API as a data source, the query returns an array of objects containing information about various digital currencies.
')

 [ { "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" } ] 

First, let's write the data loader:

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

I will not explain this code, since it is quite simple and has a rather indirect relation to our topic.

Now we can get data in the form of an array of objects of the Ticker class. It's time to learn how to display this data on the current page.

To display the data, we need an instance of the class Microsoft.Office.Interop.Excel.Application . It provides access to the Excel object model, through it we will be able to get an object-page (worksheet) and write our data in the right cells. Let's write a class to write data to the page.

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

When working with an object model, we must remember that we work with references to COM objects. In the main Excel thread, we can safely use these objects and not worry about releasing the links ( Marshal.ReleaseComObject ), however, if we want to use the object model from a separate stream, we have two options:

1. Independently monitor all used objects and clear links to them. This approach is fraught with errors and I do not recommend it.
2. ExcelDna provides the ability to add a task for execution in the main thread; for this purpose, the ExcelAsyncUtil.QueueAsMacro method is used , example of use:
   
   

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

Thus, we have learned to display data on the page. Let's get to work with the user interface. ExcelDna allows you to make changes to the standard Ribbon, add new tabs and buttons to it. Create your own tab and place a button on it. By clicking on the button, data will be downloaded to the current page. To do this, we must inherit from the ExcelRibbon class and override the GetCustomUI method, the method returns a RibbonXML with a description of our add-in interface.

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

We announced a button located on a tab and a group called cryptostar. The button has an onAction = 'OnButtonPressed' handler, when you press the button, the OnButtonPressed method in the RibbonController class will be called.

In addition to the handler, we specified an image for the button: image = 'bitcoin'. The name of the image is set in the configuration file - Cryptostar-AddIn.dna. This file is automatically added to the project when nuget is connected. Example:

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

Build and Debug

Our plugin is ready, let's try to build it. Press F5. After that we get a set of * .xll files:
Cryptostar-AddIn64-packed.xll, Cryptostar-AddIn-packed.xll, Cryptostar-AddIn.xll, Cryptostar-AddIn64.xll
We see that the resulting files differ both in bit depth and in the presence of the word packed. With digit capacity everything is clear, you need to choose the one that matches the digit capacity with Excel. What is the difference between packed and not packed add-ins? ExcelDNA allows you to package plug-in dependencies in an .xll file. Dependencies can be any files used in the project, such as external libraries or images. Dependencies are set in the configuration file, it looks like this:

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

Pay attention to the attribute Pack = ”true”, it indicates that this file should be packaged.

If we use unpacked add-ins, then all its dependencies should be in the same directory as it.

Now select the appropriate .xll file and run it. If you did everything correctly, then after opening Excel you will see a new Cryptostart tab and a Get Data button, and when you click on it, the page will be filled with data on currencies:



Unfortunately, programs rarely work the first time, so we may need a debugger. Configuring ExcelDna add-in debugging is simple. To do this, in the project properties on the Debug tab, select the Start External Program and set the path to Excel.exe, in my case this is G: \ Program Files \ Microsoft Office \ Office14 \ Excel.exe. In the start options, we write the name of the packaged add-in file, taking into account the digit capacity of Excel. For example, Cryptostar-AddIn64-packed.xll. Everything, now we can press F5 and fully debug add-in.

Make installer

So, add-in is done, debugged, tested and ready to go. The question is how to distribute it. One of the options to entrust the installation of add-in'a user. This is done through the Excel interface, on the developer tab tab-> Add-ins-> Browse specify the path to the .xll file. This method will work only if the .xll file is signed with a certificate and the certificate is present at the trusted root certification authorities store . How to create a certificate and sign a file with it is well described here .

An alternative way is to write your own add-in installation program, which would register the necessary keys in the registry and thus register our add-in. This task is not easy, because It is necessary to take into account different versions of Excel in which the paths and keys in the registry differ. But fortunately, this problem has already been solved and there is a template for the project - the installer that performs the necessary actions. The template can be taken here .

Conclusion

As a result, we got acquainted with the Excel-DNA library and went the whole way from developing an add-in to debugging it and creating an installer.

The source code of the project is available here .