How to append new row to existing sheet in xlsm workbook in SheetJS

Question

I am trying to append new rows into an existing Excel sheet but no luck.

When I use writeFile instead of write function, then it creates a whole new file with all the data, but I want to append the rows to the existing records.

This is the code till now. I am using SheetJs.

const reader = require('xlsx')
const wb = reader.readFile('./plan.xlsm')    
const sheets = wb.SheetNames
var xl_data = reader.utils.sheet_add_json(wb.Sheets[sheets[0]], [
{ A: 4, B: 5, C: 6 }], {header: ["A", "B", "C"], skipHeader: true, origin: "A31"});
reader.utils.book_append_sheet(wb, xl_data);
reader.write(wb, {type: "binary", bookType: "xlsm" });

score 1 · Answer 1 · answered Oct 03 '21 at 12:54

You can append new row to existing sheet with free version of 'xlsx'

const workbook = XLSX.readFile(serverPath);
const firstSheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[firstSheetName];

const cellRef = XLSX.utils.encode_cell({c: YourColumn, r: YourRow});
const cell = sheet[cellRef];
if (cell) {
    // update existing cell
    cell.v = 'YourValue';
} else {
    // add new cell
    XLSX.utils.sheet_add_aoa(sheet, [['YourValue']], {origin: cellRef});
}
XLSX.writeFile(workbook, path);

If your question really about update file instead of write, see @Jesper answer

score 0 · Answer 2 · answered Jul 27 '21 at 06:50

0

To edit files files with Sheetjs you would need the "Pro Edit" version. If you need to edit files you can buy the pro version or try to find another library.

answered Jul 27 '21 at 06:50

Jesper

1,007
7
24

@PrasannaKumarSR It seems that the library i use might work for this: [xlsx-js-style](https://github.com/gitbrent/xlsx-js-style/). It's a fork of some other libraries that include reading, editing and styling like this: [js-xlsx](https://github.com/protobi/js-xlsx/tree/beta#readme) – Jesper Jul 28 '21 at 06:53
this looks great. Thank you. – Prasanna Kumar S R Jul 29 '21 at 07:15

JavierQuispe · Answer 3 · 2023-04-22T21:28:52.017

for someone who wants to add a row at the beginning of the spreadsheet data json

const workbook = XLSX.utils.book_new();
const worksheet = XLSX.utils.json_to_sheet(jsonData,{ origin:1});
XLSX.utils.sheet_add_aoa(
  worksheet,
  [['Created ' + new Date().toISOString()]],
  { origin: 0 }
);
//If you want this record to go in the last row change {origin:-1 }
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
XLSX.writeFile(workbook, 'DataSheet.xlsx');

How to append new row to existing sheet in xlsm workbook in SheetJS

3 Answers3