Friday, April 9, 2010

Shared Service Provider – Excel Services

Excel Services is a SSP that ships with MOSS, which allows end users to work with Excel spreadsheets without having MS Excel installed on their local computers. This SSP also has a calculation engine that supports calculating spreadsheets on the server in order to offload resource-intensive calculations from client machines. Note that, Excel Services only allows sharing spreadsheets but do not allow creating them. To configure Excel Services follow these steps

  1. Administrative Tools -> SharePoint 3.0 Central Administration
  2. Application Management -> Create or Configure This Farm's Shared Services
  3. Open Shared Services Administration site

NOTE: For spreadsheets to be processed by Excel Services, they need to be located in so called "Trusted file location". Trusted file location is nothing but web sites, file folders or SharePoint sites which need to setup as trusted file locations.

Excel Services can be divided into the following three major components

  1. Excel Web Access Web Part
    1. Used to display a spreadsheet as HTML within the browser.
  2. Excel Calculation Services
    1. Server-side Calculation, caching and session management for spreadsheets.
  3. Excel Web Services
    1. Provide programmatic interface to Excel Services
    2. Available at /_vti_bin/excelservice.asmx

Features provided by Excel Services are

  • Using Parameters
    • Input can be accepted by the user within the spreadsheet when they open. This is done through "Save As" dialog -> Excel Services Options button in Microsoft Excel just before publishing the document into trusted file location
  • Using Data Connections
    • It is quite common to use data from external systems in Excel. SharePoint has a library called "Data Connection Library", which holds Office Data Connection (ODC) files that define connections to external data sources. These files are created in Excel and reused across spreadsheets.
  • Using Web Services
    • Calculation engine provided on the server-side, can be consumed by any client application (Console application for example) using web services located in /_vti_bin/excelservice.asmx. This will access to methods like OpenWorkBook() etc which can be called by the client applications and off-load the calculation burden.
  • Creating User-Defined functions
    • Similar to the built-in functions in Excel available to workbooks, custom functions can be created in an assembly and deployed to GAC. Only limitation is that such functions are only available to workbooks that are published through Excel Services.l
    • The class we need to create should be decorated with UdfClass() attribute and methods should have UdfMethod() as attributes. These attributes are defined in Microsoft.Office.Excel.Server.Udf namespace.


No comments:

Post a Comment