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

Marsh Jariwala

6 Comments

Join the conversation! Your thoughts help the community grow.

B Reddy3y ago
Hi Marsh. how do we import the excel sheet data SharePoint list using Spfx?
boreddy Pallavireddy5y ago
How we will make header in bold or apply some styles in header?
Madhan Thurai5y ago
Https://www.c-sharpcorner.com/article/export-sharepoint-list-items-in-excelspfx-react/
Madhan Thurai5y ago
Excat copy of my article,with some changes
- Marsh Jariwala5y ago
  Have you read this article properly ? Your article is about export to CSV and this article is about export to Excel. From this article, you can learn about FileSaver.js, xlsx.js with SPFX. Have you mentioned any thing about this in your article ? Is your article know how to export to excel ? Answer is NO so tell me how its Copy ?
- Madhan Thurai5y ago
  Other than excel plugin check all other code,you will know

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,

What is your solution name? ExportToExcel
Which baseline packages do you want to target for your component (s)? SharePoint Online only (latest)
Where do you want to place the files? Use the current folder
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
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
Which type of client-side component to create? WebPart
What is your Web part name? ExportToExcel
What is your Web part description? ExportToExcel description
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

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,

Thanks for reading my blog.

Reference links

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