vue 中使用 exceljs 生成省市区关联表格

由于项目业务需要生成，后端需要前端生成表格进行批量导入进行处理上传的要求，所以在是 xlsx-js 与 excelj 两个库进行了选择，xlsx-js 官方有文档，英文类型的，个人嫌看起来麻烦，所以 exceljs 库有中文文档对我来讲还是挺友好的哈哈哈哈，个人选择哈，exceljs 的使用例子比较少所以写了这篇。

前提说明

项目基于 vue 框架，使用了 elementui，省市区数据使用 element-china-area-data 库里的数据，个人最稳妥的方法其实是后台有省市的数据结构传递过来进行数据绑定，而非传递数据字符串的值，不通过页面接口进行传一些不合理值也是不太好的，或者数据后台进行校验。

省市区结构

引入库

 import { regionData,CodeToText, TextToCode} from 'element-china-area-data'; // 数据来源
 import ExcelJS from "exceljs";
 import FileSaver from "file-saver"; // 用于生成文件

数据分级切割

	  //输出模板方法内
	  let province = {}; // 省市对象
      let city = {}; // 市区对象
      let provinceData = []; //  省数据表 
      let cityData = []; // 城市数据 用于添加单独表内建立省名称管理器
      let areaData = []; // 区级数据 用于添加单独表内建立市名称管理器
      let areaTotalSize  = 0; // 用来表示区级数据的起点
      let cityTotalSize = 0; // 用来标志市级数据的起点
      // 名称管理器名字不能含括号特殊符号
      for (let i = 0; i < regionData.length; i++) {
        provinceData.push([regionData[i].label]);
        province[regionData[i].label] = {totalSize: 0, start: 0} ;
        if (regionData[i].children&&regionData[i].children.length) {
          for (let j = 0; j < regionData[i].children.length; j ++) {
          // 基本都在第一位 位置索引为0
            if (j == 0 ) {
              // 对市辖区处理 因为 json 数据结构里有多个"市辖区"名称，同名管理器会覆盖
              if (regionData[i].children[0].label == "市辖区") {
                city[regionData[i].children[0].label +  regionData[i].label] = {totalSize: 0, start: 0};
                cityData.push([regionData[i].children[j].label  +regionData[i].label]);
              } else {
                city[regionData[i].children[0].label] = {totalSize: 0, start: 0};
                cityData.push([regionData[i].children[j].label]);
              }
              // 写总数与开始标志位
              province[regionData[i].label]['totalSize'] = regionData[i].children.length;
              province[regionData[i].label]['start'] = cityTotalSize;
            } else {
              cityData.push([regionData[i].children[j].label]);
              city[regionData[i].children[j].label] = {totalSize: 0, start: };
            }
            cityTotalSize = cityTotalSize + 1;
            if (regionData[i].children[j].children&&regionData[i].children[j].children.length){
              // 区及数据处理
              for (let v = 0; v < regionData[i].children[j].children.length; v ++) {
                if (v == 0) {
                  // 市辖区名称变化一下
                  if (regionData[i].children[j].label == "市辖区") {
                    let keyName = regionData[i].children[j].label + regionData[i].label;
                    city[keyName]["totalSize"] = regionData[i].children[j].children.length;
                    city[keyName]['start'] = areaTotalSize;
                  } else {
                    
                    city[regionData[i].children[j].label]["totalSize"] = regionData[i].children[j].children.length;
                    city[regionData[i].children[j].label]['start'] = areaTotalSize;
                  }
                } 
                areaTotalSize = areaTotalSize + 1;
                areaData.push([regionData[i].children[j].children[v].label]);
              }
            }
          }
        }
      }

生成的省对象数据（变量：province）结构为下图（{键名: { totalSize: 总数, start: 开始位置 }}）

生成的省对象数据（变量：city）结构为下图（{键名: { totalSize: 总数, start: 开始位置 }}）

开始建表

 	  const workbook = new ExcelJS.Workbook();
      const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
      workbook.creator = 'Me';
      workbook.lastModifiedBy = 'Her';
      workbook.created = new Date(1985, 8, 30);
      workbook.modified = new Date();
      workbook.lastPrinted = new Date(2016, 9, 27);
      const Sheet1 = workbook.addWorksheet('小区数据表');
      const Sheet3 = workbook.addWorksheet("市名表");
      const Sheet4 = workbook.addWorksheet("省名表");
      const Sheet5 = workbook.addWorksheet("区名表");
      Sheet3.addRows(cityData);
      Sheet4.addRows(provinceData);
      Sheet5.addRows(areaData);

建立省名称管理器

Excel限制名称管理器不能含有特殊符号，切不能为数字开头，因此对数据建立名称管理器最好做下合理范围内的限制修改

// 遍历上面代码建立的省级对象，对象键名为省名字。建立省名称管理器。
    for (let name in province) {
     // i 为 1开始，Excel表格最小下标为1
         Sheet3.eachRow(function(row, i) {
         // 遍历到当前格
          row.eachCell(function (cell) {
            if (i > province[name].start && i <=  province[name].start  + province[name].totalSize ) {
            // 为格添加名称管理器
              cell.addName(name);
            } 
          })
          })
      }

建立市名称管理器

// 遍历上面代码建立的市级对象，对象键名为区名字。建立省名称管理器。
    for (let cityItem in city) {
        this.areaSizeIndex += city[cityItem].totalSize;
        // let self = this;
        // console.table(['start',city[cityItem]['start'], "end", city[cityItem]['start'] + city[cityItem].totalSize])
        Sheet5.eachRow(function (row, i) {
          row.eachCell(function (cell) {
            if (i > city[cityItem]['start'] && i <= city[cityItem]['start'] + city[cityItem].totalSize) {
              cell.addName(cityItem);
            }
          })
        })
      }

小区数据表建立列名

Sheet1.columns = [
        {
          header: "小区名称",
          key: "name",
          width: 30
        }, // A1
        {
          header: '所在省',
          key: 'name',
          width: 30
        },  // B2
        {
          header: '所在市',
          key: 'number',
          width: 20
        }, // C3
        {
          header: '所在区',
          key: 'type',
          width: 30
        }, // D4
        {
          header: "所在地址",
          key: "address",
          width: 100
        } // E5
      ];

小区表格加校验

    // 新建200行数据
     new Array(200).fill(0).forEach((_, idx) => {
     	// 行数以1开始，第一行写了列名，故加二
        const row = idx + 2;
        Sheet1.getCell(row, 1).dataValidation = {
          showInputMessage: true, // 显示注意信息
          promptTitle: '注意：',
          prompt: '小区名字请勿以数字开头'
        };
        Sheet1.getCell(row, 2).dataValidation = {
          type: "list",
         formulae: [`=省名表!$A$1:$A${provinceData.length+1}`],
        };
       // 数据范围 即省数据表里的省名
        Sheet1.getCell(row, 3).dataValidation = {
          type: "list",
          formulae: [`=INDIRECT(B${row})`]
        }; // indirect=名称管理器名 B即省名坐在单元格列为B
        Sheet1.getCell(row, 4).dataValidation = {
          type: "list",
          formulae: [`=INDIRECT(C${row})`]
        }
      });

生成表格

      workbook.xlsx.writeBuffer().then((data) => {
        const blob = new Blob([data], {type: EXCEL_TYPE});
        // Given name
        FileSaver.saveAs(blob, 'test.xlsx');
      });

效果图

写了注意信息的单元格

设置了数据有效性的省名范围

根据所选换成对应的名称管理器，下一列同理

帮助

如何查看Excel里的名称管理器是否生成，菜单列 “公式” -> “名称管理器”

表保护

因为表名称管理器是基于另外三个表数据进行公式运算的，因此最好设置保护

	  Sheet3.protect('123456');
      Sheet4.protect('123456');
      Sheet5.protect('123456');

Exceljs github地址

中文文档