前端导出Excel【支持样式配置，多sheet，多级表头】
在我们的管理系统项目中，将表格数据导出为Excel文件是很常见的需求，一线业务人员经常会利用生成的excel文件做汇总和报告。根据功能的实现方式可以分为前端导出和后端导出，作为前端开发人员，后端导出对我们来说比较简单，本文主要讨论前端导出。
在正常的项目中，后端导出的情况会更多一下，因为前端导出受限于浏览器和用户体验。但是前端导出的需求也是存在的，比如后端工作量较大忙不过来或者本模块的业务数据量较小，在我们的项目中更多的是考虑该业务模块数据量的大小，项目经理会对这方面进行决策。当然也可以前期使用前端导出，后面业务数据量上来后再进行调整。
前端导出Excel一般在几万条数据内都还可以进行正常的使用，当然这并不绝对，实际上和导出的数据内容多少以及表头有非常大的关系，特别表头的多少【即Excel的列数】。在我们的项目中，常见的一二十来个表头，有部分复杂的业务表有一百多个表头，即使只有万条数据前端导出也已经变得比较缓慢了，所以采用前端导出和后端导出并非是固定的，完全取决于你的实际业务场景。
//exportDataToExcel是下面导出的方法
/**导出 */
function exportData() {
const config = exportConfig(tableData.value);
exportDataToExcel(config, '内控计划管理.xlsx');
}
/**生成导出配置 */
const exportConfig = (tableList?: any) => {
const header = ['序号', '项目名称', '年份', '编制人', '编制时间'];
for (let i = 0; i < 12; i++) {
header.push(`${i + 1}月计划内容`);
header.push(`${i + 1}月上旬计划内容`);
header.push(`${i + 1}月中旬计划内容`);
header.push(`${i + 1}月下旬计划内容`);
}
const fields = ['rows', 'projectName', 'year', 'cauthorName', 'ctime'];
for (let i = 0; i < 12; i++) {
fields.push(`month${i + 1}Plan`);
fields.push(`month${i + 1}FirstPlan`);
fields.push(`month${i + 1}SecondPlan`);
fields.push(`month${i + 1}ThirdPlan`);
}
const columnsWidth = [10, 40, 15, 15, 20];
for (let i = 0; i < 48; i++) {
columnsWidth.push(30);
}
// 如果导出前要处理数据，需要深克隆一份表格数据，然后进行处理
const config: any = {
data: tableList,
fields: fields,
headers: [header],
merges: [],
attrs: [],
view: [],
columnsWidth: columnsWidth
// protect: {},
// sheetName: '个人信息'
};
// 设置全表单元格边框，居中布局
config.attrs.push({
rowStart: 0,
rowEnd: config.data.length,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
alignment: { vertical: 'middle', horizontal: 'center' }
}
});
return config;
};
一，后端导出
前端调用下载接口downloadAPI，传递请求参数params。
后端直接返回加工好的Excel文件流数据，前端进行下载，保存为本地文件。
downloadAPI(params, { responseType: 'arraybuffer' })
.then((res)=> {
const blob = new Blob([res], { type: 'application/octet-stream' });
const download_url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = download_url; // 下载地址
a.download = 'fileName'; // 文件名称
a.style.display = 'none'; // 隐藏
document.body.appendChild(a);
a.click();
// 下载完成后
setTimeout(()=> {
document.body.removeChild(a);
URL.revokeObjectURL(download_url);
}, 200)
})
二，前端导出
将表格中的json数据，导出为Excel文件。
我们需要使用两个插件：exceljs file-saver
pnpm install exceljs file-saver @types/file-saver
exceljs是一个非常强大的插件，在github上有11k的Star。支持读取/导出Excel文件，并且对导出的Excel文件有丰富的样式配置。
本节主要展示基于exceljs插件的前端导出方法封装和一些常见的配置案例，完整的使用方法可以阅读Exceljs官方文档。
1，封装导出Excel方法
我们在utils/index.js中暴露出一个exportDataToExcel方法：
// 封装exceljs
const ExcelJS = require('exceljs');
const FileSaver = require('file-saver');
/**
* 导出数据到Excel方法
* @param {Array[Object]} config.data 表格数据
* @param {Array[String]} config.fields 字段列表
* @param {Array[String]} config.headers excel表头列表[[]]，可以是多级表头[['A1'，'B1'],['A2'，'B2']]
* @param {Array[Object]} config.merges 需要合并的单元格，需要考虑表头的行数[{row:1, col:1, rowspan: 1, colspan: 2}]
* @param {Array[Object]} config.attrs 单元格样式配置
* @param {Array[Object]} config.views 工作表视图配置
* @param {Array[Number]} config.columnsWidth 每个字段列对应的宽度
* @param {Object} config.protect 工作表保护【此配置会保护全表，一般推荐只针对单元格进行保护配置】
* @param {String} config.sheetName 工作表名称，默认从sheet1开始
* @param {String} fileName excel文件名称
*/
export function exportDataToExcel(config, fileName) {
if (!config) return;
const options = {
fileName: fileName || `导出excel文件【${Date.now()}】.xlsx`,
worksheets: []
}
if (!Array.isArray(config)) {
config = [config]
}
config.forEach((item) => {
// 深拷贝data【JSON.stringify有缺陷，可自行换成_.cloneDeep】
const data = JSON.parse(JSON.stringify(item.data));
const results = data.map(obj => {
return item.fields.map(key => {
return obj[key]
})
})
// 生成完整excel数据
let excelData = [];
excelData = excelData.concat(item.headers).concat(results);
// 单元格合并处理【excel数据的第一行/列是从1开始】
let excelMerges = [];
excelMerges = item.merges.map(m => {
return [m.row + 1, m.col + 1, m.row + m.rowspan, m.col + m.colspan]
})
// 单元格配置处理【excel数据的第一行/列是从1开始】
let excelAttrs = [];
excelAttrs = item.attrs.map(attr => {
attr.rowStart += 1;
attr.rowEnd += 1;
attr.colStart += 1;
attr.colEnd += 1;
return attr
})
options.worksheets.push({
data: excelData,
merges: excelMerges,
attrs: excelAttrs,
views: item.views,
columnsWidth: item.columnsWidth,
protect: item.protect,
sheetName: item.sheetName
})
})
createExcel(options)
}
// 创建Excel文件方法
async function createExcel(options) {
if (!options.worksheets.length) return;
// 创建工作簿
const workbook = new ExcelJS.Workbook();
for (let i = 0; i < options.worksheets.length; i++) {
const sheetOption = options.worksheets[i];
// 创建工作表
const sheet = workbook.addWorksheet(sheetOption.sheetName || 'sheet' + (i + 1));
// 添加数据行
sheet.addRows(sheetOption.data);
// 配置视图
sheet.views = sheetOption.views;
// 单元格合并处理【开始行，开始列，结束行，结束列】
if (sheetOption.merges) {
sheetOption.merges.forEach((item) => {
sheet.mergeCells(item);
});
}
// 工作表保护
if (sheetOption.protect) {
const res = await sheet.protect(sheetOption.protect.password, sheetOption.protect.options);
}
// 单元格样式处理
if (sheetOption.attrs.length) {
sheetOption.attrs.forEach((item) => {
const attr = item.attr || {};
// 获取开始行-结束行; 开始列-结束列
const rowStart = item.rowStart;
const rowEnd = item.rowEnd;
const colStart = item.colStart;
const colEnd = item.colEnd;
if (rowStart) { // 设置行
for (let r = rowStart; r <= rowEnd; r++) {
// 获取当前行
const row = sheet.getRow(r);
if (colStart) { // 列设置
for (let c = colStart; c <= colEnd; c++) {
// 获取当前单元格
const cell = row.getCell(c);
Object.keys(attr).forEach((key) => {
// 给当前单元格设置定义的样式
cell[key] = attr[key];
});
}
} else {
// 未设置列，整行设置【大纲级别】
Object.keys(attr).forEach((key) => {
row[key] = attr[key];
});
}
}
} else if (colStart) { // 未设置行，只设置了列
for (let c = colStart; c <= colEnd; c++) {
// 获取当前列，整列设置【大纲级别】
const column = sheet.getColumn(c);
Object.keys(attr).forEach((key) => {
column[key] = attr[key];
});
}
} else {
// 没有设置具体的行列，则为整表设置
Object.keys(attr).forEach((key) => {
sheet[key] = attr[key];
});
}
})
}
// 列宽设置
if (sheetOption.columnsWidth) {
for (let i = 0; i < sheet.columns.length; i++) {
sheet.columns[i].width = sheetOption.columnsWidth[i]
}
}
}
// 生成excel文件
workbook.xlsx.writeBuffer().then(buffer => {
// application/octet-stream 二进制数据
FileSaver.saveAs(new Blob([buffer], { type: 'application/octet-stream' }), options.fileName)
})
}
2，项目中的使用
然后在需要的地方引入exportDataToExcel方法：
<template>
<div>
<button @click="onExport">导出excel数据</button>
</div>
</template>
<script>
import { exportDataToExcel } from "../utils/index.js";
export default {
data() {
return {
tableList: [
{ name: "张三", age: 20, address: "重庆市江北区福泉路123号" },
{ name: "张三", age: 20, address: "重庆市江北区福泉路123号" },
{ name: "张三", age: 20, address: "重庆市江北区福泉路123号" }
]
};
},
methods: {
onExport() {
const config = this.exportConfig();
exportDataToExcel(config, "张三个人信息表.xlsx");
},
# 导出配置【根据自己的需求自由配置】
exportConfig() {
const header = ["姓名", "年龄", "地址"];
// 如果导出前要处理数据，需要深克隆一份表格数据，然后进行处理
const config = {
data: this.tableList,
fields: ["name", "age", "address"],
headers: [header],
merges: [],
attrs: [],
view: [],
columnsWidth: [20, 20, 30],
// protect: {},
sheetName: "个人信息"
};
// 设置全表单元格边框，居中布局
config.attrs.push({
rowStart: 0,
rowEnd: config.data.length,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
alignment: { vertical: "middle", horizontal: "center" },
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" }
}
}
});
// 设置表头填充颜色，字体加粗
config.attrs.push({
rowStart: 0,
rowEnd: 0,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
fill: {
type: "pattern",
pattern: "solid",
fgColor: { argb: "99CCFF" }
},
font: {
bold: true
}
}
});
return config;
}
}
};
</script>
3，常见的导出配置
全表保护
可以在config中定义protect选项，即可开启全表保护：
exportConfig() {
const config = {
...
# 全表保护
protect: {
password: '123',
options: {}
},
}
}
如果想要修改默认的保护选项options，可以查看官方文档工作表保护选项。
单元格保护
在更多的情况下，我们应该针对某个字段的列进行保护，而不是整个工作表。注意单元格的保护是在全表保护的前提下设置的，解锁某些列，剩下的列就是需要保护的。
比如我们要保护【姓名】不允许修改，可以修改年龄和地址。
# 第一种设置方式
exportConfig() {
const config = {
...
# 全表保护
protect: {
password: '123',
options: {}
},
}
// 解锁年龄，地址列
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: 1,
colEnd: 2,
attr: {
protection: {
// 解锁
locked: false
}
}
});
}
# 第二种设置方式
exportConfig() {
const config = {
...
# 全表保护
protect: {
password: '123',
options: {}
},
}
// 解锁全表
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
protection: {
// 解锁
locked: false
}
}
});
// 给姓名列加锁
config.fields.forEach((item, index) => {
if (item === 'name') {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
protection: {
// 加锁
locked: true
}
}
});
}
})
}
- 第一种方式：解锁可以编辑列，剩下的就是保护的列。
- 第二种方式：先解锁全列，再加锁需要保护的列。
单元格对齐
一般给全表设置一个对齐方式即可：
exportConfig() {
const config = {
...
attrs: []
}
// 设置全表对齐
config.attrs.push({
rowStart: 0,
rowEnd: config.data.length,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
// 对齐方式
alignment: {
vertical: "middle", // 垂直居中
horizontal: "center", // 水平居中
wrapText: true // 自动换行
}
}
}
}
也可以给某一列设置不同的对齐：
exportConfig() {
const config = {
...
attrs: []
}
// 给age列独立设置水平靠右
config.fields.forEach((item, index) => {
if (item === "age") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
alignment: {
horizontal: "right", // 水平靠右
}
}
});
}
});
}
单元格边框
一般直接设置全表边框即可：
exportConfig() {
const config = {
...
attrs: []
}
// 设置全表边框
config.attrs.push({
rowStart: 0,
rowEnd: config.data.length,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
// 边框
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" }
}
}
}
}
单元格字体
设置单元格的字体类型，大小以及加粗等等：
exportConfig() {
const config = {
...
attrs: []
}
// 给姓名列设置字体样式
config.fields.forEach((item, index) => {
if (item === "name") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
font: {
name: "Arial", // 字体名称
size: 10, // 字体大小
color: { argb: 'eeeeee' }, // 字体颜色
bold: true, // 是否加粗
italic: true, // 是否倾斜
...
}
}
});
}
});
}
单元格填充
exportConfig() {
const config = {
...
attrs: []
}
// 给姓名列设置填充
config.fields.forEach((item, index) => {
if (item === "name") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
fill: {
type: "pattern",
pattern: "solid",
// 一般只需要配置填充前景色即可
fgColor: { argb: "eeeeee" }
}
}
});
}
});
}
单元格数字格式
一般金额字段和日期字段的显示需要进行配置：
我们给表格新加两列：【出生日期】【存款】
tableList: [
{ name: "张三", birthday: '2003-01-01', age: 20, deposit: 20000, address: "重庆市江北区福泉路123号" },
{ name: "张三", birthday: '2003-01-01', age: 20, deposit: 20000, address: "重庆市江北区福泉路123号" },
{ name: "张三", birthday: '2003-01-01', age: 20, deposit: 20000, address: "重庆市江北区福泉路123号" }
]
exportConfig() {
const config = {
...
attrs: []
}
config.fields.forEach((item, index) => {
// 设置日期显示
if (item === "birthday") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
numFmt: 'yyyy"年"m"月"d"日"'
}
});
}
// 设置金额显示
if (item === "deposit") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
numFmt: "¥#,##0.00;¥-#,##0.00"
}
});
}
});
}
常见的配置格式：
numFmt: "0.00%" // 百分比 0.015 => 1.5%
numFmt: "#,##0.00" // 普通数值格式，保留两位小数
numFmt: "#,##0.000" // 普通数值格式，保留三位小数
numFmt: "¥#,##0.00;¥-#,##0.00" // ￥金额格式，保留两位小数
numFmt: "$#,##0.00;$-#,##0.00" // $金额格式，保留两位小数
numFmt: "yyyy-mm-dd" // 2003-01-01
# 更多的配置格式参数Excel单元格数字格式...
数据验证
数字格式numFmt字段更多的情况下是配合dataValidation进行数据验证，控制用户的编辑：
exportConfig() {
const config = {
...
attrs: []
}
config.fields.forEach((item, index) => {
// 数据验证，只允许输入日期格式
if (item === "birthday") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
numFmt: 'yyyy-mm-dd',
dataValidation: {
type: "date",
showErrorMessage: true,
error: "必须为日期格式[yyyy-MM-dd]",
allowBlank: true
}
}
});
}
// 限制输入的范围
if (item === "salary") {
config.attrs.push({
rowStart: 1,
rowEnd: config.data.length,
colStart: index,
colEnd: index,
attr: {
numFmt: "0",
dataValidation: {
type: "decimal",
operator: "between",
showErrorMessage: true,
error: "工资只允许在[0, 3000]范围内",
allowBlank: true,
formulae: [0, 3000]
}
}
});
}
});
}
多级表头
多级表头是我们项目也比较常见的需求，它的实现也并不复杂。
tableList: [
{ name: "张三", birthday: "2003-01-01", province: '重庆', city: '江北区', address: "重庆市江北区福泉路123号" },
{ name: "张三", birthday: "2003-01-01", province: '重庆', city: '江北区', address: "重庆市江北区福泉路123号" },
{ name: "张三", birthday: "2003-01-01", province: '重庆', city: '江北区', address: "重庆市江北区福泉路123号" }
]
exportConfig() {
// 有几行表头就需要定义几个表头
const header1 = ["人员", "详细信息", "", "", ""];
const header2 = ["", "出生日期", "居住地址", "", ""];
const header3 = ["", "", "省份", "城市", "地址"];
const merges = [
{row: 0, col: 0, rowspan: 3, colspan: 1},
{row: 0, col: 1, rowspan: 1, colspan: 4},
{row: 1, col: 1, rowspan: 2, colspan: 1},
{row: 1, col: 2, rowspan: 1, colspan: 3},
]
const config = {
data: this.tableList,
fields: ["name", "birthday", "province", "city", "address"],
headers: [header1, header2, header3],
merges: [],
attrs: [],
columnsWidth: [20, 20, 30, 30, 30],
sheetName: "个人信息"
};
// 设置单元格样式
config.attrs.push({
rowStart: 0,
rowEnd: config.data.length + 2,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
alignment: { vertical: "middle", horizontal: "center" },
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" }
}
}
});
// 设置表头填充颜色，字体加粗
config.attrs.push({
rowStart: 0,
rowEnd: 2,
colStart: 0,
colEnd: config.fields.length - 1,
attr: {
fill: {
type: "pattern",
pattern: "solid",
fgColor: { argb: "99CCFF" }
},
font: {
bold: true
}
}
});
return config;
}
merges的参数说明：【开始行，开始列，合并单元格的行数，合并单元格的列数】
# 人员-表头
第一行，第一列， 合并三行，合并一列
{row: 0, col: 0, rowspan: 3, colspan: 1},
# 详细信息-表头
第一行，第二列， 合并一行，合并四列
{row: 0, col: 1, rowspan: 1, colspan: 4},
merges以及attrs的配置都会根据表头的变化而变化，所以先确定表头headers的配置是关键。
扩展： merges的参数与你的封装方式息息相关。
export function exportDataToExcel(config, fileName) {
let excelMerges = [];
// 修改封装方式
excelMerges = item.merges.map(m => {
return [m.rowStart + 1, m.colStart + 1, m.rowEnd + 1, m.colEnd + 1]
})
}
那么merges的参数就应该使用Exceljs默认的的【开始行，开始列，结束行，结束列】
# 人员-表头
第一行，第一列， 到第三行结束，到第一列结束
{rowStart: 0, colStart: 0, rowEnd: 2, colEnd: 0},
# 详细信息-表头
第一行，第二列， 到第一行结束，到第四列结束
{rowStart: 0, colStart: 1, rowEnd: 0, colEnd: 3},
以上两种配置在处理后都是一样的效果，随便选择哪种方式都可以。
三，结语
本节主要讨论了基于Exceljs插件的前端导出，封装了一个导出数据到excel方法，以及展示了一些常见的导出配置。觉得对你有帮助的小伙伴可以点赞收藏，封装的exportDataToExcel方法可以直接复制到项目进行使用。
