in

Dot Net Mafia

Group site for developer blogs dealing with (usually) .NET, SharePoint 2013, SharePoint 2010, Office 365, SharePoint Online, and other Microsoft products, as well as some discussion of general programming related concepts.

This Blog

Syndication

Archives

Corey Roth [MVP]

A SharePoint MVP bringing you the latest time saving tips for SharePoint 2013, Office 365 / SharePoint Online and Visual Studio 2013.

How to: Use the Chart Web Part with Excel Services

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 said:

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

September 2, 2010 5:56 PM
 

Tom C said:

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.

September 3, 2010 2:58 PM
 

Bob said:

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...

February 10, 2011 1:16 PM
 

CoreyRoth said:

@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.

February 10, 2011 1:30 PM
 

CoreyRoth said:

@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.

April 19, 2011 5:19 PM
 

Paul said:

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

October 19, 2011 11:45 AM
 

CoreyRoth said:

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

November 7, 2011 8:28 PM
 

Eric said:

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?

January 10, 2012 1:38 PM
 

Liat said:

Hi, i cannot use excel file with formulas?  

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

February 8, 2012 4:32 AM
 

Josh said:

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!

June 13, 2012 12:07 AM
 

Chris D said:

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.

August 2, 2012 7:56 PM
 

Jignesh said:

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

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

January 30, 2013 8:57 AM
 

CoreyRoth said:

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

January 31, 2013 12:44 PM
 

sunilks said:

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.

April 4, 2013 10:55 AM
 

JWLehman said:

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 :(

June 6, 2013 12:33 PM
 

Jome said:

JWLehman, I got the same message

December 20, 2013 12:30 PM
 

Helped said:

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!

December 26, 2013 3:39 PM

Leave a Comment

(required)  
(optional)
(required)  
Add

About CoreyRoth

Corey Roth is an independent SharePoint consultant specializing in ECM, Apps, and Search.
2012 dotnetmafia.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems