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 Services is all built on Office SharePoint Server 2007. In fact, the spreadsheet just resides in a document library. There are two components to its Front End, the Excel Web Access which is the UI and the Excel Web Services which is the Programmatic Access to the Excel Calculation Services, or the engine, of Excel Services. The Excel Web Access is just a Web Part in SharePoint which displays your spreadsheet (only the parts you decide to publish, attacking problem #1 stated above) and since it's strictly HTML and JavaScript, end users do not need to install any activeX component. There is complete fidelity in the calculation and you can get lost switching between the client and the server, that's how exact the conditional formats and pivotTables look. Nevertheless, end users do not need to install the client and that's why it's zero footprint.

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.

Comments

• Anonymous
  May 24, 2007
  PingBack from http://stevepietrekweblog.wordpress.com/2007/04/03/links-432007/

• Anonymous
  April 12, 2008
  Hi Can anyone tell me if there is a huge benefit to upgrade to excel 2007. I am an Excel consultant and so far it seems to me that 90% of businesses are still using 2003, and they seem very happy with it. Ive read quite a bit about Excel 2007 but it seems to me that most people are happy with what they have.... whats the point in changing something that already works? Apppreciate any comments back .. thanks Lee

• Anonymous
  April 17, 2008
  The comment has been removed