使用exceljs导出部门-职位联动下拉框的excel

import { Workbook } from 'exceljs';///
const wb = new Workbook();
const ws = wb.addWorksheet('tb');
const dict2 = wb.addWorksheet('dict2');
const test = wb.addWorksheet('test');const test_data = [{post_name: '方案',department_name: '产品'
}, {post_name: '设计',department_name: '产品'
}, {post_name: 'js开发',department_name: '产品'
}, {post_name: '部门主管',department_name: '产品'
}];
const departments = Array.from(test_data.reduce((p, c) => {p.add(c.department_name);return p;
}, new Set()));
dict2.addRows(departments);ws.columns = [{ header: '员工编号', key: 'staff_no', width: 20 },	// A1{ header: '员工姓名', key: 'staff_name', width: 20 },	// B2{ header: '性别', key: 'sex', width: 20 },	// C3{ header: '部门名称', key: 'department_name', width: 20 },	// D4{ header: '岗位名称', key: 'post_name', width: 20 },	// E5{ header: '身份证号', key: 'id_no', width: 20 },	// 6{ header: '出生日期', key: 'birthday', width: 20 },	// 7{ header: '年龄', key: 'age', width: 20 },	// 8{ header: '联系电话', key: 'contact_number', width: 20 },	// 9{ header: '入职时间', key: 'staff_hiredate', width: 20 },	// 10{ header: '员工状态', key: 'staff_state', width: 20 }	// 11
];
///
new Array(5000).fill(0).forEach((_, idx) => {const row = idx + 2;// 渲染部门下拉框ws.getCell(row, 4).dataValidation = {type: 'list',formulae: [`=dict2!$C$2:$C${departments.length}`]};// 使用indirect函数添加引用, 渲染岗位ws.getCell(row, 5).dataValidation = {type: 'list',formulae: [`=INDIRECT(D${row})`]};
});// 添加名称管理器
const _data = test_data.reduce((p, c: any) => {if (!p.has(c.department_name)) {p.set(c.department_name, [c.post_name]);} else {const arr = p.get(c.department_name);arr.push(c.post_name);}return p;
}, new Map<string, string[]>());const department_names = Array.from(_data.keys());
const post_names = Array.from(_data.values());
test.addRows(post_names);test.eachRow(function (row, i) {const department_name = department_names[i - 1];console.log(department_name, i);row.eachCell(function (cell, colNumber) {cell.addName(department_name);});
});await wb.xlsx.writeFile('/home/feidao/Desktop/模板列表.xlsx');
return;
// const buff = await wb.xlsx.writeBuffer();
// return {
//  buff,
//  content_type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
//  attachment: '模板列表.xlsx'
// };