PMG Digital Made for Humans

Writing an Excel Add-In for Beginners

5 MINUTE READ | August 8, 2016

Writing an Excel Add-In for Beginners

Author's headshot

Eric Elsken

Eric Elsken has written this article. More details coming soon.

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


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


//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,, 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++;}


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.

Example ListObject

Stay in touch

Bringing news to you

Subscribe to our newsletter

By clicking and subscribing, you agree to our Terms of Service and Privacy Policy

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.

Related Content

thumbnail image

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

thumbnail image

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

thumbnail image

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

thumbnail image

Tips for Holiday Reporting Preparedness

3 MINUTES READ | November 5, 2018

thumbnail image

Navigating the Amazon Ecosystem

2 MINUTES READ | September 10, 2018

thumbnail image

Our Approach to Marketing Automation

7 MINUTES READ | November 16, 2017

thumbnail image

ICYMI: The Next Big Title in Media Agencies

1 MINUTE READ | July 11, 2017

thumbnail image

A Simple Look At Natural Language Processing

2 MINUTES READ | November 9, 2016