Excel Services part 6: Building applications with Excel Web Services

While talking about scenarios for Excel Services, I mentioned “Reusing the logic encapsulated in Excel spreadsheets in custom applications” – which means accessing spreadsheets and their contents server-side via web services in a way that’s scalable and manageable.  I would like to elaborate on this topic over a couple of posts.  Today, I will introduce “Excel Web Services”; in a follow-up post I will show how to develop a small sample application using said web services.

In a nutshell…
Let’s briefly revisit Excel Services’ architecture
.  The Excel Calculation Service is the “engine” of Excel Services; it is the component that loads and calculates spreadsheets.  In the “interact with the spreadsheet in your browser” scenarios covered in previous posts, Excel Calculation Services loads and calculates a spreadsheet and then hands it off to the Excel Web Access, which is the component that produces the HTML that ends up in your browser.  The point of today’s post (and the post that will follow) is that developers can also use Excel Calculation Services without needing to interact with the Excel Web Access.  Specifically, we have built a web service API directly on top of the Excel Calculation Services so that developers can call server-side spreadsheets directly from their own applications.  For example, developers could write code that opens a spreadsheet on a server, sets cells and ranges to specific values, controls external data refresh and workbook calculation, and finally retrieves values from the calculated workbook (or retrieves the workbook in its entirety).  Let’s look at some more specific examples.

What sorts of things are these web services good for?

Excel Web Services enables many interesting scenarios.  Here are two examples that we have heard repeatedly from our customers.
Using the logic in Excel spreadsheets in a server application – without the “traditional” need to recode this logic in a programming language.  In this scenario, the business expert who authored the spreadsheet can keep maintaining the model in Excel; the administrator can protect that model on the server using the appropriate set of users, roles, permissions, and a firewall; and the developer can call Excel Web Services to incorporate the logic in the spreadsheet into the rest of a custom solution.  Another variation on this would be providing custom UI to Excel-based server applications which use Excel Web Services to interact with a server-side calculation session.

  • Automating spreadsheet updates on servers.  This works especially well in combination with the new Open XML file format, which greatly simplifies the task of programmatically creating an Excel file from scratch or using a template.  Once the new file has been created, it often needs to be calculated – for example, if there are external data feeds that need to be updated.  It is straightforward for developers to write code to use Excel Web Services to do all of this, then retrieve an up-to-date copy of the calculated file and save it back to the server, or deliver it to any other destination.
  • What specifically can the web service do?
    Here is the summary: your code can start a session with the Excel Calculation Service, set values into cells and ranges, process the workbook, and get calculated values or the entire workbook back into your application.
    Here is the longer answer… a full list of what your code can do with Excel Web Services:

    • GetApiVersion: Get a version string of the installed web service API build.
    • sessionId = OpenWorkbook: Open a server-side calculation session. The method takes a workbook file path, and a few other arguments, and returns a sessionId.
    • GetSessionInformation: Get a few properties of the server session, primarily the language context of the session.
    • SetCell: Set a value into a cell on one of the workbook’s sheets. Two flavors of this method exist: one takes a cell address (e.g “B52”) or a named range (e.g. “Interest”), and the other accepts integer coordinates, for cases where it is more convenient for your code to use them (typically when you have indexes in the code and want to use them to index the sheet).
    • SetRange: Same as SetCell, but for setting values into an entire contiguous range. Same two flavors exist.
    • Refresh: Read data from an external data connection (or all of the workbook’s connections) and refresh the values in the relevant cells, e.g. in PivotTable cells or in the results of cube formulas.
    • Calculate: Recalculate the formulas in a specific range or in the entire workbook. Useful when the workbook author has turned off automatic calculation. Two flavors – using a string or integer coordinates to refer to a range – much like in the Set methods.=
    • CalculateWorkbook: Calculate the entire workbook, using one of two calculation methods:
      • Recalculate: Calculate only formulas that have dependencies that changed (aka “dirty” formulas).
      • CalculateFull: Calculate all formulas, regardless of dependency changes.
    • GetCell: Get a value out of a cell. The two regular addressing flavors exist. You can either get formatted string values, or the raw binary values.
    • GetRange: Get a set of values out of a contiguous range of cells. Same addressing flavors.
    • GetWorkbook: Get the entire calculated workbook into your application memory, as a byte array. You can either get the live result, or a snapshot – essentially, a workbook with the layout of the original workbook, with all the original formatting and with up-to-date values – but with all the formulas and external connections stripped, and without the portions of the workbook that were marked not for viewing during publish

    . More on snapshots in a future post.

  • CancelRequest: If your application runs the Excel Web Services session in a separate thread, and wishes to abort a long-running server request (e.g. a long calculation that the user got tired of waiting to) – it can do so by calling this method.
  • CloseWorkbook: Tell the server to close the workbook that it opened for this session, thereby also allowing the server to release all the resources that it maintained for the context of your session.
  • Error handling
    Errors are exposed to a developer’s application in three ways:

    1. Excel calculation errors show up just like they do in Excel – as cell error values (e.g. #VALUE!). When you call GetCell or GetRange and ask for formatted values, you’ll get the #-style error string; when you ask for unformatted values, you’ll get an enumerated error code.
    2. An error in processing one of the web service methods (which does not enable the method to finish successfully) is exposed as a SOAP exception that your code can catch.
    3. Less critical errors, which do not prevent the method from returning normal results, are returned as part of the method arguments (specifically, as an output argument). The reason for this is that an exception would divert the code from its normal execution path, and this is not desirable with these non-critical errors. Checking for them is optional.

    One thing that developers will need to be aware of is the way Excel Calculation Services maintains sessions for performance reasons. A good way to understand the benefit of server state is to think about a user who interacts with an Excel spreadsheet in a web browser.  Each time the user takes the next interactive step, e.g. drills down a PivotTable, changes an input parameter, refreshes data connections and so on – we want the server to only compute the difference between what the user saw on the screen before taking the current step, and what they should see as a result of this step.  For performance reasons, we do not want the server to read the workbook file from disk again, or to recalculate formulas that do not need recalculation.

    This is also desirable when an Excel server-side calculation is performed by the server for a custom solution that uses the Excel Web Services.  For example, if the solution code sets a cell to a new value, we only want the server to calculate formulas that depend on that cell’s value – and nothing else.  So the server keeps the “state”, or context, of a custom application’s calculation in server memory.  This context is called a session. In order to let the application tell the server which session it is working with, a session id is used.  The server returns this id to the application when it starts a new session (by opening a workbook), and then the application code passes this session id to subsequent web service calls.

    What’s next?
    I will review a sample application that provides a specific example of how to use Excel Web Services.