September 2010


In order to connect to an Excel file in a SharePoint 2010 and access or manipulate elements via web services, the file must not contain any unsupported features, in terms of Excel Services.

This can be demonstrated via a simple test program which interrogates SharePoint 2010 via a web service and returns all named ranges in an Excel file.

Where the file contains no unsupported features, the named range is returned:

This web service connection in SharePoint 2010 now functions even if there are macros in the file, and it is saved with an xlsm extension, unlike in SharePoint 2007.

 

However, once there is an unsupported feature in the file, in this case a Drop-Down List for data validation, the file can no longer be accessed via web services.

 

 

To download the test program, click here.

Advertisements

REST can be used to connect to named ranges, charts, tables and pivot tables as well as individually defined cell contents using a simple URI via Excel Services. In the following Excel file, held in a normal document library in SharePoint 2010, each element can be referenced.

A single cell – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Sheet1!b2’)

A Named Range – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Range1’)

A Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Table(‘Table1’)

A Chart – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Charts(‘Chart%202’)

A Pivot Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/PivotTables(‘PivotTable1’)

However, as soon as an Excel file contains elements which are not compatible with Excel Services such as data validation or password protection, then REST no longer functions for that file and no elements can be accessed.

If no element is specified, and atom feed is returned listing all named elements which can be accessed using REST.

server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model

Macro-enabled Excel files, with the extension .xlsm, though not compatible with Excel Services, may still be displayed and may still be accessible via REST.

Unsupported Features in Excel Services

 Information Rights Management (IRM) protection.

 ActiveX controls.

 Embedded smart tags.

 PivotTables report with multiple consolidation ranges.

 External references (workbooks that contain links to other workbooks).

 Workbooks saved in formula view.

 XML expansion packs.

 XML maps.

 Data validation.

 Query tables, SharePoint lists, Web queries, and text queries.

 Workbooks that reference add-ins.

 Workbooks that use the RTD() function.

 Workbooks that use workbook and worksheet protection.

 Embedded pictures or clip art.

 Cell and sheet background pictures.

 AutoShapes and WordArt.

 Ink annotations.

 Organization charts and diagrams.

 DDE links.

Features That Are Not Displayed

Workbooks that contain one or more of the following features will load in Excel Services, but the features will not be displayed. The features won’t be removed from the file, so the next time you open the file in Excel 2007, they will be displayed again.

Split and frozen panes

Headers and footers

Page layout view

Cell patterns

Zoom

Microsoft SQL Server Analysis Services’ member properties in ScreenTips

Some cell formatting, such as diagonal borders and border types not supported by HTML

Excel Services displays all charts that were created using Excel 2007. However, depending on how the charts are designed, some minor visual differences occur when some charts are displayed using Excel Web Access. For example, Excel Web Access cannot show new chart effects such as shadows, bevels, and reflections.

Features with Limited Interactive Support

Certain features that are interactive in Excel 2007 are not completely interactive in Excel Web Access.

PivotChart reports

PivotChart reports are not interactive when displayed in a browser. You cannot filter a PivotChart report directly, but if you interact with the PivotTable report that supplies data for the PivotChart report, the PivotChart report updates accordingly. This means if you filter the PivotTable report, the PivotChart report updates to show the filtered data set.

PivotTable reports

You can sort, filter, expand, and collapse data in PivotTable reports in a browser, but you cannot use SQL Server Analysis Services actions, add or remove fields, or rearrange fields.

Go To

In a browser, you can use the named range object view or the provided navigation controls to move around a workbook. However, there is no Go To feature.

Zoom, minimize, and maximize

Users cannot zoom, minimize, or maximize worksheets when viewing them in the browser.

Switching to page layout view

Page layout view is a new view in Excel 2007. You cannot switch to this view when viewing a workbook using Excel Web Access. Page layout view is designed to facilitate printing. If you want to print a workbook, it is best to use Excel 2007.

Goal Seek and Scenario Manager

You cannot use Goal Seek or Scenario Manager when viewing a workbook using Excel Web Access.

Formulas

You cannot audit formulas using trace precedents, trace dependents, show formulas, and so on.

Changing a workbook’s calculation mode

You cannot change a workbook calculation mode using Excel Web Access.

Watch window

You cannot use the Watch window when using Excel Web Access.