ExcelJS 导入导出excel带下拉框筛选数据

import ExcelJS from "exceljs";

 

async function exportExcelTemplate(deptList: any) {

  const workbook = new ExcelJS.Workbook();

  const worksheet = workbook.addWorksheet("模板");

  worksheet.columns = [

    {

      header: "编号",

      key: "code",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "姓名",

      key: "name",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "电话",

      key: "tel",

      width: 20,

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "性别",

      key: "sex",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "部门",

      key: "dept_name",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "权限",

      key: "type",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

    {

      header: "状态",

      key: "status",

      style: {

        alignment: {

          vertical: "middle",

          horizontal: "left",

        },

      },

    },

  ];

  const rows = [

    {

      code: "1101",

      tel: "14789654211",

      name: "xx",

      type: "普通用户",

      sex: "女",

      dept_name: "部门1",

      status: "可用",

    },

    {

      code: "1101",

      tel: "14789654211",

      name: "xx2",

      type: "普通用户",

      sex: "女",

      dept_name: "部门2",

      status: "可用",

    },

  ];

  const newRows = worksheet.addRows(rows);

  for (let i = 2; i < 102; i++) {

    worksheet.getCell("D" + i + "").dataValidation = {

      type: "list",

      allowBlank: true,

      formulae: ['"男,女"'],

    };

    worksheet.getCell("F" + i + "").dataValidation = {

      type: "list",

      allowBlank: true,

      formulae: ['"管理员1,普通用户1"'],

    };

    worksheet.getCell("G" + i + "").dataValidation = {

      type: "list",

      allowBlank: true,

      formulae: ['"隐藏,正常"'],

    };

    if (deptList && deptList.length > 0) {

      const str = `"` + deptList.join(",") + `"`; // 动态填充

      worksheet.getCell("E" + i + "").dataValidation = {

        type: "list",

        allowBlank: true,

        formulae: [str.toString()],

      };

    }

  }