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