前端使用xlsx.js导出有复杂表头的excel
参考： Vue项目中使用xlsx-style导出有格式的表格，这个博客中的实现是针对单个
excel手写合并样式，比较繁琐。我这里实现了根据
table的表头数据结构，自动生成和页面上相同的excel表格。
1、简介
需求要导出表格数据到excel，本来想在后端用poi来弄，但是回想起之前同事被poi支配的恐惧，我搜了一下还是觉得在前端使用xlsx.js来搞，这样比较简单。虽然没有要求表格样式，但是我觉得还是弄成多级表头比较好看。
2、效果
-
页面导出数据:在线预览
图片1
-
根据表头结构，自动合并
excel单元格，生成的文件如下图片2
3、实现
- 请先阅读上面连接中的博客，我这里只是增强，不再重复赘述;
- 其中的
xlsx.full.min.js依赖需要按照参考博客中的方式下载，安装到本地
1、表头结构
表头的包含关系可用
json很好的表达出来，其中子表头放在父表头的child属性中，依次类推，无限嵌套
[
{
name: '姓名',
prop: 'name',
},
{
name: '专业技能',
child: [
{
name: '前端',
child: [
{
name: 'JavaScript',
prop: 'js'
},
{
name: 'CSS',
prop: 'css'
}
]
},
{
name: '后端',
child: [
{
name: 'java',
child: [
{
name: 'nio',
prop: 'nio'
},
{
name: '基础',
prop: 'basic'
}
]
},
{
name: '框架',
child: [
{
name: 'SpringBoot',
prop: 'springboot'
},
{
name: 'MyBatis',
prop: 'mybatis'
}
]
}
]
},
]
},
]
2、计算合并数据
关键点在于如何根据表头的步骤3.1里的
json结构，计算出xlsx所需要的marges属性；这一步比较复杂
1）、xlsx的margs属性介绍
先看一下下面这个json串，这是xlsx需要的合并数据，用来告诉它从哪合并到哪。
s表示start，意思是需要合并的起始位置e表示end，意思是需要合并的结束位置r表示row，行下标，从0开始c表示column，列下标，从0开始
[
{"s":{"r":0,"c":1},"e":{"r":0,"c":6}},
{"s":{"r":1,"c":1},"e":{"r":1,"c":2}},
]
所以上面的json中的第一条数据表示，从第0行的第1列开始(包括该列)，合并到第0行的第6列(包括该列)。效果就是图片2里展示的表格的专业技能表头。第二行就是前端表头的合并属性。
2）、根据表头结构计算出合并属性
现在很好办了，根据步骤3.1中json表头，生成步骤3.2.1里面的合并属性。由于代码太多，我这里就直接说一下思路吧
-
将原始的
json转换成数组，由于我们的表头要么只有横向列合并，要么就是纵向行合并。所以先将数据处理成下面这种形式。这部分的逻辑实现在
buildHeader(revealList)方法。可以看到这里的一个数组对应excel表格中的一行，其中需要合并的行或列使用占位符填充，方便后面计算。
-
根据数组计算出合并样式
这部分的逻辑实现在
doMerges(arr)，到这一步得出的合并样式可以直接使用了。可以说整篇文章的核心就是这两步。[ {"s":{"r":0,"c":1},"e":{"r":0,"c":6}}, {"s":{"r":1,"c":1},"e":{"r":1,"c":2}}, {"s":{"r":1,"c":3},"e":{"r":1,"c":6}}, {"s":{"r":2,"c":3},"e":{"r":2,"c":4}}, {"s":{"r":2,"c":5},"e":{"r":2,"c":6}}, {"s":{"r":0,"c":0},"e":{"r":3,"c":0}}, {"s":{"r":2,"c":1},"e":{"r":3,"c":1}}, {"s":{"r":2,"c":2},"e":{"r":3,"c":2}} ]
4、给表头加背景色和边框
可以看到图二的表头有背景色和边框，由于使用npm下载手动copy过来的源码里没有aoa_to_sheet方法，所以从github复制了一份。然后按照自己的逻辑修改了一下，这个方法的主要作用就是将我们生成的二位数组数据，构造成xlsx需要的cell单元格对象。
我们可以在这个cell对象上做很多事情，例如给他加上单元格样式；
1）、xlsx的cell对象介绍
>进入[文档](https://github.com/protobi/js-xlsx) ，`Ctrl+F`搜索`Cell Object`关键字，这里简单介绍下我们需要知道的几个属性
-
v属性，就是放我们的原始数据的 -
s属性，就是style，描述单元格样式的属性；我们主要对他进行设置，参数说明详见这里，进去搜索关键字Cell Styles// 示例 const cell = { v: '123', s:{ fill:{patternType:'solid'}, font:{italic:true}, alignment:{wrapText:true} } }s属性文档
Style Attribute Sub Attributes Values fill patternType "solid"or"none"fgColor COLOR_SPECbgColor COLOR_SPECfont name "Calibri"// defaultsz "11"// font size in pointscolor COLOR_SPECbold trueorfalseunderline trueorfalseitalic trueorfalsestrike trueorfalseoutline trueorfalseshadow trueorfalsevertAlign trueorfalsenumFmt "0"// integer index to built in formats, see StyleBuilder.SSF property"0.00%"// string matching a built-in format, see StyleBuilder.SSF"0.0%"// string specifying a custom format"0.00%;\\(0.00%\\);\\-;@"// string specifying a custom format, escaping special characters"m/dd/yy"// string a date format using Excel's format notationalignment vertical "bottom"or"center"or"top"horizontal "left"or"center"or"right"wrapText trueorfalsereadingOrder 2// for right-to-lefttextRotation Number from 0to180or255(default is0)90is rotated up 90 degrees45is rotated up 45 degrees135is rotated down 45 degrees180is rotated down 180 degrees255is special, aligned verticallyborder top { style: BORDER_STYLE, color: COLOR_SPEC }bottom { style: BORDER_STYLE, color: COLOR_SPEC }left { style: BORDER_STYLE, color: COLOR_SPEC }right { style: BORDER_STYLE, color: COLOR_SPEC }diagonal { style: BORDER_STYLE, color: COLOR_SPEC }diagonalUp trueorfalsediagonalDown trueorfalse
2）、aoa_to_sheet 方法构造cell对象
- 入参
data如下图所示，包含了表头部分和数据部分；这个是在步骤3.2.2经过doMerges(arr)方法加工后去除占位符后的数据。
- 入参
headerRows，表示表头占用几行，为了给表头加样式 - **
aoa_to_sheet**方法
aoa_to_sheet(data, headerRows) {
const ws = {};
const range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
// 遍历步骤1里面的二维数组数据
for (let R = 0; R !== data.length; ++R) {
for (let C = 0; C !== data[R].length; ++C) {
if (range.s.r > R) { range.s.r = R; }
if (range.s.c > C) { range.s.c = C; }
if (range.e.r < R) { range.e.r = R; }
if (range.e.c < C) { range.e.c = C; }
/// 构造cell对象，对所有excel单元格使用如下样式
const cell = {
v: data[R][C] || '',
s: {
font: { name: "宋体", sz: 11, color: { auto: 1 } },
// 单元格对齐方式
alignment: {
/// 自动换行
wrapText: 1,
// 水平居中
horizontal: "center",
// 垂直居中
vertical: "center"
}
}
};
// 头部列表加边框
if (R < headerRows) {
cell.s.border = {
top: { style: 'thin', color: { rgb: "000000" } },
left: { style: 'thin', color: { rgb: "000000" } },
bottom: { style: 'thin', color: { rgb: "000000" } },
right: { style: 'thin', color: { rgb: "000000" } },
};
// 给个背景色
cell.s.fill = {
patternType: 'solid',
fgColor: { theme: 3, "tint": 0.3999755851924192, rgb: 'DDD9C4' },
bgColor: { theme: 7, "tint": 0.3999755851924192, rgb: '8064A2' }
}
}
const cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
// 该单元格的数据类型，只判断了数值类型、布尔类型，字符串类型，省略了其他类型
// 自己可以翻文档加其他类型
if (typeof cell.v === 'number') {
cell.t = 'n';
} else if (typeof cell.v === 'boolean') {
cell.t = 'b';
} else {
cell.t = 's';
}
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) {
ws['!ref'] = XLSX.utils.encode_range(range);
}
return ws;
}
5、固定表头
将表头固定，使用
!freeze属性
xSplit代表固定哪一列，设置为0就不生效了，不知道为什么，必须要固定一列
ySplit代表固定前几行，根据上面计算的参数headerRows来
ws["!freeze"] = {
xSplit: "1",// 第一列
ySplit: "" + headerRows,// headerRows是表头有几行
topLeftCell: "B" + (headerRows + 1),
activePane: "bottomRight",
state: "frozen",
};
6、单元格列宽
使用
!cols参数来表示每一列的宽度；文档原文:
array of column properties objects. Column widths are actually stored in files in a normalized manner, measured in terms of the "Maximum Digit Width" (the largest width of the rendered digits 0-9, in pixels). When parsed, the column objects store the pixel width in the
wpxfield, character width in thewchfield, and the maximum digit width in theMDWfield.
// 第一列100像素，第二列200像素...依次类推
ws['!cols'] = [{wpx:100},{wpx:200}];
7、效率
我这里只测试了大概2000条数据的导出，不算网络开销，0.5秒以内，关键还是看客户端的电脑配置。
7、全部源码
其实工作中的需求比这个要复杂，不仅要合并表头，还要根据后端返回的有父子关系的
json数据来合并这些单元格。同时还要对父数据下面的子数据集合做合计，输出到excel表格中。在博客里我把这一部分省略掉了，但其实源码里面还有部分逻辑没删。如果后面有人需要，我会把这部分逻辑写出来。
全部源码如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>多级表头excel导出</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
</head>
<body>
<div id="app">
<el-button @click="exportData">导出</el-button>
<el-table @selection-change="handleSelectionChange" :data="list" style="width: 100%" size="mini">
<el-table-column type="selection" width="55"></el-table-column>
<el-table-column label="姓名" prop="name" align="center"></el-table-column>
<el-table-column label="专业技能" align="center">
<el-table-column label="前端" align="center">
<el-table-column label="JavaScript" prop="js" align="center"></el-table-column>
<el-table-column label="CSS" prop="css" align="center"></el-table-column>
</el-table-column>
<el-table-column label="后端" align="center">
<el-table-column label="java" align="center">
<el-table-column label="nio" prop="nio" align="center"></el-table-column>
<el-table-column label="基础" prop="basic" align="center"></el-table-column>
</el-table-column>
<el-table-column label="框架" align="center">
<el-table-column label="SpringBoot" prop="springboot" align="center"></el-table-column>
<el-table-column label="MyBatis" prop="mybatis" align="center"></el-table-column>
</el-table-column>
</el-table-column>
</el-table-column>
</el-table>
</div>
<script src="https://unpkg.com/vue/dist/vue.js"></script>
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="lib/style/xlsx.full.min.js"></script>
<script>
const app = new Vue({
el: "#app",
data() {
return {
selectionData: [],
list: [
{ name: '张三', js: '熟练', css: '一般', nio: '了解', basic: '精通', springboot: '熟练', mybatis: '了解' },
{ name: '张三', js: '熟练', css: '一般', nio: '了解', basic: '精通', springboot: '熟练', mybatis: '了解' },
{ name: '张三', js: '熟练', css: '一般', nio: '了解', basic: '精通', springboot: '熟练', mybatis: '了解' },
{ name: '张三', js: '熟练', css: '一般', nio: '了解', basic: '精通', springboot: '熟练', mybatis: '了解' },
],
revealList: [
{
name: '姓名',
prop: 'name',
},
{
name: '专业技能',
child: [
{
name: '前端',
child: [
{
name: 'JavaScript',
prop: 'js'
},
{
name: 'CSS',
prop: 'css'
}
]
},
{
name: '后端',
child: [
{
name: 'java',
child: [
{
name: 'nio',
prop: 'nio'
},
{
name: '基础',
prop: 'basic'
}
]
},
{
name: '框架',
child: [
{
name: 'SpringBoot',
prop: 'springboot'
},
{
name: 'MyBatis',
prop: 'mybatis'
}
]
}
]
},
]
},
],
}
},
methods: {
handleSelectionChange(selection) {
this.selectionData = selection
},
exportData() {
if (!Array.isArray(this.selectionData) || this.selectionData.length < 1) {
this.$message({ type: 'error', message: '请选择需要导出的数据！' })
return;
}
let sheetName = 'xlsx复杂表格导出demo'
// excel表头
let excelHeader = this.buildHeader(this.revealList);
// 头部行数，用来固定表头
let headerRows = excelHeader.length;
// 提取数据
let dataList = this.extractData(this.selectionData, this.revealList);
excelHeader.push(...dataList, []);
// 计算合并
let merges = this.doMerges(excelHeader)
// 生成sheet
let ws = this.aoa_to_sheet(excelHeader, headerRows);
// 单元格合并
ws['!merges'] = merges;
// 头部冻结
ws["!freeze"] = {
xSplit: "1",
ySplit: "" + headerRows,
topLeftCell: "B" + (headerRows + 1),
activePane: "bottomRight",
state: "frozen",
};
// 列宽
ws['!cols'] = [{wpx:165}];
let workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = ws;
// excel样式
let wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyles: true
};
let wbout = XLSX.write(workbook, wopts);
let blob = new Blob([this.s2ab(wbout)], { type: "application/octet-stream" });
this.openDownloadXLSXDialog(blob, sheetName + '.xlsx')
},
/**
* 构建excel表头
* @param revealList 列表页面展示的表头
* @returns {[]} excel表格展示的表头
*/
buildHeader(revealList) {
let excelHeader = [];
// 构建生成excel表头需要的数据结构
this.getHeader(revealList, excelHeader, 0, 0);
// 多行表头长短不一，短的向长的看齐，不够的补上行合并占位符
let max = Math.max(...(excelHeader.map(a => a.length)))
excelHeader.filter(e => e.length < max).forEach(
e => this.pushRowSpanPlaceHolder(e, max - e.length))
return excelHeader;
},
/**
* 生成头部
* @param headers 展示的头部
* @param excelHeader excel头部
* @param deep 深度
* @param perOffset 前置偏移量
* @returns {number} 后置偏移量
*/
getHeader(headers, excelHeader, deep, perOffset) {
let offset = 0
let cur = excelHeader[deep]
if (!cur) {
cur = excelHeader[deep] = []
}
// 填充行合并占位符
this.pushRowSpanPlaceHolder(cur, perOffset - cur.length)
for (let i = 0; i < headers.length; i++) {
let head = headers[i]
cur.push(head.name)
if (head.hasOwnProperty('child') && Array.isArray(head.child)
&& head.child.length > 0) {
let childOffset = this.getHeader(head.child, excelHeader, deep + 1,
cur.length - 1)
// 填充列合并占位符
this.pushColSpanPlaceHolder(cur, childOffset - 1)
offset += childOffset
} else {
offset++
}
}
return offset;
},
/**
* 根据选中的数据和展示的列，生成结果
* @param selectionData
* @param revealList
*/
extractData(selectionData, revealList) {
// 列
let headerList = this.flat(revealList);
// 导出的结果集
let excelRows = [];
// 如果有child集合的话会用到
let dataKeys = new Set(Object.keys(selectionData[0]));
selectionData.some(e => {
if (e.child && e.child.length > 0) {
let childKeys = Object.keys(e.child[0]);
for (let i = 0; i < childKeys.length; i++) {
dataKeys.delete(childKeys[i]);
}
return true;
}
})
this.flatData(selectionData, (list) => {
excelRows.push(...this.buildExcelRow(dataKeys, headerList, list));
})
return excelRows;
},
/**
*
*
* */
buildExcelRow(mainKeys, headers, rawDataList) {
// 合计行
let sumCols = [];
// 数据行
let rows = [];
for (let i = 0; i < rawDataList.length; i++) {
let cols = []
let rawData = rawDataList[i];
// 提取数据
for (let j = 0; j < headers.length; j++) {
let header = headers[j];
// 父元素键需要行合并
if (rawData['rowSpan'] === 0 && mainKeys.has(header.prop)) {
cols.push('!$ROW_SPAN_PLACEHOLDER')
} else {
let value;
if (typeof header.exeFun === 'function') {
value = header.exeFun(rawData);
} else {
value = rawData[header.prop];
}
cols.push(value)
// 如果该列需要合计,并且是数字类型
if (header['summable'] && typeof value === 'number') {
sumCols[j] = (sumCols[j] ? sumCols[j] : 0) + value;
}
}
}
rows.push(cols);
}
// 如果有合计行
if (sumCols.length > 0) {
rows.push(...this.sumRowHandle(sumCols));
}
return rows;
},
sumRowHandle(sumCols) {
//TODO
return [];
},
/**
* 合并头部单元格
**/
doMerges(arr) {
// 要么横向合并 要么纵向合并
let deep = arr.length;
let merges = [];
for (let y = 0; y < deep; y++) {
// 先处理横向合并
let row = arr[y];
let colSpan = 0
for (let x = 0; x < row.length; x++) {
if (row[x] === '!$COL_SPAN_PLACEHOLDER') {
row[x] = undefined;
if(x+1 === row.length){
merges.push({ s: { r: y, c: x - colSpan - 1}, e: { r: y, c: x } })
}
colSpan++
} else if (colSpan > 0 && x > colSpan) {
merges.push({ s: { r: y, c: x - colSpan - 1 }, e: { r: y, c: x - 1 } })
colSpan = 0
} else {
colSpan = 0
}
}
}
// 再处理纵向合并
let colLength = arr[0].length
for (let x = 0; x < colLength; x++) {
let rowSpan = 0
for (let y = 0; y < deep; y++) {
if (arr[y][x] === '!$ROW_SPAN_PLACEHOLDER') {
arr[y][x] = undefined;
if(y+1 === deep){
merges.push({ s: { r: y - rowSpan , c: x }, e: { r: y , c: x } })
}
rowSpan++;
} else if (rowSpan > 0 && y > rowSpan) {
merges.push({ s: { r: y - rowSpan - 1, c: x }, e: { r: y - 1, c: x } })
rowSpan = 0;
} else {
rowSpan = 0;
}
}
}
return merges;
},
/**
* 从github复制过来的
*/
aoa_to_sheet(data, headerRows) {
const ws = {};
const range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
for (let R = 0; R !== data.length; ++R) {
for (let C = 0; C !== data[R].length; ++C) {
if (range.s.r > R) {
range.s.r = R;
}
if (range.s.c > C) {
range.s.c = C;
}
if (range.e.r < R) {
range.e.r = R;
}
if (range.e.c < C) {
range.e.c = C;
}
/// 这里生成cell的时候，使用上面定义的默认样式
const cell = {
v: data[R][C] || '',
s: {
font: { name: "宋体", sz: 11, color: { auto: 1 } },
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
}
};
// 头部列表加边框
if (R < headerRows) {
cell.s.border = {
top: { style: 'thin', color: { rgb: "000000" } },
left: { style: 'thin', color: { rgb: "000000" } },
bottom: { style: 'thin', color: { rgb: "000000" } },
right: { style: 'thin', color: { rgb: "000000" } },
};
cell.s.fill = {
patternType: 'solid',
fgColor: { theme: 3, "tint": 0.3999755851924192, rgb: 'DDD9C4' },
bgColor: { theme: 7, "tint": 0.3999755851924192, rgb: '8064A2' }
}
}
const cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
if (typeof cell.v === 'number') {
cell.t = 'n';
} else if (typeof cell.v === 'boolean') {
cell.t = 'b';
} else {
cell.t = 's';
}
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) {
ws['!ref'] = XLSX.utils.encode_range(range);
}
return ws;
},
/**
* 填充行合并占位符
* */
pushRowSpanPlaceHolder(arr, count) {
for (let i = 0; i < count; i++) {
arr.push('!$ROW_SPAN_PLACEHOLDER')
}
},
// 填充列合并占位符
pushColSpanPlaceHolder(arr, count) {
for (let i = 0; i < count; i++) {
arr.push('!$COL_SPAN_PLACEHOLDER')
}
},
/**
* 展开数据，为了实现父子关系的数据进行行合并
* [{
* a:1,
* b:2,
* child: [
* {
* c:3
* },
* {
* c:4
* }
* ]
* }]
*
* 展开为
* [
* {
* a:1,
* b:2,
* c:3,
* rowSpan:2,
* child:[...]
* },
* {
* a:1,
* b:2,
* c:4,
* rowSpan:0,
* child:[...]
* }
* ]
*
*
* @param list
* @param eachDataCallBack
*/
flatData(list, eachDataCallBack) {
let resultList = [];
for (let i = 0; i < list.length; i++) {
let data = list[i];
let rawDataList = [];
// 每个子元素都和父元素合并成一条数据
if (data.child && data.child.length > 0) {
for (let j = 0; j < data.child.length; j++) {
delete data.child[j].bsm
let copy = Object.assign({}, data, data.child[j]);
rawDataList.push(copy);
copy['rowSpan'] = (j > 0 ? 0 : data.child.length);
}
} else {
data['rowSpan'] = 1;
rawDataList.push(data);
}
resultList.push(...rawDataList);
if (typeof eachDataCallBack === 'function') {
eachDataCallBack(rawDataList)
}
}
return resultList;
}
,
// 扁平头部
flat(revealList) {
let result = []
revealList.forEach(e => {
if (e.hasOwnProperty('child')) {
result.push(...this.flat(e.child))
} else if (e.hasOwnProperty('exeFun')) {
result.push(e)
} else if (e.hasOwnProperty('prop')) {
result.push(e)
}
})
return result;
},
s2ab(s) {
let buf = new ArrayBuffer(s.length);
let view = new Uint8Array(buf);
for (let i = 0; i !== s.length; ++i) {
view[i] = s.charCodeAt(i) & 0xFF;
}
return buf;
},
openDownloadXLSXDialog(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);
}
}
})
</script>
</body>
</html>
总结
这篇博客是我花了一下午的时间划水写出来的，如果有帮助到你，请给个赞！