步骤

安装依赖

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);
      // });

      // 方式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");

        wb.xlsx.writeBuffer().then((buffer) => {
          let file = new Blob([buffer], {
            type: "application/octet-stream",
          });
          FileSaver.saveAs(file, excel_name);
        });
      });
    },
  },
};
</script>
<style scoped></style>

参考文档

• 官方文档：https://github.com/exceljs/exceljs/blob/master/README_zh.md
• https://segmentfault.com/a/1190000042028092

本栏目推荐文章
• C++实现文件内查找字符串
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• IDEA项目名称后面出现中括号，模块Modules的名子和文件夹名称不同，可以右键修改名称也可以在File->Project Structure 修改Modules的Name(快捷键ctrl+Shift+Alt+s)
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据

样式 单元 exceljs 文件 数据样式 单元exceljs文件 exceljs文件excel exceljs数据excel 表头zurmokeeper exceljs文件 单元 公式 样式 字体 样式 单元 方式excel 样式 单元 解决方案 方案 单元 文件 数据excel python-docx样式 表格 单元 样式 单元listview字体