Welcome to a tutorial on how to add rows to an existing Excel file in Jascript. 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 Jascript directly. Read on for the examples!
TABLE OF CONTENTS
Download & Notes
Excel Add Rows
Extras
The End
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t he to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Source code on GitHub Gist | Example on CodePen
Just click on “download zip” or do a git clone. I he 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
ADD ROWS TO EXCEL FILE
All right, let us now get into the examples of how to append, prepend, and insert rows to an existing Excel file in Jascript.
THE HTML 1-demo.html There are no native functions in Jascript 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 2-append.js 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) Se and force download the updated Excel file.
PREPEND ROWS 3-prepend.js 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) SE 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 he 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 4-insert.js 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) SE 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.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
NOTES ON PERFORMANCE
Please take note that we are reading the entire Excel file into the memory. While most modern devices he 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 Jascript – Code Boxx
THE END
Thank you for reading, and we he come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!