Unable to write data (addRow) into Excel file in Nodejs

Question

I'm trying to write data into an Excel file using ExcelJS library. I was successfully able to create worksheet and add column data.

However, while trying to implement addRow() or addRows() method, the data is not added into the Excel worksheet.

Here is the code I tried:

 const ExcelJS = require('exceljs');
 var workbook = new ExcelJS.Workbook();
 var worksheet = workbook.addWorksheet('Payment Data');
 worksheet.columns = reportHeaders; //reportHeaders is an array of header objects

I'm able to see the columns created successfully in the excel sheet. The trouble starts from below, where I'm trying to add data (into rows):

1st method :

  worksheet.addRows(excelData);//excelData is an array of data objects

2nd method:

  for(var rowItem in excelData){
  worksheet.addRow(excelData[rowItem]);}

However, it seems either of these methods aren't working for me. Finally, the file is saved:

  workbook.xlsx.writeFile('PaymentData.xlsx')

Is there anything I'm missing? Any help will be appreciated.

can you please create an online working demo for the project on the site like https://repl.it/languages/nodejs that would be much helpful. — Vibhor, Jul 11 '20 at 12:06
tried something similar over https://repl.it/@ShameesIbn/OblongWeeklyDribbleware#index.js — Shamees Ibn Yousaf, Jul 11 '20 at 12:59
Shamees you can mark the answer as the accepted answer if that worked for you. — Vibhor, Jul 13 '20 at 10:51

score 9 · Accepted Answer · answered Jul 11 '20 at 16:42

9

So the issue with your code was, you were trying to add the data to the columns without specifying the key property in the columns array and hence it was unable to add the data.

I modified the worksheet.columns array to look something like the following:

worksheet.columns = [
    { header: "A", key: "a" },
    { header: "B", key: "b" },
];

This will solve your problem

answered Jul 11 '20 at 16:42

Vibhor

535
4
14

Marking this answer as correct (partly); In addition to adding `key` property; while assigning values to `excelData` array I was assigning it wrong. The assignment of value should be made to the `key` property instead of column `header`. What I was doing: `excelData[row].headerName = value;` What I have corrected: `excelData[row].key = value;` (key is the value of `key` property corresponding to the headerName) – Shamees Ibn Yousaf Jul 15 '20 at 07:43

score 1 · Answer 2 · answered Jul 11 '20 at 14:29

1

I managed to convert the object to an array and passed it to the addRow method. This worked for me.

I'm still not sure why I'm not able to pass an array of objects to addRow method.

answered Jul 11 '20 at 14:29

Shamees Ibn Yousaf

103
2
9

1

Because you didn't define keys for your headers so it didn't know what column each object field would map to. See Vibhor's answer – CherryDT Jul 11 '20 at 16:46

Unable to write data (addRow) into Excel file in Nodejs

2 Answers2