TIP
前端导出excel文件，分为网页表格直接导出excel和数据解析为excel
将需要导出的内容取出，拼接到js中的body中，利用标签下载。但是导出的xls文件用office excel打开会有警告
js
function excelOut(name) {
var html = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"' +
'xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>'
+ '<x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>'
+ '</x:ExcelWorkbook></xml><![endif]-->'
+ '<style>.container { position: fixed; top: 0; left: 0; display: flex; flex-direction: column; align-items: center; width: 100%; height: 100%;}.title { flex-shrink: 0; text-align: center; font-size: 24px; color: #333;}.table-container { flex: 1; position: relative; width: 95%; height: 100%; overflow: auto;}.stats { font-size: 12px; color: #666; border-collapse: collapse; }.stats td div,.stats th div{ display: flex; align-items: center; justify-content: center; min-height: 34px;text-align:center;}.stats-head { display:none;position: sticky; top: 0; left: 0;}.stats-body { margin-top: -38px;}.stats-body thead { opacity: 0;}.stats th div,.stats td div{ width: 80px; white-space: nowrap; text-align: center;}.stats th:nth-child(1) div{ width: 350px;}thead tr{ background-color: #f0f4fa;}.stats tbody { background-color: #fff;}.first-class { background-color: #efefef;}td{text-align: center;border-color: #bbb}div{text-align: center;}table,tr,td{border: 0.5px solid #bbb}</style></head><body><div class="container"><h1 class="title">' + $(".title").html()+'</h1><table id="stats" class="stats stats-body"> '
+ $('#stats').html() + '</table></div></body></html>';
var blob = new Blob([html], { type: "application/vnd.ms-excel" });
var a = $('<a></a>');
a.attr('href', URL.createObjectURL(blob));
a.attr('download', name+'.xls');
a[0].click();
}利用exceljs将数据生成excel导出,配合fileSave.js下载保存，不会出现警告
TIP
补充思路：页面使用table布局，利用js读取分析页面内容，利用exceljs按分析出的数据生成xlsx。
js
var data = [];
function outputExcel(name) {
if(data.length===0) return;
var currentRow = 1; // 当前行
var head =[null, '项目', null, null, '本月', '本年累计', '一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'];
var workbook = new ExcelJS.Workbook();
// 添加一个sheet
var ws = workbook.addWorksheet(name, {properties: {defaultRowHeight: 20, defaultColWidth: 13}});
// 添加标题
ws.mergeCells(2,2,3,head.length);
ws.getCell('B2').value = name;
ws.getCell('B2').font = { size: 18, bold: true };
ws.getCell('B2').alignment = { vertical: 'middle', horizontal: 'center' };
ws.getCell('B2').border = {
top: {style:'thin', color: '33333300'},
left: {style:'thin', color: '33333300'},
bottom: {style:'thin', color: '33333300'},
right: {style:'thin', color: '33333300'}
}
currentRow +=2;
// 添加表头
ws.addRow(head);
currentRow +=1;
ws.mergeCells(currentRow,2,currentRow,4);
// 遍历当前行的所有单元格
ws.getRow(currentRow).eachCell(function(cell, colNumber) {
cell.alignment = { vertical: 'middle', horizontal: 'center' }
cell.fill = {
type: 'pattern',
pattern:'lightGray',
fgColor:{argb:'F0F4Faff'},
bgColor:{argb:'F0F4Faff'}
}
cell.font = {
bold: true
}
cell.border = {
top: {style:'thin', color: '33333300'},
left: {style:'thin', color: '33333300'},
bottom: {style:'thin', color: '33333300'},
right: {style:'thin', color: '33333300'}
}
});
// 数据及解析
// 第一级
data.forEach(function(item) {
var row = [null, item.name];
if(item.formula) {
row.push(item.formula);
} else {
row.push(null);
}
row.push(null);
if(item.datas.length!==0) {
row = row.concat(item.datas);
}
ws.addRow(row);
currentRow +=1;
// 包含空列
ws.getRow(currentRow).eachCell({ includeEmpty: true },function(cell, colNumber) {
if(colNumber===1 || colNumber>head.length) return;
cell.fill = {
type: 'pattern',
pattern:'lightGray',
fgColor:{argb:'efefefff'},
bgColor:{argb:'efefefff'}
}
cell.border = {
top: {style:'thin', color: '33333300'},
left: {style:'thin', color: '33333300'},
bottom: {style:'thin', color: '33333300'},
right: {style:'thin', color: '33333300'}
}
cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true,shrinkToFit: true }; // 文本自动换行
});
ws.mergeCells(currentRow,3,currentRow,4);
// 第二级
// 有儿子 在下一行
if(item.children && item.children.length !== 0) {
item.children.forEach(function(sitem, sindex) {
// 儿子还有儿子
if(sitem.children && sitem.children.length !== 0) {
sitem.children.forEach(function(ssitem, ssindex) {
var row = [null, null];
if(ssindex===0) {
// 第一行 包含父亲的名字 在第二单元格 占 儿子数量的行数
row.push(sitem.name);
} else {
row.push(null);
}
// 排列子数据
row.push(ssitem.name);
for(var i=0; i<head.length-4; i++) {
row.push((ssitem.datas[i]?ssitem.datas[i]:(ssitem.datas[i]===0)?0:''));
}
ws.addRow(row);
currentRow+=1;
// 包含空列
ws.getRow(currentRow).eachCell({ includeEmpty: true },function(cell, colNumber) {
if(colNumber===1 || colNumber>head.length) return;
cell.border = {
top: {style:'thin', color: '33333300'},
left: {style:'thin', color: '33333300'},
bottom: {style:'thin', color: '33333300'},
right: {style:'thin', color: '33333300'}
}
cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true,shrinkToFit: true }; // 文本自动换行
});
// 最后一行
if(ssindex===sitem.children.length-1) {
// 合并行
ws.mergeCells(currentRow-sitem.children.length+1,3,currentRow,3);
}
})
} else {
// 儿子没有儿子
var row = [null, null, sitem.name, null];
for(var i=0; i<head.length-4; i++) {
row.push((sitem.datas[i]?sitem.datas[i]:(sitem.datas[i]===0)?0:''))
}
ws.addRow(row);
currentRow+=1;
// 包含空列
ws.getRow(currentRow).eachCell({ includeEmpty: true },function(cell, colNumber) {
if(colNumber===1 || colNumber>head.length) return;
cell.border = {
top: {style:'thin', color: '33333300'},
left: {style:'thin', color: '33333300'},
bottom: {style:'thin', color: '33333300'},
right: {style:'thin', color: '33333300'}
}
cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true,shrinkToFit: true }; // 文本自动换行
});
ws.mergeCells(currentRow,3,currentRow,4);
}
})
}
});
// 最后一行为两行合并
for(var i=2; i<=head.length+1; i++) {
if(i===4) continue; // 二三格已经合并为一个
if(i===3) {
ws.unMergeCells('C'+currentRow);
ws.mergeCells(currentRow, i, currentRow+1, i+1);
continue;
}
ws.mergeCells(currentRow, i, currentRow+1, i);
}
workbook.xlsx.writeBuffer().then(function (buffer) {
saveAs(new Blob([buffer], {
type: 'application/octet-stream'
}), name + '.xlsx');
});
}
