Display Charts in SharePoint 2010 using Excel Services and Chart Web Parts

Intra-organization portal users often need access to some form of Sales, Production or Revenue generation graphs or charts by directly querying Data Stores (persistent database like SQL Server) or even a spreadsheet like MS Excel. To achieve this, developers usually write a huge amount of complex code in Web Applications using concepts like ADO.NET or VSTO programming. If SharePoint 2010 is used for such scenarios, then such complex solutions like these  can be easily designed by developers or even the non-developer IT-staff in the organization, within no time.

SharePoint (SPS) 2010 is a competent technology for managing contents like documents, spreadsheets etc. In SPS2010, we have a set of services using which we can manage various operations needed for intra-organization automation like managing document library where Excel Workbooks, Word Documents, PDF’s etc can be stored.

One important feature of SPS 2010 is that if you have Excel worksheets in the document library, then using Excel Services, data from a specific range of the worksheet can be used to generate charts. The Excel service is a part of SPS 2010 and built upon ASP.NET and SharePoint Foundation.

In this article, we are going to discuss the mechanism of fetching data in SharePoint 2010 from Excel Spreadsheet using Excel Services.

Reading Data from Excel Workbook to Generate Charts using Excel Service

This feature is really exciting. We can simply consider a scenario where various marketing personals are maintaining worksheets to record the sales information in it. Once they submit their worksheet to the office, the IT personal generates a consolidated worksheet where state wise sales data is stored. Now this data is to be represented in a chart format without any hassles. To achieve this task, the Excel workbook must be stored in the Shared document library of the SharePoint 2010 portal as below:

The Sales data in the workbook is as shown below:

Step 1: Browse to the site, and click on ‘Edit’:

Step 2: You will see the ‘Editing Tools’ on the ribbon. Click on ‘Insert’ and select a ‘Web Part’ as shown here:

Step 3: Select ‘Business Data’ from Categories and ‘Chart Web Part’ from Web Parts and click on the ‘Add’ button:

You will see a default chart Web Part similar to the one shown below. To retrieve data for the chart, click on ‘Data & Appearance’:

You will be promoted to save, just click on ‘Ok’.

Step 4: The ‘Data Connection and Chart Wizard’ starts. Click on ‘Connect Chart to Data’

On clicking ‘Connect Chart to Data’, you will get a ‘Data Connection Wizard’ page with Data source options:

The above image shows various data source options. You can connect the chart to another web part, a List, to an External Content type (e.g. SQL Server, WCF etc.) and even Excel Services. In this case, we will be using Excel Services. As for this scenario, a typical use of Excel Service is as shown below:

The above diagram represents that Excel Workbooks are stored in SPS content database. The Application server which contains Excel Calculation service, loads the workbooks and performs calculations on it, if required. One of the major responsibilities of the excel calculation service is to maintain the session for the duration within which caller interacts with the Workbook. When caller closes the workbook, the session is closed. The Excel Web service is a part of Excel Service which provides access to Workbook to perform operations; in our case currently we are making use of Excel Service to generate Chart in the Chart Web Part by providing a Cell Range.

Step 5: Click on ‘Connect to Excel Service’ and click on Next. This step will ask the Excel Workbook path for an excel document which is already stored in the Shared Document. A sample URL is as follows:

hxxp://MySite.Organization.com/sites/MIS/Shared%20Documents/State_wise_Sales.xlsx

Here the information of the ‘Excel Workbook Path’ and ‘Range’ needs to be entered:

After clicking on ‘Next’, data will be retrieved from Excel sheet as shown below:

Click on ‘Next’ and the UI Display requirements can be set – like the X-field, Y-field etc

Clicking on Finish displays the chart

The appearance of the chart can be changed as per your requirements. To change the appearance of the chart, click on ‘Data & Appearance’ once again and you will get the ‘Data Connection & Chart Appearance Wizard’ page as shown below:

Click on ‘Customize Your Chart’. Here in the ‘Chart Customization Wizard’, you will get the ‘Chart Type Categories’ and ‘Chart Templates’. From the ‘Chart Type Categories’ select ‘Pie’ and select ‘template’ as per your requirements:

Click on Finish and a chart gets displayed

Conclusion:

By providing a deep integration with Excel Services and Chart Web Parts, it is easily possible to build statistical data representation in SharePoint 2010. Users who are interested in visual representation than plain figures, will find this feature very exciting and quick to implement. One more benefit here is that any member of your IT-staff can easily perform such operations without any additional developer skillsets.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn