How to dynamically populate an Excel file using exceljs in express?

Question

Right now I have the following function:

const generateXLSX =  (res, data) => {
    let baseFile = './src/utils/boop.xlsx';
    let wb = new Excel.Workbook();
    wb.xlsx.readFile(baseFile)

    .then (async () => {
        let ws = wb.getWorksheet(1);
        
        let row = ws.getRow(9);
        row.getCell(3).value = 'Simple and not so funny test';
        row.commit();
        res.header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        await wb.xlsx.write(res);
        res.end();
        })
};

This will edit my base Excel document and produce this:

The problem here is that I want to populate this template using a JSON Object like the following:

        "id": 1,
        "urlImagen": "http://placeimg.com/640/480",
        "name": "test national",
        "pdu": "53014",
        "creationDate": 2020,
        "appevel": "ascending",
        "ddlevel": "descending",
        "mapa": 1,
        "Module": "Lead",
        "sector": "Something"

As you can see it contains data that i don't want to render into the Excel. I want to implement a dynamic way to allocate the information without writing the same code like:

let row = ws.getRow(9);
row.getCell(3).value = 'Simple and not so funny test';
let row = ws.getRow(10);
row.getCell(3).value = 'Value 2';
let row = ws.getRow(11);
row.getCell(3).value = 'Value 3';

And so on, but I don't know how to implement an optimal way to solve this...

Answer 1

you need to loop the data and write it into desired cells.

try this

const generateXLSX = async(res, data) => {

    const baseFile = './src/utils/boop.xlsx';

    const wb = new Excel.Workbook();

    await wb.xlsx.readFile(baseFile);

    const ws = wb.getWorksheet(1);

    // loop and write data
    for (const [rowNum, inputData] of data.entries()) {

        console.log('row: ', rowNum, ', data', inputData);

        // increment rowNum to change the row start position if needed
        // for example, start at 5th row:
        // const row = ws.getRow(rowNum+6);
        const row = ws.getRow(rowNum + 1);

        // insert values
        row.getCell(1).value = inputData.pdu;
        row.getCell(2).value = inputData.name;
        row.getCell(3).value = inputData.appevel;
        
        row.commit();
    }

    const fileName = 'excel.xlsx';

    res.header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.header("Content-Disposition", "attachment; filename=" + fileName);

    await wb.xlsx.write(res);

    res.end();
};

// data format
const data = [{
    "id": 1,
    "urlImagen": "http://placeimg.com/640/480",
    "name": "test national",
    "pdu": "53014",
    "creationDate": 2020,
    "appevel": "ascending",
    "ddlevel": "descending",
    "mapa": 1,
    "Module": "Lead",
    "sector": "Something"
}];


generateXLSX(res, data);