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);
},