前端使用exceljs实现Excel文件导出

1、安装依赖

npm install file-saver exceljs

2、导出excel文件案例

//引入依赖
const ExcelJS = require("exceljs");
import { saveAs } from "file-saver";
export default {
data(){
    return {
      headerRow:["年度", "班级", "排名",'学号','姓名','科目一','科目二','科目三'],
      titleRow:[
           { header: "年度", key: "year", width: 10 },
           { header: "班级", key: "className", width: 30 },
           { header: "排名", key: "sort", width: 30 },
           { header: "学号", key: "studentNumber", width: 30 },
           { header: "姓名", key: "name", width: 30 },
      ],
      tableLafterData:[
          { year: "2023", className: "2301", sort: 1 ,studentNumber:'20231301101',name:''},
           { year: "2023", className: "2301", sort: 2 ,studentNumber:'20231301102',name:''},
          { year: "2023", className: "2301", sort: 3,studentNumber:'20231301103',name:''},
           { year: "2023", className: "2301", sort: 4 ,studentNumber:'20231301104',name:''},
           { year: "2023", className: "2301", sort: 5 ,studentNumber:'20231301105',name:''},
       ]
    }
},
 
methods:{
    exportToExcel() {
      // 创建工作簿
      const _workbook = new ExcelJS.Workbook();
      // 添加工作表
      const _sheet1 = _workbook.addWorksheet("sheet1");
 
       // 设置表格内容
      const _titleCell0 = _sheet1.getCell("A1");
      const _titleCell = _sheet1.getCell("A2");
      _titleCell0.value = "附件1";
      _titleCell.value = "2023年xxx中学高一年级期中考试成绩汇总表";
 
      // 按左，右合并
      _sheet1.mergeCells("A1:H1");
      _sheet1.mergeCells("A2:H2");
 
 
      //第3行插入一行设置表头
      _sheet1.spliceRows(3, 0, []);
 
      this.headerRow.forEach((header, index) => {
        const cell = _sheet1.getCell(3, index + 1);
        cell.value = header;
        cell.font = { bold: true };
        
        // 设置表头单元格的背景颜色为蓝色
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: "00b0f0" }, // 设置为蓝色
        };
        cell.border = {
          top: {
            style: "medium",
            color: {
              argb: "FFEBEEF5",
            },
          },
          left: {
            style: "medium",
            color: {
              argb: "FFEBEEF5",
            },
          },
          bottom: {
            style: "medium",
            color: {
              argb: "FFEBEEF5",
            },
          },
          right: {
            style: "medium",
            color: {
              argb: "FFEBEEF5",
            },
          },
        };
 
            
        // 设置超出列宽时的换行显示
        cell.alignment = {
          wrapText: true,
          vertical: "middle",
          horizontal: "center",
        };
 
     });
 
     // 设置第3行的行高为
      _sheet1.getRow(3).height = 63;
 
 
     // 添加数据到工作表
      this.tableLafterData.forEach((item, rowIndex) => {
        const rowValues = this.titleRow.map((config) => item[config.key]);
        const row = _sheet1.addRow(rowValues, rowIndex + 3);
 
        row.eachCell((cell) => {
          cell.alignment = { vertical: "middle", horizontal: "center" };
          cell.font = { size: 9 };
          cell.alignment = {
            wrapText: true,
            vertical: "middle",
            horizontal: "center",
          };
        });
      });
 
       // 数据相同时，需要进行纵向合并的列
      const mergeColumns = []
      this.titleRow.forEach((column, columnIndex) => {
        // 设置列宽度
        _sheet1.getColumn(columnIndex + 1).width = column.width;
        if (mergeColumns.includes(column.key)) {
          let mergeStartRow = 3; // 开始合并的行
          let mergeEndRow = mergeStartRow;
 
          let previousValue = _sheet1.getCell(
            mergeStartRow,
            columnIndex + 1
          ).value;
 
          for (
            let rowIndex = mergeStartRow + 1;
            rowIndex <= _sheet1.rowCount;
            rowIndex++
          ) {
            const currentValue = _sheet1.getCell(
              rowIndex,
              columnIndex + 1
            ).value;
 
            if (currentValue !== previousValue) {
              // 字段值发生变化，进行合并
              if (mergeEndRow > mergeStartRow) {
                _sheet1.mergeCells(
                  mergeStartRow,
                  columnIndex + 1,
                  mergeEndRow,
                  columnIndex + 1
                );
              }
 
              mergeStartRow = rowIndex;
              mergeEndRow = rowIndex;
              previousValue = currentValue;
            } else {
              // 字段值相同，继续向下合并
              mergeEndRow = rowIndex;
            }
          }
 
          // 处理最后一个字段值的合并
          if (mergeEndRow > mergeStartRow) {
            _sheet1.mergeCells(
              mergeStartRow,
              columnIndex + 1,
              mergeEndRow,
              columnIndex + 1
            );
          }
        }
      });
 
 
      // 设置第一行的高度
      _titleCell0.height = 18;
      _titleCell.height = 36;
 
      // 设置第一行的字体样式
       _titleCell.font = {
        name: "方正小标宋_GBK",
        bold: true,
        size: 14,
      };
      _titleCell0.font = {
        name: "黑体",
        bold: true,
        size: 12,
        color: {
          argb: "FF999999",
        },
      };
      // 设置第一行的对齐方式（水平垂直）
      _titleCell.alignment = {
        vertical: "middle",
        horizontal: "center",
      };
      // 设置边框线的样式
      _titleCell.border = {
        top: {
          style: "medium",
          color: {
            argb: "FFEBEEF5",
          },
        },
        left: {
          style: "medium",
          color: {
            argb: "FFEBEEF5",
          },
        },
        bottom: {
          style: "medium",
          color: {
            argb: "FFEBEEF5",
          },
        },
        right: {
          style: "medium",
          color: {
            argb: "FFEBEEF5",
          },
        },
      };
      /// 设置单元的样式
      _titleCell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: {
          argb: "FFF5F7FA",
        },
      };
      // 导出表格
      _workbook.xlsx.writeBuffer().then((buffer) => {
        let _file = new Blob([buffer], {
          type: "application/octet-stream",
        });
        saveAs(_file, "data.xlsx");
      });
    }
    }
}

3、官方文档

https://github.com/exceljs/exceljs/blob/master/README_zh.md

4、效果图展示