Writing an Excel Add-In for Beginners

One of PMG’s larger software applications is a Datawarehouse web app that allows our account services teams to load in tabular data from a multitude of datasources on a scheduled basis. This allows for the teams to see historical data and report on this data however they choose – the most common being through SQL or Excel ODBC connections.

One of the more recent features of our Datawarehouse is a Reporting interface that enables users to generate tabular reports by combining datasources through SQL joins, aggregating metrics, etc. The importance of this reporting feature is growing every day, and thus we needed a method to better connect our most common reporting tool, Excel, to these reports. So we wrote an Excel Add-In that is, in essence, an API connection to our Datawarehouse within Excel.

Let me preface this whole section and post by saying I am not a Windows, .NET, or Excel developer by any means. My comfort zone is in the terminal and on a Linux machine. But, I took on this project because it is a valuable continuation of the reporting we have built into our web applications, and it was/is a great learning experience. I do not claim to be a new expert in Excel development, or that what I propose is the absolute correct way to do something in this environment. We are simply trying to share our learnings and receive feedback from the larger community.

First of all, I would try to become familiar with C# and Visual Studios, since this is where the development is going to happen. Here is a link to getting your Visual Studio installation set up for Office and Excel development.

Once the Excel Add-In was set up and running, we created a Windows Form in the application that allows our users to select an application’s URL (mostly for development purposes) and retrieve API keys that we can store as part of the user storage of the application.

All of our API endpoints use JSON for data transmission, aside from the tabular data which uses CSV, and for those endpoints we are using the third party library http://www.newtonsoft.com/json/help/html/Introduction.htm to deserialize the responses. You can simply create a class with public members and types, and the library will deserialize the JSON into those members. For example:

class User
{
public string Id;
public string Name;
}

//and

Stream body = response.GetResponseStream(); //response is a System.Net.HttpWebResponse.
TextReader bodyReader = new StreamReader(body);
JsonReader jsonReader = new JsonTextReader(bodyReader);
JsonSerializer jsonSerializer = JsonSerializer.CreateDefault();

User user = jsonSerializer.Deserialize(jsonReader);

response.Close();

//user now has user.Id and user.Name filled out from
//a JSON object response with Id and Name string fields.

Whenever a user needs to modify or specify something to the application, we show a Windows Form and make the API call in the Load event and populate the Form controls when the response comes back. For example, when a user wants to add a new table to the Excel sheet, after clicking on an add relation button in the ribbon, we make a call to the relations list endpoint in the Load event callback of the add relation form, and then populate a spinner in the Form.

For getting actual tabular data from the API we have endpoints that know how to stream CSV data from our databases and pass through our reporting interfaces. Once again, we use an external library, https://www.nuget.org/packages/LumenWorksCsvReader/, to convert the CSV stream into rows that can be inserted into a sheet. Once the CSV stream is available, we can parse it into rows and add them to the sheet like so:

CsvReader csv = new CsvReader(new StreamReader(stream), true);
int fields = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();

putTableRow(sheet, range, 0, headers);

long row = 1;
while (csv.ReadNextRecord())
{
string[] rowValues = new string[fields];
csv.CopyCurrentRecordTo(rowValues);
putTableRow(sheet, range, row, rowValues);
row++;
}

//and

private void putTableRow(Worksheet sheet, Range topLeft, long row, string[] values)
{
Range c1 = sheet.Cells[topLeft.Row + row, topLeft.Column];
Range c2 = sheet.Cells[topLeft.Row + row, topLeft.Column + values.Length - 1];
Range tableRow = sheet.get_Range(c1, c2);
tableRow.Value = values;
}

Once this is done, we can get the data formatted nicely in table by adding a new ListObject to the sheet covering the whole range of the inserted data. A ListObject is one of these constructs that allows for easy sorting and searching that we can format within the TABLE TOOLS Design tab in Excel.

We store the identifiers of relations and other such information in the name of the ListObjects in the Workbook so that we don’t have to manage any external storage and everything stays in the Workbook file. So, in order to refresh, all we need is to read from the ListObjects in the Workbook and Worksheets to make the proper API calls again and load any new data into the sheet.