首先安装插件
npm install exceljs file-saver
第一种 简单导出
//页面引入
import ExcelJS from 'exceljs';
import {saveAs} from 'file-saver';
export default {
methods: {
/** 导出操作 */
async handleExportFun() {
let that = this
// 获取当前年月日 用户下载xlsx的文件名称设置
const now = new Date();
const year = now.getFullYear();
const month = String(now.getMonth() + 1).padStart(2, '0'); // 月份从0开始，需要加1，并确保两位数
const day = String(now.getDate()).padStart(2, '0'); // 确保两位数
const hours = String(now.getHours()).padStart(2, '0'); // 确保两位数
const minutes = String(now.getMinutes()).padStart(2, '0'); // 确保两位数
var currentTime = `${year}${month}${day}${hours}${minutes}`;
//创建
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet1');
// 假设 exportData 的第一行是列头
const headers = this.exportData[0]; // 确保这里是获取的第一行数据作为列头
// 填充表头数据 因为我的数据是不固定的二维数组
worksheet.columns = headers.map((headerText, index) => ({
header: headerText,
key: index.toString(),
width: 22,
}));
// 设置表头行高
const headerRow = worksheet.getRow(1);
headerRow.height = 35; // 设置行高为35（可以根据需要调整）
//申明样式
let style = {
alignment: {
vertical: "middle",
horizontal: "center",
wrapText: true,
},
fill: {
type: "pattern",
pattern: "solid",
fgColor: {
argb: "EEEEEE"
},
},
border: {
top: {
style: "thin",
color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色
}, // 设置上边框样式为细线
bottom: {
style: "thin",
color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色
}, // 设置下边框样式为细线
left: {
style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色
}, // 设置左边框样式为细线
right: {
style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色
}, // 设置右边框样式为细线
},
font: {
bold: true, // 设置字体加粗
name: 'Arial', // 设置字体名称（可选）
size: 12 // 设置字体大小（可选）
}
};
// 填充工作表的其他数据，并设置行高
for (let rowIndex = 1; rowIndex < this.exportData.length; rowIndex++) {
const row = this.exportData[rowIndex];
const excelRow = worksheet.getRow(rowIndex + 1);
excelRow.height = 30; // 设置其他行的行高为25（可以根据需要调整）
row.forEach((cellValue, cellIndex) => {
const cell = excelRow.getCell(cellIndex + 1);
cell.value = cellValue;
});
}
//给每行设置样式
worksheet.eachRow((row, rowNumber) => {
if (rowNumber > 0) {
row.height = 35;
row.alignment = style.alignment;
// 你还可以根据单元格的内容或列号执行不同的操作
row.eachCell((cell, colNumber) => {
if (rowNumber === 1&&colNumber<=that.exportData[0].length) {
//对第一列的单元格执行特殊操作
cell.font=style.font
cell.fill=style.fill
}
cell.border = style.border;
});
}
});
try {
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
saveAs(blob, currentTime + '_设备数据.xlsx'); // 导出的文件名可以根据需要动态设置
} catch (err) {
console.error('导出Excel时出错:', err);
}
}
}
}
第二种复杂的表单导出
如图
数据格式
this.summaryStatistics={"type": null,"xuhao": null,"user": "汇总",
"timeJson": [{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":129584000.80,\"fyPrice\":129592366.80,\"fyzb\":738.96,\"htPrice\":17537026.00,\"swPrice\":8366.00,\"time\":\"2024年\"}],
"htXj": 17537026,"htZb": null,"fyXj": 129592366.8,"fyZbXj": 738.96
}
this.times = this.summaryStatistics.timeJson
this.userStatistics = [
{
"type": null,"xuhao": 1,"user": "admin",
"timeJson": "[{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":274000.00,\"swPrice\":0.00,\"time\":\"2024年\"}]","htXj": 274000,"htZb": 1.56,"fyXj": 0,"fyZbXj": 0
},
{
"type": null,"xuhao": 2,"user": "肖永梅",
"timeJson": "[{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2024年\"}]",
"htXj": 0,"htZb": 0,"fyXj": 0,"fyZbXj": 0
}
]
//将数据添在第一行
this.userStatistics.unshift(this.summaryStatistics);
this.userStatistics.forEach((item) => {
if (item.timeJson != null) {
JSON.parse(item.timeJson).forEach((child, index) => {
item[`htPrice${index}`] = child.htPrice;
item[`fyPrice${index}`] = child.fyPrice;
item[`fyzb${index}`] = child.fyzb;
});
}
});
数据准备好后就是导出方法了（handleExport）
/** 导出按钮操作 */
handleExport() {
const loading = this.$loading({
lock: true,
text: "导出excel中...（若长时间无响应请刷新页面）",
spinner: "el-icon-loading",
background: "rgba(0, 0, 0, 0.7)",
});
const Exceljs = require("exceljs"); // 引入exceljs
const workbook = new Exceljs.Workbook(); // 创建工作簿
const workSheet = workbook.addWorksheet("sheet1"); // 创建工作表（sheet1）
workSheet.getRow(1).height = 60;
workSheet.getRow(2).height = 36;
workSheet.getRow(3).height = 28;
workSheet.getRow(4).height = 28;
let style = {
alignment: {
vertical: "middle",
horizontal: "center",
wrapText: true,
},
fill: {
type: "pattern",
pattern: "solid",
fgColor: { argb: "E3F2D9" },
},
border: {
top: { style: "thin" }, // 设置上边框样式为细线
bottom: { style: "thin" }, // 设置下边框样式为细线
left: { style: "thin" }, // 设置左边框样式为细线
right: { style: "thin" }, // 设置右边框样式为细线
},
};
// 获取动态年份条数
let years = this.times.length;
workSheet.mergeCells(`A1:${this.numberToMatchLetter(3 * years + 6)}1`); //将A1到xx的单元格合并
const cell_A1 = workSheet.getCell("A1"); // 获取A1单元格
cell_A1.value = "碧朗科技销售员销售额及费用统计表";
cell_A1.font = { size: 18, bold: true, name: "宋体" }; // 设置字体大小为16，加粗，仿宋
cell_A1.alignment = style.alignment;
workSheet.mergeCells(
`${this.numberToMatchLetter(3 * years + 7)}1:${this.numberToMatchLetter(
3 * years + 10
)}1`
); //将xx1到xx1的单元格合并
const cell_P1 = workSheet.getCell(
`${this.numberToMatchLetter(3 * years + 7)}1`
); // 获取xx1单元格
cell_P1.value = "注：第4行的年份根据筛选条件动态变化";
cell_P1.font = {
size: 11,
name: "Times New Roman",
color: { argb: "FF0000" },
};
cell_P1.alignment = style.alignment;
workSheet.mergeCells("A2:E2"); //将A2到E2的单元格合并
const cell_A2 = workSheet.getCell("A2"); // 获取A2单元格
cell_A2.value = `年份：${
this.queryParams.startYear ? this.queryParams.startYear : ""
}-${this.queryParams.endYear ? this.queryParams.endYear : ""} 月份：${
this.queryParams.startMonth ? this.queryParams.startMonth : ""
}-${this.queryParams.endMonth ? this.queryParams.endMonth : ""}`;
cell_A2.font = { size: 8, bold: true, name: "宋体" };
cell_A2.alignment = {
vertical: "middle",
wrapText: true,
};
workSheet.mergeCells("A3:A4"); //将A3到A4的单元格合并
const cell_A3 = workSheet.getCell("A3"); // 获取A3单元格
cell_A3.value = "序号";
cell_A3.style = style;
cell_A3.font = { size: 11, bold: true, name: "宋体" };
// 遍历合并A3到A4的单元格的区域，为其余部分设置边框
for (let row = 3; row <= 4; row++) {
for (let col = 1; col <= 1; col++) {
const cell = workSheet.getCell(row, col);
cell.border = cell_A3.border;
}
}
workSheet.mergeCells("B3:B4"); //将B3到B4的单元格合并
const cell_B3 = workSheet.getCell("B3"); // 获取B3单元格
cell_B3.value = "销售员";
cell_B3.style = style;
cell_B3.font = { size: 11, bold: true, name: "宋体" };
// 遍历合并B3到B4的单元格的区域，为其余部分设置边框
for (let row = 3; row <= 4; row++) {
for (let col = 2; col <= 2; col++) {
const cell = workSheet.getCell(row, col);
cell.border = cell_B3.border;
}
}
workSheet.mergeCells(`C3:${this.numberToMatchLetter(years + 4)}3`);
const cell_diy1 = workSheet.getCell("C3");
cell_diy1.value = "销售额";
cell_diy1.style = style;
cell_diy1.font = { size: 11, bold: true, name: "宋体" };
// 遍历合并的单元格的区域，为其余部分设置边框
for (let row = 3; row <= 3; row++) {
for (let col = 3; col <= years + 4; col++) {
const cell = workSheet.getCell(row, col);
cell.border = cell_diy1.border;
}
}
workSheet.mergeCells(
`${this.numberToMatchLetter(years + 5)}3:${this.numberToMatchLetter(
2 * years + 5
)}3`
);
const cell_diy2 = workSheet.getCell(
`${this.numberToMatchLetter(years + 5)}3`
);
// 创建富文本内容
const richText = [
{
text: "费用",
font: {
name: "宋体",
size: 11,
bold: true,
},
},
{
text: "（报销的费用+备案商务费）",
font: {
name: "宋体",
size: 9,
bold: true,
color: { argb: "FF0000" },
},
},
];
cell_diy2.value = { richText: richText };
cell_diy2.style = style;
// 遍历合并的单元格的区域，为其余部分设置边框
for (let row = 3; row <= 3; row++) {
for (let col = years + 5; col <= 2 * years + 5; col++) {
const cell = workSheet.getCell(row, col);
cell.border = cell_diy2.border;
}
}
workSheet.mergeCells(
`${this.numberToMatchLetter(2 * years + 6)}3:${this.numberToMatchLetter(
3 * years + 6
)}3`
);
const cell_diy3 = workSheet.getCell(
`${this.numberToMatchLetter(2 * years + 6)}3`
);
cell_diy3.value = "费用占比(%)";
cell_diy3.style = style;
cell_diy3.font = { size: 11, bold: true, name: "宋体" };
// 遍历合并的单元格的区域，为其余部分设置边框
for (let row = 3; row <= 3; row++) {
for (let col = 2 * years + 6; col <= 3 * years + 6; col++) {
const cell = workSheet.getCell(row, col);
cell.border = cell_diy3.border;
}
}
const cell_XJ1 = workSheet.getCell(
`${this.numberToMatchLetter(3 + years)}4`
); // 获取单元格
cell_XJ1.value = "小计";
cell_XJ1.style = style;
cell_XJ1.font = { size: 11, bold: true, name: "宋体" };
const cell_ZB1 = workSheet.getCell(
`${this.numberToMatchLetter(4 + years)}4`
); // 获取单元格
cell_ZB1.value = "占比(%)";
cell_ZB1.style = style;
cell_ZB1.font = { size: 11, bold: true, name: "宋体" };
const cell_XJ2 = workSheet.getCell(
`${this.numberToMatchLetter(5 + 2 * years)}4`
); // 获取单元格
cell_XJ2.value = "小计";
cell_XJ2.style = style;
cell_XJ2.font = { size: 11, bold: true, name: "宋体" };
const cell_XJ3 = workSheet.getCell(
`${this.numberToMatchLetter(6 + 3 * years)}4`
); // 获取单元格
cell_XJ3.value = "小计";
cell_XJ3.style = style;
cell_XJ3.font = { size: 11, bold: true, name: "宋体" };
// 销售额动态年份填充
for (let i = 1; i <= years; i++) {
workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).value =
this.times[i - 1].time;
workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).style = style;
workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).font = {
size: 11,
bold: true,
name: "宋体",
};
workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).value =
this.times[i - 1].time;
workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).style =
style;
workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).font =
{
size: 11,
bold: true,
name: "宋体",
};
workSheet.getCell(
`${this.numberToMatchLetter(2 * years + 5 + i)}4`
).value = this.times[i - 1].time;
workSheet.getCell(
`${this.numberToMatchLetter(2 * years + 5 + i)}4`
).style = style;
workSheet.getCell(
`${this.numberToMatchLetter(2 * years + 5 + i)}4`
).font = {
size: 11,
bold: true,
name: "宋体",
};
}
let exportTableProp = [
{ key: "xuhao" },
{ key: "user" },
{ key: "htXj", width: 18 },
{ key: "htZb", width: 18 },
{ key: "fyXj", width: 18 },
{ key: "fyZbXj", width: 18 },
];
if (this.times.length > 0) {
this.times.forEach((item, index) => {
exportTableProp.splice(index + 2, 0, {
key: `htPrice${index}`,
width: 18,
});
});
this.times.forEach((item, index) => {
exportTableProp.splice(index + years + 4, 0, {
key: `fyPrice${index}`,
width: 18,
});
});
this.times.forEach((item, index) => {
exportTableProp.splice(index + 2 * years + 5, 0, {
key: `fyzb${index}`,
width: 18,
});
});
}
workSheet.columns = exportTableProp; // 工作表添加表头
workSheet.addRows(this.userStatistics); // 往工作表插入数据
workSheet.eachRow((row, rowNumber) => {
if (rowNumber > 4) {
row.height = 20;
row.alignment = style.alignment;
row.eachCell(
{
start: 1,
end: 3 * years + 6,
},
(cell, colNumber) => {
cell.border = style.border;
}
);
}
});
// 下载工作簿
workbook.xlsx.writeBuffer().then((buffer) => {
saveAs(
new Blob([buffer], { type: "application/octet-stream" }),
"销售员销售额及费用表格导出.xlsx"
);
});
loading.close();
}
