XLSX插件 前端导出 使用记录

xlsx 普通导出
xlsx-style 设置导出样式

报错解决

import XLSX from "xlsx-style"  //ps  需要修改源码：在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable 不然会报错;

// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
  sheetName = sheetName || "sheet1";
  var workbook = {
    SheetNames: [sheetName],
    Sheets: {},
  };
  workbook.Sheets[sheetName] = sheet;
  // 生成excel的配置项
  var wopts = {
    bookType: "xlsx", // 要生成的文件类型
    bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    type: "binary",
  };
  var wbout = XLSXS.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;
}
function 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 || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
  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);
}
//[[2,4],[4,6],[7,8]] =>[[2,6],[7,8]]
function arycat(arr) {
  let newArr = [];
  let i = 0;
  while (i < arr.length) {
    newArr.push(...arr[i]);
    i++;
  }

  let v = [];
  let j = 1;
  while (j < newArr.length - 1) {
    if (j == 1) {
      v.push(newArr[j - 1]);
    }
    if (newArr[j] != newArr[j - 1] && newArr[j] != newArr[j + 1]) {
      v.push(newArr[j]);
    }
    if (j == newArr.length - 2) {
      v.push(newArr[j + 1]);
    }
    j++;
  }
  let result = [];
  for (let z = 0; z < v.length; z += 2) {
    if (newArr[z]) result.push(v.slice(z, z + 2));
  }
  return result;
}
//过滤多余的签到和签退
function filterName(data) {
  let result = [];
  for (let i in data) {
    let s = [];
    let e = [];
    let list = [];

    data[i].forEach((item, index) => {
      if (item.types && item.types.indexOf("签到") > -1) {
        s.push(index);
      }
      if (item.types && item.types.indexOf("签退") > -1) {
        e.push(index);
      }
      if (
        (item.types &&
          item.types.indexOf("签到") == -1 &&
          item.types.indexOf("签退") == -1) ||
        !item.types
      ) {
        list.push(item);
      }
    });
    if (s.length > 0) {
      let start = data[i][s[0]];
      list.unshift(start);
    }
    if (e.length > 0) {
      let end = data[i][e[e.length - 1]];
      list.push(end);
    }
    list = list.sort((a, b) => {
      return new Date(a.time) - new Date(b.time);
    });
    result.push(...list);
  }
  return result;
}
    export() {
      const carBrand = StarSoft.Data.Car.fGetCarName(this.form.CarID);
      let tripList = deepCopy(this.exportTripList);
      let exportData = deepCopy(this.exportDetail);
      const header = [
        "车牌号",
        "行程",
        "开始时间",
        "结束时间",
        "开始位置",
        "结束位置",

        "行驶时间",
        "行驶里程",
        "报警次数",
        "时间/车辆状态",

        "时间/签到方式",
        "报警名称",
        "报警速度",

        "处理时间",
        "处理人",
        "处理方式",
        "处理内容",
      ];

      let dataList = [];

      exportData.forEach((item) => {
        let row = {
          carBrand: null,
          tripNum: null,
          startTime: null,
          endTime: null,
          startAddress: null,
          endAddress: null,

          runTime: null,
          mileage: null,
          alarmNum: null,
          carStatus: null,

          signType: null,
          alarmName: null,
          alarmSpeed: item.speed,

          handleTime: null,
          handlePerson: null,
          handleType: null,
          handleContent: null,

          time: item.time,
          types: null,
        };

        if (this.statusList.indexOf(item.name.trim()) > -1) {
          if (item.name == "停车") {
            row.carStatus = item.time + "/" + item.name + item.address;
          } else {
            row.carStatus = item.time + "/" + item.name;
          }
          row.types = row.carStatus;
          row.signType = null;
          row.alarmName = null;
        } else if (this.signList.indexOf(item.name.trim()) > -1) {
          row.carStatus = null;
          row.signType = item.time + "/" + item.name;
          row.alarmName = null;
          row.types = row.signType;
        } else {
          row.carStatus = null;
          row.signType == null;
          row.alarmName = item.time + "/" + item.name;
          row.types = row.alarmName;
        }

        if (item.recordList.length > 0) {
          item.recordList.forEach((alarm) => {
            let row2 = deepCopy(row);
            row2.handleTime = alarm.HandleTime;
            row2.handlePerson = alarm.HandlePerson;
            row2.handleType = alarm.NoticeTypeName;
            row2.handleContent = alarm.NoticeInfo;
            dataList.push(row2);
          });
        } else {
          dataList.push(row);
        }
      });

      dataList.forEach((item) => {
        for (let idx = 0; idx < tripList.length; idx++) {
          if (
            new Date(item.time) >= new Date(tripList[idx].StartTime) &&
            new Date(item.time) <= new Date(tripList[idx].EndTime)
          ) {
            item.carBrand = tripList[idx].CarBrand;
            item.tripNum = tripList[idx].Index;
            item.startTime = tripList[idx].StartTime;
            item.endTime = tripList[idx].EndTime;
            item.startAddress = tripList[idx].StartAddress;
            item.endAddress = tripList[idx].EndAddress;
            item.runTime = tripList[idx].Duration;
            item.mileage = tripList[idx].Mileage;
            item.alarmNum = tripList[idx].AlarmNum;
            if (item.types) {
              if (item.types.indexOf("签到") == -1) {
                break;
              }
            } else {
              break;
            }
          }
        }
      });

      //根据tripNum分组
      let groupList = {};
      dataList.forEach((item) => {
        if (!(item.tripNum in groupList)) {
          groupList[item.tripNum] = [];
        }
        groupList[item.tripNum].push(item);
      });

      //分组去除每组多余的签到签退
      dataList = filterName(groupList);

      //删除time属性
      let exportList = [];
      dataList.forEach((item) => {
        if (item.tripNum != null) {
          let exportItem = [];
          for (let i in item) {
            if (i !== "time" && i !== "types") {
              exportItem.push(item[i]);
            }
          }
          exportList.push(exportItem);
        }
      });

      let tempArray = [];
      let temp = 0;
      while (temp < 13) {
        let itemArray = [];
        let itemNum = 0;
        while (itemNum < header.length) {
          itemArray.push(null);
          itemNum++;
        }
        tempArray.push(itemArray);
        temp++;
      }

      let data = exportList;
      data.push(tempArray);
      data.unshift(header);
      let merges = {};
      let arr = [];

      merges = {
        0: [], //车牌号合并
        1: [], //行程合并
        2: [], //开始时间
        3: [], //结束时间
        4: [], //开始地址
        5: [], //结束地址
        6: [], //行驶时间
        7: [], //行驶里程
        8: [], //报警次数
        9: [], //车辆状态
        10: [], //签到方式
        11: [], //报警名称
        12: [], //报警速度
      };
      let e = 0;
      while (e < 13) {
        name(1, e);
        e++;
      }
      //循环设置合并规则
      function name(s = 1, column) {
        for (let i = s; i <= data.length; i++) {
          let ed = 0;
          arr.forEach((item) => {
            if (column > 1) {
              if (data[i + 1] && item.num == data[i][1]) {
                ed = item.val[1];
              }
            }
          });
          if (data[i + 1] && data[i][column] == data[i + 1][column]) {
            for (let j = i; j <= data.length - 2; j++) {
              if (column < 2) {
                if (
                  data[i][column] == data[j + 1][column] &&
                  data[i][column] != data[j + 2][column]
                ) {
                  if (column != 0) {
                    const sta = { num: data[i + 1][1], val: [i, j + 1] };
                    arr.push(sta);
                  }
                  merges[column].push([i, j + 1]);
                  return name(j + 2, column);
                }
              } else {
                if (
                  data[i][column] == data[j + 1][column] &&
                  data[i][column] == data[j + 2][column] &&
                  j + 2 <= ed
                ) {
                  merges[column].push([i, j + 2]);
                  return name(j + 2, column);
                } else if (
                  data[i][column] == data[j + 1][column] &&
                  j + 1 <= ed
                ) {
                  merges[column].push([i, j + 1]);
                  return name(j + 1, column);
                }
              }
            }
          }
        }
      }
      if (arr.length > 1) {
        let l = 2;
        while (l < 13) {
          merges[l] = arycat(merges[l]);
          l++;
        }
      }

      let merge = [];
      let j = 0;
      while (j < 13) {
        merges[j].forEach((item) => {
          const obj = { s: { r: item[0], c: j }, e: { r: item[1], c: j } };
          merge.push(obj);
        });
        j++;
      }

      var aoa = data;
      var sheet = XLSX.utils.aoa_to_sheet(aoa);
      sheet["!merges"] = merge;
      sheet["!cols"] = [
        { wpx: 80 },
        { wpx: 50 },
        { wpx: 150 },

        { wpx: 150 },
        { wpx: 150 },
        { wpx: 150 },

        { wpx: 100 },
        { wpx: 100 },
        { wpx: 100 },
        { wpx: 180 },

        { wpx: 200 },
        { wpx: 150 },
        { wpx: 80 },

        { wpx: 150 },
        { wpx: 100 },
        { wpx: 100 },
        { wpx: 200 },
      ];

      let c = 0;
      while (c < header.length) {
        const styleHeader = {
          font: {
            bold: true,
          },
          alignment: {
            wrapText: true,
            horizontal: "center",
            vertical: "center",
            indent: 0,
          },
        };
        const style = {
          alignment: {
            wrapText: true,
            horizontal: "center",
            vertical: "center",
            indent: 0,
          },
        };
        let r = 1;
        while (r < data.length + 1) {
          if (sheet[`${this.numberList[c]}${r}`]) {
            if (r == 1) {
              sheet[`${this.numberList[c]}${r}`].s = styleHeader;
            } else {
              sheet[`${this.numberList[c]}${r}`].s = style;
            }
          }
          r++;
        }
        c++;
      }

      this.loading2 = false;
      const starttime = this.form.StartTime.format("yyyy-MM-dd HH:mm:ss");
      const endtime = this.form.EndTime.format("yyyy-MM-dd HH:mm:ss");

      const exportName = `${carBrand}-${starttime}至${endtime}风险运行分析.xlsx`;
      openDownloadDialog(sheet2blob(sheet), exportName);
    },