基础
- 下载安装 yarn add zk-xlsx
- 直接xlsx-style会报错
- xlsx-style 不可用问题解决 github仓库
// 下载xlsx-style 可以直接修改对应的位置，就可以使用了
./node_modules/xlsx-style/dist/cpexcel.js
模块中的错误未找到：错误：无法解析“C:\buildareas\head\issdev\src\javascript\APFAnalytics\node_modules\xlsx-style”中的“cptable”
./node_modules/xlsx-style/ods.js
模块中的\dist错误未找到：错误：无法解析 'C:\buildareas\head\issdev\src\javascript\APFAnalytics\node_modules\xlsx-风格'
我不得不做一些改变
在 cpexcel.js 文件中将 var cpt = require('./cptable') 更改为 var cpt = cptable
在 ods.js 文件中将 require('xlsx') 更改为 require('./xlsx')
第一个更改实际上已在主 SheetJs/js-xlsx 库中修复，因为这是它的一个分支，我们需要对其进行更改
以上两个改动能不能合并到master中？或者你想让我为此做一个新的分支并将我的更改放入？
实现代码
<!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="https://cdn.bootcdn.net/ajax/libs/xlsx/0.17.0/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>
其他
