Overview
Utilizing Excel as an information source, PnP JS for SharePoint operations, and the XLSX library for parsing Exceed expectations records, I will walk you how to perform out CRUD (Create, Read, Update, Delete) method in a SharePoint list.
We’ll cover two ways to handle the data:
- Static Approach – Hardcoded fields for quick implementation.
- Dynamic Approach – Automatically adapts to any list structure by reading Excel headers.
Whether you're a beginner or a seasoned SharePoint developer, this guide will help you integrate Excel uploads with your SharePoint list seamlessly!
Prerequisites
Sometime recently we start, guarantee you’ve got the desired bundles installed. Execute the following command in your terminal:
npm install @pnp/sp @pnp/spfx @pnp/odata xlsx --save
Set Up Your SharePoint Framework (SPFx) Project
Configure the WebPart.ts File
import { SPFI, spfi } from "@pnp/sp";
import { SPFx } from "@pnp/sp";
export let sp: SPFI;
protected async onInit(): Promise<void> {sp = spfi().using(SPFx(this.context));try {const message = await this._getEnvironmentMessage();this._environmentMessage = message;return super.onInit();} catch (error) {console.error("Error fetching environment message:", error);}}
Create a Simple State Interface
If you don't already have a state file, create one:
export interface IExcelThroughUploadDataToSharePointState {fileName: string | null;}
Handling Excel Upload in .tsx File
Import Required Modules
import * as XLSX from 'xlsx';import { sp } from '../ExcelThroughUploadDataToSharePointWebPart';import "@pnp/sp/webs";import "@pnp/sp/lists";import "@pnp/sp/items";
Add Constructor
constructor(props: any) {super(props);this.state = {fileName: null};}
Handling File Upload and Data Processing
Upload & Process Excel File
private handleFileUpload = async (event: React.ChangeEvent<HTMLInputElement>) => {this.setState({ fileName: null });const file = event.target.files?.[0];if (!file) return;this.setState({ fileName: file.name });const reader = new FileReader();reader.onload = async (e: ProgressEvent<FileReader>) => {const data = e.target?.result;const workbook = XLSX.read(data, { type: 'binary' });const worksheet = workbook.Sheets[workbook.SheetNames[0]];const jsonData = XLSX.utils.sheet_to_json(worksheet) as any[];/*We are using as any[] with XLSX.utils.sheet_to_json(worksheet) to quicklyconvert the Excel sheet into JSON format without TypeScript errors orwarnings. This is a fast and flexible mthod, easpecially useful duringearly development stages when typing isn't necessary. Alternatively,for better type safety and IntelliSense support, you can definea TypeScript interface like this:interfacee ITask {Title: string;Description: string;Status: string;DueDate: date;}Then use the generic version of sheet_to_json like this:const jsonData = XLSX.utils.sheet_to_json<ITask>(worksheet);*//* To fetch existing items from the SharePoint list named "ProjectTasks",we use the following PnPjs method: */const existingItems = await sp.web.lists.getByTitle("ProjectTasks").items.select("Id", "Title", "Description", "Status")();/*In this code:- () is important - in the newer SPFI-based version of PnPjs(@pnp/sp version 2.x and above), instead of calling .get() at theend of the chain, you invoke the entire chain as a function using ().This returns the array of results directly.*//*Create a map for easy lookup of Excel data.This helps in checking whether a specific item (based on Title)already exists.- If the item exists, it can be updated.- If it doesn't exist, it can be added.- If an existing item is no longer present in the Excel data,it can be removed.*/const excelMap = new Map<string, any>();jsonData.forEach(item => {if (item.Title) excelMap.set(item.Title.trim(), item);});const spMap = new Map<string, any>();existingItems.forEach(item => {if (item.Title) spMap.set(item.Title.trim(), item);});/*Perform update or create a new entry:- Check in the event that the thing from Excel exists within the SharePoint list.This ensures the Excel information, and the SharePoint list remains in at this point.*/for (const [title, excelItem] of excelMap) {/* update items */if (spMap.has(title)) {const spItem = spMap.get(title);await await sp.web.lists.getByTitle('ProjectTasks').items.getById(spItem.Id).update({Title: excelItem.Title,Description: excelItem.Description,Status: excelItem.Status,DueDate: this.convertExcelSerialToDate(excelItem.DueDate).toISOString()});spMap.delete(title);} else {await sp.web.lists.getByTitle('ProjectTasks').items.add({Title: excelItem.Title,Description: excelItem.Description,Status: excelItem.Status,DueDate: this.convertExcelSerialToDate(excelItem.DueDate).toISOString()});}}/*Delete remaining SharePoint items not found in Excel*/for (const [title, spItem] of spMap) {await sp.web.lists.getByTitle("ProjectTasks").items.getById(spItem.Id).recycle(); // Use .delete() if permanent removal is needed}alert('Excel data synced with SharePoint list successfully!');};reader.readAsBinaryString(file);}
Dynamic Approach
/*Dynamic field mapping:- This approach allows you to add or remove columns in the Excel file,and those changes will automatically reflect in the SharePoint List itemswithout needing to change the code.*//* #### How it works: */const itemToAdd: any = {};Object.keys(excelItem).forEach(key => {itemToAdd[key] = excelItem[key];});/* #### add item */await sp.web.lists.getByTitle("ProjectTasks").items.add(itemToAdd);/* #### update item */await sp.web.lists.getByTitle("ProjectTasks").items.getById(spItem.Id).update(itemToAdd);/*Explanation:- `Object.keys(excelItem)` retrieves all column names (as key) from the Excel row.- The loop dynamically builds a SharePoint item using those keys and values.- This ensures that any number of columns (2 or more) in Exceel will be correctly handled.- You do not need to hard-code field names - updates to Excel structure will still work.*/
Converting Excel Date to SharePoint-Compatible Format
public convertExcelSerialToDate = (serial: number): Date => {return new Date((serial - 25569) * 86400 * 1000);};
Explanation:
Excel stores dates as serial numbers. This method converts it into a valid JavaScript Date object.
/*Convert Excel date to Unix Epoch (in milliseconds):- Excel stores dates as serial numbers, starting from Jan 1, 1970.- To convert Excel serial date to Unix epoch time (starts from Jan 1, 1970):Substract 25569 from the Excel serial date- because 25569 is the number of days between Jan 1, 1900 and Jan 1, 1970.Convert the result to seconds:- (serial - 25569) * 86400- 86400 is the number of seconds in a day.Convert to milliseconds:- seconds * 1000- Final result will be a JavaScript-compatible timestamp.*/
Render Upload UI
public render(): React.ReactElement<IExcelThroughUploadDataToSharePointProps> {return (<div><input type='file' accept=".xlsx, .xls" onChange={this.handleFileUpload} />{this.state.fileName && <p>Uploading: {this.state.fileName}</p>}</div>);}
Tips for Using Excel Effectively
- Ensure your Excel column headers match the internal field names in SharePoint.
- If you're using dates, convert them into text format in Excel before uploading.
- Dynamic field handling allows you to change list columns without editing the code.
What You’ve Achieved
By the end of this tutorial, you now know how to:
- Upload an Excel file in SPFx.
- Parse the file using XLSX.
- Perform Create, Update, and Delete operations powerfully on a SharePoint list.
- Handle dates and dynamic columns gracefully.
Final Thoughts
- Using Excel as a front-facing CRUD interface makes SharePoint even more accessible for end-users. With PnP JS and XLSX to make the syncing handle makes it dynamic and future-proof!
- Let me know in the comments if you'd like a downloadable sample, a reusable component, or a ready-to-go SPFx solution package.
