Analyzing an Excel 2013 Spreadsheet Programmatically using an Add-In

I set out to write an Excel add-in that will programmatically analyze the contents of a spreadsheet. I was surprised that the documentation I found was weak in the sense that there were few end-to-end examples. So, here we go.

An Excel add-in is like a mini-program that can add fairly sophisticated new functionality. Excel add-ins are, to a large extent, specific to a particular version of Excel. I was using Excel 2013 and Visual Studio 2012. The first step is to get your development machine set up to create an add-in. I was extremely confused by the weak documentation here and can’t remember all the hoops I had to jump through. But, as far as I can recall, VS gave me most of the code libraries I needed, but I also had to download and install the “Microsoft Office Developer Tools for Visual Studio 2012 ENU” which was surprisingly difficult to find.

Note: I was working with a new machine and had trouble installing the Tools for VS 2012 component. I eventually got the package installed, but I’m not sure what fixed the problem — I was on a clean-machine and updated VS (“Update 4”), and downloaded the package directly, and rebooted a couple of times.

The next few steps were mostly about weeding out all the unnecessary information from the MSDN documentation – there was far too much noise in the documentation. First I launched Visual Studio 2012 and did a File | New Project and selected the Excel 2013 Add-in template (which wasn’t there until I finally had all the downloads my machine needed). I use C#. I named the project MyDemoExcelAddIn.


Next I selected the project in the VS Solution Explorer window, right-clicked, selected Add | New Item, selected the User Control item, and renamed the associated file to MyDemoUserControl.cs.


The user control provides the add-in UI and you can think of it as a cousin to a classic WinForm. In VS, in the user control design view, I added a TextBox for user input, a Button to trigger action, and a ListBox to display results.


The user control needs a so-called task pane to live in. In file ThisAddIn.cs, I added two declarations as class members:

private MyDemoUserControl myDemoUserControl;
private Microsoft.Office.Tools.CustomTaskPane myDemoCustomTaskPane;

Then in the ThisAddIn_Startup method, I added this code to fire up the add-in:

myDemoUserControl = new MyDemoUserControl();
myDemoCustomTaskPane = this.CustomTaskPanes.Add(myDemoUserControl,
  "My Add-In");
myDemoCustomTaskPane.Visible = true;
myDemoCustomTaskPane.DockPosition =
myDemoCustomTaskPane.Height = 400;
myDemoCustomTaskPane.Width = 350;


At this point I was able to test the add-in by hitting the F5 key.


Now to add useful functionality, in most cases you need to use interop. This requires an entirely different set of libraries. Again, I was quite confused about what functionality came from which library. It seems as if VS loaded all the required libraries for me because I specified the add-in template. Anyway, in design view I double-clicked on the Button control to register its event handler. At the top of MyDemoUserrControl.cs I added these interop using statements:

using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;

Then I edited the event handler like so to just access the spreadsheet:

private void button1_Click(object sender, EventArgs e)
  listBox1.Items.Add("Placing value 77 in cell B2");
  Excel.Worksheet excelWorksheet =
  Excel.Range cellB2 = excelWorksheet.get_Range("B2");
  cellB2.Value2 = "77";


Whew. That’s kind of annoying and confusing. But you can really do some amazing things with Excel add-ins once you get over the initial hurdles. In my case I grabbed the spreadsheet contents and inferred what the spreadsheet was about by looking at header names and column types (inferred from the formatting).

This entry was posted in Machine Learning, Software Test Automation. Bookmark the permalink.