exceljs库实现excel表样式定制化
const exportTemplate2 = () => {
// 创建工作簿
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('sheet1');
// 定义列
worksheet.columns = [
{ header: 'Id11111111111111111111', key: 'id', width: 20 },
{ header: 'Name233', key: 'name', width: 20 },
{ header: 'D.O.B999', key: 'dob', width: 20 }
];
// 添加二级表头
worksheet.addRow(['二级表头A', '二级表头B', '二级表头C']);
// 填充数据
const columnData = [
{ id: 15, name: 99, dob: '2024-04-16' },
{ id: 50, name: 101, dob: '2024-04-17' }
];
columnData.forEach(item => {
worksheet.addRow(item);
});
// 设置第一行表头背景色和字体颜色
worksheet.getRow(1).eachCell({ includeEmpty: true }, (cell, colNumber) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: (colNumber === 1 || colNumber === 2) ? 'FF79bbff' : 'FF95d475' }
};
cell.font = {
size: 14,
color: { argb: 'FFFFFFFF' } // 白色
};
});
// 设置第二行表头字体颜色
worksheet.getRow(2).eachCell({ includeEmpty: true }, (cell) => {
cell.font = {
size: 14,
color: { argb: 'FFF89898' } // 浅红色
};
});
// 应用自动筛选
worksheet.autoFilter = 'A1:C2'; // 筛选整个表头区域
// 合并单元格
worksheet.mergeCells("A1:C1"); // 合并整个第一行
// 添加条件格式
worksheet.addConditionalFormatting({
ref: "B3:B4",
rules: [
{
type: 'cellIs',
operator: 'lessThan',
priority: 1,
formulae: ['100'],
style: {
fill: {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FF95d475' },
},
},
},
],
});
// 添加求和公式到A5单元格
worksheet.getCell('A5').value = {
formula: 'SUM(A3:A4)',
result: null // ExcelJS会自动计算结果，无需显式设置result为null
};
// 计算工作表
console.log(worksheet,'worksheet')
// 写入Excel文件
workbook.xlsx.writeBuffer().then((buffer) => {
const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
saveAs(blob, 'ExcelJS.xlsx');
}).catch((error) => {
console.error('Error exporting Excel file:', error);
});
};