Display Pivot Chart And Table From Excel Document In SharePoint

In most of the projects we may need to present the reports / dashboards in graphic format for better understanding. If the version of SharePoint that you are using does not have Chart web part / Performance Point (BI Feature), then the below steps will help you to create reports/ dashboards using excel and REST API.

Step 1-

Create an excel workbook and in the worksheet fill the data for report.



Step 2-

Create pivot table and chart using the option available in the ribbon.



Select the range of data for generating the report as shown below



Then click ok.

Step 3-

Now, Pivot table and Chart template is created in the worksheet. In the right hand side the Pivot Chart Fields are displayed.




We need to drag and drop the fields in Filters, Legends, Axis and Values to create the chart.



The Chart will be automatically created as soon as we map the fields to axis and values.

We can change the color and theme of the chart using the ribbon shortcuts as shown below.




Step 4-

We need to provide a name to the Pivot Chart and table.






Step 5-

Upload the excel document to SharePoint Document library.



Now create a new or edit the existing SharePoint page and add page viewer web part (Approach 1).



Pivot Chart-

https://<companydomainserver>/Site/_vti_bin/ExcelRest.aspx/Shared%20Documents/ExcelChart.xlsx/Model/Charts('FY2015SalesReport')

Add another page viewer web part to add the pivot table.

Pivot Table-

https://<companydomainserver>/Site/_vti_bin/ExcelRest.aspx/Shared%20Documents/ExcelChart.xlsx/Model/Ranges('FY2015SalesData')

Note - If iframe or Content editor web part is not available then you can show the pivot chart as shown below. But in this scenario pivot table will not be allowed to display, as it needs iframe to display.

<img src=’https://<companydomainserver>/Site/_vti_bin/ExcelRest.aspx/Shared%20Documents/ExcelChart.xlsx/Model/Charts('FY2015SalesReport')’/>

Finally the Chart is displayed in the SharePoint page.



Or

If you have Excel Web Access webpart as shown below, then you can use the following steps-



Now we can configure the Excel Web Access webpart with following details.



Select the excel file (workbook) and also in the Named Item you can mention the Chart name or Range Names.



Here “FY2015SalesReport” is name of the Chart in the Excel file “ExcelChart.xlsx”.



Final output of the Excel Web Access webpart.