0
453
VistasHow to dynamically populate an Excel file using exceljs in express?
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...
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
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);
·
Juan Pablo Isaza
Denunciar
Responde la pregunta