Front-end export of dynamic multi-level headers of Excel

A while ago, I received a front-end export request for home office Excel . I was enthusiastic and told the product that there was no problem with this thing, and then the product explained his needs to me in detail. , at the time I wanted to take back what I just said.

The general requirements are as follows:

The page needs to display different header information and table data according to different query conditions. When exporting Excel , it also needs to export data according to the current header information. The header may be Level 1, it may be Level 2, it may be...N Level. I was thinking of slashing the product with a big knife.

I discussed with the backend whether they can do this. For the sake of performance, it was finally decided that both the frontend and the backend must be done (no one can run...), when the amount of data is large, the backend is exported, and when the amount of data is small, the frontend is exported.

The background story is probably like this, because the author originally did Excel export, but it was only a simple application, and there were very few second-level headers, let alone N-level. In order to realize this requirement, I re-looked at the original framework and research in other directions, so after the author's unremitting efforts and busy for two hours, the author finally felt that opening Taobao...wrong, start over. In the end, two versions were implemented, and the effect was the same, but there were still differences between the two.

Export Excel multi-level header 1.0

At the beginning of the research, I used xlsx this toolkit, because this package is relatively familiar and it is faster to get started. It was a priority at the very beginning.

Install dependencies:

 npm install xlsx -S
or
yarn add xlsx -S

创建文件DownExcel.js xlsx ， class ，程序设计前期思考是这样的， class initializing, you need to receive a tableHead that is the header of the data that needs to be exported. When downloading a file, you need to call the down method, and you need to receive it in the down method data and the file name that needs to be exported.

 class DownExcel {

  constructor({ header = [] }) {
    this.tableHeader = header;
  }
  
  down(fileName, tableData = []){
      
  }
    
}

The most difficult thing is not to fill in the form data, but how to make the exported Excel support N level dynamic header. xlsx excel的时候支持Csv的，最后Csv成Sheet Then export the file, which means that I need to know the merge information of the header before sorting the Csv data. But xlsx the frame's merge information is stored separately, roughly as follows:

 const marge = [{
    s: { r: 0, c: 1 },
    e: { r: 0, c: 2 }
}];

In the above content, s represents the starting node position, e represents the ending node position, r represents the line, c represents the column. It can be seen by analyzing the data. The two coordinate points are confirmed respectively, and the cells are merged according to the two coordinate points. So if you want to support multi-level header data, then you need to support marge what is the information, if it can be generated based on the header information, wouldn't it work?

There is header information in the page table, and the export Excel needs to be generated according to the page header. The header data information looks like this:

 const tableHeader = [{
  field: "a111",
  title: "a1"
},{
  field: "a333",
  title: "a3",
  children: [{
    field: "b111",
    title: "b1",
  },{
    field: "b222",
    title: "b2",
    children: [{
      field: "c1",
      title: "c111",
      children: [{
        file: "d444",
        title: "d4"
      }]
    }]
  }]
}];

The above is that the data of the header is in the nested data format. As long as the lower level exists children then this level needs to be merged, if not, it needs to be merged to the bottom of the header.

Create method resetMergeHeaderInfo

 class DownExcel {

  down(fileName, tableData = []){
    this.resetMergeHeaderInfo();
  }

 // 表头数据                tableHeader
 // 表头深度                maxLevel
 // 合并表头临时存储信息    outMarge
 // 最终结果                result
  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
      
  }    

}

One of the parameters in the method is maxLevel , is this parameter the maximum depth of the current header information? That is, how many layers of the current header information are nested. With this parameter, you can know the outermost data. If there is no children , the merged range of the cell. Then it is also necessary to confirm which are the outermost layers, which are the inner layers, which ones have children and which ones are not children . These are roughly the situations that appear in the header.

First, confirm the outer layer data. If you want to implement nested data, you will use recursion in many cases, and you need to mark the outer layer book.

 class DownExcel {

 // 表头数据                tableHeader
 // 表头深度                maxLevel
 // 合并表头临时存储信息    outMarge
 // 最终结果                result
  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
  }    
  
  //  标记最外层数据
  tagHeadIn(){
    const { tableHeader } = this;
    tableHeader.forEach((el) => {
      el.isOut = true;
      return el;
    })
  }

}

Process the outer data first, as I said just now, you need to know how big the maximum nesting level is, so here you need a method to get the required parameter.

 class DownExcel {

  down(){
    const { tableHeader } = this;
    let maxLevel = this.maxLevel(tableHeader);
    this.resetMergeHeaderInfo(tableHeader,maxLevel);
  }
  
  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
  }
    
 tagMaxLevel(tableHeader){
    const maxLevel = this.maxLevel(tableHeader, false);
    tableHeader.forEach((el) => {
      if(!el.children){
        el.maxLen = maxLevel;
      }
      else{
        this.tagMaxLevel(el.children);
        el.maxLen = maxLevel;
      }
    });
  }
    
}

When marking the level, each level is marked, and the maximum level below it is marked, so that you do not need to traverse each time to obtain the maximum depth. It is thought that the sub-level is also nested. If there is no nesting, the more you need to know how many down the current cell is merged, otherwise the merge will not be unified.

The next step is to process the outermost table information:

 class DownExcel {

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    for(let i = 0; i < tableHeader.length; i++){
        let item = tableHeader[i];
         //  纵向跨度
         const { maxLen } = item;
        //  开始节点信息
        let s = {};
        //  结束节点信息
        let e = {};
        //  如果没有子级
        if(!item.children){
            //  如果是最外层元素
            if(item.isOut){
                //  当前列开始位置
                outMarge.startCell += 1;
                //  全局列开始位置
                outMarge.basisCell += 1;
                //  开始行
                s.r = 0;
                //  结束行
                e.r = maxLevel;
                //  开始列
                s.c = outMarge.startCell;
                //  结束列
                e.c = outMarge.startCell;
                result.push({ s, e, item });
            }else{  //  不是外层元素
                
            }
        }
        //  如果有子级
        if(item.children){
            //  如果是最外层元素
            if(item.isOut){
                
            }else{  //  不是外层元素
                
            }
        }
    }
  }
}

According to the known information, the information of the cells with no children in the outer layer has been obtained. Why do you need to record the start information of two columns? If A there are two children under the cell, one without children (A1) and the other with children (A2) After confirming the information, start the column Changes and self-increment 1 , A2 When rendering, how many children need to be added to the line information of the global record, so enter the B cell During the cycle, the next merge information record will be started from the corresponding place.

When there are children, there will be a problem, how many cells are horizontally merged in the current cell? Then, according to the children of the current cell children all without children , it is the horizontal span of the current cell.

 class DownExcel {

  //  获取当前下面所有子级
  //  即：表头横向跨度单元格数量
  getLastChild (arr, result = []){
    for(let i = 0,item; item = arr[i++];){
      if(!item.children){
        result.push(item);
      }else{
        result = this.getLastChild(item.children, result);
      }
    }
    return result;
  }

}

After knowing the horizontal span, you can handle the collection of cell merge information with children outside:

 class DownExcel {

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    for(let i = 0; i < tableHeader.length; i++){
        let item = tableHeader[i];
         //  纵向跨度
         const { maxLen } = item;
         //  横向跨度
        let lastChild = this.getLastChild(item.children || []);
        //  开始节点信息
        let s = {};
        //  结束节点信息
        let e = {};
        //  如果没有子级
        if(!item.children){
            //  如果是最外层元素
            if(item.isOut){
                //  .....
            }else{  //  不是外层元素
                
            }
        }
        //  如果有子级
        if(item.children){
            //  如果是最外层元素
            if(item.isOut){
              // 开始行
              s.r = 0;
              // 结束行
              e.r = 0;
              // 局部开始列自增
              outMarge.startCell += 1;
              // 开始列
              s.c = outMarge.startCell;
              // 开始列加上横向跨度
              outMarge.startCell += lastChild.length - 1;
              // 结束列
              e.c = outMarge.startCell;
              result.push({ s, e, item });
            }else{  //  不是外层元素
                
            }
        }
    }
  }
}

Because the horizontal start position needs to be recorded, the current horizontal span should be added to avoid errors in the start position of the next cycle.

 class DownExcel {

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    for(let i = 0; i < tableHeader.length; i++){
        let item = tableHeader[i];
         //  纵向跨度
         const { maxLen } = item;
         //  横向跨度
        let lastChild = this.getLastChild(item.children || []);
        //  开始节点信息
        let s = {};
        //  结束节点信息
        let e = {};
        //  如果没有子级
        if(!item.children){
            //  如果是最外层元素
            if(item.isOut){
                //  .....
            }else{  //  不是外层元素
              // 开始行 
              let r = maxLevel - (outMarge.basisRow + maxLen);
              r = Math.max(r, 0);
              s.c = outMarge.basisCell;
              e.c = outMarge.basisCell;
              s.r = outMarge.basisRow;
              e.r = r + outMarge.basisRow + maxLen;
              result.push({ s, e, item });
              //  开始行数据 + 1
              outMarge.basisCell += 1;
            }
        }
        //  如果有子级
        if(item.children){
            //  如果是最外层元素
            if(item.isOut){
              //    ... 
            }else{  //  不是外层元素
                
            }
        }
    }
  }
}

After processing the external data, you can process the internal data. In fact, processing internal data is similar to processing external data. When processing children, if there are two children under the cell if A there are two children under the cell, one has no children (A1) and the other has children (A2), at this time A1 need to merge down to the bottom, use the maximum depth - (start position + merge height) to get the difference, which is the end position of the current unit.

 class DownExcel {

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    for(let i = 0; i < tableHeader.length; i++){
        let item = tableHeader[i];
         //  纵向跨度
         const { maxLen } = item;
         //  横向跨度
        let lastChild = this.getLastChild(item.children || []);
        //  开始节点信息
        let s = {};
        //  结束节点信息
        let e = {};
        //  如果没有子级
        if(!item.children){
            //  如果是最外层元素
            if(item.isOut){
                //  .....
            }else{  //  不是外层元素
               //   .....
            }
        }
        //  如果有子级
        if(item.children){
            //  如果是最外层元素
            if(item.isOut){
              //    ... 
            }else{  //  不是外层元素
                s.c = outMarge.basisCell;
                e.c = outMarge.basisCell + lastChild.length - 1;
                s.r = outMarge.basisRow;
                e.r = outMarge.basisRow;
                result.push({ s, e, item });
            }
        }
    }
  }
}

The processing logic here is similar to the processing logic of the outer layer with children.

 class DownExcel {

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    for(let i = 0; i < tableHeader.length; i++){
        let item = tableHeader[i];
         //  纵向跨度
         const { maxLen } = item;
         //  横向跨度
        let lastChild = this.getLastChild(item.children || []);
        //  开始节点信息
        let s = {};
        //  结束节点信息
        let e = {};
        //  如果没有子级
        if(!item.children){
            //  如果是最外层元素
            if(item.isOut){
                //  .....
            }else{  //  不是外层元素
               //   .....
            }
        }
        //  如果有子级
        if(item.children){
            //  如果是最外层元素
            if(item.isOut){
              //    ... 
            }else{  //  不是外层元素
              // ....
            }
        }
        outMarge.basisRow += 1;
        this.resetMergeHeaderInfo(item.children, maxLevel, outMarge, result);
    }
    outMarge.basisRow -= 1;
    return result;
  }
}

After processing the collected information, it is necessary to collect the information of all sub-levels. It is necessary to increase the number of local columns to ensure that the merged coordinate values are synchronized with the traversal and recursion when the merged is merged. After each recursion, it needs to be subtracted by one, that is, it needs to be added once for each recursion.

In this way, the collection of merged information is completed, and the obtained data is consistent with what the xlsx framework needs.

 class DownExcel {

  down(fileName, tableData = []){
    const { tableHeader, outMarge } = this;
    let maxLevel = this.maxLevel(tableHeader);
    const mergeInfo = this.resetMergeHeaderInfo(tableHeader, maxLevel, outMarge);
    const lastChild = this.getLastChild(tableHeader);
    const headCsv = this.getHeadCsv(tableHeader, lastChild, maxLevel, mergeInfo);
    const dataCsv = this.getDataCsv(tableData, lastChild);
    const allCsv = this.margeCsv(headCsv, dataCsv);
  }
  
  
  //  将数据转换成Csv格式
  getHeadCsv(tableHeader, lastChild, maxLevel, mergeInfo){
    let csvArr = [];
    let csv = "";
    for(let i = 0; i < (maxLevel + 1); i++){
      let item = [];
      for(let j = 0; j < lastChild.length; j++){
        item.push(null);
      }
      csvArr.push(item);
    }
    for(let i = 0; i < mergeInfo.length; i++){
      let info = mergeInfo[i];
      const { s, item } = info;
      const { c, r } = s;
      const { title } = item;
      csvArr[r][c] = title;
      console.log(mergeInfo);
    }
    csvArr = csvArr.map((el) => {
      return el.join("^");
    });
    return csvArr.join("~");
  }
  
  //  获取data的Csv
  getDataCsv(data, lastChild){
    let result = [];
    for(let j = 0, ele; ele = data[j++];){
      let value = [];
      for(let i = 0, item; item = lastChild[i++];){
        value.push(ele[item.field] || "-");
      };
      result.push(value);
    }
    result = result.map((el) => {
      return el.join("^");
    });
    return result.join("~");
  }
  
  //  合并Csv
  margeCsv(headCsv, dataCsv){
    return `${headCsv}~${dataCsv}`;
  }
  
}

After the merged information is processed, the current header data and list data need to be processed into csv format, which is convenient for exporting to excel. The first consideration is that part of the header merged information needs to be replaced with empty data, not all Fill in the data directly, otherwise the problem of data confusion will occur when exporting.

 class DownExcel {

  down(fileName, tableData = []){
    const { tableHeader, outMarge } = this;
    let maxLevel = this.maxLevel(tableHeader);
    const mergeInfo = this.resetMergeHeaderInfo(tableHeader, maxLevel, outMarge);
    const lastChild = this.getLastChild(tableHeader);
    const headCsv = this.getHeadCsv(tableHeader, lastChild, maxLevel, mergeInfo);
    const dataCsv = this.getDataCsv(tableData, lastChild);
    const allCsv = this.margeCsv(headCsv, dataCsv);
    
    const cscSeet = this.csv2sheet(allCsv);
    console.log(cscSeet);
    let blob = this.sheet2blob(cscSeet);
    this.openDownloadDialog(blob,`${fileName}.xlsx`);
  }
  
  //  将 csv转换成sheet数据
  csv2sheet(csv) {
    csv = csv.split('~');
    //  缓存
    let arr = [];
    //  剪切未数组
    csv.forEach((el) => {
      //  剪切数据并添加答题arr
      arr.push(el.split("^"));
    });
    //  调用方法
    return XLSX.utils.aoa_to_sheet(arr);
  }
  
  //  sheet转blob文件
  sheet2blob(sheet, sheetName) {
    //  导出文件类型
    sheetName = sheetName || 'sheet1';
    var workbook = {
      SheetNames: [sheetName],
      Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet;
    var wopts = {
      bookType: "xlsx",
      bookSST: false,
      type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
    // 字符串转ArrayBuffer
    function s2ab(s) {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    }
    return blob;
  }
  
  // 导出excel
  openDownloadDialog(url, saveName){
    if(typeof url == 'object' && url instanceof Blob){
      url = URL.createObjectURL(url); // 创建blob地址
    }
    var aLink = document.createElement('a');
    aLink.href = url;
    aLink.download = saveName || '';
    var event;
    if(window.MouseEvent) event = new MouseEvent('click');
    else
    {
      event = document.createEvent('MouseEvents');
      event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
  }
  
}

Convert Csv to sheet data, and assign the merged information to sheet , then you can convert it to blob by calling the framework method The object completes the final data export.

Full code:

 import * as XLSX from "xlsx";

export default class DownExcel {

  constructor({ header = [] }) {
    this.tableHeader = header;
    this.outMarge = {
      startCell: -1,
      basisRow: 0,
      basisCell: 0,
      maxRow: 0
    };
  }

  down(fileName, tableData = []){
    const { tableHeader, outMarge } = this;
    let maxLevel = this.maxLevel(tableHeader);
    const mergeInfo = this.resetMergeHeaderInfo(tableHeader, maxLevel, outMarge);
    const lastChild = this.getLastChild(tableHeader);
    const headCsv = this.getHeadCsv(tableHeader, lastChild, maxLevel, mergeInfo);
    const dataCsv = this.getDataCsv(tableData, lastChild);
    const allCsv = this.margeCsv(headCsv, dataCsv);
    const cscSeet = this.csv2sheet(allCsv);
    cscSeet['!merges'] = mergeInfo;
    console.log(cscSeet);
    let blob = this.sheet2blob(cscSeet);
    this.openDownloadDialog(blob,`${fileName}.xlsx`);
  }

  //  sheet转blob文件
  sheet2blob(sheet, sheetName) {
    //  导出文件类型
    sheetName = sheetName || 'sheet1';
    var workbook = {
      SheetNames: [sheetName],
      Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet;
    var wopts = {
      bookType: "xlsx",
      bookSST: false,
      type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
    // 字符串转ArrayBuffer
    function s2ab(s) {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    }
    return blob;
  }

  //  导出Excel
  openDownloadDialog(url, saveName){
    if(typeof url == 'object' && url instanceof Blob){
      url = URL.createObjectURL(url); // 创建blob地址
    }
    var aLink = document.createElement('a');
    aLink.href = url;
    aLink.download = saveName || '';
    var event;
    if(window.MouseEvent) event = new MouseEvent('click');
    else
    {
      event = document.createEvent('MouseEvents');
      event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
  }

  //  获取data的Csv
  getDataCsv(data, lastChild){
    let result = [];
    for(let j = 0, ele; ele = data[j++];){
      let value = [];
      for(let i = 0, item; item = lastChild[i++];){
        value.push(ele[item.field] || "-");
      };
      result.push(value);
    }
    result = result.map((el) => {
      return el.join("^");
    });
    return result.join("~");
  }

  //  将数据转换成Csv格式
  getHeadCsv(tableHeader, lastChild, maxLevel, mergeInfo){
    let csvArr = [];
    let csv = "";
    for(let i = 0; i < (maxLevel + 1); i++){
      let item = [];
      for(let j = 0; j < lastChild.length; j++){
        item.push(null);
      }
      csvArr.push(item);
    }
    for(let i = 0; i < mergeInfo.length; i++){
      let info = mergeInfo[i];
      const { s, item } = info;
      const { c, r } = s;
      const { title } = item;
      csvArr[r][c] = title;
      console.log(mergeInfo);
    }
    csvArr = csvArr.map((el) => {
      return el.join("^");
    });
    return csvArr.join("~");
  }

  //  合并Csv
  margeCsv(headCsv, dataCsv){
    return `${headCsv}~${dataCsv}`;
  }

  //  将 csv转换成sheet数据
  csv2sheet(csv) {
    csv = csv.split('~');
    //  缓存
    let arr = [];
    //  剪切未数组
    csv.forEach((el) => {
      //  剪切数据并添加答题arr
      arr.push(el.split("^"));
    });
    //  调用方法
    return XLSX.utils.aoa_to_sheet(arr);
  }

  resetMergeHeaderInfo(tableHeader, maxLevel, outMarge, result = []){
    this.tagHeadIn();
    this.tagMaxLevel(tableHeader);
    for(let i = 0; i < tableHeader.length; i++){
      let item = tableHeader[i];
      //  纵向跨度
      const { maxLen } = item;
      //  横向跨度
      let lastChild = this.getLastChild(item.children || []);
      //  s :  开始  e : 结束
      //  c : 列（横向）  
      //  r ： 行（纵向）
      let s = {};
      let e = {};
      if(!item.children){
        if(item.isOut){
          outMarge.startCell += 1;
          outMarge.basisCell += 1;
          s.r = 0;
          e.r = maxLevel;
          s.c = outMarge.startCell;
          e.c = outMarge.startCell;
          result.push({ s, e, item });
        }
        else{
          let r = maxLevel - (outMarge.basisRow + maxLen);
          r = Math.max(r, 0);
          s.c = outMarge.basisCell;
          e.c = outMarge.basisCell;
          s.r = outMarge.basisRow;
          e.r = r + outMarge.basisRow + maxLen;
          result.push({ s, e, item });
          outMarge.basisCell += 1;
        }
      };
      if(item.children){
        if(item.isOut){
          s.r = 0;
          e.r = 0;
          outMarge.startCell += 1;
          s.c = outMarge.startCell;
          outMarge.startCell += lastChild.length - 1;
          e.c = outMarge.startCell;
          result.push({ s, e, item });
        }else{
          s.c = outMarge.basisCell;
          e.c = outMarge.basisCell + lastChild.length - 1;
          s.r = outMarge.basisRow;
          e.r = outMarge.basisRow;
          result.push({ s, e, item });
        }
        outMarge.basisRow += 1;
        this.resetMergeHeaderInfo(item.children, maxLevel, outMarge, result);
      };
    };
    outMarge.basisRow -= 1;
    return result;
  }

  tagHeadIn(){
    const { tableHeader } = this;
    tableHeader.forEach((el) => {
      el.isOut = true;
      return el;
    })
  }

  //  标记最大层级
  tagMaxLevel(tableHeader){
    const maxLevel = this.maxLevel(tableHeader, false);
    tableHeader.forEach((el) => {
      if(!el.children){
        el.maxLen = maxLevel;
      }
      else{
        this.tagMaxLevel(el.children);
        el.maxLen = maxLevel;
      }
    });
  }

  //  获取最大层级
  //  只包含子级最大层级(不包含本级)
  maxLevel(arr, isSetFloor = true){
    let floor = -1;
    let max = -1;
    function each (data, floor) {
      data.forEach(e => {
        max = Math.max(floor, max);
        isSetFloor && (e.floor = (floor + 1));
        if (e.children) {
          each(e.children, floor + 1)
        }
      })
    }
    each(arr,0)
    return max;
  }

  //  获取当前下面所有子级
  //  即：表头横向跨度单元格数量
  getLastChild (arr, result = []){
    for(let i = 0,item; item = arr[i++];){
      if(!item.children){
        result.push(item);
      }else{
        result = this.getLastChild(item.children, result);
      }
    }
    return result;
  }

};

use:

 new DownExcel({
  header:  [{
    field: "c1",
    title: "c111",
    children: [{
      field: "c2",
      title: "c222"
    },{
      field: "c3",
      title: "c333"
    },{
      field: "c4",
      title: "c444"
    }]
  }]
}).down(`${+new Date()}`,[{
  a111: "LLL",
  c1: "HHH",
  c2: "AAA",
  a444: "III"
}]);

Thank you for taking a long time to read this article. If there are mistakes in the article, I will always thank you for correcting me, and I will make corrections as soon as possible. If you like it, it will be updated next, export and bring in styles and support multi-level headers.