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.

ExcelServicesViewInBrowser

Clicking on that link, shows us the Excel document we are working with. 

ExcelServicesView

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.

ChartWebPartExcelServicesConfig2

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.

ChartWebPartExcelServicesConfig3

Click next, and you will choose the Axis and other settings for the chart.  Change any settings you want and then you are done.

ChartWebPartExcelServicesConfig4

Once you click finish you should be able to see your data using the default chart.

ChartWebPartExcelServicesComplete

Of course if you want another type of chart, you can click Data & Appearance and pick another type from the Customize your Chart link.

ChartWebPartExcelServicesComplete2

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.

Comments

# Twitter Trackbacks for How to: Use the Chart Web Part with Excel Services - Corey Roth - DotNetMafia.com - Tip of the Day [dotnetmafia.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 How to: Use the Chart Web Part with Excel Services - Corey Roth - DotNetMafia.com - Tip of the Day         [dotnetmafia.com]        on Topsy.com

# re: How to: Use the Chart Web Part with Excel Services

Friday, September 3, 2010 2:58 PM by Tom C

Corey,

Great Post -  Do you know of a way to leverage the Chart Web Part with summary data from a SharePoint List? I would like to graph data similar to this post,

www.endusersharepoint.com/.../finally-dynamic-charting-in-wss-no-code-required

but use the OOB Chart Web Part using web part connections.  

TIA - Tom

Looking for an easier solution for end users to create their own dynamic dashboards, without the need to modify or write script.

# re: How to: Use the Chart Web Part with Excel Services

Thursday, February 10, 2011 1:16 PM by Bob

What if the excel file is on a server and not using a URL? When I plug in the server name and path, I get Exception has been thrown by the target of an invocation error. What am I doing wrong? I believe the error is coming from the server path and not the sheet/range name...

# re: How to: Use the Chart Web Part with Excel Services

Thursday, February 10, 2011 1:30 PM by CoreyRoth

@Bob like on a file server?  I don't think it will work there.  Yes, you will get that error when there is an issue finding your spreadsheet.  It's too bad a more friendly error isn't given.

# re: How to: Use the Chart Web Part with Excel Services

Tuesday, April 19, 2011 5:19 PM by CoreyRoth

@arshad It almost always has something to do with the contents of the xlsx file.  Start with a simpler file and see if it works and then paste things back into it and see if you get the error.

# re: How to: Use the Chart Web Part with Excel Services

Wednesday, October 19, 2011 11:45 AM by Paul

So I keep getting the •Exception has been thrown by the target of an invocation. Any ideas on how to fix this?

# re: How to: Use the Chart Web Part with Excel Services

Monday, November 7, 2011 8:28 PM by CoreyRoth

@Paul try a different spreadsheet or try removing elements from the one you are trying until it works.

# re: How to: Use the Chart Web Part with Excel Services

Tuesday, January 10, 2012 1:38 PM by Eric

I can get everything to work fine except for the "First rown contains column names"  it pushes through just fine if i dont have it checked but it wont work when it is.  It gives me an weird foundation error that i cant look up anywhere.  Any Clues?

# re: How to: Use the Chart Web Part with Excel Services

Wednesday, February 8, 2012 4:32 AM by Liat

Hi, i cannot use excel file with formulas?  

i want to add a chart that will be updated from a list...

# re: How to: Use the Chart Web Part with Excel Services

Wednesday, June 13, 2012 12:07 AM by Josh

HI there,

Wondering if there's a way to have the chart webpart automatically refresh to data connections in the excel work book.

Great instructions btw, thanks!

# re: How to: Use the Chart Web Part with Excel Services

Thursday, August 2, 2012 7:56 PM by Chris D

Paul--what'd you find?  I keep getting the same error!  I don't think it's correlated to the data workbook.  The same error is thrown even without populating the workbook (data source) url.

# re: How to: Use the Chart Web Part with Excel Services

Wednesday, January 30, 2013 8:57 AM by Jignesh

I have chart web part i want to convert that chart into excel.

is it possible? if yes then how can i do that?

# re: How to: Use the Chart Web Part with Excel Services

Thursday, January 31, 2013 12:44 PM by CoreyRoth

@Jignesh Not with the chart web part I am afraid.

# re: How to: Use the Chart Web Part with Excel Services

Thursday, April 4, 2013 10:55 AM by sunilks

Hi,

Iam getting the Exception has been thrown by the target of an invocation error.

Iam nt using any fancy data, i am jst using the simple file like ur budget 2010. But iam still getting the error.

# re: How to: Use the Chart Web Part with Excel Services

Thursday, June 6, 2013 12:33 PM by JWLehman

I get the same “Exception has been thrown by the target of invocation”.  What is it that is unsupported?  I created a simple worksheet with 3 columns, nothing fancy.  Two months since last comment and nobody offered any further advice :(

# re: How to: Use the Chart Web Part with Excel Services

Friday, December 20, 2013 12:30 PM by Jome

JWLehman, I got the same message

# re: How to: Use the Chart Web Part with Excel Services

Thursday, December 26, 2013 3:39 PM by Helped

Thanks for the help Corey!  This saved me from more insanity. To anyone still struggling with the invocation error, in my case I had to re-create the data on a new sheet (Sheet2) inside the workbook on the SharePoint server with absolutely no formatting (the original had colors in the cells).  The new data is linked to the original Sheet1, as I couldn't change the formatting on that sheet (others needed the color-coding).  It should still auto-update whenever someone edits the data on Sheet1 in the workbook.

And then I followed Corey's example of removing the xlviewer lines from the url (or checking to make sure they are gone).  

Another way to get a true URL is If you open the spreadsheet actually in Excel, then add the Document Location to your Quick Access toolbar (at the top).  Once you add that, you can copy/paste the URL from that box into the Web Part setup.  it should be devoid of the whole xlviewer nonsense in the URL.

At any rate, I honestly hope that anyone who encounters the invocation error can figure it out.  I was on the edge of going crazy.  Thanks again Corey!

# re: How to: Use the Chart Web Part with Excel Services

Monday, April 6, 2015 1:22 PM by Emilio

This may be a little late but it helped me with the "Exception has been thrown.." error. Make sure your excel file is at the root of your document library and not in a subfolder within it. It worked for me hope it does for you.

# re: How to: Use the Chart Web Part with Excel Services

Monday, December 14, 2015 12:29 AM by samolpp

Hi Corey,

Am getting getting error  after entering the   url of the workbook for the excel services url in the text box proivded.

I dont have access to central admin, and having access  till site collec.admin only.

unfortunately , i cant see uls logs with the correlation id.

is there any other things, i need to mention in the central admin's  excel services section/page?

Please help

Leave a Comment

(required)
(required)
(optional)
(required)