How to read and search in an Excel sheet in ReactJS

Key takeaways:
Create a React app that lets users search for a specific word in an Excel sheet and shows the rows where that word appears.
Set up the project using npx create-react-app and install the xlsx library to read Excel files.
Main parts:
App.js: Contains the search input where users type their queries.
ExcelReader.js: Handles uploading the Excel file and filtering the data based on the search input.
The app reads the Excel file using the FileReader API and converts the data into a format that can be searched.
This app works well for small to medium-sized Excel files, but may slow down with larger files.

In this Answer, we will develop a React application that takes the input of an Excel file, a sheet name, and a target word. The objective is to search for the specified target word in the Excel sheet. If the target word is located within the sheet, we display the rows where it is present.

Prerequisites

Before we begin, ensure you have the following:

Basic knowledge of ReactJS.
A React environment set up on your machine (use npx create-react-app if you haven’t already).
A modern browser for testing.

We’ll use the xlsx library to parse the Excel sheet, so let’s start by setting up the project.

Step 1: Setting up the project

First, navigate to your project folder and run the following command to create a new React app and install the xlsx library:

import React, { useState } from 'react';
import ExcelReader from './components/ExcelReader';
function App() {
  const [searchQuery, setSearchQuery] = useState('');
  return (
    <div className="App">
      <h1>Excel Sheet Reader and Search</h1>
      <input
        type="text"
        placeholder="Search..."
        value={searchQuery}
        onChange={(e) => setSearchQuery(e.target.value)}
        className="search-box"
      />
      <ExcelReader searchQuery={searchQuery} />
    </div>
  );
}
export default App;

Main React component for reading and searching Excel sheets

import React, { useState } from 'react';
import * as XLSX from 'xlsx';
function ExcelReader({ searchQuery }) {
  const [excelData, setExcelData] = useState([]);
  // Function to handle file upload and parsing
  const handleFileUpload = (event) => {
    const file = event.target.files[0];
    const reader = new FileReader();
    
    reader.onload = (e) => {
      const data = new Uint8Array(e.target.result);
      const workbook = XLSX.read(data, { type: 'array' });
      const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
      const jsonData = XLSX.utils.sheet_to_json(firstSheet);
      setExcelData(jsonData);
    };
    reader.readAsArrayBuffer(file);
  };
  // Filter data based on the search query
  const filteredData = excelData.filter((row) => {
    return Object.values(row).some((value) =>
      value.toString().toLowerCase().includes(searchQuery.toLowerCase())
    );
  });
  return (
    <div className="excel-reader">
      <input type="file" onChange={handleFileUpload} className="file-input" />
      <table>
        <thead>
          <tr>
            {excelData.length > 0 &&
              Object.keys(excelData[0]).map((key) => <th key={key}>{key}</th>)}
          </tr>
        </thead>
        <tbody>
          {filteredData.map((row, index) => (
            <tr key={index}>
              {Object.values(row).map((value, i) => (
                <td key={i}>{value}</td>
              ))}
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}
export default ExcelReader;

ExcelReader component for uploading, parsing, and searching Excel data

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    background-color: #f4f4f4;
  }
  
  .App {
    text-align: center;
    padding: 20px;
  }
  
  h1 {
    color: #333;
  }
  
  .search-box {
    width: 200px;
    padding: 8px;
    margin-bottom: 20px;
    border: 1px solid #ccc;
    border-radius: 4px;
  }
  
  .excel-reader {
    margin-top: 20px;
  }
  
  .file-input {
    padding: 10px;
    margin-bottom: 20px;
  }
  
  table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 20px;
  }
  
  table, th, td {
    border: 1px solid #ccc;
  }
  
  th, td {
    padding: 12px;
    text-align: left;
  }
  
  th {
    background-color: #f2f2f2;
  }

Adding styles to a React app with a responsive layout and table.

Step 4: How it works

When you upload an Excel file, the handleFileUpload function reads the file using the FileReader API, converts it into an array buffer, and parses it using the XLSX.read function.
The XLSX.utils.sheet_to_json function converts the sheet into a JSON object, which is then saved in the state (excelData).
As you type in the search box, the application filters the data. The filteredData array only includes rows where any value matches the search query.
The filtered data is displayed in a table format, with each column representing a key from the Excel sheet.

Below is the complete working code to search in the Excel sheet.

Try it yourself

We have provided the project structure along with CSS files to style our forms and tables in the React app.

AAABAAQAEBAAAAEAIADjAQAARgAAABgYAAABACAADAMAACkCAAAgIAAAAQAgADkDAAA1BQAAQEAAAAEAIACwBgAAbggAAIlQTkcNChoKAAAADUlIRFIAAAAQAAAAEAgDAAAAKC0PUwAAAORQTFRFIiIiIiIiIiIiIiIiIiIiIiIiMlBYPXKAKTc7Kjo+SJCkLUJHRYiaOGRvNVhiNltlS5muS5qvMU1VOWdzM1NcSZOnOmd0JzAzQHuLVrnUVLTPQX2NVrvWQHmJNlxmSJKmLUNJSZSoRYibKztAN19pN19qRoqdSparS5uwSJCjLUJIYdr7LEBGQ4OUTJ2zJi4wV73ZTqO6SZOoJCkrQoGRSpWqUq/IP3aGPnOBPnWEU7HKPXF/UKnBUKjAUKe/LD9ENFVeJSstTaC2S5iuJSssMk9YKzxBTJyyIyYmRIaYJSwuSZWpdvRU9gAAAAV0Uk5TSebnSuRlwGWmAAAAqUlEQVR4AU2OtVpFQQyE/9mzCe7uJVKh79/hVFRox0eFuyzBrsU9A7IW0n/ube1m7W1uZpJBliTihz4hA6ZnuvRIZ72QRd+P3LV9AgkG3bv9h7q8axBkE/qnr0pvEZeZzjPmTCJKuaCbzNX8UYck4ufEvB9mZrUs6YA1aTkCGcyfr0ioHC9tQgKGh3fN3Hc7RA3YKyaCV6sVqrmoRPRvFEDKtFJJqdBCoW9tGi4H27MHwAAAAABJRU5ErkJggolQTkcNChoKAAAADUlIRFIAAAAYAAAAGAgDAAAA16nNygAAAWVQTFRFIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiMlJaTqK5SparM1JbSpesKTU5WcHeMEtTNFdgUavETJyyJSstMEpSKTY6Omd0Q4KTIiMjP3aGVbfRP3eGPG57Ji0vWcLfPXF/Oml1Qn+QQX2NQXuMQoGRL0lQUKe/PXGAS5muX9TzTJ2zJCgpO2x6Uq/JYdr7SZOoWsbjM1VeMU1VM1RdWsXiSZOnWL/cRoueLUJHUKjANltlLEBFNVpkT6e/LUJIWL/bKz1BV77aLEBGV7zYUarDP3aFV7zXKzxBQHmIIiMkXMzqNFdhXMzrU7LMJCgqLkVMXtHxVrrVNl1oIyQlR46hIyYnO2p3VrnULUNJTJuxWsThXtDvPnWEU7DKL0hOTaC3X9X1XMvpTaC2OGRvRIWXRIaYPnSDKz1CW8jmLD5DPnSCPG99QHmJMExTLkVLVLTOJCcoJSwuQX6OV73ZQ4GSJSssXMroQyHkHQAAAAd0Uk5TBpHt7pCIiZxHvtYAAAFPSURBVHgBbInDQrZRFIWfdbTfX8izPMsWpt12HGWN6gayjfNhFjYWQc6+jBcE+3aiewdAzgcARScA3mQASdUh5xye7RogAPBDl9RUiwv+ZwAcADHCRSjPBRTxU1FzGuJpDcSPhVr/txZWBrkLAALQKelnIYmqaM75EAJ0Bem6LWl9mLURtTzVPxW2j2xCWmHEtDR7RNPijLtfZTileceBrcAfM54pn6U/sGY7OC7jHKR0zD3lOw4JOuM1MmZ/S1rsf2wS+ajYms05Lp4jg9ICQcFxzPrbFgYfrqNW/4FgPQMDJKzCP1+2vq4F1HFN55Au70okDwo/XnH38e3bj+8ufSyC7HPm3QwMX1R0dNS+ABUxIweJLyjsly1bBhKB6GCB6GDMBNrvxfBaH2Q1ko6Zmz/OZWJav96IaS7b5pkMEBnscc6EKzGwAX3CjDX5AADv52SoR5XP+AAAAABJRU5ErkJggolQTkcNChoKAAAADUlIRFIAAAAgAAAAIAgDAAAARKSKxgAAAUFQTFRFAAAAIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiMlJaVrrWX9T0VbfRPXKAJCgpJzI1Xc7tQ4OVMExTPG99WMDcWL/bMEpRPXB+RISWPG57VrrVRYeZOGRvX9TzRYiaRoueSJGlOWRwX9X1SJCkSZOoRoyfNltlYNb3YNf4TJ2zL0dNX9PyVbjTJzAyUKnBL0lQUKrCWL/cJi8xO2x5M1JbYNj4R46hVLPNXMzqYdr7MU9XIiMjUavEYdn6Uq7HPXGAYNb2LkVMKz1BVLXQLUNJS5iuIyYnQ4KTXtDvSZSoJi4wNFVeXtHwQHmIWsfkJCgqW8nnXtHxLD9EUq/JIiMkMU5WMU1URIWXPnWEO2t4R42gUq/IL0hOJi0vQHmJPnOBPnSCQHqKXc3sUKjAJCcoRIaXXc/uPG58MEpSVbfSDtdcvAAAAAl0Uk5TACWt8Sfv8yjyftHd6AAAAZ5JREFUeAGNkwOaQzEURl/dv01t27Y0tm17/wuYpG6G5+HTCa4EQSSW4AckYpEgSGX4BZlUEONXxMJof5WaaLQ6UHRaDVGrMEAuYIDeYDSZLVYbYLNa7CaHQY8BI8HpYmvdxOPx+tg+Licn+ANgBEMkCEbYzwmRKBixeCI2WBDhBGMSlFTa6UynQEka+TtkAGQtuXy+UCwByJQ5oVKt1RvNZjMUor9GvVZtTQvtWKfa7c0Z5xdAWZg3zvW61U6sPRIWlwyO5cjKqnUNfdasqyuRZYdhaXEgpNY3NoGt7Z3dPWgN+1rs7e4cbAGbG+uHfeGoBsqxpVtGq0k5QblrOQal1usL5BSMM6LDORPOofOegXFCftjhYrjD5VVfuF6P3ABbB7f0DneG/Xt6hwd2hxt6h9koiqMoiqMo/psHhopm8nGUyUeWye9r8ZTP54pZvhb/qmbk8sd+4DoqNNNRXE8+n5Ggh7w8f9eT+lej6c1afAfei8U3k/GV72pU1OTjbg+UvbsPoq5gJEjwK/K/R0/x+/Aq6HiLlfgBpVgqfAKUGF7/BQ9kDwAAAABJRU5ErkJggolQTkcNChoKAAAADUlIRFIAAABAAAAAQAgDAAAAnbeB7AAAAjpQTFRFAAAAIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiJSstQHmJV7zXYNf4XtHxU7LMQ4KTLD5DKjg8WMHdYdr7Ydn5TZ+1KzxBYdn6WMDcIyUlVLXQTJ60PXF/PnWES5uwYNb2QX2NTJuxOGRvPXKAJSssQHmIXtDvU7LLKTU5PXGAUKnBVbfSIiMkUKrCXMroMEpRUarDIiMjVbfRIyYmX9T0PnSCPnOBYNj4X9TzNVljMU9XNVpkMU5WKz1CMU1VKztAL0dNXMzqKjs/M1NcKTg8MlJaX9X1Kz1BXc/uLEBGLEBFJi0vMEtTN2FsT6a+OWRwOGFtQn6PRYiaUKjATqK5JzAyM1RdXc7tP3iHM1VeQ4OVNlxnVbjTJi4wRISWN19pUq3GSJGlSZSoTaC2JCgqP3aFWsbjWL/cLUFHIyQlW8nnOGNvW8jmV73ZQ4GSJzI1P3aGOGJuJSorTJyyTqO6MlBYKTc7PXB+KDU4XtLyUKe/NFdhUavEVLTPMEpSUazFVLTOSZOoNl1oUq/JVLPNMExTRoudOWVxRYibRYmcOWdzRoqdQ4OUIyYnXMvpYNb3Ji8xNlxmQoGRQXuMUq7HXc3sKDQ3JSwuKjo+Kjk9OWZyOmd0N15pVbbQP3eGWsfkWL/bXtHwUa3FSJCjKTY6VrnUJzAzNFVeRYeZLkVMLkZNS5muQHqKU9ccDgAAABl0Uk5TACqO1/jWBpT9mAm7vZUr/I/589SQLNj69MA9Vo8AAAQMSURBVHgBpMu1AUMhAATQ74qWh0N8/wXj2gVeeVJd1U3b4U9d39TV3TBOyDLNy+2/IhsZqqqiKDBWFeMowFklUERULX4obazzIeJLDN5ZoxV+tJX8SdJme7PbH97hYX+8p6f0M5fVT3AmlBzQpImhAHiO9yOfaq302rZtjW3p8mMpXW2kkofJKcanZ2bnFGhrsvHO0qDmZ2emx5ma7BnQK1iAxep1aXkFVteqt2ursLJcW/w6LNgINtjcasSyDTu7Irs7sNdY+f4B22bB4RHHzfv9k1POzs/POD3eb7475ujQKBiDi/bTpUJr1GX7zRVcGwU3cCtt7u7h/k7aPMCNUfAIT9Lm+QX0s7R5gme7FXR00Osbb5X9vaOf7FbwAbvS5HMC/fysmfiUJl/wYRT8ge9WFX7gt9YZP60qOOCPUSA7OKXBLLikggtmpYGTHTEL3Hikhten8Z8EKpz40UGv1PCwYSMIsbMWtqYjim5QkWkrvLZDyCyIxujkzV/hjU5i0eGCw7jnBQCVSI5CKtrSpiCdTCgAXjzxw4GCTDYHcEq+UJTJCKWOqaIlIpNSLOQ5BchlM2VSy2ExkCgKot9xxzjZjrSKbUvLsW3Gtm3b/Ld5aSHoDs6qWc9166DAz1/A7z9p94j9K/IPisRBEXwW+XuRmLQ/v4FfRW4BKS4BSstEpDyACqmsolpcVHOpUioIKBcJr6kFSoqdAoV1EFgvGg00ShM5mqE1t0RFtTRrxzSHJmmlVjTaAqGu0BYIr4Z2c2Y64GcsnaLoQkO77iS2CLrN+e6Bh+GWQBH0iklfPwM8HRSRIQxeisjg073H9uT1QpElEEzgd7EYBkZEMYrBqChGgB6x+B5ImCUwxgOxGb/F2IQoojCIEsXEGBcnxeYBl04uMHWEQO1Zh1AEV+xJ/GFO4ksMmg+ZxD/w07WMPY5lnHYvY5exjNMwYxYw9zLK6zqIMDZSLXXSxK1cUcy25OS0vLU2UiMNxkaaszbS8bdy1YGt7HWY5o1t4vDCf56HSfFpfgEglsX8oMOPc1D+IrEAC/OfvA1laXnlo8tQmuDjyvKSp6Gc3dIsU11VprpWhRuq1pSprlqm6m/r6xu3eHrjmeLGU25trHvaundh2YTPHoXFu7Td9ShtZy+u/uW9wL+8+wWMW6cIGBlis7UXcbbFJtWvB7mQJBbxesiKF4sESD5hzEtUMS/m+DFPgokwvi5/aAfNh1n+QdP2owot6j7bi7qroljVou5936jrDNvROwfD9k60f9h2xf2Pzrj/MQCN69fET0Amdi/E/l+X9lz0Dsdc7S4Pmb0rMJQDOxwUASEGZsoMEKa00yXCIEpZt0+Uwo4nL8VdX4o73xDAJyImTqpuCWERUZBeABmUY3imQeHSAAAAAElFTkSuQmCC

Reading Excel file and displaying a table for the searched keyword

We have successfully parsed an input Excel file, integrated search functionality through filtering mechanisms, state management using React hooks, and presented the results in a styled table.

Conclusion

This Answer demonstrated how to read and search for specific content in an Excel sheet using ReactJS. By utilizing modular components, such as Form.js for user input and ExcelData.js for processing, we enhanced maintainability. The implementation leverages the xlsx npm package, along with useState and useEffect hooks, to manage state and perform case-insensitive searches.

While effective for small to medium Excel files, performance may be impacted with larger datasets. Overall, this solution provides a solid foundation for Excel data handling in React applications.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How do you search within a spreadsheet in Excel?

To search within a spreadsheet in Excel, press Ctrl + F (or Cmd + F on Mac) to open the Find dialog. Enter the target text in the “Find what” box and click “Find Next” to locate instances of the text in the spreadsheet.

How to read an `xlsx` file in React Native?

To read an .xlsx file in React Native, you can use the xlsx package. Install it with npm install xlsx, then use FileSystem from expo-file-system to read the file, and xlsx to parse it. Here’s a basic example:

import XLSX from 'xlsx';
import * as FileSystem from 'expo-file-system';

const readXlsxFile = async (fileUri) => {
    const fileContent = await FileSystem.readAsStringAsync(fileUri, {
        encoding: FileSystem.EncodingType.Base64,
    });
    const workbook = XLSX.read(fileContent, { type: 'base64' });
    const sheetName = workbook.SheetNames[0];
    const data = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
    return data;
};

How do I read data from an `.ENV` file in ReactJS?

Create a new file called .env in the root directory of your project.
In the newly created .env file, include a key-value pair in the format key=value.
Restart your development server.
Your newly added variables will now be accessible throughout your React application via the global process.