How To Create JavaScript Objects From an Excel File

If you're like me, you might like to use excel files occasionally to keep track of certain data. It is, after all, a pretty universal standard for keeping track of numbers and having a visual interface to append to and manipulate said numbers. Well it might surprise you to know that there is a tool that can be installed with a single npm command that allows you to read data from an excel file. I will be showing in this blog how to do this, as well as how to create an object and add insert that object into a SQL database.

There are a lot of reasons this could be useful. For example, suppose a teacher has their student's grades saved in excel files and you wanted to import them into a JS app automatically. You might have a spreadsheet that looks something like this:

To get started, enter this command into your terminal (You will need Node installed as well. I am using VS code)

npm install read-excel-file

Now, in your JS file, just add this require statement to the top:

const xlsxFile = require('read-excel-file/node');

And that's the entire setup! The xlsxFile function takes a single argument—the path to your excel file as a string. The file path will be relative to the JS file that the xlsxFile was "required" on. So if you have an excel file named 'ExcelFile.xlsx' and it is in the same folder, the path would look something like: './ExcelFile.xlsx'

A successful call of the xlsxFile function will return a promise that contains an array of all the rows of your excel file. Each of those rows are also arrays which contain the value of each cell. So to bring it all together:

const xlsxFile = require('read-excel-file/node');

xlsxFile('./ExcelFile.xlsx')
  .then((rows) => {
    rows.forEach((row) => {
      row.forEach((cell) => {
        console.log(cell);
      });
    });
  });

The above function will display every single cell on the console, left to right top to bottom. Now, to manipulate that data into something more useful:

xlsxFile('./ExcelFile.xlsx')
  .then((rows) => {
    const columnNames = rows.shift(); // Separate first row with column names
    const objs = rows.map((row) => { // Map the rest of the rows into objects
      const obj = {}; // Create object literal for current row
      row.forEach((cell, i) => {
        obj[columnNames[i]] = cell; // Use index from current cell to get column name, add current cell to new object
      });
      return obj;
      console.log(objs); // Display the array of objects on the console
    });
  });

Now, each row has been converted to a JavaScript object using the first row as its key names, and every row after used to create an object with key values from their cells and key names of the name at the top of their columns—basically like an excel-to-JavaScript constructor function.

And finally, if you wanted to use an ORM like Sequelize to insert these into a database:

    xlsxFile('./ExcelFile.xlsx')
      .then(async (rows) => {
        const columnNames = rows.shift();
        const promises = rows.map((row) => { // Map the rows array into an array of promises that each create an entry in the DB
          const obj = {};
          row.forEach((cell, i) => {
            obj[columnNames[i]] = cell;
          });
          return Grade.create(obj); // 'Grade' is a hypothetical Sequelize model where the grades can be saved
        });
        await Promise.all(promises); // Use Promise.all to execute all promises
      });

And that's the tutorial! It should be noted that are limitations to the size of the .xlsx files that can be imported, although they are not explicitly stated by the developer. While this may not be the absolute most practical method of handling data to be switching back and forth between Excel and JavaScript data, it can certainly be useful for small projects and save a lot of time if you needed to update an older data set and import it for a JavaScript project.
More information about this app and its developer are available on GitHub here: https://gitlab.com/catamphetamine/read-excel-file

Comments

[Tobias Nickel]

can it choose what page in the excel file to load?

[Chris Shepherd]

CSV is the devil's file format. Given the opportunity whatever creates your CSV file will end up putting commas inside your data fields.
Excel is an open XML format that provides some degree of protection against junk in the file.