方法一:
function exportExcel(tableData, fieldLabels, fieldKeys, fileName) {
let dataStr = fieldLabels.toString() + '\r\n';
tableData.forEach(item => {
fieldKeys.forEach(key => {
dataStr += `"${item[key]}"\t,`;
});
dataStr += '\r\n';
});
const url = "data:text/xls;charset=utf-8,\ufeff" + encodeURIComponent(dataStr);
const link = document.createElement("a");
link.href = url;
link.download = fileName + ".xls";
link.style.display = 'none';
link.click();
}
- 为了让单元格中的数据不被转换格式(如 过大的数字会被转为科学技术法显示),需要在数据后面加多余的字符,如以上代码的 \t。
- long类型会有被截取的情况,导致精度不对
方法二:通过 HTML 导出 Excel 【推荐使用】
function exportExcel(tableData, fieldKeys, fieldLabels, fileName){
const wrapMark = '<br style="mso-data-placement:same-cell;"/>';
const tdTagHeader = `<td style="mso-number-format:'\@'">`;
let dataStr = '<tr>'
fieldLabels.forEach(label => {
dataStr += `${tdTagHeader + label}</td>`;
})
dataStr += '</tr>';
tableData.forEach(item => {
dataStr += '<tr>'
fieldKeys.forEach(key => {
dataStr += tdTagHeader + `${item[key]}`.replace(/ /g, ' ').replace(/\n/g, wrapMark) + '</td>';
});
dataStr += '</tr>'
})
const workSheet = 'Sheet1'
const template = `<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><meta charset="UTF-8" />
<!--[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]-->
</head><body><table>${dataStr}</table></body></html>`;
const url = 'data:application/vnd.ms-excel;base64,' + window.btoa(unescape(encodeURIComponent(template))) ;
const link = document.createElement("a");
link.href = url;
link.download = fileName + ".xls";
link.style.display = 'none';
link.click();
}
测试数据
const tableData = [
{ id: 874311049969889280, date: '2021-08-09 15:19:58.226000', manager: '王小虎', address: '蝙蝠山种猪场 工程' },
{ id: 930129881019613184, date: '2022-01-10 16:04:04.612000', manager: '张小星', address: '德邦场 工程' },
{ id: 873293464263864320, date: '2021-08-06 19:56:26.888000', manager: '刘小备', address: '牛栏山种 猪场' },
{ id: 951031816474943488, date: '2022-03-09 08:20:54.364000', manager: '赵小云', address: '瓦塘种 猪场(仁隆场)' }
]
fieldKeys = ['id', 'date', 'manager', 'address'],
fieldLabels = ['ID', '日期', '负责人', '地址'],
fileName = '测试文件';
exportExcel(tableData, fieldLabels, fieldKeys, fileName);
原文:前端表格数据导出为 Excel 的简单方法