Abstract: In this article, we are going to discuss the mechanism of fetching data in SharePoint 2010 from an Excel Spreadsheet using Excel Services and represent that data in the form of a Chart
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.
No comments:
Post a Comment