1. 版本说明
// "exceljs": "^4.2.1" https://www.npmjs.com/package/exceljs
// "file-saver": "^2.0.5",
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
2. 简单导出（无合并单元格）
// 前置数据
const resList = [
{applyForName:"张三",title:"报销主题1",/*....其他数据*/},
{applyForName:"李四",title:"报销主题2",/*....其他数据*/},
/*....其他数据*/
];
const columns = [
{header:"序号",key:"index",width:10},
{header:"报销人",key:"applyForName",width:20},
{header:"报销主题",key:"title",width:40},
];
// ----------------------------------------------
handleExport() {
const resList = this.resList.map((i, idx) => ({
index: idx + 1,
applyForName:i.applyForName,//报销人
title:i.expenseTitle,//报销主题
// ...其他数据
}));
this.commonExport("expenseTableRef", resList , "报销单明细数据");
// this.commonExport(columns, resList , "报销单明细数据");
},
/**
* 简单数据(无合并单元格)excel导出
* @param elTableRef elTable ref对象
* @param tableData elTable 数据
* @param fileName 文件名
*/
commonExport(elTableRef, tableData, fileName) {
// 返回表头数组
const getTableHeader = () => {
return this.$refs[elTableRef].columns.map(column => column.label);
};
// 返回表格行数据数组
const getTableRow = row => {
return this.$refs[elTableRef].columns.map(
column => row[column.property]
);
};
const workbook = new ExcelJS.Workbook(); // 创建工作簿
const worksheet = workbook.addWorksheet("Sheet"); // 创建工作表
worksheet.addRow(getTableHeader()); // 添加表头
// 添加表格数据
tableData.forEach(row => { worksheet.addRow(getTableRow(row)); });
// 定义表格样式
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.border = { style: "thin" };
});
});
// 定义每列的宽度
worksheet.columns.forEach((column, index) => {
column.width = index < 1 ? 10 : 20; // 假设前三列宽度为20，其余为30
});
// 定义工作表视图
workbook.xlsx.writeBuffer().then(data => {
// 使用 FileSaver 保存文件
const blob = new Blob([data], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"
});
saveAs(blob, (fileName || "数据导出") + ".xlsx");
});
}
/**
* 简单数据(无合并单元格)excel导出
* @param columns 表头数据
* @param tableData elTable 数据
* @param fileName 文件名
*/
commonExport2(columns, tableData, fileName) {
// 返回表格行数据数组
const getTableRow = row => {
return columns.map(c=> row[c.key]);
};
const workbook = new ExcelJS.Workbook(); // 创建工作簿
const worksheet = workbook.addWorksheet("Sheet"); // 创建工作表
//// 添加表头 方式1
// worksheet.addRow(columns.map(c=>c.header));
// 添加表头 方式2 ,自动读取width,style等参数，具体参照官网 https://www.npmjs.com/package/exceljs#styles
worksheet.column = column;
// 添加表格数据
tableData.forEach(row => { worksheet.addRow(getTableRow(row)); });
// 定义表格样式
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
if(rowNumber <= 1) {
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.border = { style: "thin" };
cell.font = {
bold: true, // 字体加粗
size: 12, // 字体大小
color: { argb: "FF000000" } // 字体颜色
};
cell.fill = {
type: "pattern",
pattern: "solid",
fgColor: { argb: "FFF0F0F0" } // 背景颜色
};
cell.alignment = {
horizontal: "center", // 水平居中
vertical: "middle" // 垂直居中
};
});
}else{
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.border = { style: "thin" };
});
}
});
// 定义工作表视图
workbook.xlsx.writeBuffer().then(data => {
// 使用 FileSaver 保存文件
const blob = new Blob([data], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"
});
saveAs(blob, (fileName || "数据导出") + ".xlsx");
});
}
2. 合并行导出（合并行）
// 前置数据
const resList = [
{applyForName:"张三",title:"报销主题1",/*....其他数据*/,itemList:[
{ticketNum:"发票号1",money:"20元",time:"2025-01-17"},
{ticketNum:"发票号2",money:"25.5元",time:"2025-01-27"},
{ticketNum:"发票号3",money:"55元",time:"2025-01-27"},
]},
{applyForName:"李四",title:"报销主题2",/*....其他数据*/,itemList:[]},
/*....其他数据*/
];
const columns = [
{header:"序号",key:"index",width:10,fixed:true},// fixed表示是否合并行
{header:"报销人",key:"applyForName",width:20,fixed:true},
{header:"报销主题",key:"title",width:40,fixed:true},
{header:"发票号",key:"ticketNum",width:20,},
{header:"发票金额",key:"money",width:20,},
{header:"开票时间",key:"time",width:20,},
];
// ----------------------------------------------
handleExport() {
this.commonExport(columns, resList, "报销单明细数据");
},
commonExport(columns, tableData, fileName) {
// 生成行数据
const getRowData = row => {
return columns.map(i => row[i.key]);
};
// 创建工作簿
const workbook = new ExcelJS.Workbook();
// 创建工作表
const worksheet = workbook.addWorksheet("Sheet");
// 添加表头 此种方式自动添加表头（header：表头，key：对应值）
worksheet.columns = columns.map(i => ({ ...i /* style: headerStyle */ }));
// 添加表格数据
let currentIndex = 2, xh = 1; //从此行添加数据 这个是下表（from 0 to ~）
tableData.forEach(rowData => {
//rowData 第一层，itemList.item 第二层
let itemList = rowData.itemList|| [];
if (itemList.length == 0) { // 无子集数据
worksheet.insertRow( currentIndex, getRowData({ xh, ...rowData }) );
currentIndex++;
xh++;
} else { // 存在子集数据
itemList.forEach((itemData, idx2) => {
//在currentIndex插入行数据
worksheet.insertRow( currentIndex,
getRowData({ xh, ...rowData, ...itemData }) ); //如果存在相同key，需自行调整
// 子集数据全部写入完成后，合并行数据
if (idx2 == itemList.length - 1) {
// 写入完成后行合并，merge by start row, start column, end row, end column (equivalent to K10:M12)
// 参考 https://www.npmjs.com/package/exceljs#merged-cells
columns.forEach((c, cidx) => {
if (c.fixed) {
// merge by start row, start column, end row, end column (equivalent to K10:M12)
worksheet.mergeCells(
currentIndex - itemList.length + 1, cidx + 1,
currentIndex,cidx + 1
);
}
});
}
// ++
currentIndex++;
xh++;
});
}
});
// 定义表格样式
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
if (rowNumber <= 1) { //表头加粗、背景色、居中
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.border = { style: "thin" };
cell.font = {
bold: true, // 字体加粗
size: 12, // 字体大小
color: { argb: "FF000000" } // 字体颜色
};
cell.fill = {
type: "pattern",
pattern: "solid",
fgColor: { argb: "FFF0F0F0" } // 背景颜色
};
cell.alignment = {
horizontal: "center", // 水平居中
vertical: "middle" // 垂直居中
};
});
} else { //表数据居中
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.border = { style: "thin" };
cell.alignment = { vertical: "middle", horizontal: "center" };
});
}
});
// 定义工作表视图
workbook.xlsx.writeBuffer().then(data => {
// 使用 FileSaver 保存文件
const blob = new Blob([data], {
type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"
});
saveAs(blob, (fileName || "数据导出") + ".xlsx");
});
},
