Last week I gave an overview of Excel Net Services and then the types of situations these website solutions will permit. 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 Providers 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, 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). to enlarge) example here is a simplified version of such a tool,genuine microsoft office Pro Plus 2007
, 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 site form. 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. to enlarge) what’s going on here? Behind the scenes, the website page uses an Excel Web site Providers session to load the mortgage calculator workbook,genuine office 2010 x64 key
, set its parameter cells to the values provided by the user, and retrieve the calculated result from the appropriate formula cell. - the UI for this application is a SharePoint website part. An application does not need to be in SharePoint, however – the user interface could be anything that can “speak” web solutions.) workbook
Here is the Excel workbook with the mortgage calculating model – mortgagecalc.xlsx. enlarge) 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,microsoft office 2007 update key
, PMT (but the website site users don’t have to know that). 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 World wide web Services - they can use cell references too). This enables better isolation between the workbook along with 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. to enlarge) workbook is published to a server document library (see my discussion about publishing for more details). 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 net part code. void CalculateUsingWebService()
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
sessionId = es.OpenWorkbook(this.MortgageCalculatorWorkbookUrl , String.Empty, String.Empty, out status);
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.";
Step 5: Set parameters
es.SetCellA1(sessionId, "SimpleCalculator",discount win 7 64bit key
, "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);
_lblError.Text = "Error getting total value from workbook.";
Step 7: End the session
status = es.CloseWorkbook(sessionId);
go over the main steps in this piece of code. To see a full description of Excel Net Services methods and functionality, please look at last week’s post – Building applications with Excel Web site Providers. Instantiate the internet service: Create an instance of the ExcelService object. This object is generated by Visual Studio when a developer adds a web reference to the website service’s asmx file. As you can see, it is generated in the application’s namespace. Set world wide 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 site 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 net part property in our case), and receiving a sessionId. This sessionId is subsequently used in other World wide 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 internet 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,buy microsoft office 2007 cd key
, the “Payment” named range. The sample workbook was set to be automatically recalculated, so as soon as all the parameters are set, 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 Solutions process the Excel spreadsheet model; in addition to the Excel Web Service enables tying it all together.
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. Edited to fix a screen-shot problem.