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:
Using a server-side Excel spreadsheet calculation as part of another application. Writing custom interactive UI around the Excel Calculation Service. Protecting and maintaining proprietary business models, while still providing your users the ability to provide their own inputs and receive answers. No need to recode an Excel model just to protect it.
The sample app
For this example,
Cheap office 2007 key, 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,
office 2010 Home and Student product key, 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",
Windows 7 Home Basic Sale, "MortgageAmount", _txtMortgageAmount.Text.Trim());
es.SetCellA1(sessionId, "SimpleCalculator", "MortgageLength", _txtMortgageLength.Text.Trim());
es.SetCellA1(sessionId,
Windows 7 Ultimate X64, "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.
Instantiate the web service: Create an instance of the ExcelService object. This object is generated by Visual Studio when a developer adds a web reference to the web service’s asmx file. As you can see, it is generated in the application’s namespace. Set web service link: Set the URL to the web service. Set credentials: In this case, we set the credentials that are passed to the web service for authentication to DefaultCredentials, meaning that the application’s own credentials are used. Start the session: Call OpenWorkbook, passing the path to the mortgage calculator Excel workbook (a web part property in our case), and receiving a sessionId. This sessionId is subsequently used in other Web service calls, to identify our session. Set parameters: Call SetCellA1 to set the three parameter cells to the values that the user typed into the web part form. We can see how named ranges are used, as opposed to direct cell references, to make the code robust – insensitive to layout changes in the workbook. Get result: Call GetCellA1 to get the calculation result, the “Payment” named range. The sample workbook was set to be automatically recalculated, so as soon as all the parameters are set,
Cheap Office Standard 2010, we can immediately expect the result to be available. In some cases, automatic recalculation is turned off while authoring the Excel workbook; a call to Calculate is then necessary at the point in the code where we want to tell the Excel Calculation Service to explicitly calculate formulas. End the session: Call CloseWorkbook to end the session. This call tells the Excel Calculation Service that we are done with this session, and all resources that were associated with our session can be released.
That’s it - with 7 lines of code and some exception handling, we have integrated an Excel spreadsheet calculation on the server with our mortgage calculator application. The application provides the custom user interface; Excel Services process the Excel spreadsheet model; and the Excel Web Service enables tying it all together.
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.
<div
office 2010 Home and Student product key Excel Ser
Linear Mode
