asp:Feature
Excel Services
A Mature Design Concept
By Alvin Bruney
For some time now, the developer community has been uncertain about the future of Office automation applications in ASP.NET. Office technology has a long, hard history of meandering sometimes aimlessly between ActiveX controls and server-side automation. Fortunately, that s all about to change. The future of Office automation is here, and it s built around Excel Services.
Excel Services is a brand new technology built on Microsoft Office SharePoint Server 2007 (a.k.a MOSS) and the .NET Framework. It stops just short of being a replacement for the Office Web Components, Excel Interop libraries, and all versions and mannerisms of Office automation. As you can see, these goals are rather ambitious and imply a redefinition of the existing Office programming model at both the design and implementation level. It s a paradigm shift aimed at consolidating the often ad hoc approaches to Office automation.
The architecture that fuels Excel Services is made up of a Web front end, Excel Web Access, and an application service tier, Excel Web services. Excel Web Access provides the same consistent look and feel of Microsoft Excel, albeit without the new Ribbon User Interface (see Figure 1).
Figure 1: Office 12 Excel
spreadsheet.
The Excel visual fidelity is presented with zero footprint on the client. In case this went over your head, let s rephrase! There is no Excel ActiveX object or rich-client installation requirement for browser-based access. The interface is built with JavaScript and DHMTL only. The implication here is that the front end is cross-browser compliant by default.
The Excel Web Access client is fully customizable at run time and design time, and can easily be incorporated into MOSS dashboards or customized to enrich the data mining experience of the knowledge worker. Excel Web Access includes feature-rich offerings and additional tools that improve the functionality of common components. One example of this is the extended toolbars summary and navigation that complement the default toolbar on the user interface. These new additions are security-aware; they display toolbar elements based on the trusted location in which the workbook is published. They are also run-time adaptable so that content options are intelligently tailored to the material being displayed in the document.
Excel Web Access also contains a proxy that facilitates communication between the application tiers in a server farm scenario. The proxy provides load balancing functionality in clustered environments using either the round-robin, local, or hash-based settings. That is a welcomed tool for administrators interested in throttling load during peak times or developers who require sticky application server access in server farm environments.
Let this soak in for a bit, because there s more! The absence of an ActiveX control requirement removes the licensing barriers that plagued the Office Web Components. And while you ponder this, let s add one more tasty morsel. There is no need to have an installed version of Microsoft Office on the client or on the server, for that matter. You can now write applications that breach Internet and operating system boundaries! The age of developing platform-agnostic Office applications is upon us!
So how is all this possible? It turns out that the Excel Web Access front end is built around Web parts in ASP.NET. In fact, the Excel front end is just another flavor of an ASP.NET Web part able to run in any browser on the client and respond to and interact with the end user. The Web part maintains the Excel-in-a-browser feel, (again, see Figure 1), while providing the operational integrity and functional excellence of Microsoft Excel. And it s not just the spreadsheet that is available; Excel Charts and Pivot tables will find a suitable home inside the browser, as well. As far as Web parts go, the model is easy to use. Let s examine some simple code to display a workbook similar to that presented in Figure 1:
using Microsoft.Office.Excel.WebUI;
//create the class and display the workbook
ExcelWebRenderer ewa = new ExcelWebRenderer();
ewa.WorkbookUri = "a work book uri goes here";
Let s dig a little deeper to see how all this fits together. The model derives its scalability by loosely coupling the Excel Web part front end to the Excel Service running on the SharePoint server. This Excel Service is the brains behind the outfit and is implemented as a .NET Web service that is able to invoke the Excel calculation engine. The Excel Web service (and its helper assemblies) is installed as a Windows service during the SharePoint setup process and contains security and version info that enables administrators to manage and configure access across the enterprise.
But hold on to your horses, this isn t just any ordinary Web service; it was built from the ground up, with scalability and robustness in mind. The Web service will support multiple users querying a single workbook. The Web service will support server farm scenarios in multi-CPU hardware configurations so that load balancing is supported out of the box! The Web service will enforce security restrictions imposed by the workbook author. The Web service will support data snapshots so that clients with view-only access can indulge in a rich data experience while sensitive data remains protected and inaccessible. Couple that with the benefits of a service-oriented architecture and you have a flame that is capable of sparking an Office automation revolution all over again.
And if you are feeling a bit overwhelmed by all this, consider that user-defined functions (UDF) are now supported as regular .NET class library templates. These UDFs maintain the same caller semantics as previous versions, but they are now managed artifacts. For instance, you can call a UDF in the spreadsheet by typing the following into cell A1:
"=myFunction()"
Then, simply create a class library in .NET as you usually would, tag it with an attribute, and make the compiled code available to the spreadsheet. Here s an example:
using Microsoft.Office.Excel.Server.Udf;
namespace mySampleUdf
{
[UdfClass]
public class myClass
{
[UdfMethod]
public string myFunction ()
{
return "hello world";
}
}
}
There s a lot of frazzle and dazzle in here that s enough to distract you, but you should be focused enough to realize that UDF support allows for extensibility of the Excel model. You can now ambitiously roll your own code using the Excel model as your platform to build robust, scalable pieces of enterprise software. Office developers have never had it so good. Never!
Figure 2 shows an enterprise production setup. A typical use-case would involve a client-initiated request that is authenticated by MOSS. MOSS scopes its authentication to the permissions set configured by the workbook author. These rights typically involve reader, contributor, administrator, or viewer privileges. Privileges can extend from the workbook all the way down to the spreadsheet cell level to include embedded objects that form part of the document. The authentication process is applied to browser-initiated requests, and implicitly to the Excel Web service calling code, as well.
Figure 2: Excel Services enterprise
setup.
The response to the client request is immediately cached by the Excel Services framework. Excel Services contains a multi-level approach to caching that can act in concert to drastically improve response times. The multi-level cache is configurable and is able to share objects among clients with the same permissions set. Expect these features to improve the collaboration experience when spreadsheets are shared across the enterprise.
From Figure 2, notice the Excel load balancer. It is cache-aware and capable of adapting its behavior to account for load, cache configurations, and session-state settings. The load balancer effectively negates the purchase of expensive, specialized load balancing software for the application tier.
For client requests that involve the calculation engine, each spreadsheet calculation request is handled on individual threads. Though a single spreadsheet calculation invocation is atomic, Excel Services is able to distribute concurrent computations for different spreadsheets by distributing these requests across the server farm. The design accommodates distributed computation on high-performance clusters with redundancy software. The architecture has been tuned to provide near-linear performance increase with each new server added to the farm. The performance increase occurs without penalty to the computational integrity of the calculation engine. In addition, the calculation engine achieves computational fidelity with the Excel desktop version so you can rest assured that your calculated results, distributed or not, will be Excel accurate.
If the client requests involve a database to include ODBC or OLE-DB, Data Connection Libraries (DCL) may be involved in the process. DCLs are created by administrators and contain end-user friendly names that map to database connection information. The process abstracts the technical aspects of database connectivity behind a simple name that knowledge workers can leverage to negotiate access with client-facing data sources. DCLs enforce security and protect sensitive connection information while allowing data sources to be discoverable, manageable, and scalable.
So what s the catch? There are a few, so let s slow down to take a look at the dark side. Microsoft chose to platform Excel Services on MOSS because of the value of the SharePoint framework as an enterprise solution. On the plus side of things, MOSS contains strong document management technologies to include configuration, administration, profiles, document libraries, search capabilities, and content management that Excel Services can leverage for free. However, on the minus side, to benefit from these advantages you may need a dedicated server to host MOSS.
And then there s the new SharePoint server licensing to contend with. To use Excel Services you must purchase MOSS, a standard Client Access License (CAL), and the additive Enterprise CAL. In layman-speak, this is the revenue generating mechanism for Microsoft Excel Services, so expect it to be priced accordingly.
Although Excel Services supports UDFs, it does not support macros, VBA, and code that can access the Excel Object Module. You ll most likely need to rewrap these legacy approaches into managed UDFs. For the enterprise, it will mean further development during the migration phase.
The zero footprint we boasted about earlier does contain some notable limitations. For one, client input is handled through a custom task pane. Users are not allowed to enter data directly into cells. This seems unnatural and disappointing, especially when compared to Google Spreadsheets, which allows end users to enter data directly into the cells (see Figure 3).
Figure 3: Google Spreadsheets.
Excel Services is not designed to author Excel workbooks. For that, you ll need to use the Office Excel desktop version. Preventing spreadsheet authoring from thin clients helps eliminate Sarbanes-Oxley (SOX) compliance regulation issues. In addition, the available feature set in Excel Services is scaled down to avoid competition with the desktop version. There is also a long list of incompatibilities and unsupported features with which you may want to be familiar. That familiarization effort can pay dividends as it helps decision makers gauge the level of effort and functional expectations before taking the plunge.
One final gotcha worth mentioning is that Excel Services opens workbooks in user sessions. Think of the session approach as a personal copy of the workbook similar to pass-by-value semantics for structures in C# method calls. Any changes made to the workbook in these sessions are temporary and volatile in nature, retaining significance only for the duration of the session. If the session is closed or inadvertently lost, the customizations will not be persisted to the underlying workbook. Again, that design decision was driven by SOX compliancy.
All in all, Excel Services is a mature design concept built to avoid the pitfalls of previous ad hoc Office technologies. The implementation of this design is consistent with .NET programming such that the learning curve is a non-issue. I would venture a guess that Microsoft has gotten it right with respect to licensing, scalability, and ease of use when compared to previous Office automation approaches. These metrics can be critical because they influence the degree to which corporate entities will buy in to the new technology. And, ultimately, that fuels revenue.
Excel Services offers much in terms of secure sharing of spreadsheets, Business Intelligence, extensibility, reliability, and availability. The Web services component offers the ability to manage sessions, process workbooks, and return calculation services results. These options allow corporations to provide data while preserving intellectual property.
It s also nice to see Office automation benefiting from a legitimate security initiative. In the past, the security vacuum had forced developers to bludgeon out homegrown alternatives that tended to be overly complicated, easily circumvented, and poorly supported. It remains to be seen whether this bold initiative will succeed, especially given the entry of titans like Google into the Excel spreadsheet domain. But certainly, Excel Services should set a solid pace for Office automation in the future, all things considered. Expect this beauty to burst on the scene in early 2007 fingers and toes seriously crossed.
Alvin Bruney is an ASP.NET MVP. He has been involved in .NET technology since the beta version. His book, The Microsoft Office Web Components Black Book with .NET, is available at http://www.lulu.com/owc.