Convert your Excel file to a Javascript object (Using React) - without storing the static file in your database!

Emmanuel David — Tue, 14 Sep 2021 11:17:32 +0000

My mom is working in an University and at the end of every semester she used to literally copy the results of hundreds of students from an excel file and mail them individually which costed her a lot of time. As I came to know about the problem I planned to create a mass email sender which would enable them to mail all candidates within a click of a button. I was a facing a few minor challenges with the excel file and that's what made me write this article! So let's discuss how I did that!

Create a React App

Write the following command in your terminal to create a React app

npx create-react-app excel-file-converter

You can give any name of your choice.

So let's go!!🚀

Workspace Setup

We will be using Visual Studio Code for this demo and my workspace looks like this

I've created two component files: UploadFile.js for uploading our file and DisplayData.js for displaying the recieved data. I've also created some CSS files for them respectively.

Step 1: Uploading the Excel file

First we have to upload our file and we can do this either by dragging the file and dropping it in our app or by opening your files through our app, anyway we will be doing both the ways.

Inside UploadFile.js we are defining three functions to handle and extract the data

fileDropHandler => triggered by onDrop event
fileDragOverHandler => triggered by onDragOver event
uploadFileHandler => triggered by onChange event from input field

Dragging and dropping our files

First, dragging and dropping the file triggers some default events
which we don't need so we are preventing them first

const fileDragOverHandler = (event) => {
    event.preventDefault();
  };

While dropping the files, the excel file is uploaded in binary format and we can extract it from the event object by event.dataTransfer.files[0]

const fileDropHandler = (event) => {
    event.preventDefault();
    const file = event.dataTransfer.files[0];
  };

Opening the file through our app

This is similar to the fileDropHandler as we extract the binary data from the event object by event.target.files[0]

const uploadFileHandler = (event) => {
    const file = event.target.files[0];
  };

Step 2: Processing the uploaded file

Now we will be needing a package to handle the data we got now.
I found a few but none of them were accepting data like this as they expect us to show the excel file location but it makes things complicated as we will have to store the static file using services like AWS.
So I took the code from the npm package excelToJson github repository and found that it is taking the excel file from the given location and processing the binary data. But we already have that binary data! So all we need to do is to directly feed the data to the code rather than mentioning the file location. So I modified last few lines which was not super hard to do.
You can visit their repository here

Here's the modified code which I stored in a file named excelToJson.js:

const XLSX = require("xlsx");
const extend = require("node.extend");

const excelToJson = (function () {
  let _config = {};

  const getCellRow = (cell) => Number(cell.replace(/[A-z]/gi, ""));
  const getCellColumn = (cell) => cell.replace(/[0-9]/g, "").toUpperCase();
  const getRangeBegin = (cell) => cell.match(/^[^:]*/)[0];
  const getRangeEnd = (cell) => cell.match(/[^:]*$/)[0];
  function getSheetCellValue(sheetCell) {
    if (!sheetCell) {
      return undefined;
    }
    if (sheetCell.t === "z" && _config.sheetStubs) {
      return null;
    }
    return sheetCell.t === "n" || sheetCell.t === "d"
      ? sheetCell.v
      : (sheetCell.w && sheetCell.w.trim && sheetCell.w.trim()) || sheetCell.w;
  }

  const parseSheet = (sheetData, workbook) => {
    const sheetName =
      sheetData.constructor === String ? sheetData : sheetData.name;
    const sheet = workbook.Sheets[sheetName];
    const columnToKey = sheetData.columnToKey || _config.columnToKey;
    const range = sheetData.range || _config.range;
    const headerRows =
      (sheetData.header && sheetData.header.rows) ||
      (_config.header && _config.header.rows);
    const headerRowToKeys =
      (sheetData.header && sheetData.header.rowToKeys) ||
      (_config.header && _config.header.rowToKeys);

    let strictRangeColumns;
    let strictRangeRows;
    if (range) {
      strictRangeColumns = {
        from: getCellColumn(getRangeBegin(range)),
        to: getCellColumn(getRangeEnd(range)),
      };

      strictRangeRows = {
        from: getCellRow(getRangeBegin(range)),
        to: getCellRow(getRangeEnd(range)),
      };
    }

    let rows = [];
    for (let cell in sheet) {
      // !ref is not a data to be retrieved || this cell doesn't have a value
      if (
        cell === "!ref" ||
        (sheet[cell].v === undefined &&
          !(_config.sheetStubs && sheet[cell].t === "z"))
      ) {
        continue;
      }

      const row = getCellRow(cell);
      const column = getCellColumn(cell);

      // Is a Header row
      if (headerRows && row <= headerRows) {
        continue;
      }

      // This column is not _configured to be retrieved
      if (columnToKey && !(columnToKey[column] || columnToKey["*"])) {
        continue;
      }

      // This cell is out of the _configured range
      if (
        strictRangeColumns &&
        strictRangeRows &&
        (column < strictRangeColumns.from ||
          column > strictRangeColumns.to ||
          row < strictRangeRows.from ||
          row > strictRangeRows.to)
      ) {
        continue;
      }

      const rowData = (rows[row] = rows[row] || {});
      let columnData =
        columnToKey && (columnToKey[column] || columnToKey["*"])
          ? columnToKey[column] || columnToKey["*"]
          : headerRowToKeys
          ? `{{${column}${headerRowToKeys}}}`
          : column;

      let dataVariables = columnData.match(/{{([^}}]+)}}/g);
      if (dataVariables) {
        dataVariables.forEach((dataVariable) => {
          let dataVariableRef = dataVariable.replace(/[\{\}]*/gi, "");
          let variableValue;
          switch (dataVariableRef) {
            case "columnHeader":
              dataVariableRef = headerRows
                ? `${column}${headerRows}`
                : `${column + 1}`;
            // break;
            default:
              variableValue = getSheetCellValue(sheet[dataVariableRef]);
          }
          columnData = columnData.replace(dataVariable, variableValue);
        });
      }

      if (columnData === "") {
        continue;
      }

      rowData[columnData] = getSheetCellValue(sheet[cell]);

      if (sheetData.appendData) {
        extend(true, rowData, sheetData.appendData);
      }
    }

    // removing first row i.e. 0th rows because first cell itself starts from A1
    rows.shift();

    // Cleaning empty if required
    if (!_config.includeEmptyLines) {
      rows = rows.filter((v) => v !== null && v !== undefined);
    }

    return rows;
  };

  const convertExcelToJson = function (config = {}) {
    _config = config.constructor === String ? JSON.parse(config) : config;

    // ignoring empty lines by default
    _config.includeEmptyLines = _config.includeEmptyLines || false;

    // source has to be defined and should have a value
    if (!(_config.source)) {
      throw new Error(":: 'source' required for _config :: ");
    }

    let workbook = XLSX.read(_config.source, {
      type: "array",
    });

    let sheetsToGet =
      _config.sheets && _config.sheets.constructor === Array
        ? _config.sheets
        : Object.keys(workbook.Sheets).slice(
            0,
            (_config && _config.sheets && _config.sheets.numberOfSheetsToGet) ||
              undefined
          );

    let parsedData = {};
    sheetsToGet.forEach((sheet) => {
      sheet =
        sheet.constructor === String
          ? {
              name: sheet,
            }
          : sheet;

      parsedData[sheet.name] = parseSheet(sheet, workbook);
    });

    return parsedData;
  };

  return convertExcelToJson;
})();

export default excelToJson;

//The MIT License (MIT)
// Copyright (c) 2015 INFOinvest http://infoinvest.com.br

You will be needing two dependencies to run the code

xlsx
node.extend

Write the following command to install them

npm install xlsx node.extend

Now we are ready to process the data we recieved.
I'm creating a function called convertExcelToObject for converting the binary data to Uint8Array which our package uses to convert the data to a javascript object.

const convertExcelToObject = (file) => {
    const reader = new FileReader();
    reader.onload = function (event) {
      const data = new Uint8Array(event.target.result);
      let result = excelToJson({ source: data });
      props.onUploadExcelFile(result.Sheet1);
    };
    reader.readAsArrayBuffer(file);
  };

In this function we are converting the file to Uint8Array type and passing it into the excelToJson function that we exported from excelToJson.js. The result is lifted up to our parent component App.js so that we can display the given data.

The object looks like:

{
  Sheet1: [
      {
        A: 'data of cell A1',
        B: 'data of cell B1',
        C: 'data of cell C1'
      },
      {
        A: 'data of cell A2',
        B: 'data of cell B2',
        C: 'data of cell C2'
      }
   ]
}

And we are passing the file to convertExcelToObject through our functions we defined earlier.

  const dropHandler = (event) => {
    event.preventDefault();
    const file = event.dataTransfer.files[0];
    convertExcelToObject(file);
  };

  const uploadFileHandler = (event) => {
    const file = event.target.files[0];
    convertExcelToObject(file);
  };

So finally our UploadFile.js looks like:

import "./UploadFile.css";
import excelToJson from "./excelToJson";

const UploadFile = (props) => {
  const convertExcelToObject = (file) => {
    const reader = new FileReader();
    reader.onload = function (event) {
      const data = new Uint8Array(event.target.result);
      let result = excelToJson({ source: data });
      props.onUploadExcelFile(result.Sheet1);
    };
    reader.readAsArrayBuffer(file);
  };
  const dropHandler = (event) => {
    event.preventDefault();
    const file = event.dataTransfer.files[0];
    convertExcelToObject(file);
  };

  const uploadFileHandler = (event) => {
    const file = event.target.files[0];
    convertExcelToObject(file);
  };

  const dragOverHandler = (event) => {
    event.preventDefault();
  };

  return (
    <div className="uploadFile">
      <label>Upload your Excel file:</label>
      <div>
        <label onDrop={dropHandler} onDragOver={dragOverHandler} htmlFor="file">
          <div>
            <input
              onChange={uploadFileHandler}
              id="file"
              type="file"
              accept=".xlsx, .xls, .csv"
            />
            <div>or drop excel files here</div>
          </div>
        </label>
      </div>
    </div>
  );
};

export default UploadFile;

UploadFile.css:

.uploadFile > label {
  font-size: 1.4em;
}

.uploadFile > div {
  background-color: rgb(0, 211, 148);
  height: 11em;
  margin-top: 1em;
}

.uploadFile > div > label {
  border: 2px solid black;
  height: 98%;
  cursor: pointer;
  border-style: dashed;
  display: flex;
  justify-content: center;
}

.uploadFile > div > label > div {
  display: flex;
  justify-content: center;
  flex-direction: column;
}

.uploadFile input {
  margin-top: 1em;
  width: 13.2em;
}

.uploadFile input + div {
  text-align: center;
  margin-top: 0.6em;
  margin-bottom: 7px;
}

.uploadFile input::file-selector-button {
  width: 11em;
  height: 2.5em;
  border: none;
  border-radius: 5px;
  font-size: 16px;
  color: rgb(51, 51, 51);
  background-color: white;
  cursor: pointer;
  font-weight: 600;
  text-transform: uppercase;
}

.uploadFile input::file-selector-button:hover {
  background-color: rgb(235, 235, 235);
  transition: all 0.1s ease-in-out;
}

.uploadFile input::file-selector-button:active {
  background-color: rgb(214, 214, 214);
  transition: all 0.2s ease-in-out;
}

And our upload part looks like:

Pretty cool right!!😉

Step 3: Storing and Displaying the data:

Now let's go to our App.js file which looks like

import { useState } from "react";
import "./App.css";
import DisplayData from "./DisplayData";
import UploadFile from "./UploadFile";

function App() {
  const [uploadedExcelData, setUploadedExcelData] = useState([]);
  const uploadedExcelDataHandler = (data) => {
    setUploadedExcelData(data);
  };
  return (
    <div className="container">
      <UploadFile onUploadExcelFile={uploadedExcelDataHandler} />
      <DisplayData excelData={uploadedExcelData} />
    </div>
  );
}

export default App;

App.css:

.container {
  padding: 1.5em 3em;
}

Here we are receiving the data we lifted in UploadFile.js and storing it in a state and passing the data to the DisplayData.js file.

Inside first we have to make sure it doesn't if it finds no data to display by adding this:

if (!props.excelData.length) {
    return <div className="noFileContainer">No File Uploaded</div>;
}

And finally we are using the data we recieved and displaying the data we got in a table:

DisplayData.js:

import "./DisplayData.css";

const DisplayData = (props) => {
  if (!props.excelData.length) {
    return <div className="noFileContainer">No File Uploaded</div>;
  }
  const table = props.excelData;
  const tableBody = table?.slice(1);
  const tableHead = table[0];
  const keys = Object.keys(tableHead);

  return (
    <div className="displayData">
      <table>
        <thead>
          <tr>
            {keys.map((key) => (
              <th>{tableHead[key]}</th>
            ))}
          </tr>
        </thead>
        <tbody>
          {tableBody.map((row) => (
            <tr>
              {keys.map((key) => (
                <td>{row[key]}</td>
              ))}
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
};

export default DisplayData;

DisplayData.css:

.displayData {
  margin-top: 2em;
  display: flex;
  justify-content: center;
}

.displayData thead th {
  text-align: left;
  transition: all 0.2s ease-in-out;
}

.displayData table {
  background-color: white;
  width: 100%;
  padding-top: 1em;
  border-spacing: 1px;
  border-collapse: collapse;
}

.displayData td,
.displayData th {
  border: 0.5px solid rgb(0, 0, 0);
  padding: 8px;
}

.displayData tr:nth-child(even) {
  background-color: #f2f2f2;
}

.displayData th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: rgb(0, 211, 148);
  color: white;
}

.noFileContainer {
  text-align: center;
  margin-top: 2em;
}

And that's it!!

I've created a excel file Test.xlsx to test our app which contains the following table:

Initially our app looks like this:

Now I can drag and drop our file or else I can open the file through our app. After uploading our Test.xlsx:

So that's it!! We did it!!🥳
I know I didn't go in depth since I wanted this to be crisp and short, not confusing.

I hope you learnt how to handle excel files using React. This is my first post, so suggestions are most welcomed 🙏.

DEV Community: Emmanuel David