CODEWITHSUNDEEP
node.jsexcelappendbuffer
Syed Faizan
Syed Faizan

Reputation: 961

how to append to a excel file in nodejs

i am currently working on a form which will take user input and add it to one row of an excel sheet, as of now i have managed to make excel sheet using 3rd party plugins(node-xls to be specific). issue arises if i wanna add another row to the excel , it deletes the old entry and adds the new instead of appending the data to the next row.

tried reading the excel and then concatenating the buffers and writing the file again, but turns out it is corrupting the file and is rendering it unusable.

How do i append data to the end of the excel sheet? i am new to nodejs and buffers

var fs = require('fs');
var NodeXls = require('node-xls');
var tool = new NodeXls();
var xls = tool.json2xls({firstName: "arjun", lastName: "u", dob:"12/3/2008"}, {order:["firstName", "lastName", "dob"]});
fs.appendFile('output.xlsx', xls, 'binary', function(err){
if(err)
  alert("File is readOnly or is being used by another application,  please close it and continue!");
});

Upvotes: 9

Views: 15342

Answers (4)

Mohit Gupta
Mohit Gupta

Reputation: 323

Use below code for the same -

const fs = require('fs');
const xlsx = require('xlsx');

function appendFromExcelToExcel(filePath, data) {
  try {
    const workbook = xlsx.readFile(filePath);

    const sheetName = 'Sheet1';
    const worksheet = workbook.Sheets[sheetName] || xlsx.utils.aoa_to_sheet([]);

    const lastRow = worksheet['!ref'].match(/R(\d+)/)[1]; 

    const newRow = xlsx.utils.aoa_to_sheet([data]);

    xlsx.utils.sheet_add_json(worksheet, newRow, { origin: `A${lastRow}` });
    xlsx.writeFile(workbook, filePath);

    console.log('appended success!');
  } catch (error) {
    console.error('Error appending :', error);
  }
}


const filePath = 'myData.xlsx'; 
const newData = ['New Data 1', 'New Data 2']; 

appendFromExcelToExcel(filePath, newData);

Upvotes: 0

naresh kaktwan
naresh kaktwan

Reputation: 11

It will add one row at a time

var spread_sheet = require('spread_sheet');
var row = "1,2,Jack,Pirate";
var filePath = '/home/Pranjal/Desktop/test.xlsx';
var sheetName = "Sheet1";

spread_sheet.addRow(row,filePath,sheetName,function(err,result){
    console.log(err,result)
})

Upvotes: 0

Pranjal Sharma
Pranjal Sharma

Reputation: 81

Install npm module 'spread_sheet', it will definitely resolve the issues of adding row as well as reading rows from spreadsheet from any sheet.

Upvotes: 0

Mohit Gupta
Mohit Gupta

Reputation: 323

Have you tried Exceljs. It helps in Read, manipulate and write spreadsheet data and styles to XLSX and JSON.

check on the below link for details

https://www.npmjs.com/package/exceljs

Your problem of adding rows or append rows is solved here:

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});

var rowValues = [];
rowValues[1] = 4;
rowValues[5] = 'Kyle';
rowValues[9] = new Date();
worksheet.addRow(rowValues);

// Add an array of rows 
var rows = [
    [5,'Bob',new Date()], // row by array 
    {id:6, name: 'Barbara', dob: new Date()}
];
worksheet.addRows(rows);

Upvotes: 6

Related Questions