1，export2Excel.js
/* 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}` ); } }
2，关键代码
3，使用
// 前端导出 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" }); }
4，结果
