【exceljs】导出excel文件，比如设置列样式、单元格样式、数据校验、添加图片
步骤
安装依赖
npm i exceljs@4.3.0
npm i file-saver@2.0.5
基本案例
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
exportExcel() {
const excel_name = "test.xlsx";
// 创建工作簿
const wb = new ExcelJS.Workbook();
// 添加工作表
const ws = wb.addWorksheet("sheet1");
// 设置表格内容
const _titleCell = ws.getCell("A1");
_titleCell.value = "Hello ExcelJS!";
// 导出表格
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
设置表头，添加数据
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
// 表头与key之间的关系
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 20,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 30,
},
{
header: "电话号码",
key: "phone_number",
width: 50,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
ws.addRows(this.tableData);
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
列样式设置+筛选+渐变
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
// 方式1：单独为每个表头column设置
// ws.columns = [
// {
// header: "员工ID",
// key: "employee_id",
// },
// {
// header: "姓名-呀呀呀",
// key: "first_name",
// width: 30,
// style: {
// alignment: {
// horizontal: "center",
// vertical: "center",
// },
// },
// },
// {
// header: "电话号码",
// key: "phone_number",
// },
// {
// header: "雇佣日期",
// key: "hire_date",
// },
// ];
// ws.addRows(this.tableData);
// 方式2：为某个单元格设置
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 10,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 20,
},
{
header: "电话号码",
key: "phone_number",
width: 20,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
const row1 = ws.getRow(1);
row1.height = 30;
// 设置字体样式
row1.font = {
name: "黑体",
bold: true,
size: 14,
color: {
// 注意：在 exceljs 中所有的的颜色值均为 argb 格式，且不带 # 符号
argb: "ff0000",
},
};
// 设置对齐方式（水平垂直）
row1.alignment = {
vertical: "middle",
horizontal: "center",
};
// 设置单元格填充的样式
row1.fill = {
type: "pattern",
pattern: "solid",
fgColor: {
argb: "FFF5F7FA",
},
};
// 设置变框
row1.border = {
left: {
style: "dotted",
color: "#d81ef7",
},
};
// 设置筛选
// 方式1：设置某个单元格
// ws.autoFilter = "A1";
// 方式2：设置从哪个单元格到哪个单元格
// ws.autoFilter = {
// from: {
// row: 1,
// column: 1,
// },
// to: {
// row: 1,
// column: 3,
// },
// };
ws.addRows(this.tableData);
// 渐变
ws.getCell("C4").fill = {
type: "gradient",
gradient: "angle",
degree: 0,
stops: [
{ position: 0, color: { argb: "FF0000FF" } },
{ position: 0.5, color: { argb: "FFFFFFFF" } },
{ position: 1, color: { argb: "FF0000FF" } },
],
};
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
单元格合并
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
data() {
return {
tableData: [
{
employee_id: 100,
first_name: "Steven",
phone_number: "123456789",
hire_date: "1987-06-17",
address: "广州市",
},
{
employee_id: 100,
first_name: "Neena Kochhar",
phone_number: "123456789",
hire_date: "1989-09-21",
},
{
employee_id: 102,
first_name: "Alexander",
phone_number: "5874517",
hire_date: "1990-01-03",
},
],
};
},
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "员工ID",
key: "employee_id",
width: 10,
},
{
header: "姓名-呀呀呀",
key: "first_name",
width: 20,
},
{
header: "电话号码",
key: "phone_number",
width: 20,
},
{
header: "雇佣日期",
key: "hire_date",
width: 20,
},
];
// 注意：要在合并单元格前将数据填充进去
ws.addRows(this.tableData);
// 行与行之间的合并
ws.mergeCells("A2:A3");
ws.getCell("A2").alignment = {
vertical: "middle",
horizontal: "center",
};
ws.getCell("A2").font = {
bold: true,
};
// 按左上，右下合并
// ws.mergeCells("C5:D6");
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
公式值
比如：统计总和，平均值等
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook({});
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "学生ID",
key: "stu_id",
width: 10,
},
{
header: "语文",
key: "chinese",
width: 20,
},
{
header: "数学",
key: "math",
width: 20,
},
{
header: "英语",
key: "english",
width: 20,
},
];
const data = [
{
stu_id: 100,
chinese: 85,
math: 99,
english: 76,
},
{
stu_id: 101,
chinese: 70,
math: 90,
english: 80,
},
{
stu_id: 102,
chinese: 99,
math: 99,
english: 97,
},
];
ws.addRows(data);
// 注意：ExcelJS 无法处理公式以生成结果，必须提供该公式。
// formula 只是设置公式
// ws.getCell("B5").value = {
// formula: "SUM(B2,B4)",
// };
// 方式1：自己去计算，将结果放到result里
// ws.getCell("B5").value = {
// formula: "SUM(B2,B4)",
// result:
// ws.getCell("B2").value +
// ws.getCell("B3").value +
// ws.getCell("B4").value,
// };
// 方式2:设置在初始加载时让excel重新计算所有结果。
// 缺点：打开文件后文件不是保存状态的了
// 参考文档：https://github.com/exceljs/exceljs/issues/431
wb.calcProperties.fullCalcOnLoad = true;
ws.getCell("C5").value = {
formula: "=AVERAGE(C2:C4)",
};
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
数据验证
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook({});
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "学生ID",
key: "stu_id",
width: 10,
},
{
header: "语文",
key: "chinese",
width: 20,
},
{
header: "数学",
key: "math",
width: 20,
},
{
header: "英语",
key: "english",
width: 20,
},
];
const data = [
{
stu_id: 100,
chinese: 85,
math: 99,
english: 76,
},
{
stu_id: 101,
chinese: 70,
math: 90,
english: 80,
},
{
stu_id: 102,
chinese: 99,
math: 99,
english: 97,
},
];
ws.addRows(data);
// 设置数据校验规则
ws.getCell("E1").dataValidation = {
type: "list",
// 注意格式
formulae: ['"值1,值2,值3"'],
};
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
条件格式
比如：根据不同的条件对单元格的样式进行调整
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook({});
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "学生ID",
key: "stu_id",
width: 10,
},
{
header: "语文",
key: "chinese",
width: 20,
},
{
header: "数学",
key: "math",
width: 20,
},
{
header: "英语",
key: "english",
width: 20,
},
];
const data = [
{
stu_id: 100,
chinese: 85,
math: 99,
english: 76,
},
{
stu_id: 101,
chinese: 70,
math: 90,
english: 80,
},
{
stu_id: 102,
chinese: 99,
math: 99,
english: 97,
},
{
stu_id: 103,
chinese: 80,
math: 80,
english: 80,
},
];
ws.addRows(data);
// 需求：对语文小于80分显示粉色
ws.addConditionalFormatting({
ref: "B2:B4",
rules: [
{
type: "cellIs",
operator: "lessThan",
priority: 1,
formulae: [80],
style: {
fill: {
type: "pattern",
pattern: "solid",
bgColor: { argb: "FF0000" },
},
},
},
],
});
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
},
},
};
</script>
<style scoped></style>
将图片添加到工作表
<template>
<div class="test">
<button @click="exportExcel">点击导出excel</button>
</div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
methods: {
getBase64Image(url) {
const img = new Image();
//因为是网络资源所以会有图片跨域问题产生，此属性可以解决跨域问题，下文详解
img.setAttribute("crossOrigin", "anonymous");
//如果需要兼容ios，这两个顺序一定不能换，先设置crossOrigin后设置src
img.src = url;
return new Promise((resolve, reject) => {
img.onload = () => {
//canvas基本配置
const canvas = document.createElement("canvas");
canvas.width = img.width;
canvas.height = img.height;
const ctx = canvas.getContext("2d");
ctx.drawImage(img, 0, 0, canvas.width, canvas.height);
resolve({
success: true,
//canvas.toDataURL的方法将图片的绝对路径转换为base64编码
base64: canvas.toDataURL(),
});
};
img.onerror = () => {
reject({ success: false });
};
});
},
exportExcel() {
const excel_name = "test.xlsx";
const wb = new ExcelJS.Workbook({});
const ws = wb.addWorksheet("sheet1");
ws.columns = [
{
header: "学生ID",
key: "stu_id",
width: 10,
},
{
header: "语文",
key: "chinese",
width: 20,
},
{
header: "数学",
key: "math",
width: 20,
},
{
header: "英语",
key: "english",
width: 20,
},
];
const data = [
{
stu_id: 100,
chinese: 85,
math: 99,
english: 76,
},
{
stu_id: 101,
chinese: 70,
math: 90,
english: 80,
},
{
stu_id: 102,
chinese: 99,
math: 99,
english: 97,
},
{
stu_id: 103,
chinese: 80,
math: 80,
english: 80,
},
];
ws.addRows(data);
// 需求：往某个单元格设置图片
// 方式1：通过base64编码方式
// const imgPath = require("./assets/1.png");
// const { base64 } = await this.getBase64Image(imgPath);
// const imageId1 = wb.addImage({
// base64,
// extension: "png",
// });
// // 注意：参数2要求的格式 单元格x:单元格y
// ws.addImage(imageId1, "E2:E2");
// wb.xlsx.writeBuffer().then((buffer) => {
// let file = new Blob([buffer], {
// type: "application/octet-stream",
// });
// FileSaver.saveAs(file, excel_name);
// });
// 设置行高
const row6 = ws.getRow(6);
row6.height = 50;
// 方式2：通过网络请求资源得到buffer
// 参考文档：https://github.com/exceljs/exceljs/issues/1216
const filePath = require("./assets/1.png");
fetch(filePath).then((res) => {
const imageId2 = wb.addImage({
buffer: res.arrayBuffer(),
extension: "png",
});
// ws.addImage(imageId2, "B6:D10");
// 将图片设置在 第6行第2列的位置； +0.5 即在单元格在偏移 （值要在[0,1)之间）
ws.addImage(imageId2, {
tl: {
col: 2 - 1 + 0.8,
row: 6 - 1 + 0.5,
},
ext: {
width: 30,
height: 30,
},
});
wb.xlsx.writeBuffer().then((buffer) => {
let file = new Blob([buffer], {
type: "application/octet-stream",
});
FileSaver.saveAs(file, excel_name);
});
});
},
},
};
</script>
<style scoped></style>
效果图：
