/* eslint-disable */
// import { saveAs } from "file-saver";
const saveAs = require("file-saver");
// import XLSX from "xlsx";
import XLSX from "yxg-xlsx-style";
import moment from "moment";
export default class Export2Excel {
public static generateArray(table: any) {
const out = [];
const rows = table.querySelectorAll("tr");
const ranges = [];
for (let R = 0; R < rows.length; ++R) {
const outRow = [];
const row = rows[R];
const columns = row.querySelectorAll("td");
for (let C = 0; C < columns.length; ++C) {
const cell = columns[C];
let colspan = cell.getAttribute("colspan");
let rowspan = cell.getAttribute("rowspan");
let cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function(range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (let i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1,
c: outRow.length + colspan - 1
}
});
}
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan) for (let k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
}
public static datenum(v: any, date1904?: any) {
if (date1904) v += 1462;
const epoch: number = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30)).getTime()) / (24 * 60 * 60 * 1000);
}
public static sheet_from_array_of_arrays(data: any, opts?: any) {
const ws: any = {};
const range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (let R = 0; R != data.length; ++R) {
for (let C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
const cell: any = {
v: data[R][C]
};
//空数据使用短线替换
if (cell.v == null) {
cell.v = "-";
}
const cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === "number") cell.t = "n";
else if (typeof cell.v === "boolean") cell.t = "b";
else if (cell.v instanceof Date) {
cell.t = "n";
cell.z = (XLSX.SSF as any)._table[14];
cell.v = this.datenum(cell.v);
} else cell.t = "s";
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws["!ref"] = XLSX.utils.encode_range(range);
return ws;
}
public static s2ab(s: any) {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
public static export_table_to_excel(id: any) {
const theTable = document.getElementById(id);
const oo = this.generateArray(theTable);
const ranges = oo[1];
/* original data */
const data = oo[0];
const ws_name = "SheetJS";
const wb: any = {
SheetNames: [],
Sheets: []
},
ws = this.sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws["!merges"] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
const wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: false,
type: "binary"
});
saveAs(
new Blob([this.s2ab(wbout)], {
type: "application/octet-stream"
}),
"test.xlsx"
);
}
/*
参数 说明 类型 可选值 默认值
header 导出数据的表头 Array / []
data 导出的具体数据 Array / [[]]
filename 导出文件名 String / excel-list
autoWidth 单元格是否要自适应宽度 Boolean true / false true
bookType 导出文件类型 String xlsx, csv, txt, more xlsx
*/
public static export_json_to_excel({
multiHeader = [],
header = [],
data = [],
filename = "excel-list",
merges = [],
autoWidth = true,
bookType = "xlsx"
} = {}) {
/* original data */
data = [...data];
if (header.length) (data as any).unshift(header);
if (multiHeader.length) {
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i]);
}
}
// 添加时间
const timer = moment().format("YYYY-MM-DD HH:mm:ss");
const timerHeader: any = [];
for (let i = 0; i < header.length; i++) {
timerHeader.push(timer);
}
if (timerHeader.length) (data as any).unshift(timerHeader);
const ws_name = "SheetJS";
const wb: any = {
SheetNames: [],
Sheets: []
},
ws = this.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));
});
} else {
ws["!merges"] = [
{
s: {
c: 0,
r: 0
},
e: {
c: header.length - 1,
r: 0
}
}
];
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map((row: any) =>
row.map((val: any) => {
/*先判断是否为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 dataInfo = wb.Sheets[wb.SheetNames[0]];
// 设置单元格框线
const borderAll = {
top: {
style: "thin"
},
bottom: {
style: "thin"
},
left: {
style: "thin"
},
right: {
style: "thin"
}
};
// 给所有单元格加上边框，内容居中，字体，字号，标题表头特殊格式部分后面替换
for (var i in dataInfo) {
if (i == "!ref" || i == "!merges" || i == "!cols" || i == "!rows" || i == "A1") {
} else {
dataInfo[i + ""].s = {
border: borderAll,
alignment: {
horizontal: "center",
vertical: "center"
},
font: {
name: "微软雅黑",
sz: 10
}
};
}
}
// 设置表格样式
const arrabc = [
"A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z"
];
// 给标题、表格描述信息、表头等部分加上特殊格式
arrabc.some(function(v) {
for (let j = 1; j < multiHeader.length + 3; j++) {
const _v = v + j;
if (dataInfo[_v]) {
dataInfo[_v].s = {};
// 标题部分A1-Z1
if (j == 1) {
dataInfo[v + j].s = {
font: {
name: "微软雅黑",
sz: 12,
color: {
rgb: "000000"
},
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: "center",
vertical: "center"
}
};
} else {
// 表头部分,根据表头特殊格式设置
if (multiHeader.length == 0) {
// multiHeader.length = 0 时表头没有合并单元格，表头只占1行A2-Z2
const fv = v + (multiHeader.length + 2);
dataInfo[fv].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 11,
bold: true
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
}
};
} else if (multiHeader.length == 1) {
// multiHeader.length = 0 时表头有合并单元格，表头只占2行A2-Z2，A3-Z3，这是没有描述信息只有表头合并的
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 11
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
}
};
} else {
// multiHeader.length = 0 时表头有合并单元格，表头多行
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 9
},
alignment: {
horizontal: "left",
vertical: "center"
}
};
}
}
// multiHeader.length + 2 是表头的最后1行
dataInfo[v + (multiHeader.length + 2)].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 10
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
}
};
}
}
});
const wbout = XLSX.write(wb, {
bookType: bookType as any,
bookSST: false,
type: "binary"
});
saveAs(
new Blob([this.s2ab(wbout)], {
type: "application/octet-stream"
}),
`${filename}.${bookType}`
);
}
}
// 前端导出
protected handleExport() {
if (this.tableData.length === 0) {
this.$message.info("导出数据为空");
return;
}
const columns = this.$refs["coverTable"]?.columns;
// 注意mutiHeader中的数据是[]
const multiHeader: any = [["时间", "进水口", "", "", "", "", "", "", "", "出水口", "", "", "", "", "", "", ""]];
const header: any = [];
columns.slice(0, columns.length).forEach((item: any) => {
header.push(item.label);
});
const data = this.tableData.map((v: any) =>
columns.slice(0, columns.length).map((j: any) => {
return v[j["property"]];
})
);
const merges: any = ["A1:Q1", "A2:A3", "B2:I2", "J2:Q2"];
excel.export_json_to_excel({
multiHeader,
header,
merges,
data,
filename: "监测报表_" + moment().format("YYYY-MM-DD_HHmmss"),
autoWidth: true,
bookType: "xlsx"
});
}
