For building Add-ins for Excel, Word, and OneNote 2016+, across Desktop, Web, iOS, and Mac
Canonical code sample: reading data and performing actions on the document
To set the context for the sort of code you’ll be writing, let’s take a very simple but canonical example of an automation task. This particular example will use Excel, but the exact same concepts apply to any of the other applications (Word, OneNote) that have adopted the new host-specific/Office 2016+ API model.
Scenario: Imagine I have some data on the population of the top cities in the United States, taken from “Top 50 Cities in the U.S. by Population and Rank” at http://www.infoplease.com/ipa/a0763098.html. The data – headers and all, just like I found it on the website – describes the population over the last 20+ years.
Let’s say the data is imported into Excel, into a table called “PopulationData”. The table could just as easily have been a named range, or even just a selection – but having it be a table makes it possible to address columns by name rather than index. Tables are also very handy for end-users, as they can filter and sort them very easily. Here is a screenshot of a portion of the table:
Now, suppose my task is to find the top 10 cities that have experienced the most growth since 1990. How would I do that?
The code in the next few pages shows how to perform this classic automation scenario. As you look through the code, if not everything will be immediately obvious – and it probably won’t be – don’t worry: the details of this code is what the rest of this chapter (and, to some extent, the book) is all about! But I think it’s still worth reading through the sample as is for the first time, to gain a general sense of how such task would be done via Office.js.
Note: In a more real-world scenario, this sample would be broken down into ~4 functions: one to read the data, another to calculate the top 10 changed cities, a third to write out a table, and a forth to bind a chart to the data. For purposes of this sample, though – and in order to make it easily readable from top to bottom, rather than having the reader jump back and forth – I will do it in one long function.
If you run the above code, here is what the resulting sheet looks like: