CODEWITHSUNDEEP
javascriptnode.jsxlsxexceljs
user9597158
user9597158

Reputation:

Write data starting from a specific row/column using module exceljs with node.js

I want to write data to a xlsx file starting from a specific row/column. I've found a lot of websites about exceljs but none helps me.

for (let i = 0; i < people.length; i++) {
    let {name, age} = people[i]

    worksheet.addRow({name: name, age: age})
}

worksheet.eachRow(function(row, rowNumber) {
    row.eachCell(function(cell, colNumber) {
        // some code
    }
}

Upvotes: 1

Views: 22636

Answers (3)

Neurothustra
Neurothustra

Reputation: 297

I also ran up against this. You want to use insertRow, not addRow. If you already have your header defined with column keys, you can pass your data object along with a position number. From the documentation:

// Insert a couple of Rows by key-value, shifting down rows every time

worksheet.insertRow(1, {id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.insertRow(1, {id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});

that first number parameter (position) will insert the row and shift down any existing row, as opposed to overwriting what's already there. So if you wanted to, for example, insert a row at the bottom of your existing worksheet, you would use the number that indicates the desired position.

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

In the above example, the new row would be inserted at position 12 (the twelfth row down).

https://www.npmjs.com/package/exceljs#insert-rows

Upvotes: 0

Raymond Lu
Raymond Lu

Reputation: 2236

You could use Excel JS API, the code example shows how to write the data to a special row/column. the gist can be found at:https://gist.github.com/lumine2008/796915d73a56a9e89ff9393c77845759 

 await Excel.run(async (context) => {
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");

    const data = [
      ["Product", "Qty", "Unit Price", "Total Price"],
      ["Almonds", 2, 7.5, "=C3 * D3"],
      ["Coffee", 1, 34.5, "=C4 * D4"],
      ["Chocolate", 5, 9.56, "=C5 * D5"]
    ];

    const range = sheet.getRange("B2:E5");
    range.values = data;
    range.format.autofitColumns();

    const header = range.getRow(0);
    header.format.fill.color = "#4472C4";
    header.format.font.color = "white";

    sheet.activate();

    await context.sync();

Upvotes: 0

Zeeshan Hassan Memon
Zeeshan Hassan Memon

Reputation: 8325

I don't know other way but could managed it like this:

const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Node-Cheat');
// keep {} where you wan to skip the column
sheet.columns = [{}, {key: 'first', header: 'First'}, {}, {key: 'last', header: 'Last'}];
// keep {} where you wan to skip the row
const data = [{first:'John', last: 'Doe'}, {}, {first:'Jenny', last: 'Smith'}];

data.forEach((item, i) => {
  sheet.addRow(item);
});

workbook.xlsx.writeFile('node-cheat.xlsx').then(() => {
    console.log('Finished...');
});

For complete running example clone node-cheat and run node write_xlsx.js.

P.S. Using this approach requirements could be fulfilled as far as I understand.

Output screenshot:

enter image description here

Upvotes: 1

Related Questions