Excel as a Calculation Service #2

If you haven’t read my Part 1 of this series then let me recap. Last week the Microsoft Office team announced the general availability of Excel REST APIs for the Microsoft Graph. With these new endpoints it is now possible to interact with your existing Excel Workbooks using REST from your mobile, desktop and web apps without the need to have to open the Excel app – pretty awesome eh?

In my previous post I demonstrated how you can call one of the 300+ built-in functions that Excel supports out-of-the-box. Sometimes though, the built-in functions that come with Excel are not enough. Sometimes we need to build our own Excel calculations to support our own LoB reports and visualisations. In this post I will demonstrate how you can re-use that functionality within your own modern day web and mobile apps.

For the purposes of this post I have created a simple Restaurant Bill Calculator within Excel:.

Excel Bill Calculator

This is a very simple example but it’s the concept of re-use of the calculation logic, in this case Bill+Tip and Amount per person, that I am trying to convey not it’s complexity.

I then created a visual representation of the Excel Workbook in an ASP.NET application – of course I didn’t need to create a web app, I could have created just a web API or a Console app,

Web App Calculator

So in this example we use the new Excel REST API to set the Excel Workbook named items, Bill Amount, Tip, and Number in Party. We then use the new Excel REST API to get the results by asking for the Excel Workbook named items Total Bill and Amount per Person..

In summary, complex business models no longer need to be rebuilt repeatedly; developers can leverage Excel to perform those calculations instantly and retrieve the results with simple API calls.

The full source-code for my sample 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 RestaurantBillCalculator.xlsx in the root of your tenant’s OneDrive – if the file does not exist the file is uploaded when you run the application for the first time. There are obvious changes that can be made to code to be more performant such as a namedItem that contains all of the fields rather than a nameditem per field, and not checking if the file exists each time.

If you want to start writing your own apps that use the Microsoft Graph then 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.

Warning: Missing argument 1 for cwppos_show_review(), called in D:\home\site\wwwroot\wp-content\themes\flat\content-single.php on line 29 and defined in D:\home\site\wwwroot\wp-content\plugins\wp-product-review\includes\legacy.php on line 18

Leave a Reply

Your email address will not be published. Required fields are marked *