排除工具
xlsx(sheet.js): 缺点:社区版无法自定义格式,需要购买pro版本 xlsx-style: 缺点:代码多年未更新,存在bug,需要改动node_modules包 选用工具安装xlsx-js-style
npm install xlsx-js-styled
使用思路
设定每个单元格基础格式,遍历所有数据,填入所需要的格式
使用方法
引入
import * as XLSXStyle from "xlsx-js-style";
设定基础样式
class ConstantCell { constructor(obj) { this.obj = { r: obj.r, c: obj.c, v: { v: obj.v, ct: { fa: "General", t: "g" }, bg: obj.bg || "ffffff", bl: obj.bl || 0, it: 0, fc: obj.fc || "#000", ff: 1, fs: "11", ht: 0, vt: 0, m: obj.m, bold: false } }; } }
获取基础数据
我的数据格式,可以自己设定
pAll = [{data.content:[数据]}]
tables=[{label:'sheet名称',columns:[{label:'列名称',prop:'列字段'}]}]
fileName = '表名称'
将数据改写为EXCEL导出所需格式
function formatData(pAll, tables, fileName) { const mapRes = new Map(); pAll.map((v, i) => { let content = []; if (v.data) { content = v.data.content; } mapRes.set(tables[i].label, { tableData: content, columns: tables[i].columns }); }); const sheetDataArr = []; for (let key of mapRes.keys()) { const tableList = []; const headerList = []; const { tableData, columns } = mapRes.get(key); let objColIndex = 0; //设定表头 columns.map(({ prop, label }) => { objColIndex = objColIndex + 1; const headerConstantCellObj = new ConstantCell({ r: 0, c: objColIndex, v: label, bg: "f8f8f9", m: label, bl: 1 }); headerList.push(headerConstantCellObj.obj); }); tableList.push(headerList); //设定行 tableData.map((v, i) => { const rowList = []; objColIndex = 0; columns.map(({ prop, label }) => { objColIndex = objColIndex + 1; let fc = null; const ConstantCellObj = new ConstantCell({ r: i + 1, c: objColIndex, v: v[prop], bg: "ffffff", m: v[prop], fc }); rowList.push(ConstantCellObj.obj); }); tableList.push(rowList); }); sheetDataArr.push({ tableList, sheetTitle: key }); } formatExcel(sheetDataArr, fileName); }
根据自己所需设置样式:(A1:每个单元格信息)
网上复制的
function formatExcel(sheetDataArr, fileName) { const SheetNames = []; const Sheets = {}; sheetDataArr.map(({ tableList, sheetTitle }, sheetDataIndex) => { let downOriginData = tableList; let arr = []; // 所有的单元格数据组成的二维数组 let bgConfig = {}; let percentageReg = /%$/; let cellValue = null; //列下标 数字转字母 function chatatABC(n) { var orda = "a".charCodeAt(0); var ordz = "z".charCodeAt(0); var len = ordz - orda + 1; var s = ""; while (n >= 0) { s = String.fromCharCode((n % len) + orda) + s; n = Math.floor(n / len) - 1; } return s.toUpperCase(); } // 设置样式 function setStyle(row, col, cellValue) { var colA = chatatABC(col); var key = colA + (row + 1); bgConfig[key] = cellValue; // bgConfig[key] = cellValue.bg.replace(/\#?/, ""); } // 判断值类型是否为百分比 % function isPercentage(value) { return percentageReg.test(value.m) && value.ct && value.ct.t === "n"; } // 获取二维数组 for (let row = 0; row < downOriginData.length; row++) { let arrRow = []; for (let col = 0; col < downOriginData[row].length; col++) { cellValue = downOriginData[row][col].v; setStyle(row, col, cellValue); if (cellValue.ct != null && cellValue.ct.t == "d") { // d为时间格式 2019-01-01 或者2019-01-01 10:10:10 arrRow.push(new Date(cellValue.m.replace(/\-/g, "/"))); //兼容IE } else if (cellValue.m && isPercentage(cellValue)) { //百分比问题 arrRow.push(cellValue.m); } else { arrRow.push(cellValue.v); } // } } arr.push(arrRow); } let opts = { dateNF: "m/d/yy h:mm", cellDates: true, cellStyles: true }; const wb = XLSXStyle.utils.book_new(); let ws = XLSXStyle.utils.aoa_to_sheet(arr, opts); XLSXStyle.utils.book_append_sheet(wb, ws, "readme demo"); let reg = /[\u4e00-\u9fa5]/g; for (let key in ws) { let item = ws[key]; if (item.t === "d") { if (item.w) { //时间格式的设置 let arr = item.w.split(" "); if (arr[1] && arr[1] == "0:00") { ws[key].z = "m/d/yy"; } else { item.z = "yyyy/m/d h:mm:ss"; } } } // 设置单元格样式 if (bgConfig[key]) { const { bg, bold, fc } = bgConfig[key]; ws[key]["s"] = { alignment: { vertical: "center", horizontal: "center" }, fill: { bgColor: { indexed: 32 }, fgColor: { rgb: bg } }, font: { bold, color: { rgb: fc } }, border: { top: { style: "thin", color: { rgb: "999999" } }, bottom: { style: "thin", color: { rgb: "999999" } }, left: { style: "thin", color: { rgb: "999999" } }, right: { style: "thin", color: { rgb: "999999" } } } }; } } //第四步:组装下载数据格式 SheetNames.push(sheetTitle); Sheets[sheetTitle] = Object.assign({}, ws); }); let tmpWB = { SheetNames, //保存的表标题 Sheets }; // 第六步:写入文件 XLSXStyle.writeFile(tmpWB, fileName + ".xlsx"); }
最简单的基本功能完成,后续根据需求慢慢完善
标签:xlsx,style,vue,const,let,key,push,obj,cellValue From: https://www.cnblogs.com/truegrit/p/16739633.html