NodeJS Add New Rows To Excel File (Append Prepend Insert)

Welcome to a tutorial on how to add new rows to an existing Excel file in NodeJS. So you need to append, prepend, or insert a new row into an Excel file in NodeJS? Read on for the examples!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

QUICK SETUP

For the examples, we will be using the SheetJS library – npm i xlsx

 

1) APPEND NEW ROW

// (A) LOAD SHEETJS
const xlsx = require("xlsx");

// (B) LOAD EXISTING WORKSHEET
var workbook = xlsx.readFile("x-dummy.xlsx"),
    worksheet = workbook.Sheets[workbook.SheetNames[0]];

// (C) APPEND ROW
xlsx.utils.sheet_add_aoa(worksheet, [["NEW", "ROW"]], {origin: -1});

// (D) SAVE UPDATED
xlsx.writeFile(workbook, "demoA.xlsx");

This should not be too much trouble.

1. Load the SheetJS library.
2. Load the existing Excel file/worksheet.
3. Use xlsx.utils.sheet_add_aoa() to append the new row, origin: -1 refers to the “last row”.
4. Save the updated worksheet, Captain Obvious at your service.

 

 

2) PREPEND NEW ROW

// (A) LOAD SHEETJS
const xlsx = require("xlsx");

// (B) LOAD EXISTING WORKSHEET
var workbook = xlsx.readFile("x-dummy.xlsx"),
    worksheet = workbook.Sheets[workbook.SheetNames[0]];
    data = xlsx.utils.sheet_to_json(worksheet, {header: 1})

// (C) PREPEND NEW ROW
delete(worksheet); delete(workbook);
data.splice(0, 0, ["NEW", "ROW"]);

// (D) SAVE UPDATED
workbook = xlsx.utils.book_new();
worksheet = xlsx.utils.aoa_to_sheet(data),
xlsx.utils.book_append_sheet(workbook, worksheet);
xlsx.writeFile(workbook, "demoB.xlsx");

Now, some of you guys may be thinking – Why not just use xlsx.utils.sheet_add_aoa(WORKSHEET, DATA, {origin:ROW})? At the time of writing, that will replace the row instead of insert. Yes, I cannot find an option to “insert” in the free version of SheetJS, thus this “stupid” way:

• (A & B) Read the entire worksheet into an array.
• (C) We prepend the new row by pushing it to the top of the array.
• (D) Save the updated worksheet.

P.S. If anyone has a better solution, feel free to share in the comments below.

 

 

3) INSERT NEW ROW

// (A) LOAD SHEETJS
const xlsx = require("xlsx");

// (B) LOAD EXISTING WORKSHEET
var workbook = xlsx.readFile("x-dummy.xlsx"),
    worksheet = workbook.Sheets[workbook.SheetNames[0]];
    data = xlsx.utils.sheet_to_json(worksheet, {header: 1})

// (C) INSERT AT EXACT ROW
delete(worksheet); delete(workbook);
data.splice(2, 0, ["NEW", "ROW"]);

// (D) SEARCH FOR "INSERT LOCATION"
var at = null;
for (let [i,r] of Object.entries(data)) {
  if (r.includes("Jon Doe")) { at = i; break; }
}
if (at !== null) { data.splice(at, 0, ["ANOTHER", "ROW"]); }

// (E) SAVE UPDATED
workbook = xlsx.utils.book_new();
worksheet = xlsx.utils.aoa_to_sheet(data),
xlsx.utils.book_append_sheet(workbook, worksheet);
xlsx.writeFile(workbook, "demoC.xlsx");

Lastly, if you want to insert a row:

• (A & B) As in the previous example, we read the entire workbook into an array.
• (C) Simply use ARRAY.splice(AT, REMOVE, DATA) to insert a row.
• (D) If you do not know the exact location to insert the new row, the only way is to look through all the rows.
• (E) Save the updated Excel file.

 

 

 

NOTES ON PERFORMANCE

Yes, we are reading the entire Excel file into the memory. While most modern systems can handle large files without much issue, there is still something called “limited system resources”. So when it comes to large Excel files, do your own due diligence to handle them properly:

• Find some ways to split it into multiple worksheets/workbooks.
• Or just disallow massive Excel files.

 

LINKS & REFERENCES

• Display Excel In HTML Table – Code Boxx
• SheetJS Documentation