Updated Excel Services Web Services API in SharePoint 2010

Thanks to Christian Stich, the founder of Excel Services Consulting, L.L.C., for putting together this post on the updated Excel Web Services API.

The Excel Web Services API that initially shipped with Excel Services 2007 has been updated and expanded for Excel Services in SharePoint 2010. I will address the additions and changes for the Excel Web Services API for SharePoint 2010 in this post. Additional information about the Web Services API covering the 2007 implementation can be found in the earlier posts Excel Services part 6: Building applications with Excel Web Services and Excel Services part 7: Sample application with Excel Web Services.

Updated Web Services Methods

The following methods used to set values now allow setting formulas into the workbook - in the 2007 implementation setting a formula resulted in an error.

• SetCell
• SetCellA1
• SetRange
• SetRangeA1

Setting formulas requires open or edit permissions and is supported for view and edit sessions. If the user (under whose credentials the program running the Web Services code is running) is limited to view item permissions, then setting formulas fails as it did in the 2007 implementation. Setting a formula or a value does not affect the formatting of a cell.

New Web Services Methods

Session/Workbook Handling methods:

• SetCalculationOptions
  This method can be used to override the calculation behavior specified in the workbook and/or the trusted location. It can, for example, be used to disable automatic recalculation prior to setting values or formulas into multiple individual cells using the SetCell/SetCellA1 methods. This avoids time consuming recalculations between each value/formula being set.
  
• OpenWorkbookForEditing
  This method is used to open a new or to join an existing edit session and it returns the session identifier for that session. It is similar to the OpenWorkbook method that is used to open a view session in the 2007 and 2010 Web Services API.
  
• SaveWorkbook
  This method is only available with edit sessions. It forces a save to the original workbook file from which the session was opened.
  
• SaveWorkbookCopy
  This method is available with view and edit sessions. It is similar to the GetWorkbook method that was introduced with the 2007 Web Services API as it saves the current session. However, the program using the Web Services API no longer has to implement its own save to SharePoint functionality - SaveWorkbookCopy takes care of that. Please note that workbook underlying the open session remains the originally opened workbook, not the copy created using SaveWorkbookCopy. For example, if additional changes are made in an edit session, then the original workbook will be updated with those changes and not the copy of the workbook. This behavior differs from Excel client's SaveAs behavior where the underlying workbook is the last saved copy.

Output methods:

• GetChartImageUrl
  This method provides easy access to charts in a workbook. For example, a chart that is updated when values are set into a session can be accessed at the URL (Uniform Resource Locator) that is returned by this method. Moreover, this method provides for different sizes of the image (in PNG format) - the resizing is done prior to the chart's generation and thus results in a high quality chart of the specified size instead of using a pixel interpolation approach which would result in a reduced quality image.

Input methods:

• SetParameters
  The SetParameters method can be used to set parameters including PivotTable filters that are parameters. The SetCell/SetRange methods that already existed in the 2007 Web Services API could be used to set 'regular cell' parameters - however, they could not be used to change filters, nor could they be used to set multiple parameters at the same time.

Workbook Structure:

• GetPublishedItemNames
  This method returns the names and types (Named Ranges, Charts, Tables, and PivotTables) of all published items in the workbook.
  
• GetSheetNames
  This method returns the names, types (Worksheets and Chartsheets) and visibility of all published sheets in the workbook.

Additional resources

Shahar Prish, one of the developers on the Excel Services team, has been covering the 2007 and 2010 implementations of the Web Services API on his blog.  In future posts, I hope to provide more code samples of using the Web Services API.