I’ve been meaning to write a blog on this topic for some-time. I’ve spent a lot of time with various customers over the last few years and we’ve found some really innovative use of this technology in a number of scenarios, yet very few people are aware that it exists or how much it has come on since SharePoint 2007.
As part of a customer engagement back in 2010 we came across a scenario whereby end-users were using Excel Spreadsheets along with pricing functionality exposed through C++ DLLs to perform key business tasks.
In this particular context, Excel was the tool of choice for the end-user to perform manual pricing based on (initially) few customer requests. Excel provided the ability for end-users to incubate new ideas quickly and easily but as transaction volumes increased it would need to be industrialised and ideally automated.
This would often require a 6+ month project and all too often developers having to re-create excel-esque functionality within a custom application.
As part of our overall proposition, Excel Services provided a huge amount of the required functionality. Excel Services provided the ability to leverage the Excel calculation engine from a server based deployment.
Excel Services is provided up by the broader SharePoint 2010 product through the Excel Application Service, which as per the Application Services model in SharePoint can be scaled out across multiple nodes as required.
Once enabled, you can place an Excel Spreadsheet into a SharePoint document library and instantly get access to it’s contents through:
- A Web version of Excel (Excel Web App)
- Excel Web Access Web Part
- An API (REST or SOAP)
The Excel Web App is something that your probably already familiar with (if not – there’s an example here:
http://docs.com/GZB6
) and here’s a screenshot:
You can edit the spread-sheet if you have permissions and can use many of the core Excel Features without needing Excel on the client, one neat feature is that multiple people can edit the same spread sheet and changes are pushed down to all active editors.
This can be a great solution to address the desire of taking back some form of control over the many spread sheets that tend to support key business tasks but not constrain the end user along also benefit from server side recalculation.
It also helps address the many deployment and versioning problems associated with any financial calculation logic held in user-defined-functions (DLLs) that need to installed and updated across an organisation. These DLLs only need to be installed on the server.
The Excel Web Access Web Part provides the ability to emit HTML views of an Excel Spread-sheet and it’s contents (perhaps just a chart) along with basic editing which itself can be constrained to a users session enabling simulation (including recalculation) and exploration of the worksheet and it’s contents without affecting other users.
The user can even then take a snapshot of the worksheet for offline use (if they have permissions.)
Here is a screenshot of the Web Part in action showing a table, note how in this case the Web Part has dynamically built a drop-down list of all the Tables, Charts, PivotTables, etc. defined within the Excel Spreadsheet
Here’s a chart shown using the Web Part as well:
In addition to this, a set of APIs provide direct access to an Excel Worksheet and support the previous techniques under the covers, e.g. you can pull out the same pie chart shown above as an image.
By placing an Excel spreadsheet onto a Document Library you can then treat the spreadsheet as a server-side object and programmatically access it’s contents for read and write operations through a REST or SOAP API. The SOAP API is a little more flexible (you can update ranges of data in one call, save changes to a workbook, etc.) but the REST API does most of what you typically need.
The Web App and Web Part can often provide a lot of what you need, either by giving users the ability to edit worksheets on the server and benefit from server-side recalculation or to surface charts/tables within a broader portal but the API option opens up a wealth of opportunities.
Here are a few scenarios where I’ve positioned Excel Services successfully:
Excel as a content source
An organisation had requirements around constructing documents using data created by a data analysis team. The data was held in large Excel spread sheets whose contents were shredded into a SQL Server database before being reconstituted and merged with formatting requirements by an Office Add-In that would then insert a table, chart into a users document.
A huge amount of code and database logic existed to support this along with numerous global deployment challenges.
By placing the source excel worksheets on a SharePoint document library the customer immediately had the ability to remove almost the entire custom solution as ranges, charts and tables could be retrieved through the REST API by an add-in, the ability to discover available Ranges, Tables and Charts through the provided metadata meant the Office Add-In could easily enumerate available content pieces before grabbing the content and inserting directly into the document.
You can even get away with not having to write an Add-In to insert HTML and image content (although it’s not the most user friendly approach) through the Insert->Quick Parts->Field dialog and using IncludePicture ensuring you place $format=image at the end of your REST url or IncludeText and $format=html for a Table/Range.
The beauty of this approach is that any output is rendered through the Word field feature meaning you can update Fields and therefore refresh the contents in the future ensuring that your document has up to date content moving forwards.
In this scenario, the worksheets had numerous macros (not required for data retrieval/processing) which Excel Services doesn’t support but it still happily exposes the contents meaning that you don’t have to artificially sanitise the spreadsheet before placing on the portal. Porting this functionality to user-defined-functions (UDF) would be the route forward if these were required.
Server Side Pricing
I touched on this at the beginning of the post, in this scenario the "pricing” functionality was wrapped up inside C++ DLLs which were then invoked by Excel which passed in information held within the Excel Spreadsheet including user controlled data (parameters effectively).
The desire was to expose this pricing functionality through different internal and external channels quickly and easily. We wrapped the C++ DLL within a managed code UDF wrapper and placed the spreadsheet and DLLs on the server.
You can then push parameters into the worksheet within a dedicated user-session using the REST API which then triggers a recalculation on the server and then retrieve the required output values (a price in this case) through the REST API.
Apart from the managed code UDF wrapper this required no custom code! We used the Excel Web Access Web Part to provide the “UI” and this was configured to show only a section of the worksheet as part of a broader web-page.
PowerPivot for SharePoint
This isn’t strictly an Excel Services thing but relates to a scenario where users were manually stitching together data sources into a workbook to then aid analysis.
If you haven’t already looked at PowerPivot (
http://www.microsoft.com/en-us/bi/powerpivot.aspx
) then I recommend you do. It enables an end user to import content from a variety of sources (Azure Data Marketplace, Flat Files, ODATA Feeds, Reports, Text Files and even the clipboard) and then as appropriate create relationships between the various entities and then analyse the data using Excel PivotTables and PivotCharts without having to formalise ETL processes and define a Warehouse.
PowerPivot for SharePoint can then take these workbooks and refresh the data on a schedule thus providing an up-to-date data source for your solutions through Excel Services.
Summary
As you’ve hopefully seen, Excel Services offers a lot of functionality which can simplify solutions by enabling you to take advantage of the power of Excel but embedding it as part of your application or process. End users maintain their freedom and you benefit from all of the tooling client and server-side.
I’m a big proponent of enabling end-users to use tools they are familiar with rather than the development of something new that often gets in the way of completing a task. Excel Services has been key to this in a number of projects along with extensions to Office itself to ensure all the information users need is brought together in one place rather than 10!
My next post will highlight a few examples of the REST API so you can get your head around how it might work for your solution.
Have fun!
but I have no desire to write another book so it’s about time I started blogging again!
