Excel on the Server?
Yes! There is such a thing and it's an actual, server-grade, supported product! It's part of Microsoft Office SharePoint Server 2007 and it's the published version of an Excel spreadsheet which gives you the ability to share business intelligence around the enterprise in a safe, compliant way.
Why Did We Create Excel Services?
Multiple Versions of the Truth
- E-mailing your spreadsheets around quickly gets out of control: one person changes some data and forwards it on to someone else, this becomes a different copy, another person does the same
- individuals see everything that is in the spreadsheet - external data connections, all sheets, tables, pivot charts
Incorporating Excel Models into Applications often requires re-coding
- Algorithms get prototyped in Excel (the "programming language of the business world"), then re-coded in real code
- Duplication of work, the developer needs to understand the business model
- When the analyst changes the model, the developer needs to go back to the code and maintain it
Excel is a good standalone tool but does not play in the BI arena
- It needs to have a thin client so that it can act as a dashboard so that it can display live results from multiple applications
Architecture of Excel Services
Excel Web Services are offered so that we can solve problem #2 stated above. In that scenario, the developer no longer needs to know anything about the business model in the spreadsheet. All they need to know is the named range so that they can access it using the web service method. If the model changes, no update to the code is needed. This helps companies immensely in terms of protecting their data.
On the back end we have Excel Calculation Services which is the main engine of Excel Services, the part which opens and calculates the spreadsheets. We've also created User Defined Functions or UDFs which are used to extend the calculation and data import capabilities of Excel. You can create UDFs to:
- Call custom mathematical functions
- Call Web services
- Get data from unsupported sources
Excel Services still doesn't support certain scenarios such as bringing in data from SAP or Access. You need to create UDFs to bring that data up into Excel Services. The UDF code is deployed on the application server.
What kinds of Office Business Applications can you build with Excel Services?
Rob Barker did a cool demo once which took him only 7 minutes but was an Office Business Application involving Excel Services, ribbon customization, and a custom task pane. Just like I described in some of my previous posts, you can create a custom ribbon from which you can have a button which launches a custom task pane, also created in VSTO SE. In another post I wrote about how you can hook these up so that clicking on a button in the ribbon launches a custom task pane. The custom task pane is nothing more than a user control so you can throw anything you want on there. Imagine that you have some LOB data, such as some CRM data, connected to a spreadsheet which you've published up to Excel Services. The whole power of OBAs is to bring complex LOB applications right to the fingertips of end users who are not comfortable using LOB applications such as SAP or Siebel. So in the scenario we are describing here, imagine that you are always filling out documents with vendor information, PO numbers, budget amounts, etc. Your task pane could have a drop down which is populated with all the named ranges in the spreadsheet. Below that, you would have a list box so that selecting an item in the drop down would populate the list box. For example, let's say you selected "Q4_vendors" in the drop down. The Excel services web service methods would first open the workbook, return a session ID, calculates and refreshes the workbook, retrieves all the values in the named range and places it in the list box. The end user can then just choose to insert into the CRM results into their Word document seamlessly; they never have to leave the application that they are comfortable.