This post is brought to you by Andrew Stegmaier, a Program Manager on the Excel team.
Excel Services on SharePoint offers a bunch of ways for developers to extend the power of Excel and integrate Excel workbooks into larger solutions. Originally, to take advantage of this, you needed to deploy and maintain your own SharePoint servers. The world has changed, though: With Office365 for business, you can access the power of Excel Services on SharePoint through the cloud. Here’s an example to help you get started using the Excel Services REST API in the cloud.
What are the Excel Services APIs and what are they useful for?
Although there are several other ways to interact with Excel on the web, we’re going to focus here on the REST API, which has some cool things that you can do with it.
Imagine an expert auditor has created a model in Excel to assess the credit risk of an investment. Other people in the company start using the model, and pretty soon it becomes a part of the way this company does business. But as it becomes more widely used, some problems develop. Many people need to work together to use the model to accomplish the business goals, and things get confusing with many copies of it constantly being emailed back and forth. In response to changing needs, the model must be updated, and now there are multiple versions, but people often use the wrong one. Plus, the company might like to share some of the results from the model with people outside the organization, but they don’t want to share sensitive details contained in the spreadsheet.
An administrator suggests that they develop a custom application to streamline the process and address these problems. The app developer can use Excel Services to integrate the logic contained in the existing model into a larger application that adds functionality to control security, user roles, workflow, and integration with other systems. End users get a simple, integrated experience through the custom application, while the original analyst can continue to maintain the model using his Excel skills. Neat, right?
How can I start using Excel Services?
The REST API was added in SharePoint 2010. So if your company has purchased and deployed SharePoint servers in version 2010 or later, there’s plenty of useful documentation to get you started. Increasingly though, businesses are moving to the cloud. Office365 for business subscribers can take advantage of the power of SharePoint without any need to purchase, maintain, or manage on-premises infrastructure. The REST API is available through most Enterprise plans that include SharePoint Online. (For details about exactly which SKUs are supported, look for the Excel Services line item in this matrix). If your company is on one of these plans and you want to get started, you’ll find some useful tips below.
So what’s different in SharePoint Online?
The biggest difference is the authentication method. You don’t want your data to be available to just anyone, so Excel Services requires that applications that want to interact with a workbook provide some credentials. Your workbooks in SharePoint Online (and the Excel Services APIs used to interact with them) are living in Microsoft data centers, not on your corporate network. This means resources, such as your company’s Active Directory servers, aren’t available in this context, so the usual ways of authenticating won’t work. Not to fear, though-the steps below will get you working in SharePoint Online just as smoothly as before.
Using the REST API in a sample application
To illustrate how to get started connecting to a workbook in SharePoint Online through the Excel Services API, let’s make a simple Visual C# console app in Visual Studio that will connect to a workbook, retrieve some data, and process it. To expand on the example from above, imagine the workbook is a financial model that summarizes the financials of an imaginary company, Contoso, in which your firm has invested:
There might be many similar models in the organization, each showing the risk of a different company in the portfolio. If you wanted to consolidate key numbers from many different files for reporting or compliance purposes, doing this manually would be a chore. Wouldn’t it be great if you could write a program to do this? With the Excel Services REST API, you can.
First, install the SharePoint client components onto your development machine. You may have installed them with Visual Studio, but you can also download them separately. A successful install will deploy the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies on your machine, which provide the resources you’ll need to successfully connect.
Now, open Visual Studio and create a new Console project (File > New > Project):
In the Solution Explorer, add a reference to the Microsoft.SharePoint.Client.Runtime and the System.Runtime.Serialization assembly. As an example, on my own computer, these are located at:
- C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.dll
- C:Program Files (x86)Reference AssembliesMicrosoftFramework.NETFrameworkv4.5System.Runtime.Serialization.dll
When you’re done, you should see at least the following references in Solution Explorer:
Next, open the Program.cs file and import the following namespaces:
Now you’re ready to begin writing your application. First, add some code to allow the user to provide the username and password:
Now that the username and password are stored in the appropriate variables, we’ll use them to create a SharePointOnlineCredentials object (which comes from the Microsoft.SharePoint.Client assembly you installed earlier):
In a real application, you’d place this line within a try/catch block so that you can handle the case where the username or password provided are incorrect.
Now, we can create the REST request. This one will ask Excel Services for a JSON feed that represents a specific cell in the workbook. For this scenario, let’s suppose that we want to get a consolidated view of all the liabilities of companies in our portfolio. In each model, this value is stored in a named range called “liabilities,” so we want to pull this value out of the worksheet and total it.
Now we’ll create and modify the URL to point to the file that we’d like to examine. To do this, replace <SharePointOnlineSite>, <DocumentLibrary>, <FileName> and <RangeName> in the code below to point to a workbook and range on the SharePoint Online site. MSDN provides more information on how to structure the URL for different kinds of REST operations.
Note the line that modifies the HTTP header before sending. This is necessary for REST requests on SharePoint Online, because it forces the server to challenge the client for credentials instead of silently failing:
Just like with the authentication step, a real application would want to place the GetReponse() method in a try/catch block to handle any failures.
Now that we have a JSON response stream, we’ll serialize it into an object for easy access. First, define a data contract for the CellValue type. The data structure here is designed to match the JSON that Excel Services will return. We’ll do this outside of our main Program class:
The contract is defined for this type, so you can easily serialize the JSON stream using the DataContractJsonSerializer object. After it’s serialized, you can access the formatted value (fv) using a simple dot syntax:
That’s the last step! Press Start to see the program in action:
A real-world application would be able to perform these same steps over many workbooks, potentially saving countless hours of manual compilation.
That’s it–you’ve successfully created a simple application that takes advantage of the Excel REST API in SharePoint Online.
Tell us what you think
We’d love to hear about how you can apply the techniques outlined above in your own applications, so tell us what you think in the comments!
–Andrew Stegmaier, Excel Program Manager