记录一下前端表格导出，json导出xlsx，原生和vue适用

• 记录仅供参考
• 效果：

//json导出xlsx格式
    async exportJson() {
      let loading = this.$loading({
        target: 'body',
        text: `加载中...`,
        background: 'rgba(0,0,0,0.3)',
      })
      let ws //创建worksheet
      //深拷贝
      let newData = []
      let header = ['area', 'room', 'name', 'data_type', 'param', 'oid', 'remark'] //表头
      //自定义表头
      let headers = {
        area: '区域', //节点
        room: '机房',
        name: '设备名',
        data_type: '数据类型',
        param: '数据名称',
        oid: 'OID',
        remark: '备注',
      }
      //过滤数据
      let params = {}
      if (this.equipmentValueCurrent.length > 0) {
        params.subset_list = this.equipmentValueCurrent.map((item) => parseInt(item.match(/_(\d+)/)[1])) //取出真实子设备id
      }
      let { data: res } = await this.$api.request.SnmpData(params)

      this.getFlatSnmp(newData, res.data)

      //   ws['!merges'].push(merge)

      newData.unshift(headers)
      console.log('表格数据', newData)
      /* 如果没有导入xlsx组件则导入 */
      let XLSX = require('xlsx')

      ws = XLSX.utils.json_to_sheet(newData, {
        header: header,
        skipHeader: true,
        origin: 'A1',
      })

      ws['!merges'] = []
      //   合并单元格 --基于所给参数和id
      function mergeCell(ws, newData, keys) {
        keys.forEach((key, index) => {
          let start = 0 // 起始索引
          let end = 0 // 结束索引
          let result = [] // 存储结果
          for (let i = 1; i < newData.length; i++) {
            if (newData[i][key] === newData[i - 1][key] && newData[i].id === newData[i - 1].id) {
              end = i // 更新结束索引
            } else {
              if (start !== end) {
                result.push({ start, end }) // 将起始索引和结束索引添加到结果数组中
              }
              start = i // 更新起始索引
              end = i // 更新结束索引
            }
          }
          result.push({ start, end }) //最后一次没有push进去
          console.log('重复记录', result)
          let merge = {}
          result.forEach((item) => {
            merge = {
              s: {
                r: item.start,
                c: index,
              },
              e: {
                r: item.end,
                c: index,
              },
            }

            ws['!merges'].push(merge)
          })
        })
      }
      mergeCell(ws, newData, ['name', 'name', 'name', 'data_type'])

      //   列宽度
      ws['!cols'] = [
        {
          wch: 10,
        },
        {
          wch: 10,
        },
        {
          wch: 20,
        },
        {
          wch: 10,
        },
        {
          wch: 10,
        },
        {
          wch: 40,
        },
      ]

      // 设置单元格的样式
      Object.keys(ws).forEach((key) => {
        //这里遍历单元格给单元格对象设置属性,s为控制样式的属性
        if (key.indexOf('!') < 0) {
          ws[key].s = {
            alignment: {
              //对齐方式
              horizontal: 'center', //水平居中
              vertical: 'center', //竖直居中
              wrapText: true, //自动换行
            },
          }
        }
      })

      /* 新建空workbook，然后加入worksheet */
      var wb = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(wb, ws, '')

      // 必须要xlsx-style 、file-saver都用上才能居中单元格
      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
      }
      /* 生成xlsx文件 */
      const wbout = XLSXStyle.write(wb, {
        type: 'binary',
        bookType: 'xlsx',
      })
      XLSX_SAVE.saveAs(
        new Blob([s2ab(wbout)], {
          type: 'application/octet-stream',
        }),
        '北向接口.xlsx'
      )
      // XLSX.writeFile(wb)

      loading.close()
    },

• 附带一个项目里的方案（vue）, 主要是进行了封装，有了加载效果，虽然反而导致下载速度变慢了：

import { Loading, Message } from 'element-ui';
async function exportJson(options) {
  options = {
    request: options.request ?? false,
    fields: options.fields,
    params: options.params,
    multiHeader: options.multiHeader ?? [],
    limit: options.limit ?? 20,
    offset: options.offset ?? 0,
    index: options.index ?? true, //是否添加序号列
    data: options.data ?? [],
    merges: options.merges ?? [],
    filename: options.filename ?? new Date().getTime(),
    name: options.name, //res中存值的字段
    autoWidth: !!options.autoWidth,
  };
  let data;
  if (options.request) {
    data = await getData(
      options.request,
      options.params,
      options.limit,
      options.offset,
      options.name,
    );
  } else {
    data = options.data;
  }
  const excel = await import('@/vendor/Export2Excel');
  const { excelData, tHeader } = formatJson(options.fields, data);
  if (options.index) {
    tHeader.unshift('序号');
    options.multiHeader.forEach((item) => item.unshift('')); //用于在多级表头中添加一个空白单元格
    excelData.forEach((item, i) => item.unshift(i + 1));
  }
  await excel.export_json_to_excel({
    multiHeader: options.multiHeader,
    merges: options.merges,
    header: tHeader,
    data: excelData,
    filename: options.filename,
    autoWidth: options.autoWidth,
  });
  return Promise.resolve();
}

//
function formatJson(fields, data) {
  const res = {
    //表列的标头
    tHeader: Object.keys(fields),
    //列表的值
    excelData: [],
  };
  data.forEach((item) => {
    const params = [];
    // 生成一排数据
    for (const key in fields) {
      if (typeof fields[key] == 'object' && fields[key] != null) {
        //值是一个对象，意味着需要用提前定义好的方法进行处理
        params.push(fields[key].fn(item[fields[key].field]));
      } else {
        params.push(item[fields[key]]);
      }
    }
    res.excelData.push(params);
  });
  return res;
}

// 从后端获取所有 列表数据
async function getData(request, params, limit, offset, name) {
  const data = [];
  params.limit = limit; //不使用传进来的，反而使用默认的？应该是为了下载全部
  let percent = 0;
  let loading = Loading.service({
    target: 'body',
    text: `下载中...${percent}%`,
    background: 'rgba(0,0,0,0.3)',
  });
  while (percent < 100) {
    params.offset = offset * limit;
    const  res  = await request(params).catch((err) => err);
    if (!res?.success) {
      Message.error(res?.errors?.[Object.keys(res?.errors)[0]][0] || '导出失败！');
      loading.close();
      return;
    }
    data.push(...res.data[name]);
    percent = ((data.length / res.data.count) * 100).toFixed(2);
    loading.setText(`下载中...${percent}%`);
    offset++;
  }
  loading.close();
  return data;
}

export default exportJson;

//简单使用
// 导出
    exportJson() {
      const params = {
        subset_list: this.equipmentValue,
        limit: this.pageSize,
        offset: (this.currentPage - 1) * this.pageSize,
        type: 1,
      };
      const fields = {
        发生时间: 'event_time',
        所属区域: 'area_name',
        所属库房: 'room_name',
        设备名称: 'device_name',
        事件描述: 'event_desc',
        事件等级: {
          fn(event_level) {
            return ['未知', '一级告警', '二级告警', '三级告警', '四级告警'][event_level];
          },
          field: 'event_level',
        },
        持续时间: 'duration_time',
        通知状态: {
          fn(inform) {
            return ['未通知', '处理中', '已通知', '通知丢失'][inform] ?? '未知';
          },
          field: 'inform',
        },
        确认时间: 'confirm_time',
        确认备注: 'confirm_desc',
      };
      exportData({
        request: (params) => {
          return this.$api.request.realwarn(params);
        },
        params,
        fields,
        name: 'data',
      });
    },

//使用方法二
async exportJson(exj) {
      let percent = 0
      let loading = this.$loading({
        target: 'body',
        text: `下载中...${percent}%`,
        background: 'rgba(0,0,0,0.3)',
        spinner: 'el-icon-loading',
      })
      let index = 0

      for (let i = 0; i < exj.length; i++) {
        const item = exj[i]
        const axtion = [
          {
            message: 'run-task',
            func: (item, i) => {
              const data = []
              const fields = {
                设备名称: 'name',
                时间: 'time',
              }
              item.children.forEach((val) => {
                val.data.forEach((val2, index) => {
                  // fields[`${val.label}对应时间`] = `${val.key}_time`
                  // fields[`${val.label}对应数据`] = `${val.key}_data`
                  fields[val.label] = `${val.key}_data`
                  if (data[i * val.data.length + index]) {
                    data[i * val.data.length + index] = {
                      ...data[i * val.data.length + index],
                    }
                    data[i * val.data.length + index][`${val.key}_label`] = val.label
                    data[i * val.data.length + index][`${val.key}_data`] = val2
                    // data[i * val.data.length + index][`${val.key}_time`] = item.time[index]
                    data[i * val.data.length + index]['time'] = item.time[index]
                  } else {
                    data[i * val.data.length + index] = {
                      name: item.name,
                      [`${val.key}_label`]: val.label,
                      [`${val.key}_data`]: val2,
                      // [`${val.key}_time`]: item.time[index],
                      time: item.time[index],
                    }
                  }
                })
              })
              return {
                data,
                fields,
                limit: 20,
                index: false,
                filename: item.name,
                autoWidth: true,
              }
            },
          },
        ]
        const worker = this.$worker.create(axtion)
        await new Promise((resolve) => setTimeout(resolve, 100))
        const res = await worker.postMessage('run-task', [item, i])
        await exportData(res)
        percent = (((index++ + 1) / exj.length) * 100).toFixed(2)
        loading.setText(`下载中...${percent}%`)
        if (percent >= 100) {
          setTimeout(() => {
            loading.close()
          }, 500)
        }
      }
    },