How to Read Excel Files and Convert Them to JSON in React

How to Read Excel Files and Convert Them to JSON in React

In this blog post, we’ll dive into how to easily read an Excel file (both .xlsx and .xls) and convert its content into JSON format using React and the xlsx library. Whether you’re building a dashboard, data analysis tool, or just need to manipulate Excel data, this method can save you a lot of time.

Before we start, make sure you have the following installed:

1. React – Make sure you’re familiar with React and have a basic React app set up.
2. xlsx library – We’ll be using the xlsx library to parse Excel files.

You can install xlsx by running the following command:

npm install xlsx

import React, { useState } from 'react';
import * as XLSX from 'xlsx';

const ExcelReader: React.FC = () => {
  const [jsonData, setJsonData] = useState<any[]>([]); // To hold the JSON data

  const handleFileUpload = (e: React.ChangeEvent<HTMLInputElement>) => {
    const file = e.target.files?.[0];
    if (file) {
      // Read the file
      const reader = new FileReader();
      reader.onload = (event) => {
        // Parse the binary string of the file into a workbook
        const data = event.target?.result;
        const workbook = XLSX.read(data, { type: 'binary' });

        // Get the first sheet
        const sheetName = workbook.SheetNames[0];
        const sheet = workbook.Sheets[sheetName];

        // Convert the sheet to JSON
        const json = XLSX.utils.sheet_to_json(sheet);
        setJsonData(json);  // Set the JSON data to the state
      };
      reader.readAsBinaryString(file);
    }
  };

  return (
    <div>
      <h1>Read Excel File and Convert to JSON</h1>
      <input type="file" accept=".xlsx, .xls, .csv" onChange={handleFileUpload} />
      
      <h2>Parsed JSON Data:</h2>
      <pre>{JSON.stringify(jsonData, null, 2)}</pre>
    </div>
  );
};

export default ExcelReader;

Step 2: Explanation of the Code

1. State Management:
   • We use the useState hook to manage the JSON data. Initially, jsonData is an empty array.
2. File Upload:
   • The handleFileUpload function is triggered when a file is selected using the file input.
   • We use the FileReader API to read the file as a binary string.
3. Parsing the Excel File:
   • Once the file is loaded, the xlsx library is used to parse the file into a workbook.
   • We access the first sheet of the workbook using workbook.SheetNames[0].
   • We then convert the sheet into a JSON array using XLSX.utils.sheet_to_json(sheet).
4. Displaying the JSON Data:
   • The parsed JSON data is stored in the component’s state (jsonData) and rendered inside a <pre> tag for better formatting.

Step 3: Test Your Component

To test this component, simply import it into your main App.tsx (or any other component) and render it.

import React from 'react';
import ExcelReader from './ExcelReader';

const App: React.FC = () => {
  return (
    <div>
      <h1>Excel to JSON Converter</h1>
      <ExcelReader />
    </div>
  );
};

export default App;