Chapter 25
Using Excel Services
IN THIS CHAPTER
- Introduction to Excel Services
- Excel Services Architecture
- Using the Excel Services Web Service
- Creating a Managed Excel Services User-Defined Function
Excel Services is just one of many new and powerful Shared Services that are part of Microsoft Office SharePoint Server (MOSS) 2007. Using Excel Services, users can centrally store important Microsoft Excel documents and expose their calculations, functionality, and data to other users of SharePoint through Web Parts and through a web service. This chapter provides you with an in-depth look at this powerful new feature, its impact on business operations, Business Intelligence, and how you can interact with Excel Services as a developer.
Introduction to Excel Services
In virtually every organization, there are people who work with Excel. Excel enables individuals to create charts from data contained in a spreadsheet, link data in a spreadsheet with relational data in a database such as SQL Server 2005, and much more. In addition to storing data and rendering data-driven charts and graphs, Excel has a powerful formula language that enables it to perform extremely complex calculations. These calculations enable information workers to do everything from create expense reports that dynamically calculate the amount of refund due an employee to perform extremely complex calculations on sets of data taken as a snapshot from a back-end database. The key feature that Excel provides isn’t simply the ability to create formulas that operate on data; it is the fact that Excel enables information workers with no programming language skills to perform these calculations.
The problem with Excel stems from the fact that, while immensely powerful, an Excel spreadsheet is still just a simple file on a disk. When people attempt to share spreadsheets, collaborate on the data contained within the spreadsheets, and utilize the formulas and automatic calculations contained within a spreadsheet at the enterprise level—things get ugly quick.
What happens all too often in this situation is that the Excel spreadsheet becomes cumbersome. The spreadsheet is often retired and enormous, lengthy, and costly custom development projects are started to replace the Excel spreadsheet with a so-called better solution. The problem is that these solutions are the domain of programmers, and the information workers, managers, analysts, and other Excel users are then at the mercy of the application built by developers. If a formula needs to be changed, or some analytical rules need to be changed, more custom code might have to be written. The speed, flexibility, and ability of Excel to change formulas on the fly disappears and is replaced by the slow, cumbersome development life cycle of an enterprise-scale software product.
The solution to this problem is Excel Services. Excel Services is one of the new Shared Services that are part of SharePoint 2007 and it extends the functionality of Excel spreadsheets into the enterprise, allowing the functionality of the spreadsheets to be shared at the enterprise level without requiring custom programming, expensive software development projects, or even knowledge of a programming language.
Excel Services provides three main features: workbook management, Centralized Application Logic, and Business Intelligence enablement. All of these features and how Excel Services enables them are discussed in this section.
Workbook Management
Instead of managing the nightmare of having multiple versions of the same workbook spread throughout an organization on multiple machines, Excel Services allows for central workbook management. A single, trusted author can be given the rights to make changes to the workbook, which are then made visible to all clients utilizing the shared version of the workbook. Workbooks working against snapshots of back-end data can then be provided to multiple users in read-only fashion to protect the underlying data source while still providing enterprise-scope functionality for analytics, graphing, and charting.
Centralized Application Logic
Instead of spending a lot of time and money converting a workbook into a complex custom application, the application logic contained within a workbook can be maintained in Excel Services and provided to the entire enterprise in a secure, reliable, and scalable fashion. Excel Services can handle hundreds of simultaneous requests for the same piece of data from within a workbook, and will dynamically provide the results of cell calculations. For example, the custom logic embedded in a time sheet workbook can be hosted in Excel Services and provided to client applications. Any time the time sheet rules and formulas need to change, the single workbook can be modified by the trusted author without requiring expensive and time-consuming code changes.
Business Intelligence
Business Intelligence portals provide central, unified access to summary information obtained from data warehouses. This summary information includes report cards and score cards. A score card is essentially a list of Key Performance Indicators (KPI) that graphically display whether a particular aspect of the data is performing within desired thresholds. Using a workbook on a portal page via Excel Web Access (the architecture of Excel Services is discussed in detail in the next section), data supporting the score cards and KPIs can be displayed using charts, graphs, or even raw data. Moreover, administrators can configure the interactivity level of the hosted workbook such as defining read-only cell ranges and indicating which portions of the Excel user interface are displayed. This is an unbelievably powerful feature that provides functionality that required extensive custom programming or integration of third-party products in previous versions of SharePoint.
Excel Services Architecture
Excel Services contains three different components: Excel Web Access, Excel Calculation Services, and Excel Web Services. This section illustrates what each component does and how the components interact. A diagram of the Excel Services architecture is shown in Figure 25.1.
Figure 25.1. Excel Services architecture.
Excel Web Access
Excel Web Access (EWA) is the portion of Excel Services that is visible to the end user. EWA is a Web Part that can render workbooks on a SharePoint Web Part page. There are dozens of configurable options, including the ability to set how interactive the workbook will be when displayed on a page. In addition to specifying how interactive the rendered workbook will be, you can also specify which columns and rows to display, as well as how much of the Excel graphical user interface (GUI) to render, such as the toolbar.
Figure 25.2 shows a sample of the Excel Web Access Web Part on a portal home page. In this screenshot, interactivity has been disabled on the Web Part.
Figure 25.2. The Excel Web Access Web Part.
Excel Calculation Services
Excel Calculation Services (ECS) is the workhorse of the Excel Services feature suite. ECS is responsible for loading a workbook at the request of a client in a session unique to that client. After being loaded, ECS can do things like refresh snapshots of external data and dynamically perform calculations. Changes made during the session are maintained for the duration of the session but are discarded when the session is completed. For example, a client application opens a workbook and enters in the total hours worked on a project. The workbook dynamically calculates the amount to charge the client for that project based on the input. While the session is open, the client application can interrogate the cell containing the bill amount. When the application is done utilizing ECS, the session can be terminated and the workbook will be unloaded.
Note
It is important to realize that Excel Services is not a means by which end users can make and save changes to Excel workbooks. Those changes can be made through SharePoint libraries and the Excel client itself. Excel Calculation Services is responsible for refreshing snapshot data in the workbook and running calculations in the workbook. It does not allow you to make permanent changes to a cell’s value.
Excel Web Services
Excel Web Services is essentially a front end to Excel Calculation Services. By using the web service, developers can create code that can remotely open Excel workbooks, input values, perform calculations, and obtain results. All of this is done utilizing the fully scalable architecture of Excel Services and will work in a farm scenario. The next section focuses specifically on consuming the Excel Services Web Service.
Using the Excel Services Web Service
You will find the Excel Services Web Service at the portal level. The uniform resource locator (URL) for the web service is http://[server]/_vti_bin/ExcelService.asmx.
The Excel Services Web Service provides the methods described in Table 25.1. Keep in mind that you can either use a numeric, zero-based ordinal indexing mechanism for indicating cells and ranges, or you can use the Excel standard “A1” notation where you indicate cells based on their letter column and numeric row, such as E5 or A1:A7.
Table 25.1. Excel Services Web Service Methods
Setting Excel Services Trusted Locations
To provide a secure environment for Excel Services, Excel Services does not allow a workbook to be loaded by Excel Web Access, Excel Web Services, or Excel Calculation Services unless that workbook resides in a trusted location.
Trusted locations are configured through Excel Services settings and allow you to define the security settings for a workbook location as well as settings dictating data refresh behavior and whether a workbook in that location can pull data from external sources.
To add a trusted location to Excel Services, first open the SharePoint Central Administration page (you can reach this from the Start menu on the server as the port number for the administration site varies from installation to installation). Next, go to Application Management and then Shared Services. Finally, click Trusted File Locations within Excel Services. From there, you can click the Add Trusted File Location link, which can be a Universal Naming Convention (UNC) or a URL. For example, if you create a document library at the portal level called Excel Files, the trusted location URL might be: http://[server]/Excel Files/.
Canonical “Hello World” Sample, Excel Services Style
To see the Excel Services Web Service in action, you need to put an Excel workbook into a trusted location. Following the steps in the preceding section, create a new document library called Excel Files and make it a trusted location.
Next, create a new Excel 2007 spreadsheet. Set the contents of cell A1 to “hello” and the contents of cell A2 to “world”. Set the contents of cell A3 to a formula that concatenates the contents of cell A1 and A2. Figure 25.3 shows this workbook inside Excel.
Figure 25.3. A “hello world” workbook.
Save the workbook to the document library created earlier and you are now able to utilize this workbook within Excel Services because the document library is an ECS-trusted location.
Create a new C# console application in Visual Studio 2005 and add a web reference to the ExcelService.asmx Web Service on your development server. (This will also work on a Virtual PC testing server if you’re short on hardware.) Name the web reference ES. Modify the Program.cs file in the console application so that it matches the code shown in Listing 25.1. To get the fully qualified URL to the Excel spreadsheet that you will need for the following code, you can simply right-click the filename in the document library and choose Properties.
Listing 25.1. ExcelServicesHelloWorld Console Application
Obviously, you’ll want to change the server name and credentials to match your development environment. When you run the application, you will see the following output:
Formula-generated string from Excel services: helloworld
Formula-generated string from Excel services after modification: goodbye world
An important thing to note with this code sample is that the actual workbook on the server did not change. Each session within ECS gets its own private copy of the workbook to use. When a client application makes changes, those changes are made to the session and not to the actual Excel file stored in the trusted location. This allows hundreds of different people to use the same shared workbook for calculation without running into each other’s data. Of course, this only works when you open the spreadsheet through the SharePoint link rather than downloading the file.
Also note that with every web service method call after the initial call to Openworkbook, the string sessionId is passed as a parameter. This is what allows Excel Calculation Services to keep calculation sessions separate while providing calculation services for multiple clients for the same workbook at the same time.
Developing a Real-World Excel Services Client Application
Although it might be instructive to see how to use the Excel Web Service to concatenate two strings, it isn’t exactly practical. In more common usage scenarios, an Excel spreadsheet developer will define multiple named ranges that will be used as input parameters to the workbook. A client application will then supply values for the input ranges and read values from the output ranges and can even save a copy of the workbook maintained within the ECS session to disk.
A more practical example of using the web service might be to have a client application interface with a shared workbook that manages compensation amounts for mileage traveled on company business.
To create the shared spreadsheet for this example, create a workbook in an Excel 2007 spreadsheet that has a named range called MileageValues (this range contains 15 cells in this example). Also, another named range called PaybackAmounts should be created positioned alongside the first range. Finally, another range should be created called TotalPayback that contains the sum total of all reimbursements for company travel.
If you don’t want to create this spreadsheet yourself, you can use the mileage_expense.xlsx file that comes with this chapter’s code. A screenshot of this spreadsheet is shown in Figure 25.4.
Figure 25.4. A shared spreadsheet with multiple named ranges.
Using the steps detailed earlier in this chapter, add the mileage_expense.xlsx spreadsheet to your trusted document library.
Create a new console application called MileageExpenseClient, add a web reference to the Excel Web Service called excelService, and change the Program.cs file so that it contains the code in Listing 25.2.
Listing 25.2. Reading/Writing Ranges and Saving Temporary Workbooks
There is a lot going on in this sample. The first thing that differs from the “hello world” sample is the creation of a new jagged object array called rangeValues. All range-based operations on the Excel Web Service deal with jagged arrays. The first dimension of the jagged array is the list of rows in the given range. Each row, also a jagged array, is the list of cells contained within that row. So, to fill a vertical range of single-cell rows, you need to create an array of 15 single-element jagged arrays, as shown in the preceding code sample.
Next, the sample uses the GetWorkbook method to obtain the raw bytes of the workbook. The data retrieved is determined by the WorkbookType enumeration, which can contain the following values:
FullSnapshot—This workbook type returns a snapshot of the entire Excel spreadsheet file.FullWorkbook—This workbook type returns a snapshot of the indicated workbook only.PublishedItemsSnapshot—This workbook type returns a snapshot of only the published objects in the file.
Note
One thing to remember when working with ranges is that the array you supply to set a range must be the same size as the range within the workbook. You can supply null values or other placeholder values to indicate a lack of data, but if your range contains 15 rows, you need to supply a 15-element jagged array, even if the subarrays don’t contain any elements.
All range operations work with jagged arrays, so when you retrieve a range from a workbook, the values are also contained within a jagged array—even if the range contains only a single cell.
When you call GetRange or GetRangeA1, the result is an object array. Each element in this array is a jagged array representing the cells within that row. In this case, the range “TotalPayback” was a single cell. A single cell range is a single-element jagged array that contains a single-element object array. Because an object array can contain any kind of element, you have to specifically typecast each element of the outermost object array as object[] to get the actual values.
Figure 25.5 shows the MileageExpenseSnapshot.xlsx spreadsheet retrieved from the session. Note that all of the values within the input range have been specified, and all calculations were performed.
Figure 25.5. A workbook snapshot retrieved from the Excel Web Service.
When looking at the workbook snapshot, it is important to remember that the raw workbook saved from the GetWorkbook method call has no formulas or calculations contained in it. It is, quite literally, a snapshot of what the workbook looked like at the moment the workbook was retrieved. This is done by design and is extremely useful for client applications that want to allow users to fill in data, perform calculations, and then retrieve printable or viewable results.
Creating a Managed Excel Services User-Defined Function
The ability for programmers and information workers to create extremely powerful scripts directly within Excel using Visual Basic for Applications (VBA) has always been a major factor in Excel’s appeal. With VBA, the Excel users could allow their workbooks to access Component Object Model (COM) objects, read data from external sources, and perform complex conditional logic that would be too cumbersome to perform within an Excel calculation formula.
With Excel Services, you can create your own user-defined functions (UDFs). However, instead of being limited to a loosely typed scripting language like VBA, users can create UDFs in C#.
The creation of a UDF is very simple, and might seem familiar if you have created stored procedures in SQL Server 2005. To start with, you will need to locate the Microsoft.Office.Excel.Server.Udf.dll Assembly. By default, this Assembly can be found at [drive:]Program FilesCommon FilesMicrosoft Sharedweb server extensions12ISAPI.
Create a new Class Library project in C# called ExcelUDFLibrary and add a reference to the Excel UDF library. The process of creating the UDF itself is quite simple. All you need to do is create a class and decorate it with the UdfClassAttribute code attribute, and decorate each method within that class that will be used as a UDF with the UdfMethod attribute.
Take a look at the code in Listing 25.2 that illustrates an extremely simple user-defined function provided for Excel Services. Keep in mind that UDF libraries belong to the SharePoint installation and not to a specific workbook, so you can create libraries that can be used by multiple workbooks.
Listing 25.2. A Sample Excel Services UDF
In the preceding example, there is a web reference to a web service hosted on the local machine that provides a single test method called GetString. This illustrates the real power of Excel Services: Within a UDF, you can do virtually anything that the .NET Framework can do, and provide that functionality to a centrally hosted workbook. Further, UDFs invoked from within an Excel calculation in a hosted workbook are also invoked when the workbook is accessed via Excel Web Services, giving the developer unprecedented power from within a simple workbook.
There are a few restrictions. Obviously, you will not want your code to do anything that requires interaction with the console or Windows interface such as opening dialog boxes, creating Windows Forms, and so forth. Second, you are limited in the data types that can be used because Excel Services has to convert the data contained within the Excel spreadsheet to something that can be read by the .NET Framework, and vice versa.
The following is a list of the data types that can be passed as parameters to an Excel Services UDF:
- Numeric data types, such as integer, decimal, double, float, long, and so on
- String
- Boolean
- Array of object
- DateTime
In addition to these types, the return value of UDF methods can include data of type System.Object including the value null.
After your UDF library is created, you need to deploy it to the physical SharePoint 2007 server machine because Excel Services does not allow remotely located UDF libraries.
Copy the file to any location you want; just remember the location because you will need it when configuring Excel Services next.
After the file is copied, open the Shared Services Administration page and select Excel Services. Within the Excel Services Administration page, select the User-Defined Function Assemblies option. Add a new UDF library to the list, making sure to provide the local file-system location of the library and select the File path option. After the UDF library has been configured within Excel Services, your UDF maintenance screen should look similar to the one shown in Figure 25.6.
Figure 25.6. Excel Services User-Defined Functions library administration.
You’re almost done. The only thing left to do is to create an Excel workbook that utilizes this new user-defined function and then place that workbook in an Excel Services trusted location like the one created at the beginning of this chapter.
To use the new UDF, just invoke it in an Excel formula within a cell in the workbook:
=GetStringFromService()
When Excel evaluates this formula offline, the text #NAME? appears within the cell. This normally tells the Excel user that there was a problem with the name of a function call in a formula. You can safely ignore this message because the UDF will be evaluated when the workbook is placed inside Excel Services.
When you upload the new workbook to a trusted location, any cells using UDFs configured within SharePoint will automatically evaluate them properly. Because the workbook is now managed by Excel Services, you can access the value of the dynamically calculated cell either through Excel Web Services, or through Excel Web Access, as shown in Figure 25.7.
Figure 25.7. A workbook invoking a C# UDF hosted in Excel Services.
Summary
Excel Services is an incredibly powerful new Shared Service that is part of Microsoft Office SharePoint Server 2007. It enables organizations to take calculations and business logic that used to be difficult to propagate and share and place them in a central location. From there, any number of clients can create a session against that shared workbook, enter input, perform calculations, read calculated values, and even obtain their own copy of the static workbook output. Using Excel Web Services, developers can attach their application front end to the powerful shared business logic contained within the Excel Services back end, enabling new application models and allowing for the reuse of existing investment and business logic.
This chapter showed you how to configure Excel Services trusted locations, place workbooks in those locations, and begin working with the shared workbooks programmatically using Excel Web Services. Microsoft has created an immensely powerful tool that seems only limited by the creativity and imagination of developers.