Handling SpreadSheets In Javascript

Spreadsheets are very common in today's world and most people prefer to visualize and interact with their data via a spreadsheet because of the awesome features they provide. Spreadsheets are also a reliable way to store data given that the data is not bound to change much. This is especially useful for companies that do not have the technicality required to maintain a traditional database, they usually resort to using a spreadsheet as a kind of drop-in replacement and it works well for most of the time.

There are lots of UIs for interacting with a spreadsheet and there is no shortage of options to choose from in this category. However we are developers and we don't have the time and patience to use the spreadsheet with a UI we'd rather write code that would work for us, to make us feel smart for being lazy. Today's Post is going to be centered around how we can interact with a spreadsheet using our favorite programming language, Javascript. We will consider the following talking points.

• Project Setup and Installation
• Parsing a Spreadsheet
• Converting Data to Spreadsheets

Project Setup and Installation

To get started we need to bootstrap a React application with Vite, so run the following command to set up your project;

npm create vite@latest excel-app -- --template react

This command will help us to set up our React project, although you will need to follow some prompts to complete the setup. Now we have our React application successfully bootstrapped for us. We need to install the dependencies but first, let's navigate to the project directory.

cd excel-app && npm install

When the installation is complete we need to verify that everything is working as intended thus we need to start the project in development mode.

npm run dev

You should see your app running on port 5173, open up localhost:5173 in your browser to see the default React template that comes when we set up a vite React project. We now need to install the library that will help us deal with the spreadsheet. Run the following command to install the library;

npm install xlsx

Parsing a spreadsheet

Now let's see how we can parse an Excel spreadsheet. Before we jump into that we need to set up a component that will allow users to upload an Excel spreadsheet but before we can do that we need to set up a component that will enable the user to select a spreadsheet.

// src/uploadForm.jsx

const UploadForm = () => {
  return (
    <>
      <form
        ref={formRef}
        onSubmit={(e) => handleSubmit(e, formRef, readData, handleError)}
      >
        <input type="file" name="excel" />
        <button type="submit">ParseFile</button>
      </form>
    </>
  );
};

export default UploadForm;

We need to import this component into our App.jsx file to use it;

import "./styles.css";
import UploadForm from "./uploadForm";

export default function App() {
  return (
    <div className="App">
      <UploadForm />
    </div>
  );
}

Now we are going to create a helper file that will contain the function for processing the uploaded file.

// src/helper.js

export const handleSubmit = (e, formRef, cb, errCb) => {
  e.preventDefault();
  const fileReader = new FileReader();

  const { excel } = formRef.current;
  const file = excel.files[0];

  fileReader.readAsArrayBuffer(file);

  fileReader.onload = () => {
    cb(fileReader.result);
  };

  fileReader.onerror = errCb;
};

The function above accepts four arguments, the first is the event object while the second is a reference to the form we want to process, the third argument is a success callback function and the last is an error callback function. Inside the function, we call the preventDefault method on the event. Then we create a new FileReader object, then we extract an input whose name is excel from the form after which we create a reference to the first uploaded file from the input.

We call the readAsArrayBuffer method on the fileReader, this method asynchronously reads the uploaded file as an ArrayBuffer, which is more efficient for binary data like Excel files. We define an event listener for the load event, which is fired when the file is read successfully. Inside the listener, we call the success callback cb(fileReader.result) with the arrayBuffer as its argument. This allows you to use the file data further in our application. Let's go back to the uploadForm component to import and use this function, we also need to adjust this component.

// src/uploadForm.jsx

import { useRef, useState } from "react";
import { read, utils } from "xlsx";
import { handleSubmit } from "./helper";

const UploadForm = () => {

  const formRef = useRef(null);
  const [tableData, setTableData] = useState([]);

  function readData(arrayBuffer) {
    const workbook = read(arrayBuffer);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const data = utils.sheet_to_json(sheet);
    setTableData(data);
  }

  function handleError(error) {
    console.error("Error reading file:", error);
  }

  return (
    <>
      <form
        ref={formRef}
        onSubmit={(e) => handleSubmit(e, formRef, readData, handleError)}
      >
        <input type="file" name="excel" />
        <button type="submit">ParseFile</button>
      </form>
    </>
  );
};
export default UploadForm;

We have updated the uploadForm component, we imported useRef and useState from React then we imported read and utils from the xlsx library we installed when we were setting up our project. Then we import handleSubmit from the helper file. Inside the UploadForm component we have created a formRef variable which is a ref object and we set its current value to null. Then we create a stateful variable tableData.

Next, we create a function readData. The function takes an arrayBuffer as input, which is the Excel file data read using the FileReader in handleSubmit. We use the read function from the xlsx library to create a workbook object from the provided arrayBuffer. const sheet = workbook.Sheets[workbook.SheetNames[0]]; retrieves the first sheet within the workbook. The SheetNames array stores the names of all sheets, and you're accessing the first element with index 0. Then we Convert the sheet data into JSON format using the sheet_to_json function from the utils object in the xlsx library. Then we set the tableData state to the converted data.

Under that function, we have defined an error handler function that will be called when an error happens while the fileReader is trying to convert the uploaded file to an arrayBuffer. Then we set the ref attribute on the form to the formRef and then we call the handleSubmit function when the form is submitted. Now for us to see the uploaded data we need to create another component this will be a table that displays the data from the uploaded file, take note I already know the structure of the data from the Excel file so I can make assumptions.

// src/table.jsx

const DataTable = ({ tableData }) => {
  return (
    <table>
      <thead>
        <tr>
          {Object.keys(tableData[0]).map((title, index) => (
            <th key={index}>{title}</th>
          ))}
        </tr>
      </thead>
      <tbody>
        {tableData.map((data, index) => (
          <tr key={index}>
            <td>{data._id.slice(0, 6)}</td>
            <td>{data.crowd}</td>
            <td>{data.units}</td>
            <td>{data.price}</td>
            <td>{data.external_id}</td>
            <td>{new Date(data.createdAt).toDateString()}</td>
            <td>{new Date(data.updatedAt).toDateString()}</td>
          </tr>
        ))}
      </tbody>
    </table>
  );
};

export default DataTable;

The DataTable component is a simple React component that takes an array of objects as its tableData prop. It renders a table with headers and rows, where each row represents an object in the tableData array. The headers are generated from the object keys of the first object in the tableData array. The values of each header are displayed as table cells. The DataTable component uses the map method to iterate over the tableData array and render a table row for each object. We will import and use this function inside the uploadForm component.

// src/uploadForm.jsx

// ...cont'd
import { handleSubmit, downloadData } from "./helper";
import DataTable from "./table";


const UploadForm = () => {
  // ...cont'd

  return (
    <>
      <form
        ref={formRef}
        onSubmit={(e) => handleSubmit(e, formRef, readData, handleError)}
      >
        <input type="file" name="excel" />
        <button type="submit">ParseFile</button>
      </form>

      {tableData && tableData.length > 0 && (
        <div>
          <div className="download-button">
            <button onClick={() => downloadData(tableData)}>
              Download Data
            </button>
          </div>
          <DataTable tableData={tableData} />
        </div>
      )}
    </>
  );
}

// ...cont'd

Converting Data to Spreadsheets

We have imported and used the DataTable component to display the data in the uploaded Excel sheet, we have also added a button to download the data back as an Excel file and we have also imported a new function downloadFile from the helper file so we need to go and define that function

export const downloadData = (data) => {
  const ws = utils.json_to_sheet(data);
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Data");
  writeFileXLSX(wb, "data.xlsx");
};

// ...cont'd

The function downloadData is designed to export a given array of data (data) as an Excel file. const ws = utils.json_to_sheet(data); uses the json_to_sheet function from the xlsx library to convert the data array into a worksheet object suitable for Excel representation. const wb = utils.book_new(); creates a new empty Excel workbook object using the book_new function. utils.book_append_sheet(wb, ws, "Data"); adds the previously created worksheet to the workbook, assigning it the name "Data".
writeFileXLSX(wb, "SheetJSReactAoO.xlsx"); utilizes the writeFileXLSX function from the xlsx library to write the constructed workbook object to a physical Excel file named "data.xlsx".

The downloadData function is used by the UploadForm component to export the data from the uploaded Excel file. When the "Download Data" button is clicked, the UploadForm component calls the downloadData function with the tableData state as the argument.

That's going to be it for this post guys, Hope you found this useful. What are your thoughts on this approach as a means of data collection in your application, would you personally implement this approach? Have you worked with other Javascript Excel libraries and do you think they do a better job than XLSX? Please leave your thoughts on all this and more in the comment section and I will see you in the next post.