今亮点！前端使用xlsx库导出带有样式的excel文件

来源：脚本之家时间：2022-08-05 19:04:50

需求分析

最近遇到一个需求：前端导出excel文件，其中有部分数据用户不能操作，部分列数据可以筛选，并且存在前一列的数据值会影响后一列数据值的输入范围的情况。

需要导出的前端表格如上图所示，其中：

(资料图片仅供参考)

Group、Type、Region可筛选红色框内的数据用户不可操作，绿色框内用户可以操作当Type的值为BOOL时，Region的有效输入为：["Holding Register","Input Register"],否则为：["Coil","Discrete Input"]Address的输入范围为：[0,65535]

项目使用的是React + AntD

常用的库

在这个需求出来之前，前端导入导出excel文件时我使用的是xlsx这个库。但是，如果想要修改excel表格样式的话，是需要使用收费的专业版本。带着开源第一，绝不花钱的基本原则，本人就找到了ExcleJS这个库。

ExcleJS

ExcleJS不仅完全开源，还配备着中文文档这可真的是用着放心也开心！

具体实现

安装：

npm install exceljs
npm install file-saver

创建workbook，添加名为Demo的sheet，设置默认行高为20，设置列（表头）；

添加行信息（allData前端页面表格中的数据）；

最后给表头添加颜色。

// 创建工作簿
    const workbook = new ExcelJs.Workbook();
    // 添加sheet
    const worksheet = workbook.addWorksheet("Demo");
    // 设置 sheet 的默认行高
    worksheet.properties.defaultRowHeight = 20;
    // 设置列
    worksheet.columns = [
      { header: "Index", key: "index", width: 10 },
      { header: "Name", key: "name", width: 25 },
      { header: "Type", key: "group", width: 25, outlineLevel: 1 },
      { header: "Group", key: "type", width: 25, outlineLevel: 1 },
      { header: "Region", key: "modbusRegion", width: 25, outlineLevel: 1 },
      { header: "Address", key: "modbusAddress", width: 25, outlineLevel: 1 },
    ];
    // 添加行
    worksheet.addRows(allData);
    // 给表头添加背景色
    let headerRow = worksheet.getRow(1);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: {argb: "dde0e7"},
      }
    })

将自动筛选器设置为从 A2 到 F1 （Group、Type、Region）

// 将自动筛选器设置为从 A2 到 F1
    worksheet.autoFilter = {
      from: "C1",
      to: "E1",
    }

锁定整个excel表格，可筛选但不能选中锁定的单元格

// 锁定工资表
    await worksheet.protect("the-password", 
                             {
                              autoFilter:true,
                              selectLockedCells:false,
                              });

通过循环判断，哪些单元格可以被用户操作，并且判断该单元格的输入限制是什么

const allData = [...this.state.dataSource]
   let length = allData.length
   for(let i = 0 ;i < length; i++){
      // Region的输入范围
      let coilArr = [""Coil,Discrete Input""]
      let registerArr = [""Holding Register,Input Register""]
      let listArr = []
      if(allData[i].type === "BOOL"){
        listArr = coilArr
      }
      else{
        listArr = registerArr
      }
      // 可编辑的单元格在E、F中
      worksheet.getCell(`E${i+2}`).protection = {
        locked: false,
      };
      // Region的输入校验
      worksheet.getCell(`E${i+2}`).dataValidation = {
        type: "list",
        allowBlank: true,
        formulae: listArr,
        showErrorMessage: true,
        errorTitle: "非法输入",
        error: "取值范围为:"+listArr
      };
      worksheet.getCell(`F${i+2}`).protection = {
        locked: false,
      };
      // Address的输入校验
      worksheet.getCell(`F${i+2}`).dataValidation = {
        type: "whole",
        operator: "between",
        allowBlank: true,
        showErrorMessage: true,
        formulae: [0,65535],
        errorTitle: "非法输入",
        error: "取值范围为:[0,65535]"
      };
    }

以上的代码中，worksheet.getCell(E${i+2}).dataValidation是进行单元格数据验证的函数，具体的使用可参考官方文档。

导出名为"xlsx-demo.xlsx"的excel文件

// 导出excel
    this.saveWorkbook(workbook, "xlsx-demo.xlsx");

结果展示

Group、Type、Region可筛选（✅）红色框内的数据，用户不可操作，蓝色框内用户可以操作（✅）当Type的值为BOOL时，Region的有效输入为：["Holding Register","Input Register"],否则为：["Coil","Discrete Input"]（✅）

用户输入错误给出错误提醒，并且不保存错误数据。

Address的输入范围为：[0,65535]（✅）

用户输入错误给出错误提醒，并且不保存错误数据。

整个函数展示

// 导出xls
  exportXLS = async () =>{
    const allData = [...this.state.dataSource]
    let length = allData.length
    // 创建工作簿
    const workbook = new ExcelJs.Workbook();
    // 添加sheet
    const worksheet = workbook.addWorksheet("demo");
    // 设置 sheet 的默认行高
    worksheet.properties.defaultRowHeight = 20;
    // 设置列
    worksheet.columns = [
      { header: "Index", key: "index", width: 10 },
      { header: "Name", key: "name", width: 25 },
      { header: "Group", key: "group", width: 25, outlineLevel: 1 },
      { header: "Type", key: "type", width: 25, outlineLevel: 1 },
      { header: "Region", key: "modbusRegion", width: 25, outlineLevel: 1 },
      { header: "Address", key: "modbusAddress", width: 25, outlineLevel: 1 },
    ];
    // 添加行
    worksheet.addRows(allData);
    // 给表头添加背景色
    let headerRow = worksheet.getRow(1);
    // 通过 cell 设置背景色，更精准
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: {argb: "dde0e7"},
      }
    })
    // 将自动筛选器设置为从 C1 到 F1
    worksheet.autoFilter = {
      from: "C1",
      to: "E1",
    }
    // 锁定工资表
    await worksheet.protect("the-password", 
                             {
                              autoFilter:true,
                              selectLockedCells:false,
                              });
    // 判断哪些单元格可以被用户操作，并且判断该单元格的输入限制是什么
    for(let i = 0 ;i < length; i++){
      // 根据不同类型选择筛选的框
      let coilArr = [""Coil,Discrete Input""]
      let registerArr = [""Holding Register,Input Register""]
      let listArr = []
      if(allData[i].type === "BOOL"){
        listArr = coilArr
      }
      else{
        listArr = registerArr
      }
      // 可编辑的单元格在E、F中
      worksheet.getCell(`E${i+2}`).protection = {
        locked: false,
      };
      worksheet.getCell(`E${i+2}`).dataValidation = {
        type: "list",
        allowBlank: true,
        formulae: listArr,
        showErrorMessage: true,
        errorTitle: "非法输入",
        error: "取值范围为:"+listArr
      };
      worksheet.getCell(`F${i+2}`).protection = {
        locked: false,
      };
      worksheet.getCell(`F${i+2}`).dataValidation = {
        type: "whole",
        operator: "between",
        allowBlank: true,
        showErrorMessage: true,
        formulae: [0,65535],
        errorTitle: "非法输入",
        error: "取值范围为:[0,65535]"
      };
    }
    // 导出excel
    this.saveWorkbook(workbook, "xlsx-demo.xlsx");
  }