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!
![Excel]()
Prerequisites
Sometime recently, we started to 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 quickly
convert the Excel sheet into JSON format without TypeScript errors or
warnings. This is a fast and flexible mthod, easpecially useful during
early development stages when typing isn't necessary. Alternatively,
for better type safety and IntelliSense support, you can define
a 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 the
end 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 items
without 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.
![Project Tasks]()
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.
Comments are visible in the HTML source only