Differences between using a workbook in Excel and Excel Services
Excel Services is primarily designed as a web-based, data-exploration and reporting system for Excel workbooks, and supports a subset of features in Microsoft Office Excel 2007. The following sections summarize which Office Excel 2007 features are supported and unsupported in Excel Services.
In this article
Supported and unsupported features when loading a workbook
You can load a workbook in Excel Services that is in Office Excel 2007 Workbook (.xlsx) or Binary Workbook (.xlsb) file format as a read-only workbook in three ways:
-
Specify a URL or UNC path in the the Excel Web Access Web Part Workbook property.
-
Connect a List View Web Part of a document library to an Excel Web Access Web Part, and then pass the URL of the workbook stored in the document library to display it in Microsoft Office Excel Web Access.
-
View a workbook saved in a document library in the browser. (Point to the item, click the arrow next to it, and then click View in Web Browser.)
All other Microsoft Office Excel file formats are unsupported, including Office Excel 2007 Macro-Enabled Workbook (.xlsm) and Office Excel 2007 97-2003 Workbook (.xls).
Note: With appropriate permission, you can also open a workbook in Office Excel 2007 on your client computer from the Open menu on the Office Excel Web Access toolbar, either as a workbook or as a snapshot. For more information, see Open a workbook or snapshot in Excel from Excel Services.
Supported features
The following features are supported when you load a workbook:
| Feature | Comments |
| Functions | All Excel worksheet functions are supported, with a few exceptions. For more information, see the following section, Supported and unsupported worksheet functions. |
| Dates | The Windows and Macintosh date systems. |
| Excel tables | Excel table data, column headers, calculated columns, total rows, structured references, and styles. |
| Cells | Cell values, including merged cells and cell content overflow. |
| Names | Defined names and named ranges. |
| Calculation | Calculation and recalculation settings, including automatic, automatic except tables, manual, and iterative calculation settings for ranges or entire worksheets. For more information, see Calculate and recalculate data in Excel Services. |
| Charts | Charts, chart ranges, and PivotChart reports. For more information, see Using charts and PivotChart reports in a workbook in the browser. |
| Formatting | Cell and cell range formatting, conditional formatting (except by using data bars and icons) in workbooks, and number formats. |
| Connections | Connections to external data sources, including OLAP PivotTables. |
| What-If analysis | The results of What-if analysis tools, including Goal Seek, Data Tables, Scenarios, Solver, and Series. |
| Consolidation | Consolidated data from ranges. |
| Data sources | SQL Server, OLAP providers, OLEDB providers, and ODBC drivers. |
Unsupported features
Workbooks that contain the following unsupported features will not load or display in Excel Services. For best results, always save a workbook from Office Excel 2007 by using the Excel Services command. (Click the Microsoft Office Button 
, click the arrow next to Publish, and then click Excel Services under Distribute the document to other people.) You can confirm whether a feature is supported by clicking the Open this workbook in my browser after I save check box in the Save for Excel Services dialog box to attempt to display it in the browser. If a feature is not supported, Excel Services displays an alert.
The following features are not supported and prevent you from loading a workbook:
| Feature | Comments |
| VBA | Visual Basic for Applications (VBA) code, macros, add-ins, and user-defined functions (UDFs). Note: A programmer can customize Excel Services in many ways, including the creation of a user-defined function (UDF). For more information, see the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK). |
| Legacy macro languages | Microsoft Excel 4.0 Macro Functions and Microsoft 5.0 dialog sheets. |
| Controls | Form toolbar controls, Toolbox controls, and all ActiveX controls. |
| XML | XML maps and embedded smart tags. |
| Security and privacy | Workbooks, worksheets, or ranges with protection, and workbooks that have Information Rights Management (IRM). Note: To protect workbooks in Excel Services, use Microsoft Windows SharePoint Services rights and permissions. |
| Images and objects | Linked or embedded objects or images, inserted pictures, AutoShapes, WordArt, and diagrams, such as organization charts. |
| Ink | All ink features including drawing, writing, and annotations. |
| OLE and DDE | Object Linking and Embedding (OLE) objects and Dynamic Data Exchange (DDE) links. |
| Displayed formulas | Workbooks saved with the formulas that are displayed. |
| Data validation | Preventing invalid data entry and creating drop-down lists. |
| Data sources | Data retrieval services for Microsoft Business Solutions, Windows SharePoint Services lists, Microsoft SQL Server, external data ranges (also called query tables), and tables linked to Windows SharePoint Services lists. |
| Queries | Web queries and text queries. |
| External references to linked workbooks | Creating external references (also called links) to a specific cell range, to a defined name for the specific cell range, or as part of a name definition. |
| Comments | Display of and adjustment of comments. |
| Consolidation | Consolidated data from PivotTable reports. |
| Share Workbook button (Review tab) | Sharing of workbooks and resolving conflicting changes. |
| Digital signatures | Visible and invisible digital signatures in a workbook. |
| Attached toolbars | Custom toolbars attached to the workbook by using Office Excel 2003 before the workbook was converted to Excel 2007. |
Supported and unsupported worksheet functions
All functions are fully supported when you load and recalculate a workbook with the following exceptions, HYPERLINK, RTD, and SQL.Request, each of which has limited support.
| If the Function is: | Then Excel returns: | And Excel Services returns: |
| HYPERLINK | An active hyperlink that you can click and follow. | One of the following:
|
| RTD | Real-time data from a program that supports COM automation. | The following:
If you want, you can use the ISERROR or IFERROR functions to test for the return value. |
| SQL.Request | The results of a query that is connected to an external data source. | The following:
If you want, you can use the ISERROR or IFERROR functions to test for the return value. |
| CHAR | A character specified by a number, and a block character for a nonprinting character. | A character specified by a number, and a blank value for a nonprinting character. |
| CELL | Information about the formatting, location, or contents of the upper-left cell in a reference. | Information about the formatting, location, or contents of the upper-left cell in a reference. Note: The following types of cell information are not supported and return a #VALUE! error: color, filename, format, parentheses, prefix, protect, and width. |
| INFO | The path of the current directory or folder on your client computer. | A #VALUE! error. |
The following volatile functions may return different values when they are calculated in Excel Services on a server computer than when they are calculated in Excel on a client computer.
| If the Function is: | Then Excel returns: | And Excel Services returns: |
| NOW | The date and time on your client computer. | The date and time on the server computer. |
| TODAY | The date on your client computer. | The date on the server computer. |
| RAND, RANDBETWEEN | A random and therefore different number each time it is run. | A random and therefore different number each time it is run. |
Supported and unsupported features when viewing a workbook
Viewing a workbook in Excel Services on a server computer is very similar to viewing a workbook in Excel on a client computer, but there are differences. Note that whether these features are supported or unsupported, they do not prevent the workbook from loading, and these features are preserved in the workbook so that they continue to work as expected in Excel.
Supported features
The following features are supported but may display differently on a server:
| Feature | Comments |
| Hyperlinks | Supported, but controlled by Office Excel Web Access properties in the following way:
|
| Fonts | Fonts are usually the same style and size on the server as they are on the client, but if a specific font is not available on the server, then a substitute font may be used. Also, a user can ignore font styles and sizes in a browser, such as Internet Explorer. |
| Charts and PivotChart reports | Charts and PivotChart reports are static images and refresh and redisplay if you interact (filtering, sorting, and so on) with the data that the chart is based on, or with the data in the associated PivotTable report. For more information, see Using charts and PivotChart reports in a workbook in the browser. |
| Line borders | The following line borders are fully supported: all line colors; continuous and double line styles; solid, diamond, and dashed lines; and thin, medium, and thick line weights. Note: The following line borders are partially supported: triple and double line styles; and solid, diamond, square dotted, dash-short-dash, long-short-dash, and dash-short-dash-short-dash lines. |
| Color gradient directions | Horizontal and vertical color gradient directions. |
| Cell fill color | All fill colors. |
| Cell alignment | All cell alignments are supported with the following exceptions: vertical justify and vertical distributed, which are both replaced by vertical center. |
| Text rotation | Text rotation is supported, along with cell and column header content overflow, horizontal or vertical left alignment for positive rotation (+) and horizontal or vertical right alignment for a negative rotation (-). |
| Bidirectional text | Fully supported when a set of characters from one language is displayed. |
| AS conditional formatting | Microsoft SQL Server Analysis Services (AS) conditional formatting is limited to the following: font color; fill color; font flags, such as bold, italics, underline, and strikethrough; and format strings, such as number formats. |
| Worksheet scrolling | The number of rows and columns that you can scroll on a worksheet is limited to a maximum size of 500. You use navigation buttons to display the next set of rows and columns beyond the current limit. For more information, see Navigate a workbook in Excel Services. |
Unsupported features
The following features are not supported:
| Feature | Comments |
| Hyperlinks in charts | Clicking and following hyperlinks in charts. |
| Tables | Replacement of worksheet column headers by Excel table headers when scrolling headers out of view in a scrolling region. |
| Cell fill patterns | All fill patterns. |
| Color gradient directions | Diagonal up, diagonal down, and corner-to-center color gradient directions. |
| Text rotation | The cell fill or pattern is not rotated with the text, diagonal borders display as though the text was not rotated, and horizontal or vertical alignment other than left alignment for positive rotation (+) and right alignment for a negative rotation (-). |
| Bidirectional text | Mixing right-to-left and left-to-right bidirectional text characters that have a different glyph (or character shape) orientation, such as ( (left parenthesis) and ) (right parenthesis), is not supported in vertical text. |
| Line borders | The following line borders are partially unsupported: triple and double line styles; and solid, diamond, square dotted, dash-short-dash, long-short-dash, and dash-short-dash-short-dash lines. |
| Charts | 2007 Office release 3-D graphic effects, such as shadow, glow, warp, bevel, soft edges, recolor, and reflection. These effects are either removed or converted to an alternative effect. The following 3-D charts are not supported:
Rich text in an object, such as bullets and varying fonts or font sizes, which is converted to plain text. Vertical text alignment. Note: Although embedded charts on worksheets and original charts on chart sheets are supported for display in Excel Services, an embedded chart that a workbook author has copied or moved from a worksheet to a chart sheet is not supported for display. |
| Row and column headers | The following formatting in row and column headers: double accounting, double underline, superscript, and subscript. |
| Nonprinting characters | Text with a 7-bit ASCII (a subset of the ANSI character set) value of 0 through 32, and any characters not supported by Extensible Markup Language (XML) version 1.0. |
| | Page layout and page headers and footers. |
| Tooltips | Tooltips of Microsoft SQL Server Analysis Services member properties. |
| XML | XML expansion packs. |
Supported and unsupported features when interacting with a workbook
When you load a workbook into Excel Services, you can interact with it in a number of ways, but there are some interactions that are not supported.
Note: Another way to interact that is unique to Excel Services is to create and change parameters, which temporarily changes cell values in the workbook either by using a Parameters Task Pane or by passing data to the Excel Web Access Web Part from a connected Web Part, such as a Filter Web Part. For more information, see Change workbook parameters in Excel Services.
Supported features
The following features are supported but may behave differently.
| Feature | Comments |
| PivotTable reports | Report filtering, member selection, expanding (drilling down) and collapsing (drilling up) levels of data, sorting, filtering, and showing and hiding subtotals. |
| Find | Finding text, numbers, and dates by a case-sensitive match, and finding by the partial contents of a cell. |
| Simple selection | Selecting a single cell, row, or column. |
| Filtering and sorting | Using the Filter menu, filtering by text, numbers, dates and times, specifying multiple criteria, and sorting. For more information, see Filter data in Excel Services and Sort data in Excel Services. |
| Outlining | Outlining, including showing and hiding details. For more information, see Outline data in Excel Services. |
| Scenarios | Executing a predefined scenario. |
| Refresh | Refreshing external data sources, including all data sources in the current workbook, a specific data source, periodic refresh, manual refresh, and refresh upon loading the workbook. For more information, see Refresh external data in Excel Services. |
| Calculation | Automatic and manual Calculation. For more information, see Calculate and recalculate data in Excel Services. Note: Although Excel Services supports loading a workbook that contains circular references, the detection of circular references when it loads or recalculates a workbook behaves differently. If Excel Services cannot resolve a circular reference, under certain circumstances it displays a warning message that there is a circular reference. The values that are calculated are the same as the values that you would get if you cancel the operation on the Excel client. In effect, Excel Services automatically cancels the circular reference to prevent the calculation from degrading the server performance. |
Unsupported features
The following features are not supported.
| Feature | Comments |
| PivotTable reports | The Detail Group and Show Detail options (also called drill-through) for OLAP data, member search, Microsoft SQL Server Analysis Services actions, or using the field list to add, rearrange, or remove fields. |
| PivotChart reports | Interacting directly with a PivotChart (because it is a static image). |
| Asynchronous evaluation of Cube functions | Asynchronous retrieval of data when a Cube function evaluates and the display of the #GETTINGDATA message before all data is retrieved. All data is retrieved synchronously before the view is displayed or redisplayed. |
| Find | Finding by the underlying data (as opposed to the formatted data and by a case-sensitive match). |
| Replace | Replacing text and values after using Find |
| Row and column resizing | Row and column width and height adjustments. |
| Advanced selection | Selecting an adjacent and nonadjacent cell range or the entire worksheet. |
| Calculation | Changing the calculation setting of the workbook once it is loaded. For more information, see Calculate and recalculate data in Excel Services. |
| Filtering and sorting | Sorting and filtering by color, and saving a filter or sort to the workbook in Excel Services. For more information, see Filter data in Excel Services and Sort data in Excel Services. |
| Go To | All Go To operations. |
| Managing panes | Split and Freeze panes. |
| Zoom | Adjusting the view of a worksheet by a percentage of the actual size. |
No comments:
Post a Comment