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" });

Answer 1

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

Answer 2

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.

Answer 3

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');