Post
Ask Question

Showing Excel Charts And Tables In SPFx Webparts Using Excel Services REST API

Introduction

 
While PowerBI provides more advanced and sophisticated reporting and dashboards capabilities, there are users who still prefer to use standard Microsoft Excel worksheets to quickly create simple reports and charts. There are also some organizations who want to read the Excel data and show some reports or charts in their Modern intranets, while they are moving to PowerBI. So, this article explains how we can use the Excel REST API to read different resources from Excel within a SharePoint Framework web part and show the charts.
 

Excel Chart and Cell Data in SPFx web part

 
Below is a sample screenshot of a custom SPFx web part which pulls the data from an Excel sheet.
 
 
The Line Chart shows the monthly stock prices of different companies, which is a standard chart in the Excel. And the Last Updated Date is read from a specific cell within the Excel sheet.
 

Excel Services REST API

 
Excel REST APIs are powerful resources which enable a custom application to interact with a excel workbook and access the data models. Following is the URI structure for accessing an excel document
  1. http://<sitecollection-url>/_vti_bin/ExcelRest.aspx/<DocumentLibrary>/<FileName>/<ResourceLocation> 
For example, if you would like to access an Excel with name 'DataSource.xlsx' in the Shared Documents folder, the base URI should look like below.
 
https://tenant-name.sharepoint.com/sites/Internal/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx
 
Resources URI
 
Using the REST API, we can read the following resources from the Excel
 
Ranges: BaseURI/model/Ranges('range-name')
 
Reading data from a specific cell,
  1. https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/Ranges('StockData!E1')?$format=json  
  2.   
  3. // JSON OUTPUT  
  4. {"name":"StockData!E1","rows":[[{"v":43726,"fv":"18-Sep-19"}]]} 
For the resource name, it is specified 'StockData!E1' which means we are reading the data from cell E1 in the sheet named StockData.
 
Reading data from a named range
  1. https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/Ranges('SampleData')?$format=json  
  2.   
  3. // JSON OUTPUT
  4. {"name":"SampleData","rows":[[{"v":77,"fv":"77"}],[{"v":5,"fv":"5"}],[{"v":6,"fv":"6"}],[{"v":7,"fv":"7"}],[{"v":8,"fv":"8"}],[{"v":9,"fv":"9"}]]}  
Charts: BaseURI/model/Charts('[Name]') 
 
This response to this endpoint will be an image in PNG format.
  1. https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/charts('StockChart')
Tables: BaseURI/model/Tables('[Name]')
  1. https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/Tables('StockDataTable')  
PivotTables: BaseURI/model/PivotTables('[Name]')
 

Reading the Excel Data in SPFx webpart

 
A standard REST API call from an SPFx web part to these endpoints will easily fetch the data into our SPFx web part state. All the REST Endpoints can return data in JSON format by setting the $format=json parameter.
 
Below is the sample SPFx web part code which reads the cell data for showing the Last Updated date
  1. export interface IExcelChartViewerState  
  2. {  
  3.   lastUpdated: string;  
  4. }  
  5.   
  6. export default class ExcelChartViewer extends React.Component<IExcelChartViewerProps, IExcelChartViewerState> {  
  7.   public constructor(props:IExcelChartViewerProps, state:IExcelChartViewerState){  
  8.     super(props);  
  9.     this.state = {  
  10.       lastUpdated: ""  
  11.     }  
  12.   }  
  13.   public render(): React.ReactElement<IExcelChartViewerProps> {  
  14.     return (  
  15.       <div className={ styles.excelChartViewer }>  
  16.         <div className={ styles.container }>  
  17.           <div className={ styles.row }>  
  18.             <div className={ styles.column }>  
  19.               <p className={ styles.subTitle }>Last Updated : {this.state.lastUpdated}</p>  
  20.               <img src="https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/charts('Chart%202')" />  
  21.             </div>  
  22.           </div>  
  23.         </div>  
  24.       </div>  
  25.     );  
  26.   }  
  27.   
  28.   public componentDidMount(){  
  29.     fetch("https://tenant-name.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/DataSource.xlsx/model/Ranges('StockData!E1')?$format=json")  
  30.     .then(res => res.json())  
  31.     .then((data) => {  
  32.       this.setState({  
  33.         lastUpdated:data.rows[0][0].fv  
  34.       });  
  35.     });  
  36.   } 
As you can see the response returns an object with two parameters - v (raw value) and fv (formatted value). We can use the formatted value to read the date information from the cell.
 
And for showing the chart we can directly assign the REST URI to an img tag which shows the chart (as shown in line 20 in the sample snippet). If we just want to show a chart from Excel, it's basically a single line of SPFx code which displays the chart on a modern page
 

Conclusion

 
By leveraging the power of Excel REST APIs, we can easily show charts within an SPFx web part on modern SharePoint page. And the charts are updated as soon as a user updates the chart in the source excel workbook. Using this approach we can easily show dashboards and excel reports, while users can easily update the data models in the Excel sheets.


Similar Articles