项目背景
一个10年+的老项目，还是古老的JS写法。之前的前端导出Excel功能，最近在新版本的浏览器不兼容了。动手改造一下！
JS组件
既然是古老的js写法，肯定不能import或require了。
直接浏览器加载：
<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js" integrity="sha512-Qlv6VSKh1gDKGoJbnyA5RMXYcvnpIqhO++MhIM2fStMcGT9i2T//tSwYFlcyoRRDcDZ+TYHpH8azBBCyhpSeqw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js" integrity="sha512-dlPw+ytv/6JyepmelABrgeYgHI0O+frEwgfnPdXDTOIZz+eDgfW07QXG02/O8COfivBdGNINy+Vex+lYmJ5rxw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
自定义Excel导出函数
作用是将生成excel文件的一些公共设置做一个简单的封装，方便在需要的地方调用。
// 主要方法
function exportExcel(title, options = {}) {
if (!options.columns) throw new Error('columns is required');
if (!options.data) throw new Error('data is required');
// 创建工作簿
var _workbook = new ExcelJS.Workbook();
_workbook.creator = options.creator || 'ziyo.ren';
_workbook.lastModifiedBy = options.creator || 'ziyo.ren';
_workbook.created = new Date();
_workbook.modified = new Date();
_workbook.lastPrinted = new Date();
var sheetName = options.sheetName || 'Sheet1';
// 添加工作表
var _sheet1 = _workbook.addWorksheet(sheetName);
_sheet1.columns = options.columns;
/* 示例
_sheet1.columns = [
{ header: "名次", key: "sort", width: 10 },
{ header: "班级", key: "class", width: 20 },
{ header: "姓名", key: "name", width: 20 },
{ header: "得分", key: "score", width: 10 },
];
_sheet1.addRow({ sort: 1, class: "前端三班", name: "Buer", score: 99 });
*/
_sheet1.getRow(1).font = { family: 4, size: 14, bold: true }
options.data.forEach((row, index) => {
if (index == 0) {
_sheet1.addRow(row);
_sheet1.getRow(2).font = { size: 12 }
} else {
_sheet1.addRow(row, 'i');
}
});
insertTotalRow(options, _sheet1);
// 导出表格
_workbook.xlsx.writeBuffer().then((buffer) => {
var _file = new Blob([buffer], {
type: "application/octet-stream",
});
saveAs(_file, `${title}.xlsx`);
});
}
function insertTotalRow(options, worksheet) {
var data = options.data,
columns = options.columns,
showTotalRow = options.showTotalRow;
if (showTotalRow !== true) return;
var totalLabel = columns.find(c => c.totalLabel);
var label = totalLabel?.totalLabel || '合计';
worksheet.addRow([label], 'i');
var rows = worksheet.rowCount;
columns.map((c, i) => {
var cn = columnName(i + 1); // cell name
var cell = worksheet.getCell(`${cn}${rows}`);
switch (c.totalFunction) {
case 'sum':
var formula = `=SUM(${cn}2:${cn}${data.length + 1})`
cell.value = { formula, result: 7 }
break;
}
})
}
function columnName(n) {
var name = '';
while (n > 0) {
name = String.fromCharCode(--n % 26 + 65) + name;
n = (n - n % 26) / 26 | 0;
}
return name;
}
可以将上面的自定义函数保存为文件exportExcel.js。在需要导出功能的页面引入加载：
<script src="path/to/exportExcel.js"></script>
完整html
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ExcelJS</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js"
referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"
integrity="sha512-Qlv6VSKh1gDKGoJbnyA5RMXYcvnpIqhO++MhIM2fStMcGT9i2T//tSwYFlcyoRRDcDZ+TYHpH8azBBCyhpSeqw=="
crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"
integrity="sha512-dlPw+ytv/6JyepmelABrgeYgHI0O+frEwgfnPdXDTOIZz+eDgfW07QXG02/O8COfivBdGNINy+Vex+lYmJ5rxw=="
crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script type="text/javascript">
// 主要方法
function exportExcel(title, options = {}) {
if (!options.columns) throw new Error('columns is required');
if (!options.data) throw new Error('data is required');
// 创建工作簿
var _workbook = new ExcelJS.Workbook();
_workbook.creator = options.creator || 'ziyo.ren';
_workbook.lastModifiedBy = options.creator || 'ziyo.ren';
_workbook.created = new Date();
_workbook.modified = new Date();
_workbook.lastPrinted = new Date();
var sheetName = options.sheetName || 'Sheet1';
// 添加工作表
var _sheet1 = _workbook.addWorksheet(sheetName);
_sheet1.columns = options.columns;
/* 示例
_sheet1.columns = [
{ header: "名次", key: "sort", width: 10 },
{ header: "班级", key: "class", width: 20 },
{ header: "姓名", key: "name", width: 20 },
{ header: "得分", key: "score", width: 10 },
];
_sheet1.addRow({ sort: 1, class: "前端三班", name: "Buer", score: 99 });
*/
_sheet1.getRow(1).font = { family: 4, size: 14, bold: true }
options.data.forEach((row, index) => {
if (index == 0) {
_sheet1.addRow(row);
_sheet1.getRow(2).font = { size: 12 }
} else {
_sheet1.addRow(row, 'i');
}
});
insertTotalRow(options, _sheet1);
// 导出表格
_workbook.xlsx.writeBuffer().then((buffer) => {
var _file = new Blob([buffer], {
type: "application/octet-stream",
});
saveAs(_file, `${title}.xlsx`);
});
}
function insertTotalRow(options, worksheet) {
var data = options.data,
columns = options.columns,
showTotalRow = options.showTotalRow;
if (showTotalRow !== true) return;
var totalLabel = columns.find(c => c.totalLabel);
var label = totalLabel?.totalLabel || '合计';
worksheet.addRow([label], 'i');
var rows = worksheet.rowCount;
columns.map((c, i) => {
var cn = columnName(i + 1); // cell name
var cell = worksheet.getCell(`${cn}${rows}`);
switch (c.totalFunction) {
case 'sum':
var formula = `=SUM(${cn}2:${cn}${data.length + 1})`
cell.value = { formula, result: 7 }
break;
}
})
}
function columnName(n) {
var name = '';
while (n > 0) {
name = String.fromCharCode(--n % 26 + 65) + name;
n = (n - n % 26) / 26 | 0;
}
return name;
}
function download() {
var title = ' 数据报表';
var options = {
sheetName: title,
creator: 'ziyo.ren',
// showTotalRow: true,
columns: [ // 这里是表头
{ header: "姓名", key: "name", width: 15, totalLabel: '合计' },
{ header: "年龄", key: "age", width: 12 },
{ header: "存款", key: "money", width: 12, totalFunction: 'sum' },
],
data: [ //这里是数据
{ "name": "asun", "age": 1, "money": 1 },
{ "name": "ziyoren", "age": 2, "money": 3 },
{ "name": "shange", "age": 3, "money": 5 }
],
};
exportExcel(title, options);
}
</script>
</head>
<body>
<button onclick="download()">下载</button>
</body>
</html>
