exceljs三级联动下拉列表

import { Workbook } from 'exceljs';
import FileSaver from "file-saver";
let area = [[110000, "北京市"], ...];//区域数据

let tree = (() => {
    let r = [];
    for (let v of area) {
        if (v[0] % 10000 === 0) r.push({ code: v[0], name: v[1] });
    }
    for (let v1 of r) {
        v1.items = [];
        for (let v2 of area) {
            if (v2[0] % 100 === 0 && v2[0] % 10000 !== 0 && parseInt(v2[0] / 10000) === v1.code / 10000) {
                let city = { code: v2[0], name: v2[1], items: [] };
                v1.items.push(city);
                for (let v3 of area) {
                    if (v3[0] % 100 !== 0 && parseInt(v3[0] / 100) === city.code / 100) {
                        city.items.push({ code: v3[0], name: v3[1] });
                    }
                }
            }
        }
    }
    console.log("区域", r);
    return r;
})();

let wb = new Workbook();

let sheet = wb.addWorksheet('sheet');
sheet.getCell(1, 1).value = '省';
sheet.getCell(1, 2).value = '市';
sheet.getCell(1, 3).value = '县';
for (let i = 2; i < 10000; i++) {
    sheet.getCell(i, 1).dataValidation = { type: "list", formulae: [`=areasheet!$A$1:$A${tree.length}`], showErrorMessage: true };
    sheet.getCell(i, 2).dataValidation = { type: "list", formulae: [`=INDIRECT("area_"&A${i})`], showErrorMessage: true };
    sheet.getCell(i, 3).dataValidation = { type: "list", formulae: [`=INDIRECT("area_"&A${i}&"_"&B${i})`], showErrorMessage: true };
}

((wb, area) => {
    let sheet = wb.addWorksheet('areasheet');
    sheet.state = 'Hidden';//隐藏表
    let index = 1;
    for (let v of area) sheet.getCell(index++, 1).value = v.name;
    for (let v1 of area) {
        for (let v2 of v1.items) {
            sheet.getCell(index, 1).value = v2.name;
            sheet.getCell(index, 1).addName(`area_${v1.name}`);
            index++;
        }
    }
    for (let v1 of area) {
        for (let v2 of v1.items) {
            for (let v3 of v2.items) {
                sheet.getCell(index, 1).value = v3.name;
                sheet.getCell(index, 1).addName(`area_${v1.name}_${v2.name}`);
                index++;
            }
        }
    }
})(wb, tree);//创建区域表

wb.xlsx.writeBuffer().then(p => FileSaver.saveAs(new Blob([p]), '三级联动下拉列表.xlsx'));