Microsoft Excel

Excel Services part 7: Sample application with Excel Web Services

Last week I gave an overview of Excel Web Services and the types of scenarios these web services will enable.  This post, I would like to show you an example.  Let’s look at implementing a browser-based mortgage calculator. The application is simple, but still demonstrates a few key Excel Services concepts:

The sample app
For this example, I am going to walk through how to build a tool for calculating a mortgage return, much like something you would find on a bank’s website).

(Click to enlarge)

The example here is a simplified version of such a tool, with one twist: the entire calculation is done on the server by an Excel workbook, and not by a function written in a programming language.  As far as the users are concerned, the tool does not appear to be related to Excel; it is just an ordinary interactive web form.

A user can type in the mortgage amount, mortgage period length, and interest rate, click Calculate, and see the resulting monthly payment appear on the form.

(Click to enlarge)

So what’s going on here?  Behind the scenes, the web page uses an Excel Web Services session to load the mortgage calculator workbook, set its parameter cells to the values provided by the user, and retrieve the calculated result from the appropriate formula cell.

(Note - the UI for this application is a SharePoint web part.  An application does not need to be in SharePoint, however – the user interface could be anything that can “speak” web services.)

The workbook
Here is the Excel workbook with the mortgage calculating model – mortgagecalc.xlsx.

(Click to enlarge)

We can see that the model, in this case, is actually not a whole lot of proprietary information: the entire calculation is done in cell C8 by using a single Excel formula, PMT (but the web site users don’t have to know that).

We can also see that cell C8 has a name – “Payment”.  Similarly, the three input parameter cells C4, C5 and C6 each also have a name (though they don’t have to be named cells to be exposed by Excel Web Services - they can use cell references too).  This enables better isolation between the workbook and the code around it; the code refers to the sheet only through named ranges, so the model can later be freely edited and laid out in any way that the author sees fit, as long as the parameter and result names remain intact.  Here is the “Manage Name” dialog in Excel “12”, where we can see the entire set of names in the workbook.

(Click to enlarge)

The workbook is published to a server document library (see my discussion about publishing for more details).

The code
Let’s look at the application code. For brevity, I am only going to discuss the parts of the code that have to do with running the Excel Web Service session; this code is all within one method, which is called from the Calculate button’s click handler. The rest of the code for this application is standard web part code.

private void CalculateUsingWebService() 
{
   Status[] status;
   string sessionId = null;

   // Step 1: Instantiate the web service
   XlMortgageCalcWebPart.Es.ExcelService es = new XlMortgageCalcWebPart.Es.ExcelService();

   // Step 2: Set web service link
   es.Url = this.ExcelWebServiceUrl;
   // Step 3: Set credentials
   es.Credentials = System.Net.CredentialCache.DefaultCredentials;

   // Step 4: Start the session
   try
   {
    sessionId = es.OpenWorkbook(this.MortgageCalculatorWorkbookUrl, String.Empty, String.Empty, out status);
   }
   catch
   {
    sessionId = null;
   }
   if (sessionId == null)
   {
    _lblError.Text = "Error opening workbook.  Please make sure that the correct MortgageCalculatorWorkbookUrl and ExcelWebServiceUrl are specified in the Web Part Properties.";
    this.Controls.Clear();
    this.Controls.Add(_lblError);
    return;
   }

   // Step 5: Set parameters
   es.SetCellA1(sessionId, "SimpleCalculator", "MortgageAmount", _txtMortgageAmount.Text.Trim());
   es.SetCellA1(sessionId, "SimpleCalculator", "MortgageLength", _txtMortgageLength.Text.Trim());
   es.SetCellA1(sessionId, "SimpleCalculator", "InterestRate", _txtInterestRate.Text.Trim());

   // Step 6: Get result
   object o = es.GetCellA1(sessionId, "SimpleCalculator", "Payment", true, out status);
   if (o != null)
   {
      _lblTotal.Text = Convert.ToString(o);
   }
   else
   {
      _lblError.Text = "Error getting total value from workbook.";
      this.Controls.Clear();
      this.Controls.Add(_lblError);
    return;
   }

   // Step 7: End the session
   status = es.CloseWorkbook(sessionId);
  }

Let’s go over the main steps in this piece of code. To see a full description of Excel Web Services methods and functionality, please look at last week’s post – Building applications with Excel Web Services.

While this was just a simple example, you can envision much more complex calculations represented by spreadsheets that you can now call from an application.  The calculations can be provided as a service to applications, while the model is safely hidden and secured.

PS Edited to fix a screen-shot problem.