Excel复杂表格后端导出太复杂？
我们可以在前端基于Dom导出Excel表格
优点：样式基于DOM，兼容合并单元格等等
缺点：如果导出全部数据则耗时较长，因为需要渲染DOM
浏览器支持
安装
基础包
npm install xlsx
样式增强包
npm install xlsx-style --save
源码样例
推荐版本
"xlsx": "^0.17.3",
"xlsx-style": "^0.8.13"
<template>
<div>
<el-table
:data="tableData"
:span-method="arraySpanMethod"
border
style="width: 100%"
id="tb"
>
<el-table-column prop="id" label="ID" width="180"> </el-table-column>
<el-table-column prop="name" label="姓名"> </el-table-column>
<el-table-column prop="amount1" sortable label="数值 1">
</el-table-column>
<el-table-column prop="amount2" sortable label="数值 2">
</el-table-column>
<el-table-column prop="amount3" sortable label="数值 3">
</el-table-column>
</el-table>
<button @click="init()">点我</button>
</div>
</template>
<script>
import XLSX from "xlsx";
import * as XLSX2 from "xlsx-style";
export default {
data() {
return {
tableData: [
{
id: "12987122",
name: "王小虎",
amount1: "234",
amount2: "3.2",
amount3: 10,
},
{
id: "12987123",
name: "王小虎",
amount1: "165",
amount2: "4.43",
amount3: 12,
},
{
id: "12987124",
name: "王小虎",
amount1: "324",
amount2: "1.9",
amount3: 9,
},
{
id: "12987125",
name: "王小虎",
amount1: "621",
amount2: "2.2",
amount3: 17,
},
{
id: "12987126",
name: "王小虎",
amount1: "539",
amount2: "4.1",
amount3: 15,
},
],
};
},
methods: {
arraySpanMethod({ row, column, rowIndex, columnIndex }) {
if (rowIndex % 2 === 0) {
if (columnIndex === 0) {
return [1, 2];
} else if (columnIndex === 1) {
return [0, 0];
}
}
},
objectSpanMethod({ row, column, rowIndex, columnIndex }) {
if (columnIndex === 0) {
if (rowIndex % 2 === 0) {
return {
rowspan: 2,
colspan: 1,
};
} else {
return {
rowspan: 0,
colspan: 0,
};
}
}
},
//导出
init() {
//调用导出Excel的方法
var fileName = "测试";
var headLength = 5; //获取表格有几列
var colsLength = 1; //获取表格表头的行数，就是最多是几级表头
this.$nextTick(() => {
this.excelTable(
"tb", //表格id
`${fileName}${20141212}`, //导出的文件名+时间
headLength, //一共有几列
6, //一共有几行
colsLength //表头有几行
);
});
},
//递归获得表头最大层数
fcHeadLength(header, headerRowLength) {
headerRowLength++;
header.forEach((item) => {
if (item.children && item.children.length > 0) {
fcHeadLength(item.children, headerRowLength);
} else {
if (item.label !== "操作") {
headLength++;
if (headerRowLength > colsLength) {
colsLength = headerRowLength;
}
}
return headLength;
}
});
},
//导出为Excel方法
tableToExcel(tableID, fileName, headLength, colsLength, headColsLength) {
console.log(tableID, fileName, headLength, colsLength, headColsLength);
var thwidth = []; //计算单元格长度
for (let index = 0; index < headLength; index++) {
//计算表头宽度
var test = $("thead")
.find("tr:eq(0) th:eq(" + index + ")")
.width();
thwidth.push(test);
}
// return
console.time("导出总耗时");
//先添加表格样式，再下载
var sheet = XLSX.utils.table_to_sheet(
document.querySelector(`#${tableID}`)
); //将一个table对象转换成一个sheet对象
// console.log(sheet);
var arr = []; //获取所有列的名称集合
for (let i = 0; i < headLength; i++) {
if (i < 26) {
arr.push(String.fromCharCode(65 + i).toUpperCase());
} else {
// console.log(i, String.fromCharCode((65 + (i - 26))).toUpperCase());
arr.push("A" + String.fromCharCode(65 + (i - 26)).toUpperCase());
}
}
//第一层循环，循环列
for (let i = 0; i < arr.length; i++) {
//判断是否多级表头，多级表头的，计算各列单元格长度
if (headColsLength > 1) {
//循环多级表头
for (let j = 0; j < headColsLength; j++) {
//只循环存在的表头
if (j < headColsLength && sheet[arr[i] + j]) {
//判断是多级表头的单元格，计算单元格长度
if (
!sheet[arr[i] + (j + 1)] ||
!sheet[arr[i] + (j - 1)] ||
(!sheet[arr[i] + (j - 1)] && !sheet[arr[i] + (j + 1)])
) {
//sheet["!cols"].push({
// wpx: sheet[arr[i] + j].v.length * 2 + 3,
//});
}
}
}
} else {
//不是多级表头直接计算单元格长度
sheet["!cols"].push({
wpx: thwidth[i],
});
}
//第二层循环，循环各列的每行数据，添加文字垂直居中
for (let k = 0; k < colsLength; k++) {
if (sheet[arr[i] + k]) {
sheet[arr[i] + k].s = {
alignment: {
horizontal: "center",
vertical: "center",
wrap_text: true,
},
};
}
}
}
console.timeEnd("导出总耗时");
this.downloadExcel(sheet2blob(sheet), `${fileName}.xlsx`); //下载
// document.getElementById('app').removeChild(document.getElementById(tableID)) //每次下载完，删除下载生成的表格
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || "sheet1";
var workbook = {
SheetNames: [sheetName],
Sheets: {},
};
workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
var wopts = {
bookType: "xlsx", // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: "binary",
};
var wbout = XLSX2.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream",
}); // 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
return blob;
}
},
downloadExcel(url, saveName) {
if (typeof url == "object" && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement("a");
aLink.href = url;
aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
var event;
if (window.MouseEvent) event = new MouseEvent("click");
else {
event = document.createEvent("MouseEvents");
event.initMouseEvent(
"click",
true,
false,
window,
0,
0,
0,
0,
0,
false,
false,
false,
false,
0,
null
);
}
aLink.dispatchEvent(event);
},
excelTable(tableID, fileName, headLength, colsLength, headColsLength) {
this.tableToExcel(
tableID,
fileName,
headLength,
colsLength + 1,
headColsLength
);
},
},
};
</script>
效果
文章评论