How to: Use the Chart Web Part with Excel Services
Posted
Thursday, September 2, 2010 3:04 PM
by
CoreyRoth
Hopefully, you were able to impress the boss with my last post on the Chart Web Part. I explained how to use the BCS with the Chart Web Part to display great looking charts using external data. Another option is to use Excel Services. Excel Services is pretty easy to setup now and you usually don’t have to do much configuration out of the box. Let’s see what we can do. This assumes you have Excel Services installed and configured to trust spreadsheets from your SharePoint server.
First, let’s start with my super awesome spreadsheet. It's a simple example, but hopefully you get the idea. If you have Excel Services working, you should have the View in Browser option when you are looking at a file in a document library. Viewing Office documents in the browser might even be your default if you have installed Office Web Apps.
Clicking on that link, shows us the Excel document we are working with.
SharePoint 2010 has much better support for a wide variety of Excel documents. For the most part it will render most documents even if they have unsupported features in them. This post has some details on what is supported and what is not.
To use Excel Services with the Chart Web Part, we need the URL to the Excel document. You can get this in a number of ways. Just be aware if you try to copy a link from the document library it might give you a link to the xlviewer.aspx which will not work in the Chart Web Part. Make sure you get a link that references the .xslx file directly from your SharePoint server.
For example, this link will not work in the Chart Web Part:
http://sp2010/ECM/_layouts/xlviewer.aspx?id=/ECM/Company%20Documents/2010%20Budget.xlsx&Source=http%3A%2F%2Fsp2010%2FECM%2FCompany%2520Documents%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1
This link will work in the Chart Web Part:
http://sp2010/ECM/Company%20Documents/2010%20Budget.xlsx
Once you have the link to your spreadsheet, edit a page and add a Chart Web Part to it. If you don’t remember how, you can find the details on it on my previous Chart Web Part post. Now, click the Data & Appearance link and then Connect Chart to Data. Now choose Excel Services. You will then be presented with a screen prompting you for the Excel Web Service URL, Excel Workbook Path, and Range Name. The Excel Web Service URL should already be filled out for you. It will look something like this.
http://sp2010/_vti_bin/excelservice.asmx
Now you need to provide a path to the workbook. Use a complete URL like the one I used above. The last thing you need to provide is the range of the spreadsheet you want to use. Here you need to know some Excel basics. You start by specifying the sheet name followed by the first cell using the !, $, and : delimiters. In my case I want A1 through B5 on Sheet1, it looks like this:
Sheet1!$A$1:$B$5
If the first row of your spreadsheet has column names, check the box. Here is what it looks like completed.
Click Next and if Excel Services like your spreadsheet, it should give you a preview of the data. If it doesn’t like it, it might give you a user friendly error or it might give you a Exception has been thrown by the target of an invocation error. If that is the case, there is likely an unsupported feature such as a chart that you need to remove for the spreadsheet to work. Here is what the preview looks like.
Click next, and you will choose the Axis and other settings for the chart. Change any settings you want and then you are done.
Once you click finish you should be able to see your data using the default chart.
Of course if you want another type of chart, you can click Data & Appearance and pick another type from the Customize your Chart link.
One thing to note is that Excel Services does cache data for a while so if you change data in the spreadsheet, it might take a few minutes for the graph to reflect your changes. This was a simple example, but as you can see it’s not very complicated to get a nice looking chart up and running quickly. If you do run into issues, I recommend tearing your spreadsheet apart and look for unsupported features.