## Excel as a Calculation Service #1

Its been awhile since my first post, but I have been waiting for the big announcement that I’m sure is going to change people’s mind-sets on how they interact with Excel today – introducing to you the general availability of the Microsoft Graph REST APIs for Excel.

In this post I am going to give you an example of how to use Excel as a Calculation Service, demonstrating how you can call one of the 300+ Excel functions from a ASP.NET MVC application using the Graph REST APIs for Excel.

The function I am going to call is the one highlighted in the GA blog post – PMT. The Excel PMT function calculates the constant periodic payment required to pay off (or partially pay off) a loan or investment, with a constant interest rate, over a specified period. The syntax of the function is:

### PMT(rate, nper, pv, [fv], [type]]

Where:

rate = the interest rate per period as a percentage

nper = the number of periods over which the loan or investment is to be paid

pv = the present value of the loan / investment

[fv] = an optional argument that specifies the future value of the loan / investment, at the end of the nper payments

[type] = an optional argument that defines whether the payment is made at the start or the end of the period.

This function translates to the following REST API request:

### POST …/workbook/functions/pmt { “rate”: 0.035, “nper”: 20, “pv”: -2000 }

Note, don’t fall into the same trap as I did when calling the function – as it is a calculation the values of the post request (body content) are numbers not strings!!

Check out the following blog on how to get started with the Graph Excel REST API and as always, refer the Microsoft Graph API documentation for examples and syntax.

The full source-code demonstrating how to call the PMT function using the Graph REST API can be found be here. Please refer to the readme file for pre-requisites and how to configure the app based on your Azure and O365 accounts. One important note, the sample relies on the fact that you have an Excel workbook called book.xlsx in the root of your tenant’s OneDrive. To make the solution more user friendly to a wider audience, why not try extending the code to automatically create a new workbook if Book.xlsx does not exist. [Hint, my next post includes the code to show you how this can be done]

In my next post I will demonstrate how to you can re-use your own existing Excel workbook functions in modern day web applications.  .

## The Office UI Dialog API

Managing Authentication flow within Office Add-ins has just got a whole lot easier with the introduction of the new Office UI Dialog API.

This post shows you how to create a simple Excel Add-in Command in Visual Studio 2015 that uses the new Dialog API to authenticate a user and then import their Outlook Contacts using the Microsoft Graph API.

If you are new to developing O365 Add-ins then I would highly recommend you check out the Getting Started page of dev.office.com. There are reams of examples and samples to help you get up to speed with links to the Office SDKs and a Yeoman Generator for Office if Visual Studio is not your thing.

## Prerequisites

The following is required to complete this module:

### Step 1

In this step you’ll create a new Excel Office Add-in using Visual Studio. If you haven’t done so already, make sure you have downloaded and installed the Visual Studio Office Developer Tools.

With the latest Office developer tools installed open Visual Studio and choose File-New-Project. In the dialog prompt choose Office/SharePoint under Installed Templates-Visual C#.

Choose Excel Add-in and enter a project name.

You will then be prompted to choose the type of add-in:

Accept the default and press Finish.

Visual Studio will create 2 projects: an Office Project which contains the add-in manifest; a web project that contains the content for the add-in. If you want more detail about the anatomy of an office add-in please refer to the following resource.

Full source code can be found here but I just want to highlight some of the key areas:

• (Contacts.js) Following the Dialog API guidance, create a displayDialog with the appropriate parameters:

• (Contacts.js) Specify how to handle the Dialog event type DialogMessageReceived. Note how the result message is parsed to extract the AAD access token:

• (Auth.js) This solution uses the ADAL JavaScript library to simplify the Authentication process. For more information about these libraries please refer to the following resource. The key point here is the simplicity on how to authenticate with O365 credentials. The solution does not contain any login pages or credential stores – all of that is managed by ADAL based on the parameters that is provided.

• (Manifest.xml) The Office manifest defines the requirements and the capabilities of the office add-in. As this Excel add-in will use Add-in Commands to deliver its functionality, the manifest needs to be structured accordingly: Note the manifest contains only one control which exexcutes a JavaScript function rather than launching an add-in Task-pane.

• Based on the definition of the manifest this add-in has a single entry point which is a button on the Home Ribbon

### Step 2

In this step you’ll go through the steps of registering an app in Azure AD using the Office 365 App Registration Tool. You can also register apps in the Azure Management Portal but the Office 365 App Registration Tool allows you to register applications without having access to the Azure Management Portal. Azure AD is the identity provider for Office 365, so any service/application that wants to use Office 365 data must be registered with it..

Once you have signed in you will be asked for some details about the app:

.

Feel free to give the app a name of your choice but the redirect URI must match what is shown above.

Once you hit register you will be shown a second dialog which will; hopefully say “Registration Successful” and show you your Client ID. Copy this value as you will need it to complete the Office add-in solution.

### Step 3

Back in Visual Studio open the file App.js which can be found in the App folder.:

The tenant is the O365 tenant you either created as part of the app registration or your existing O365 tenant name.

### Finish

Build the solution.

Ensure the Office Project properties add-in start action is set to Office Desktop Client:

Press F5.

When you click the Get Contacts button in the Office Ribbon you should be prompted with the following dialog:

Enter your O365 credentials (a user belonging to the tenant you specified in App.js) and if the user has contacts associated with the account then they should see them automatically added once the dialog is closed.

I hope you have enjoyed this post…feel free to send me any comments.