Excel Capabilities on SharePoint 2010 Share Point 2010

As mentioned, MOSS 2007 introduced Excel Services as a mechanism for using Excel-based models and data on the server. With SharePoint 2010, Microsoft has extended this offering to Excel Services 2010, an updated version of the existing Excel Services functionality, and the Excel Web Application, a browser-based version of the Excel client application. Let’s take a look at the features available in Excel Services 2010.

Excel Application Services

The Excel Services application service can be configured on SharePoint Server 2010. Comprising Excel Calculation Services and potentially a collection of user-defined functions, Excel Services is responsible for loading workbooks and performing appropriate calculations. Where workbooks contain links to external data or user-defined functions, Excel Calculation Services is responsible for making the appropriate connections and loading any required external assemblies. Excel Calculation Services maintains a session for each open workbook user, and the session remains open until it is either explicitly closed by the user or it times out. Furthermore, when loading workbooks into memory, Excel Services maintains a cache of workbooks as well as any associated external datasets. Excel Calculation Services does not support the complete range of features offered by the Excel client application. In the previous version of Excel Services, if a workbook contained a feature that was unsupported, it was impossible to load it using Excel Services. With Excel Services 2010, a more pragmatic approach has been taken; it’s now possible to load any workbook, and if the workbook contains unsupported features, a warning is generated. This allows users to continue using supported features within Excel Services without having to modify the spreadsheet.

User-Defined Functions
As mentioned, Excel Calculation Services has the responsibility of calling external functions as required by a workbook. User-defined functions (UDFs) make it easy to extend the capabilities of Excel Services to include interactions with external systems as well as custom calculation logic. In fact, anything that can be defined using a .NET assembly
can be referenced as a UDF provided the appropriate interfaces are implemented.

Excel Client Service
As you’ve seen, Excel Calculation Services is responsible for loading workbooks and performing the necessary calculations. However, when it comes to accessing the results of those calculations both programmatically and for display purposes, the Excel Client Service provides a number of different mechanisms.

UDF Real-World Example
Here’s an example of where all of this could be useful: I was involved in the redesign of a hydrocarbon accounting system. Hydrocarbon accounting, for those uninitiated in the art, is a consequence of the fact that most oil extraction companies do not operate refineries or their own dedicated pipelines. Generally speaking, a refinery operator provides a pipeline that connects up all oil extraction companies to the refinery. In an ideal world where oil was just oil, this would be a straightforward affair; oil extraction companies would simply meter how many barrels of oil they sent down the pipeline and receive payment from the refinery based on the number of barrels. Unfortunately, oil isn’t just oil. In fact, oil is a generic name for a collection of hydrocarbons, each with different relative values—for example, lighter hydrocarbons that make up petroleum tend to have higher values than the heavier components of bitumen. As a consequence of the different hydrocarbon blends that are pumped into the pipeline by each extractor, each blend must be periodically sampled to determine exactly which hydrocarbons are present, and these samples are used to determinewhich portion of the consolidated mix that reaches the refinery belongs to which extractor. Considering that a pipeline may have 40 to 50 different extractors and each extractor may have several different wells all producing a different blend, you can see that determining the relative value of the product being pumped is no easy feat. The system that I worked on made extensive use of Microsoft Excel and Visual Basic for Applications (VBA) in performing these complex calculations. Samples were stored in a database and were extracted into a complex series of spreadsheets that were ultimately used to produce statements for the connected extraction companies. Since the application worked its magic on the client, all calculations had to be performed in real-time whenever a statement was required. As you can imagine, this was a time-consuming process. However, given the technologies of the day, Excel as acalculation engine was unsurpassed and the system worked well for a number of years. Now to get back to UDFs and Excel Services—had I been rebuilding this system today, Excel Services would have allowed the calculations to be performed on the server. Furthermore, the calculation results would be automatically cached and available to multiple users effectively instantly. UDFs could be easily used to replace the complex calculation functions that were previously coded using VBA and sample data; in my example, this was stored in an Oracle database and entered via a PowerBuilder user interface, and this could easily be captured using an InfoPath form and stored in a SharePoint list. The key thing to note in all of this is that the exact same spreadsheets that we were using in the original system could be reused with Excel Services with practically no major modifications.

Excel Web Access
So that workbooks calculated using Excel Calculation Services can be rendered in the browser, Excel Services provides the Excel Web Access web part. This web part effectivelycreates HTML based on the output of Excel Calculation Services that mirrors the output we would expect to see if we were using the Excel client application. As well as rendering output, the web part also provides a similar level of interactivity to the Excel client application as well as a few custom features that have been designed for use in web applications.
For example, an Excel Web Access web part can be placed on a page and configured to display only a particular chart. If the chart is generated based on a table of data within the spreadsheet, the data that makes up that table can be filtered by hooking up a filter web part to the Excel Web Access web part. By using techniques such as this, you can create highly interactive data visualization tools simply by leveraging the functionality of Excel.

Excel Web Services
Sometimes we don’t really need to see an entire spreadsheet; sometime we’re just interested in the bottom line and we want to use that value in our application. Using Excel Web Services allows us to interact with a workbook in much the same way as we can interact with the Excel object model when building complex client applications. For example, using Web Services, we can set values within a spreadsheet or extract values from particular cells. We can even generate a snapshot of a spreadsheet that can be downloaded. If, for example, we apply this to the hydrocarbon accounting story (in the sidebar “UDF Real-World Example”) using Excel Web Services, we could produce a simple application that accepted a range of dates and use those to provide production statistics for a particular extraction company.

JavaScript Object Model
Using Web Services is all fine and well when it comes to interacting with Excel services from a client application, but what happens if we simply want to display a particular value of a web page? Or perform a calculation based on user input? Of course, we can still use Web Services, but calling Web Services from JavaScript is not for the faint of heart and imposes restrictions on the implementation of the Web Service itself. One of the new features of Excel Services 2010 is the JavaScript Object Model. (Strictly speaking, it’s called the ECMAScript object model, but that term’s always had a hint of Ghostbusters for me so I’m going to stick with the JavaScript Object Model.) The JavaScript Object Model (JSOM) can be used by inserting JavaScript onto a page containing the Excel Web Access web part. In effect, the web part emits the client-side objects necessary to interact with its contents via JavaScript.

One technology that’s gained a lot of momentum in recent years is Representational State Transfer (REST). I say it’s gained a lot of momentum because REST, like XML, is a description of an aspect of something that we already know and love and have been using successfully for many years: the World Wide Web. REST describes the architecture of the Web, and one of its key principals is that each resource should have a unique global identifier. Resources can be accessed using a standardized interface, and representation of the resource can be exchanged using a well-known format. Sound familiar? In web terms, this means that each resource should have a uniform resource indicator (URI), and the URI can be accessed using the ubiquitous Hypertext Transport Protocol (HTTP), and a representation of the resource generally in the form of HTML can be retrieved. The reason REST has gained a lot of attention in recent years is due to the extension of these principles into other areas. What if a resource is not just a web page? What if a resource is a record in a dataset? And, what if, rather than an HTML representation, a JSON representation is returned? You can see that we’re stepping into the world of Web Services without the formality that comes with Simple Object Access Protocol (SOAP). For a lot of purposes, this is a more attractive proposition. The key difference between a REST-ful web service and a SOAP web service is the existence of a message. SOAP is all about sending a well-defined message to a particular endpoint (or resource in REST parlance), whereas REST is about communicating with the endpoint only. There is no message, just a simple request for a specific resource. We could, of course, make the case that SOAP is an implementation of a REST-ful service, but that’s a whole different story.
The introduction of REST to Excel Services is an immensely useful feature, especially when you consider that one of the main uses of Excel Services is to create and use serverbased calculation models. The thing with models is that they exist only to provide results. More often than not, the model itself is not of interest, only the conclusions that can be reached by using it. By applying REST principles to a model, we can retrieve only the conclusions. More importantly, we can do so by using a human-readable uniform resource locator (URL), and this exponentially increases the potential for reusing that data. For example, if an Excel workbook contains a chart of sales figures that is generated using data in the workbook, using REST we can pick up the URL for the chart itself and use it within our web site in the same way that we’d use any other image. The difference between using a normal image and a REST URL is that the chart will be updated automatically as the data in the underlying workbook changes. To give some contrast, consider what would be involved if we had to do this using SOAP.

Excel Web App
With the announcement of Office 2010, Microsoft introduced a new product version: the Office Web Apps. Office Web Apps consists of new web-based versions of Excel, Word, PowerPoint, and OneNote, which are available via Windows Live, Microsoft Online, or as an add-on service for SharePoint 2010. The key aim of the Excel Web App is to mirror the user experience of the client application within the browser, allowing users to access their spreadsheets from anywhere using only a web browser. Beyond this, the web-based nature of the product delivers a few additional features that are not available in the client version. Probably the most significant of these is the ability to co-author documents. Consider the current situation when using Excel client, where each user editing a file must apply a lock, and a locked file cannot be edited by any other user. As developers, we experience this type of problem regularly when using source code control systems. There’s no more heinous a crime than a developer locking a file and then going off on vacation for two weeks. The Excel Web App prevents this problem by allowing all users with the appropriate permissions to edit a document simultaneously. Changes are pushed down in real time to all user sessions. Using Excel Services, this collaboration can be further enhanced by using Excel Web Services or the REST API. It’s possible for an external application to update a spreadsheet using a web service call or the REST API. Just as with other changes, these will be automatically pushed down to open user sessions.


I read a paper recently that suggested that the average cost of a single reusable business report could be as high as $20,000. At first, this seemed like an astronomical figure, but the more I thought about it, when you factor in the infrastructure costs and the to and fro between business users and developers, it’s not an unrealistic cost. Of course, the bigger question that this raises is whether the information provided by the report is sufficiently valuable to justify the cost incurred in obtaining it, and this highlights one of the big conflicts of business intelligence. To achieve the goal of business intelligence is to make up-to-the-minute, relevant information available to the right people at the light time. The problem is that determining what information is relevant and how it should be collected and presented is usually filtered through business analysts and IT project staff. Even when report definitions remain true to their original purpose, the world has changed by the time the reports are delivered. True business intelligence must incorporate a large self-service element, not just in terms of retrieving data from a selection of predefined reports but in using this data together with data from many heterogeneous sources in a manner that best fits the problem at hand.

PowerPivot is an add-in for SQL Server 2008 R2 and Excel 2010 that aims to address this issue directly. In effect, PowerPivot allows users to create unique data models from a range of data sources and embed the completed model within an Excel workbook. After the data is embedded within Excel, it can be easily analyzed using PivotTables, charts, or any of the myriad analysis features available within Excel.

You might be thinking, what’s different between PowerPivot and simply using external data sources? Excel is a versatile tool, but it’s fair to say that it has two main functions: to create numerical models and to analyze numerical data. When it comes down to creating models, using external data sources is a great way to import raw data into a model; however, when we’re using Excel as an analysis tool, there are a few issues with imported data. The first issue is volume: Excel has a limit of 1 million rows. The next issue is performance: If you’ve tried to run a PivotTable on a sheet with a huge number of rows, you know it’s not a pretty picture! PowerPivot addresses both of these issues by using an in-memory version of Analysis Services to process queries and store the underlying data. This means that it’s possible to analyze data sets containing tens or even hundreds of millions of rows, and, furthermore, it’s fast. As the old adage goes, “Always use the right tool for the job,” and the right tool for analyzing high volumes of data is Analysis Services. PowerPivot is all about making the right tool available to the right audience at the right time.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Share Point 2010 Topics