Export SharePoint List Items In Excel (SPFX Using React, FileSaver.js, xlsx.js)

This article will help you to create SPFX webpart which exports SharePoint list data in Excel files (xlsx file) and download to your local machine. I am using PnP library for consuming SharePoint operations, FileSaver.js for saving files on the client-side and xlsx library for spreadsheet formats.
 
Open Node.js Command prompt and run the below commands , 
 
  • To create a new project directory 

    md ExportToExcel

  • To go to the project directory

    cd ExportToExcel

  • To create a new web part

    yo @microsoft/sharepoint

  • When prompted,
    1. What is your solution name? ExportToExcel
    2. Which baseline packages do you want to target for your component (s)? SharePoint Online only (latest)
    3. Where do you want to place the files? Use the current folder
    4. Do you want to allow the tenant admin the choice of being able to deploy the solution to all sites immediately without running any feature deployment or adding apps in sites? N
    5. Will the components in the solution require permissions to access web APIs that are unique and not shared with other components in the tenant? N
    6. Which type of client-side component to create? WebPart
    7. What is your Web part name? ExportToExcel
    8. What is your Web part description? ExportToExcel description
    9. Which framework would you like to use? React
At this point, Yeoman creates the project scaffolding (folders & files) and installs the required dependencies by running npm install. This usually takes 1-3 minutes depending on your internet connection.
 
In the command prompt, type the below command to open the solution in the code editor of your choice.
 
NPM Packages used,
  • npm install @pnp/sp@2.0.3 --save //for SharePoint operations
  • npm i xlsx //To parser and writer for spreadsheet formats (.xlsx file)
  • npm i file-saver //For saving files on the client-side
Necessary imports
 
In ExportToExcelWebPart.ts file
  1. protected onInit(): Promise<void> {    
  2.     
  3.     return super.onInit().then(_ => {    
  4.       
  5.       // other init code may be present    
  6.       
  7.      sp.setup({    
  8.     // set ie 11 mode    
  9.     ie11: true,    
  10.     // only needed when working within SharePoint Framework    
  11.     spfxContext: this.context    
  12.   });    
  13.     });    
  14.   }    
in ExportToExcel.tsx file
  1. import { sp } from "@pnp/sp";  
  2. import "@pnp/sp/webs";    
  3. import "@pnp/sp/lists";    
  4. import "@pnp/sp/items";   
  5. import * as XLSX from 'xlsx';  
  6. import {saveAs}  from 'file-saver';  
Full Code
 
in ExportToExcel.tsx
  1. import * as React from 'react';  
  2. import styles from './ExportToExcel.module.scss';  
  3. import { IExportToExcelProps } from './IExportToExcelProps';  
  4. import { escape } from '@microsoft/sp-lodash-subset';  
  5. import { sp } from "@pnp/sp";  
  6. import "@pnp/sp/webs";    
  7. import "@pnp/sp/lists";    
  8. import "@pnp/sp/items";   
  9. import * as XLSX from 'xlsx';  
  10. import {saveAs}  from 'file-saver';  
  11.   
  12. export interface MYListProperties {      
  13.   Title: string;    
  14.   City :String;    
  15.       
  16. }      
  17. interface IPnpstate {        
  18.   MyListData:MYListProperties[];      
  19. }   
  20. const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';  
  21. const fileExtension = '.xlsx';  
  22. var Heading = [  ["Title","City"],];  
  23. const saveExcel = (ListData) => {  
  24.   if(ListData.length>0)  
  25.   {  
  26.   const ws = XLSX.utils.book_new();  
  27.    // const ws = XLSX.utils.json_to_sheet(csvData,{header:["A","B","C","D","E","F","G"], skipHeader:false});  
  28.    XLSX.utils.sheet_add_aoa(ws, Heading);  
  29.    XLSX.utils.sheet_add_json(ws, ListData, { origin: 'A2', skipHeader: true });         
  30.     const wb = { Sheets: { 'data': ws }, SheetNames: ['data'] };  
  31.     const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });  
  32.     const data = new Blob([excelBuffer], {type: fileType});  
  33.     saveAs(data, 'Data' + fileExtension);  
  34.   }  
  35. }  
  36.   
  37.   
  38. export default class ExportToExcel extends React.Component<IExportToExcelProps, {}> {  
  39.   constructor(props: IExportToExcelProps, state: IPnpstate) {      
  40.     super(props);      
  41.     this.state = {      
  42.       MyListData: []     
  43.     };    
  44.     this.Listdata=this.Listdata.bind(this);    
  45.   }    
  46.     public componentDidMount(){      
  47.         
  48.     }    
  49.   private  Listdata=async ()=>{    
  50.     //const items: MYListProperties[] = await sp.web.lists.getByTitle("ExportToExcelList").items.get();    
  51.    // return items;    
  52.     //console.log(items);    
  53.     const items: MYListProperties[] = await sp.web.lists.getByTitle("ExportToExcelList").items.get().then((response: MYListProperties[]) => {    
  54.     let result: MYListProperties[] = [];    
  55.       //alert(response.length);  
  56.       response.forEach(element => {    
  57.         result.push({    
  58.        Title: element.Title, City: element.City    
  59.         });    
  60.         });   
  61.           
  62.        // this.setState({ MyListData: result });  
  63.         //alert(result.length);  
  64.         saveExcel(result);  
  65.         return items;    
  66.       });   
  67.             
  68.          
  69.     
  70.   }  
  71.   public render(): React.ReactElement<IExportToExcelProps> {  
  72.     return (  
  73.       <div >    
  74.         <button type='button' onClick={this.Listdata}>Export to Excel</button>          
  75.         
  76.       </div>    
  77.     );  
  78.   }  
  79. }  
Here my list name is “ExportToExcelList” and columns are Title, City (single lines of text).
 
Deploy using below command,
  • Gulp build
  • Gulp bundle –ship
  • Gulp package-solution –ship
After deployment and install app, Expected UI
 
Export SharePoint List Items In Excel (SPFX Using React, FileSaver.js, xlsx.js)
 
Once you click on export to excel button, Ii will download excel file,
 
Export SharePoint List Items In Excel (SPFX Using React, FileSaver.js, xlsx.js)
 
Thanks for reading my blog.
 
Reference links
  • https://www.npmjs.com/package/file-saver
  • https://www.npmjs.com/package/xlsx