CODEWITHSUNDEEP
javascriptnode.jsxlsxsheetjsjs-xlsx
Prasanna Kumar S R
Prasanna Kumar S R

Reputation: 143

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

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

Upvotes: 6

Views: 14831

Answers (3)

JavierQuispe
JavierQuispe

Reputation: 41

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

Upvotes: 2

Sandre
Sandre

Reputation: 756

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

Upvotes: 2

Jesper
Jesper

Reputation: 1076

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.

Upvotes: 0

Related Questions