Partager via

Excel Services REST limitations

  • Article

We just went through two or three weeks worth of posts and I tried to show you some of the new stuff we can do with Excel Services. It’s cool, it’s exciting, it’s great. But here comes the caveat post!!

There are a number of things that do not currently work in REST – the following is a partial list, but it contains the more painful missing parts:

1. No floating charts – If a range contains a chart on it and you request the range via REST, you will get just the range – where the chart is, you will actually see the stuff that’s under it.

2. No sparklines, no icon conditional formatting – due to resource constraints, we do not do either of these. We know it’s a shame, and we want it to work too, but it’s just going to have to be one of those things we add later on.

3. Not pixel-perfect with EWA – even though the HTML that REST produces is very close to the HTML the EWA produces, it’s not perfect. The fact that we do not have access to all the CSS elements that EWA has is part of it (remember, we return an HTML snippet – it needs to be self contained as much as possible).

4. No distinction in tables (when requested as ATOM) about whether or not something is a column head or a total.

5. URL size is limited to around 2000 characters. That means that if you have a large number of parameters, you may not be able to set all of them (especially if the workbook itself is pretty deep in folders).

There are probably a number of other limitations and caveats – I will add them here as you guys complain about them.

Comments

  • Anonymous
    November 25, 2010
    Hi Shahar, I tried a rather complex scenario for a customer: I have a SQL Server 2008 R2 database that contains values for each importdate. The data is imported through SSIS packages. Now I want to show the data in a excel services chart that is placed in an excel report (Excel 2010). The clou is that the data in the chart should be filtered by a parameter that comes with the URL in the REST API call. The chart in the report is connected to a Pivot table, that contains the data that is showed and a Pivot table filter should be set by the parameter in the URL. Correct me if I am wrong, but it seems that it is not possible to recieve parameter data to a range/cell that is placed in a Pivot table. That's a big lack in my opinion, because dynamic reports through REST and Excel services are not possible. Do you know any workarount or trick to solve this? Thanks for your help/comments. Regards from Conny

  • Anonymous
    October 01, 2015
    You may think that the lack of a "pixel-perfect representations in the EWA" is a minor thing, but in fact it has proved to be much more disconcerting that first anticipated, and applies to more than just the EWA.   My client does not want to use the Excel Web Access web part in their SharePoint online site (mostly because they don't like the way it behaves on the iPad), and has asked me instead to use the Excel REST API to bring back the data for display on the web page.   But using ?$format=image at the end of the REST API call does not obey the formatting of the cells (for example, if you merge and center several cells and also apply word wrapping, this is ignored and in the image representation the resulting text is cropped and therefore not readable).   If you instead use ?$format=html, you don't get the charts that are contained within the named range (despite the fact that they are bound to a cell and not floating).  So I am stumped as to how to get ALL the information that is contained in the spreadsheet to be returned via a REST API call.   Any help would be most appreciated.