前端多级表头的表格导出

安装 export2Excel依赖

  /* original data */

  var data = jsonData;
  data.unshift(th);
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary"
  });
  var title = defaultTitle || "列表";
  saveAs(
    new Blob([s2ab(wbout)], {
      type: "application/octet-stream"
    }),
    title + ".xlsx"
  );
}

// export function export_json_to_excels(th1, th, jsonData, defaultTitle, merges) {
//   /* original data */
//   var data = jsonData;
//   // 第二行表头
//   data.unshift(th);
//   // 第一行表头
//   data.unshift(th1)
//   // 这个地方定义sheet名,可更改
//   var ws_name = "SheetJS";
//   var wb = new Workbook(),
//   ws = sheet_from_array_of_arrays(data);
//   /* add worksheet to workbook */
//   wb.SheetNames.push(ws_name);
//   wb.Sheets[ws_name] = ws;
//   // 设置合并单元格,注意预留空值,否则合并后值会被覆盖
//   ws['!merges'] = merges;
//   var wbout = XLSX.write(wb, {
//     bookType: 'xlsx',
//     bookSST: false,
//     type: 'binary'
//   });
//   var title = defaultTitle || '列表'
//   saveAs(new Blob([s2ab(wbout)], {
//     type: "application/octet-stream"
//   }), title + ".xlsx")
// }

export function export_json_to_excels ({
  multiHeader,
  multiHeader2,
  // header,
  data,
  filename,
  merges,
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  // data.unshift(header);
 
  for (let i = multiHeader2.length - 1; i > -1; i--) {
    data.unshift(multiHeader2[i])
  }
  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }
 
  var ws_name = "";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);
 
  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }
 
  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    const colWidth = data.map(row => row.map(val => {
      /*先判断是否为null/undefined*/
      if (val == null) {
        return {
          'wch': 10
        };
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2
        };
      } else {
        return {
          'wch': val.toString().length
        };
      }
    }))
    /*以第一行为初始值*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch'];
        }
      }
    }
    ws['!cols'] = result;
  }
 
  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;
 
  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

在 export2Excel.js新增上面方法

调用：下面方法写在导出的按钮事件里

        const { export_json_to_excels } = require("@/excel/export2Excel"); // 这里必须使用绝对路径，使用@/+存放export2Excel的路径
        // 一级表头
        let multiHeader = [
          [
            '名称',
            '名称1',
            '',
            '名称2',
            '',
            '名称3',
            '',
            '名称4',
          ],
        ]
        // 二级表头
        let multiHeader2 = [
          [
            '',
            '名称下表格字段1',
            '名称下表格字段2',
            '名称下表格字段3',
            '名称下表格字段4',
            '名称下表格字段5',
            '名称下表格字段6',
            '',
          ],
        ]
        // const tHeader = []
        let filterVal = [
          'name',
          'name1',
          'name2',
          'name3',
          'name4',
          'name5',
          'name6',
          'name7'
        ]
        // 表格合并
        let merges = [
          'A1:A2',
          'B1:C1',
          'D1:E1',
          'F1:G1',
          'H1:H2'
        ]

        // 表头所对应的字段，这里未填写
        // console.log(this.tableData)
        // console.log(filterVal)
        // console.log(data)
        let filename = '多级表头导出';
        let copyData = JSON.parse(JSON.stringify(this.tableData))
        const data = copyData.map(v => filterVal.map(j => v[j]));
        // 进行所有表头的单元格合并
        
        export_json_to_excels({
          multiHeader, // 这里是第一行的表头
          multiHeader2, // 这里是第二行的表头
          // header: tHeader, // 这里是第三行的表头
          data,
          filename,
          merges,
          autoWidth:true,
          bookType:'xlsx'
        })
      })