Executing Excel Calculations via RESTful API using SpreadsheetWeb

In this tutorial, I will outline how to expose these Excel calculations via RESTful API and connect to them for calculations using SpreadsheetWeb. I will use JavaScript/jQuery to make AJAX calls to the web service.

For business users, Microsoft Excel is the most common platform for building formula-heavy, computational algorithms. If these calculation models are integral to any part of the business’s operations, then a typical objective is to subsequently integrate or convert these models into standard applications using a team of developer resources, often involving a ground-up rewrite of the whole application. This tutorial outlines an alternative approach to integrating Excel calculations with business applications.

The Calculation Model

Below, you will find a screenshot of the Excel file I will use throughout this tutorial, which is a simple mortgage calculator.

The following inputs are required to generate the output calculations:

• Home Value
• Loan Amount
• Interest Rate
• Loan Term
• Property Tax Rate

To identify and set values to these cells, we will need to specify a named range for the target cells.

Each of these inputs has a named range. SpreadsheetWeb exposes all inputs and outputs through these named ranges, so it is important that all inputs and outputs have named ranges defined before beginning this process. See the link for more on named ranges in Excel.

With respect to the input ranges noted above, I am outlining the mappings between the label and the actual named range name below:

Additionally, take note of the series of output values visible on this screen, which are calculated based on the inputs noted above. The cells corresponding to these values will also need to be named, in order to request them from the web service.

These are as follows:

We will be requesting these as outputs from the web service.

You can download the Excel file from this link.

Creating an Application

To begin the wire-up, we will need to expose the calculation model as a web service using the SpreadsheetWeb Control Panel. Log into the system and select the “Create a New Application” button from the application list page.

We need to select Web Service from this list:

Click Next.

In the Step 2 screen, you will need to upload your Excel file. Select the related Excel file and click Next.

In this screen, you can define advanced settings for your application. These can subsequently be edited after the application has been completed. The only required field on this screen is the Groups select, so assign the application to the Default group (or otherwise, if you are familiar with the system).

If you’ve followed the steps above, then upon clicking Next you will see your application’s Key (see screenshot below). Take a note of the Key value, as you will need this for your subsequent web service calls.

Setting up the Calculation web service call

NOTE: The remainder of this tutorial assumes that you created an unrestricted application (i.e. requiring no authentication before executing calculations). For more information on restricted applications and SpreadsheetWeb authentication, visit the wiki reference page here.

We must now set up our POST request to the server in order to run a set of Excel calculations via RESTful API. To do so, we must first identify the endpoint that we are targeting, which is constructed as follows:

{PROTOCOL}://[SUBDOMAIN].{DOMAIN}.{SUFFIX}/Api/Applications/GetResult

The server information is typically identical to the target server that you utilized to access the Control Panel login, where you uploaded your Excel model.

For example, in our case, we will target one of the accessible development servers over HTTPS:

https://dev.pagos.com/Api/Applications/GetResult

Next, we must construct the body of the request. Since we will be calling from JavaScript, we will want to construct our body as an object that can then be subsequently serialized into JSON.

We will need to specify several pieces of key information:

1. The application key, indicating which application we are targeting. We retrieved this after generating the application above.
2. A set of input objects that identify two key pieces of information:
   1. The reference to the named range for which we are attempting to set the value.
   2. The value that we are trying to set to that named range.
      1. NOTE: The system supports setting values to multi-cell ranges (i.e. named ranges that are associated with a 2-dimensional set of cells from the workbook). This means that when indicating the value that is being set to the target cells, we must indicate it in a 2-dimensional array that resembles the structure of the cells in the workbook. This is not pertinent for this specific scenario but may be depending on your requirements.
   3. A series of desired outputs from the calculation.
      1. This will be a list of named range names that correspond to outputs that we are interested in retrieving from the workbook.

Cumulatively, the JavaScript object will look as follows:

Next, we will take that object and perform a POST to the target endpoint.

If everything is set up appropriately, then our response object will be logged into the console.

When a calculation has been completed successfully, a flag on the calculation object will indicate such (the Success member). Likewise, the Outputs member will include a collection of the requested named range data.

In turn, if all was successfully set, we will receive our response,

You can retrieve the Outputs object array from the resulting JSON response object.

The outputs can be distinguished by their Ref member (reference), which corresponds to the input reference member that was defined in the request. The value of the outputs will also be a two-dimensional array of objects, each one corresponding to the metadata of a particular cell.

Each cell’s type, value, format, and text (formatted value) are exposed in the output objects. As a result, any of these properties can be utilized when building your user interface or integration endpoints.