'ExcelJS. Can i create sublevels with this Lib?
Grouping with XLSX writer on Node JS Is it possible to repeat such grouping with ExcelJS?
I found an example on codepen, but the grouping is not the one I need. Is it possible to somehow modify this code to get a multilevel grouping? Or tell me another library (namely CDN), which I can use to make such a grouping like on screen.
function RunExcelJSExport() {
let data = [
{
'salesman-name': "Jim Smith",
sales: 12345,
uri: "https://www.google.com",
'met target': true,
status: "Employee",
dob: new Date(Date.UTC(1950, 0, 1, 0, 0, 0)),
level: 1.1,
comments:
"This is a <b>longish</b> string of text that is intended to show wrapping. The width of the column is set to 30 so the text can't fit. It also includes some HTML that will be <i>removed</i> and HTML character encodings that will be <i>replaced</i> before export!!"
},
{
'salesman-name': "Mark Thompson",
sales: 1200,
'met target': false,
status: "Employee",
dob: new Date(Date.UTC(1961, 11, 24, 0, 0, 0)),
level: 1.3,
comments: ''
},
{
'salesman-name': "Susan Hamilton",
sales: 67001,
'met target': true,
status: "Freelance",
dob: new Date(Date.UTC(1970, 5, 6, 0, 0, 0)),
level: 1.0
},
{
'salesman-name': "Ian Hepburn",
sales: -500,
'met target': false,
status: "Freelance",
dob: new Date(Date.UTC(1972, 2, 23, 0, 0, 0)),
level: 1.0
},
{
'salesman-name': "Michael McMichaels",
sales: 34124,
'met target': true,
status: "Employee",
dob: new Date(Date.UTC(1980, 0, 9, 0, 0, 0)),
level: 3.2
}
];
let wb = new ExcelJS.Workbook();
let workbookName = "Sample Workbook Generated by ExcelJS.xlsx";
let worksheetName = "Demo Worksheet";
let worksheetName2 = "2nd Demo Worksheet";
let ws = wb.addWorksheet(worksheetName,
{
properties: {
tabColor: {argb:'FFFF0000'}
}
}
);
let ws2 = wb.addWorksheet(worksheetName2);
ws.columns = [
{
key: "salesman-name",
header: "Salesman-Name",
width: 20
},
{
key: "sales",
header: "Sales",
width: 15,
style: { numFmt: '"£"#,##0.00;[Red]-"£"#,##0.00' }
},
{
key: "uri",
header: "URI",
width: 30,
outlineLevel: 1 ,
hidden: false
},
{
key: "met target",
header: "Met Target?",
width: 12,
style: {
alignment: { horizontal: "center" },
font: { color: { argb: "008000" } }
}
},
{
key: "status",
header: "Status"
},
{
key: "dob",
header: "Date of Birth",
width: 12,
style: { numFmt: "dd/mm/yyyy" }
},
{
key: "level",
header: "Level",
width: 5,
style: { numFmt: "0.0" }
},
{
key: "comments",
header: "Comments",
width: 30,
style: { alignment: { wrapText: true }, numFmt: '@' },
outlineLevel: 1 ,
hidden: false
},
{
key: "dob_linked",
header: "Date of Birth (Linked and Formatted)",
width: 35,
style: { numFmt: "dddd, MMMM dd, yyyy" },
outlineLevel: 2,
hidden: false
},
];
ws.getRow(1).font = { bold: true };
ws.getCell("B1").alignment = { horizontal: "right" };
ws.getCell('F1').alignment = { textRotation: 90 };
ws.views = [{ state: "frozen", ySplit: 1 }];
ws.addRows(data);
ws.getCell("C2").value = {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "Click to go to google.com"
};
ws.getCell("C2").font = {
color: { argb: "0000FF" },
underline: true
};
let totCell = "B" + (data.length + 2);
let totFormula = "SUM(B2:B" + (data.length + 1) + ")";
ws.getCell(totCell).value = { formula: totFormula };
ws.getCell(totCell).border = {
top: { style: "thin" },
bottom: { style: "double" }
};
// We can name this cell to make it easier to reference later
ws.getCell(totCell).name = 'salestotal';
let col = ws.getColumn("D");
col.eachCell(function(cell, rowNumber) {
if (rowNumber > 1) {
if (cell.value) {
cell.value = "\u2713";
} else {
cell.value = null;
}
}
});
col = ws.getColumn("E");
col.eachCell(function(cell, rowNumber) {
if (rowNumber > 1) {
cell.dataValidation = {
type: "list",
allowBlank: false,
formulae: ['"Employee,Freelance"']
};
}
});
col = ws.getColumn("F");
col.eachCell(function(cell, rowNumber) {
if (rowNumber > 1) {
cell.dataValidation = {
type: 'date',
operator: 'lessThan',
showErrorMessage: true,
allowBlank: false,
formulae: [new Date(1981,1,1)],
errorStyle: 'error',
errorTitle: 'Date is too soon!',
error: 'The dob value must be before 01/01/1981'
};
}
});
// Remove all HTML from the comments field
col = ws.getColumn("H");
col.eachCell(function(cell, rowNumber) {
if (cell.value !== null && String(cell.value).trim() !== '') {
let doc = new DOMParser().parseFromString(cell.value, "text/html");
cell.value = doc.documentElement.textContent;
}
});
col = ws.getColumn("I");
col.eachCell(function(cell, rowNumber) {
if (rowNumber > 1) {
cell.value = {formula: 'F' + rowNumber};
}
});
// Reference cell by rownum, colnum
ws.getCell(5,1).fill = {
type: "pattern",
pattern: "solid",
fgColor: { argb: "F3FF33" }
};
ws.getCell("G4").value = {
richText: [
{ text: "This " },
{ font: { italic: true }, text: "is" },
{
font: {
size: 14,
color: { argb: "FF00FF00" },
bold: true
},
text: "rich"
},
{ font: { underline: true }, text: " text!" }
]
};
// Merge cells
ws.mergeCells('A15:I17');
ws.getCell('A15').style = {
font: {
size: 20,
bold: true
},
alignment: {
horizontal: 'center',
vertical: 'middle',
wrapText: true
}
};
ws.getCell('A15').value = 'This is merged cells (A15:I17), with large bold text, vertically and horizontally aligned';
ws.getRow(15).outlineLevel = 1;
ws.getRow(16).outlineLevel = 1;
ws.getRow(17).outlineLevel = 1;
ws.getColumn('I').hidden = true;
ws.getRow(4).hidden = true;
// Retrieve a named cell value from another worksheet
ws2.getColumn('A').width = 25;
ws2.getCell('A3').value = 'Value from 1st sheet:';
ws2.getCell('B3').value = { formula: 'salestotal' };
console.log(ws.getRow(5).getCell(9)._address);
wb.xlsx.writeBuffer()
.then(function(buffer) {
saveAs(
new Blob([buffer], { type: "application/octet-stream" }),
workbookName
);
});
}
<input type="button" value="Export to Excel" onclick="RunExcelJSExport()"></input>
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
