Handling SpreadSheets In Javascript
Tags: javascript, typescript, node
Published: 2024-03-02 07:48:12

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](https://developer.mozilla.org/en-US/docs/Learn/JavaScript/First_steps/What_is_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](https://react.dev/) application with [Vite](https://vitejs.dev/), so run the following command to set up your project;

```bash
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.

```bash
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.

```bash
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;

```bash
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.

```jsx
// 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;

```jsx
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.

```jsx
// 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`](https://developer.mozilla.org/en-US/docs/Web/API/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.

```jsx
// 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.

```jsx
// 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.

```jsx
// 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

```javascript
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.