vue3 vite4 json数据导出为excel

前话：我只是个工作的小菜鸟，如果有大佬有更好的方法，希望不吝赐教。

该文章是 json数据导出为excel，我有另一篇文章是 table的dom结构转为excel。看你自己怎么选择。如果不懂，或者需要协助，+q：四五七五零五四六八（有偿）

1.电脑环境

node版本 18  

该方法直接适用于vite项目，如果需要在webpack中使用，需要替换 xlsx-style-vite 插件为webpack版的 xlsx-style ，但是这个插件作者没有维护了，可能存在问题。请看我的另一篇关于excel导出的文章。

这是我用到的三个插件的版本，自行安装，版本请保持和我一致。

"xlsx": "^0.16.9", "file-saver": "^2.0.5", "xlsx-style-vite": "0.0.2"

适配了复杂表头情况，如下所示。

直接粘贴源代码

vue组件

下载 import { download } from "@/utils";import { onMounted, onUnmounted, ref, reactive, inject } from "vue";import { useRoute, useRouter } from "vue-router";import exportData from "@/utils/down";const tableHeader = [ { name: "日期", prop: "date", child: [ { name: "姓名", prop: "name" }, { name: "省份", prop: "state" }, { name: "城市", prop: "city" }, { name: "详细地址", prop: "address" }, { name: "门牌号", prop: "zip" }, ], },];const tableData = [ { date: "2016-05-03", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-02", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-04", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-01", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-08", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-06", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", }, { date: "2016-05-07", name: "Tom", state: "California", city: "Los Angeles", address: "No. 189, Grove St, Los Angeles", zip: "CA 90036", },];const download = () => { exportData(tableHeader, tableData);};defineExpose({}); //抛出外部可访问的属性

js代码，创建个模块

import * as XLSX from "xlsx";import XLSXstyle from "xlsx-style-vite";import FileSaver from "file-saver";export default function exportData(tableHeader, tableData = []) { let sheetName = "xlsx复杂表格导出demo"; let wb = XLSX.utils.book_new(); let excelHeader = buildHeader(tableHeader); // 头部行数，用来固定表头 let headerRows = excelHeader.length; // 提取数据 let dataList = extractData(tableData, tableHeader); excelHeader.push(...dataList, []); // 计算合并 let merges = doMerges(excelHeader); // 生成sheet let ws = aoa_to_sheet(excelHeader, headerRows); // 单元格合并 ws["!merges"] = merges; // 头部冻结 ws["!freeze"] = { xSplit: "1", ySplit: "" + headerRows, topLeftCell: "B" + (headerRows + 1), activePane: "bottomRight", state: "frozen", }; //自动设置列宽 const treeList = treeEndObj(tableHeader); const arr = json_to_array(treeList, tableData); auto_width(ws, arr); //删除空数据 let keys = Object.keys(ws); keys.forEach((item, index) => { if (ws[item].v == "") { delete ws[item]; } }); // sheet添加到工作薄上 XLSX.utils.book_append_sheet(wb, ws, "sheet1"); const etout = XLSXstyle.write(wb, { bookType: "xlsx", bookSST: false, type: "binary", }); FileSaver.saveAs( new Blob([s2ab(etout)], { type: "application/octet-stream" }), `${sheetName}.xlsx` ); return wb;}function auto_width(ws, data) { const colWidth = data.map((row) => row.map((val) => { if (val == null) { return { wpx: 10 }; } else { return getTextWidth(val.toString()); } }) ); let result = []; for (let index = 0; index < colWidth[0].length; index++) { // const element = array[index]; const list = colWidth.map((ppp, i) => ppp[index]); result.push({ wpx: Math.max(...list) }); } ws["!cols"] = result;}// 将json数据转换成数组function json_to_array(key, jsonData) { const a = jsonData.map((v) => key.map((j) => { return v[j.prop]; }) ); const b = key.map((v) => v.name); return [b, ...a];}function getTextWidth(str, fontSize = 12) { let result = 0; let ele = document.createElement("span"); //字符串中带有换行符时，会被自动转换成标签，若需要考虑这种情况，可以替换成空格，以获取正确的宽度 str = str.replace(/\n/g, " ").replace(/\r/g, " "); ele.innerText = str; //不同的大小和不同的字体都会导致渲染出来的字符串宽度变化，可以传入尽可能完备的样式信息 ele.style.fontSize = fontSize; //由于父节点的样式会影响子节点，这里可按需添加到指定节点上 document.documentElement.append(ele); result = ele.offsetWidth; document.documentElement.removeChild(ele); return result;}function treeEndObj(tableHeader) { let arr = []; find(tableHeader); return arr; function find(d) { d.forEach((item) => { if (item.child && item.child.length > 0) { find(item.child); } else { arr.push(item); } }); }}/** * 构建excel表头 * @param revealList 列表页面展示的表头 * @returns {[]} excel表格展示的表头 */function buildHeader(revealList) { let excelHeader = []; // 构建生成excel表头需要的数据结构 getHeader(revealList, excelHeader, 0, 0); // 多行表头长短不一，短的向长的看齐，不够的补上行合并占位符 let max = Math.max(...excelHeader.map((a) => a.length)); excelHeader .filter((e) => e.length < max) .forEach((e) => pushRowSpanPlaceHolder(e, max - e.length)); return excelHeader;}/** * 生成头部 * @param headers 展示的头部 * @param excelHeader excel头部 * @param deep 深度 * @param perOffset 前置偏移量 * @returns {number} 后置偏移量 */function getHeader(headers, excelHeader, deep, perOffset) { let offset = 0; let cur = excelHeader[deep]; if (!cur) { cur = excelHeader[deep] = []; } // 填充行合并占位符 pushRowSpanPlaceHolder(cur, perOffset - cur.length); for (let i = 0; i < headers.length; i++) { let head = headers[i]; cur.push(head.name); if ( head.hasOwnProperty("child") && Array.isArray(head.child) && head.child.length > 0 ) { let childOffset = getHeader( head.child, excelHeader, deep + 1, cur.length - 1 ); // 填充列合并占位符 pushColSpanPlaceHolder(cur, childOffset - 1); offset += childOffset; } else { offset++; } } return offset;}/** * 根据选中的数据和展示的列，生成结果 * @param selectionData * @param revealList */function extractData(selectionData, revealList) { // 列 let headerList = flat(revealList); // 导出的结果集 let excelRows = []; // 如果有child集合的话会用到 let dataKeys = new Set(Object.keys(selectionData[0])); selectionData.some((e) => { if (e.child && e.child.length > 0) { let childKeys = Object.keys(e.child[0]); for (let i = 0; i < childKeys.length; i++) { dataKeys.delete(childKeys[i]); } return true; } }); flatData(selectionData, (list) => { excelRows.push(...buildExcelRow(dataKeys, headerList, list)); }); return excelRows;}function buildExcelRow(mainKeys, headers, rawDataList) { // 合计行 let sumCols = []; // 数据行 let rows = []; for (let i = 0; i < rawDataList.length; i++) { let cols = []; let rawData = rawDataList[i]; // 提取数据 for (let j = 0; j < headers.length; j++) { let header = headers[j]; // 父元素键需要行合并 if (rawData["rowSpan"] === 0 && mainKeys.has(header.prop)) { cols.push("!$ROW_SPAN_PLACEHOLDER"); } else { let value; if (typeof header.exeFun === "function") { value = header.exeFun(rawData); } else { value = rawData[header.prop]; } cols.push(value); // 如果该列需要合计,并且是数字类型 if (header["summable"] && typeof value === "number") { sumCols[j] = (sumCols[j] ? sumCols[j] : 0) + value; } } } rows.push(cols); } // 如果有合计行 if (sumCols.length > 0) { rows.push(...sumRowHandle(sumCols)); } return rows;}function sumRowHandle(sumCols) { //TODO return [];}/** * 合并头部单元格 **/function doMerges(arr) { // 要么横向合并 要么纵向合并 let deep = arr.length; let merges = []; for (let y = 0; y < deep; y++) { // 先处理横向合并 let row = arr[y]; let colSpan = 0; for (let x = 0; x < row.length; x++) { if (row[x] === "!$COL_SPAN_PLACEHOLDER") { row[x] = undefined; if (x + 1 === row.length) { merges.push({ s: { r: y, c: x - colSpan - 1 }, e: { r: y, c: x }, }); } colSpan++; } else if (colSpan > 0 && x > colSpan) { merges.push({ s: { r: y, c: x - colSpan - 1 }, e: { r: y, c: x - 1 }, }); colSpan = 0; } else { colSpan = 0; } } } // 再处理纵向合并 let colLength = arr[0].length; for (let x = 0; x < colLength; x++) { let rowSpan = 0; for (let y = 0; y < deep; y++) { if (arr[y][x] === "!$ROW_SPAN_PLACEHOLDER") { arr[y][x] = undefined; if (y + 1 === deep) { merges.push({ s: { r: y - rowSpan, c: x }, e: { r: y, c: x }, }); } rowSpan++; } else if (rowSpan > 0 && y > rowSpan) { merges.push({ s: { r: y - rowSpan - 1, c: x }, e: { r: y - 1, c: x }, }); rowSpan = 0; } else { rowSpan = 0; } } } return merges;}function aoa_to_sheet(data, headerRows) { const ws = {}; 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; } /// 这里生成cell的时候，使用上面定义的默认样式 const cell = { v: data[R][C] || "", s: { font: { sz: 11, //字体大小 bold: false, //加粗 name: "宋体", //字体 color: { rgb: "000000", //十六进制，不带# }, }, alignment: { //文字居中 horizontal: "center", vertical: "center", wrapText: false, //文本自动换行 }, }, }; // 头部(表头)列表加边框 if (R < headerRows) { // 填充 cell.s.fill = { fgColor: { rgb: "C1CCEE", //只支持#格式，但是不能带# }, }; cell.s.font = { sz: 11, bold: true, color: { rgb: "32,33,36" }, }; } 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 { cell.t = "s"; } ws[cell_ref] = cell; } } if (range.s.c < 10000000) { ws["!ref"] = XLSX.utils.encode_range(range); } return ws;}/** * 填充行合并占位符 * */function pushRowSpanPlaceHolder(arr, count) { for (let i = 0; i < count; i++) { arr.push("!$ROW_SPAN_PLACEHOLDER"); }}// 填充列合并占位符function pushColSpanPlaceHolder(arr, count) { for (let i = 0; i < count; i++) { arr.push("!$COL_SPAN_PLACEHOLDER"); }}/** * 展开数据，为了实现父子关系的数据进行行合并 * @param list * @param eachDataCallBack */function flatData(list, eachDataCallBack) { let resultList = []; for (let i = 0; i < list.length; i++) { let data = list[i]; let rawDataList = []; // 每个子元素都和父元素合并成一条数据 if (data.child && data.child.length > 0) { for (let j = 0; j < data.child.length; j++) { delete data.child[j].bsm; let copy = Object.assign({}, data, data.child[j]); rawDataList.push(copy); copy["rowSpan"] = j > 0 ? 0 : data.child.length; } } else { data["rowSpan"] = 1; rawDataList.push(data); } resultList.push(...rawDataList); if (typeof eachDataCallBack === "function") { eachDataCallBack(rawDataList); } } return resultList;}// 扁平头部function flat(revealList) { let result = []; revealList.forEach((e) => { if (e.hasOwnProperty("child")) { result.push(...flat(e.child)); } else if (e.hasOwnProperty("exeFun")) { result.push(e); } else if (e.hasOwnProperty("prop")) { result.push(e); } }); return result;}function s2ab(s) { let buf = new ArrayBuffer(s.length); let view = new Uint8Array(buf); for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff; return buf;}