Javascript Add Rows To Excel File (Append Prepend Insert)

Welcome to a tutorial on how to add rows to an existing Excel file in Javascript. Need to append, prepend, or insert new rows into an Excel file? There’s no need to upload the Excel file to the server, we can do it in modern Javascript directly. Read on for the examples!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist | Example on CodePen

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

 

 

 

THE HTML

<!-- https://cdnjs.com/libraries/xlsx -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

<form onsubmit="return demo()">
  <input id="picker" type="file" accept=".xls,.xlsx" required>
  <input type="submit" value="Go">
</form>

• There are no native functions in Javascript that can work with Excel files, so we will use a library called SheetJS.
• Otherwise, there is nothing “special” here. Just a regular HTML file picker to choose an Excel file.

 

APPEND ROWS

function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();
 
  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) APPEND NEW ROW & FORCE DOWNLOAD
    XLSX.utils.sheet_add_aoa(worksheet, [["NEW", "ROW"]], {origin: -1});
    XLSX.writeFile(workbook, "demoA.xlsx");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
}

To append rows to the Excel file:

• (A) Get the HTML file picker and create a file reader object – reader = new FileReader().
• (B) Use the file reader object to read the selected Excel file – reader.readAsArrayBuffer(picker.files[0])
• (B1) When the file is loaded, we open the first worksheet.
• (B2) Use XLSX.utils.sheet_add_aoa(WORKSHEET, DATA, {origin: -1}) to append rows.
• (B2) Save and force download the updated Excel file.

 

 

PREPEND ROWS

function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();

  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) PREPEND NEW ROW
    var data = XLSX.utils.sheet_to_json(worksheet, {header: 1})
    delete(worksheet); delete(workbook);
    data.splice(0, 0, ["NEW", "ROW"]);

    // (B3) 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");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
}

Some of you smart code ninjas should have noticed origin: -1 above, and thought that origin: 1 will prepend a new row. Yes, that works. But that will replace the first row instead of inserting a new one… There is seemingly no way to “insert row” in the free version of SheetJS, thus this “stupid roundabout method”:

• (A & B) The basics are the same – Create a file reader and open the selected Excel file.
• (B2) Read the entire worksheet into a data array, then use data.splice(0, 0, [ROW]) to prepend.
• (B3) Use data to create a new worksheet and force download.

 

 

INSERT ROWS

function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();

  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) INSERT AT EXACT ROW
    var data = XLSX.utils.sheet_to_json(worksheet, {header: 1})
    delete(worksheet); delete(workbook);
    data.splice(2, 0, ["NEW", "ROW"]);

    // (B3) SEARCH & INSERT
    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"]); }

    // (B4) 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");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
}

Finally, if you want to insert a new row “somewhere in the middle”:

• (A & B) All the basics are the same – Open the selected Excel file, and read into an array.
• (B2) If you know exactly where to insert, use data.splice(ROW NUMER, 0, [ROW]) to insert.
• (B3) If you don’t know where to insert, the only way is to loop through the entire worksheet.
• (B4) Use data to create a new worksheet and force download.

 

 

 

NOTES ON PERFORMANCE

Please take note that we are reading the entire Excel file into the memory. While most modern devices have plenty of memory, there are still limited system resources. Avoid loading massive Excel files, or implement some restrictions on the file size on your own – if (picker.files[0].size > N) { ERROR TOO BIG! }

 

LINKS & REFERENCES

• Display Excel File In HTML Table – Code Boxx
• Create Excel File In Javascript – Code Boxx