1

Posted a question yesterday and got a great suggestion to use Excel REST. I've looked at the documentation but had a basic question about how to apply it.

I've got 3 workbooks in a document library on a 3rd party hosted Sharepoint Enterprise system (Sheet1.xlsx, Sheet2.xlsx, and Sheet3.xlsx). Each workbook has a Range of cells and a Chart that contain company performance data. I want to create a page that displays the 3 Ranges and 3 Charts. The page ("dashboard") will be for viewing only - i.e. the users who access the page do not need to interact with the data other than to view it.

Each workbook uses Data Validation and Sheet Protection, so using standard Excel Web Access will not work.

According to the documentation on Excel REST, the URL's I needto create are:

http:///_vti_bin/ExcelRest.aspx//Sheet1.xlsx/model/Charts('SampleChart1') http:///_vti_bin/ExcelRest.aspx//Sheet2.xlsx/model/Charts('SampleChart2') http:///_vti_bin/ExcelRest.aspx//Sheet3.xlsx/model/Charts('SampleChart3') http:///_vti_bin/ExcelRest.aspx//Sheet1.xlsx/model/Ranges('Rangename1') http:///_vti_bin/ExcelRest.aspx//Sheet2.xlsx/model/Ranges('Rangename2') http:///_vti_bin/ExcelRest.aspx//Sheet3.xlsx/model/Ranges('Rangename3')

How do I create a page in which these URL's are embedded and linked to the Workbooks stored in the Document Library? This may sound very basic, but I can't seem to figure it out.

2

Firstly make sure your URLs are correct - you need your site and library in the URL.

http://sitename/_vti_bin/ExcelRest.aspx/libraryname/Sheet1.xlsx/model/Charts('SampleChart1')

Charts will be returned as images - so you can add them to a page in the same way you would add any image in HTML:

<img src="http://sitename/_vti_bin/ExcelRest.aspx/libraryname/Sheet1.xlsx/model/Charts('SampleChart1')" />

You can even add these using Insert Picture from the ribbon.

I usually adjust my charts to include their datatable if it is a small set - this way it all comes back as part of the image. However, if you need the data ranges separately then they will come back as HTML.

  • Dave, thanks a lot for the reply. Re: URL, I did use the correct URL structure, but for some reason, they were not displayed properly when posted. – andrew Nov 5 '13 at 21:32
  • Can I ask one basic question: how can I add HTML code directly into a Sharepoint document (either via Web Part or some other means)? I have not had much luck using the pre-built tools like InsertPicture. Many thanks. – andrew Nov 5 '13 at 21:34
  • Also, once I figure out how to add HTML code into a sharepoint document, presumably I can use the properties of the img tag such as "height=" and "width=" to size the chart appropriately. Are there similar parameters that I could give to a URL with a Range so that I could also re-size the cells that are displayed? In SP 2007, there was a "RowsToDisplay" function in Excel Web Parts (goo.gl/2yqh9x), but I don't see it in SP 2010. Thanks. – andrew Nov 5 '13 at 22:32
  • If you want to change HTML like this then you probably need to be editing the page in SharePoint Designer. You can use CSS to configure the image sizes or cells sizes. – Dave Paylor Nov 6 '13 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.