In my nodejs project I have used sheetjs-style for generating a styled xlsx file. I just wanted to add a set of logo images in the excel.This is not supported in community edition of sheetjs. I tried using exceljs on top of the node buffer generated from sheetjs-style. But the resulting file is corrupted and is even losing some merged cells.
The version of node packages used are as below:
"dependencies": {
"exceljs": "^4.3.0",
"sheetjs-style": "^0.15.8"
}
Please find below the sample code which simulates the issue:
const XLSX = require('sheetjs-style');
const fs = require('fs');
var Readable = require('stream').Readable;
const Excel = require('exceljs');
const fileName = 'myexcel.xlsx';
const rows = [
{ name: "George Washington", birthday: "1732-02-22" },
{ name: "John Adams", birthday: "1735-10-19" },
{ name: "", birthday: "" },
{ name: "", birthday: "" },
{ name: "", birthday: "" },
];
const ws = XLSX.utils.json_to_sheet(rows, { origin: 'A1' });
const merge = [
{ s: { r: 4, c: 0 }, e: { r: 4, c: 2 } }
];
ws["!merges"] = merge;
ws["A5"].v = 'Merged cells';
ws["A5"].s = { // set the style for target cell
fill: {
fgColor: { rgb: "ffcccb" }
},
font: {
color: {
rgb: "008000"
}
},
};
ws["A1"].s = { // set the style for target cell
fill: {
fgColor: { rgb: "ffcccb" }
},
font: {
color: {
rgb: "008000"
}
},
};
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, ws, "Dates");
// XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });
const wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
// fs.writeFile(`./test.xlsx`, wbout,()=>console.log("Done writing"));
test(wbout).then(() => console.log("done"));
async function test(buf) {
const wb = new Excel.Workbook();
await wb.xlsx.load(buf);
const imageId = wb.addImage({
base64: 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD+wSzIAAAABlBMVEX///+/v7+jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII=',
extension: 'png',
});
const ws = wb.getWorksheet('Dates');
ws.addImage(imageId, 'A1:B1');
wb.xlsx
.writeFile(fileName)
.then(() => {
console.log('file created');
})
.catch(err => {
console.log(err.message);
});
}