Excel-Powered CRUD Operations in SharePoint List using PnP JS and Excel (XLSX) File

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)

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. 

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.

React Hooks vs Class Components: A Beginner-Friendly Comparison

If you’re new to React or have experience with class components, you may have heard that hooks have brought significant changes to the way React applications are developed.

Early in React’s history, class components were the standard approach., class components were everywhere. Managing state, dealing with lifecycle methods like componentDidMount, and passing data using context often felt confusing and repetitive. With the introduction of hooks, React development became simpler and cleaner.

In this blog, I want to walk you through the basics of four important hooks:

• useState
• useEffect
• useRef
• useContext

For each hook, I’ll show you what code looked like before hooks (using classes) and how much easier it is with hooks. I’ll also explain the pain points and benefits from my perspective.

1. useState: Managing Local Component State

Let’s start with the most used hook - useState.

Without Hooks (Class Component)

class Counter extends React.Component {

    state = { count: 0 };

 

    increment = () => {

      this.setState({ count: this.state.count + 1 });

    };

 

    render() {

      return (

        <div>

          <h3>Count: {this.state.count}</h3>

          <button onClick={this.increment}>Increment</button>

        </div>

      );

    }

  }

With Hook: useState (Function Component)

import React, { useState } from "react";

function Counter() {

    const [count, setCount] = useState(0);

 

    return (

      <div>

        <h3>Count: {count}</h3>

        <button onClick={() => setCount(count + 1)}>Increment</button>

      </div>

    );

  }

What Was Difficult Without Hooks:

• You had to use this.state, this.setState, and bind functions.
• Even for small components, you needed a class.
• State logic wasn’t reusable.

Why useState is Better:

• Much cleaner and easier to read.
• No more dealing with this.
• You can manage multiple states independently in one component.

2. useEffect: Doing Side Effects (e.g. API Calls, Timers)

When your component needs to fetch data, set up a timer, or do something after render - you use useEffect.

Without Hooks (Class Component)

class Timer extends React.Component {

  state = { seconds: 0 };

  componentDidMount() {

    this.interval = setInterval(() => {

      this.setState(prev => ({ seconds: prev.seconds + 1 }));

    }, 1000);

  }

  componentWillUnmount() {

    clearInterval(this.interval);

  }

  render() {

    return <h3>Time: {this.state.seconds} seconds</h3>;

  }

}

With Hook: useEffect(Function Component)

import React, { useEffect} from "react";

function Timer() {

  const [seconds, setSeconds] = useState(0);

  useEffect(() => {

    const interval = setInterval(() => setSeconds(s => s + 1), 1000);

    return () => clearInterval(interval);

  }, []);

  return <h3>Time: {seconds} seconds</h3>;

}

What Was Difficult Without Hooks:

• You had to spread your logic across multiple lifecycle methods.
• It was hard to keep related logic together.
• Cleanup code (like stopping a timer) was messy.

Why useState is Better:

• Everything lives in one place.
• Cleanup is easy with the return function.
• Execution is controlled by specifying dependencies in the array.

3. useRef: Getting a Reference to a DOM Element or Persistent Value

If you’ve ever needed to directly access an input field or persist a value between renders without triggering a re-render — useRef is the way to go.

Without Hooks (Class Component)

class FocusInput extends React.Component {

  constructor() {

    super();

    this.inputRef = React.createRef();

  }

  focus = () => {

    this.inputRef.current.focus();

  };

  render() {

    return (

      <div>

        <input ref={this.inputRef} type="text" />

        <button onClick={this.focus}>Focus Input</button>

      </div>

    );

  }

}

With Hook: useRef (Function Component)

import React, { useRef} from "react";

function FocusInput() {

    const inputRef = useRef(null);

 

    return (

      <div>

        <input ref={inputRef} type="text" />

        <button onClick={() => inputRef.current.focus()}>Focus Input</button>

      </div>

    );

  }

What Was Hard Without Hooks:

• You needed constructors and had to manage refs manually.
• It made the component more complex.

Why useRef is Better:

• You can declare and use it easily in functional components.
• It’s perfect for keeping mutable values around without causing re-renders.

4. useContext: Accessing Global Data Easily

When you want to pass down data (like theme or user info) to deeply nested components, useContext is your friend.

Without Hooks (Class Component)

const ThemeContext = React.createContext('light');

class ThemedButton extends React.Component {

  static contextType = ThemeContext;

  render() {

    const theme = this.context;

    return <button style={{ background: theme === 'dark' ? '#333' : '#eee' }}>

             Theme: {theme}

           </button>;

  }

}

With Hook: useContext (Function Component)

import React, { useContext} from "react";

const ThemeContext = React.createContext('light');

function ThemedButton() {

  const theme = useContext(ThemeContext);

  return <button style={{ background: theme === 'dark' ? '#333' : '#eee' }}>

           Theme: {theme}

         </button>;

}

What Was Hard Without Hooks:

• You had to use static contextType.
• It wasn’t easy to use multiple contexts at once.
• More boilerplate.

Why useContext is Better:

• Just call useContext in any functional component.
• Clean and simple syntax.
• Great for global state like auth, theme, user, etc.

Final Thoughts

Hooks made React fun again. No more fighting with this, long lifecycle methods, or confusing state logic.

For those new to hooks, it is recommended to start by gradually converting class components to functional components using hooks. As familiarity with hooks increases, many developers find them to be a more efficient and maintainable approach to building React applications.

React Dependency Types Explained: What Goes Where in package.json (With Real Examples)

Introduction

When you're working with a React project, you'll often add other packages or tools to help build or run your app. These are called dependencies, and they’re listed in a file called package.json.

But not all dependencies are the same — some are needed in production, some only during development, and some are required only when building reusable libraries.

In this guide, we’ll explain:

• What dependencies are
• What devDependencies are
• What peerDependencies are

with real examples so you can understand when and how to use each.

What are dependencies?

These are the packages your app needs to work when it runs — especially after it's been built and deployed.

When you install a package like this:

it gets added to the dependencies section of package.json.

These will be part of your app’s final bundle that runs in users' browsers.

So here, axios is used for making API calls, and React is your main library. Both are necessary for your app to function properly.

Example:

What are devDependencies?

These packages are only needed while you're developing the app — not when the app is live.

They include things like:

• Code formatters (eslint)
• Build tools (vite)
• Test frameworks (jest)
• Type checkers (typescript)

You install them using:

They’ll be added to the devDependencies section in package.json.

Example:

When you build your app, these tools help prepare your code — but they’re not included in the final bundle sent to the browser.

What are peerDependencies?

These are a little different. You use peerDependencies mostly when you’re creating a library or shared component, not a full app.

A peerDependency tells the user of your library:

“Hey! You need to have this package installed for my library to work.”

Your library expects the host app to already have that package — usually something like react.

Why do we use this?

If your library includes its own version of React, and the host app uses a different version, they might conflict. That can break things like hooks, context, or even throw errors.

By using peerDependencies, you avoid including React yourself and instead rely on the version the main app already uses.

Example:

This says: “Your project should have a React version that matches one of these.”
npm will warn the user if they don’t.

How npm Handles These Dependencies

When you run:

Here’s what happens for each dependency type:

• dependencies are always installed and included in the final build.
• devDependencies are only installed in development, not in production if you set NODE_ENV=production.
• peerDependencies are not installed automatically in older npm versions (v6 and below).
• In npm v7+, they try to install them — but only if versions match properly.

Example:

Here:

• axios is required to run your app.
• jest is for testing during development.
• react is expected to be provided by whoever installs your package.

Common Mistakes to Avoid

Here are some mistakes many developers make:

• Putting everything into dependencies, even tools that are only needed during development.
• Not adding peerDependencies in libraries — which leads to duplicate versions and weird errors.
• Including React in both the app and your custom library, causing conflict between React versions.

 

Conclusion

Understanding these three types of dependencies can really improve your project setup:

• Use dependencies for the actual code your app runs.
• Use devDependencies for tools that help you build and test.
• Use peerDependencies when building packages that need shared libraries (like react).

By keeping these organized, your project will stay clean, efficient, and easier to maintain - especially when working with multiple developers or publishing reusable components.