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/[email protected] --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

protected onInit(): Promise<void> {

    return super.onInit().then(_ => {

      // other init code may be present

     sp.setup({
    // set ie 11 mode
    ie11: true,
    // only needed when working within SharePoint Framework
    spfxContext: this.context
  });
    });
  }

in ExportToExcel.tsx file

import { sp } from "@pnp/sp";
import "@pnp/sp/webs";
import "@pnp/sp/lists";
import "@pnp/sp/items";
import * as XLSX from 'xlsx';
import {saveAs}  from 'file-saver';

Full Code

 

in ExportToExcel.tsx

import * as React from 'react';
import styles from './ExportToExcel.module.scss';
import { IExportToExcelProps } from './IExportToExcelProps';
import { escape } from '@microsoft/sp-lodash-subset';
import { sp } from "@pnp/sp";
import "@pnp/sp/webs";
import "@pnp/sp/lists";
import "@pnp/sp/items";
import * as XLSX from 'xlsx';
import {saveAs}  from 'file-saver';

export interface MYListProperties {
  Title: string;
  City :String;

}
interface IPnpstate {
  MyListData:MYListProperties[];
}
const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const fileExtension = '.xlsx';
var Heading = [  ["Title","City"],];
const saveExcel = (ListData) => {
  if(ListData.length>0)
  {
  const ws = XLSX.utils.book_new();
   // const ws = XLSX.utils.json_to_sheet(csvData,{header:["A","B","C","D","E","F","G"], skipHeader:false});
   XLSX.utils.sheet_add_aoa(ws, Heading);
   XLSX.utils.sheet_add_json(ws, ListData, { origin: 'A2', skipHeader: true });
    const wb = { Sheets: { 'data': ws }, SheetNames: ['data'] };
    const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    const data = new Blob([excelBuffer], {type: fileType});
    saveAs(data, 'Data' + fileExtension);
  }
}


export default class ExportToExcel extends React.Component<IExportToExcelProps, {}> {
  constructor(props: IExportToExcelProps, state: IPnpstate) {
    super(props);
    this.state = {
      MyListData: []
    };
    this.Listdata=this.Listdata.bind(this);
  }
    public componentDidMount(){

    }
  private  Listdata=async ()=>{
    //const items: MYListProperties[] = await sp.web.lists.getByTitle("ExportToExcelList").items.get();
   // return items;
    //console.log(items);
    const items: MYListProperties[] = await sp.web.lists.getByTitle("ExportToExcelList").items.get().then((response: MYListProperties[]) => {
    let result: MYListProperties[] = [];
      //alert(response.length);
      response.forEach(element => {
        result.push({
       Title: element.Title, City: element.City
        });
        });

       // this.setState({ MyListData: result });
        //alert(result.length);
        saveExcel(result);
        return items;
      });



  }
  public render(): React.ReactElement<IExportToExcelProps> {
    return (
      <div >
        <button type='button' onClick={this.Listdata}>Export to Excel</button>

      </div>
    );
  }
}

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

 

 

Once you click on export to excel button, Ii will download excel file,

 

 

Thanks for reading my blog.

 

Reference links

• https://www.npmjs.com/package/file-saver
• https://www.npmjs.com/package/xlsx