Excel-Powered CRUD in SharePoint List Using PnP JS and XLSX

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