话不多说 上代码
1、控制台引入插件
npm install xlsx-style --save npm install xlsx --save
2、需要修改源码:在\node_modules\xlsx-style\dist\cpexcel.js 807 行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable 不然会报错;
3、设置每个单元格样式 下面的参数 styleCallback
styleCallback(cellContent,cellPosition) { let border= { top: { style: 'thin', }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }; let alignment={ horizontal: 'center', //水平居中对齐 vertical:'center', wrapText:true }; let textFont={ sz: 12, bold: true, color: { rgb: "000000" } }; for(var po in cellPosition){ cellContent[cellPosition[po]].s = {border:border, alignment: alignment, // font:textFont, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFFFF" } } } } },
4、设置单元格的宽高 做为参数传到下面的colRowWHCallback
colRowWHCallback(cellContent) { cellContent['!cols']= [ {wpx: 150}, {wpx: 150}, {wpx: 150}, {wpx: 150}, ]; cellContent['!rows']=[ {hpx: 30,}, {hpx: 60} ]; },
5、excel导出核心
import xlsxStyle from "xlsx-style"; const ExpExcel = {}; (function (exp) { //fileName: excel的名字 //json: 数组 eg:[{0:"1",1:"2",......},{0:"1",1:"2",......}] 数组里几个json表里面就有几个sheet //type: "xlsx" //mergeCell 表的样式 eg:[{0:{s:{r:1,c:0},e:{r:4,c:0}},1:{s:{r:2,c:0},e{r:3,c:0}}},{0:{s:{r:1,c:0},e:{r:4,c:0}},1:{s:{r:2,c:0},e{r:3,c:0}}}] 跟上面json数组里面的json一一对应 // s:{r:1,c:0} A2 e:{r:4,c:0}A5 // 0:{s:{r:1,c:0},e:{r:4,c:0}}表示合并excel单元格里面的A2-A5 //styleCallback: 设置每个单元格的样式 //colRowWHCallback :单元格的宽高之类的应该 //biaomin :excel里面每个sheet的名字 eg:["sheet1","sheet2"] 跟上面的json,mergeCell,都是一一对应的 exp.downloadExl=function (fileName,json, type,mergeCell,styleCallback,colRowWHCallback,biaomin) { let workSheet={ SheetNames : [], Sheets:{} };; for(let k=0;k<json.length;k++){ let ok; //每个单元格的内容 var cellContent = getCellContent(json[k]); //获取所有的单元格 var cellPosition = Object.keys(cellContent); if (styleCallback) { styleCallback(cellContent,cellPosition); } if (colRowWHCallback) { colRowWHCallback(cellContent); } //单元格合并 cellContent["!merges"]=mergeCell[k]; //保存的表标题 workSheet.SheetNames.push(biaomin[k]); workSheet.Sheets[biaomin[k]]= Object.assign({}, cellContent, //内容 { '!ref': cellPosition[0] + ':' + cellPosition[cellPosition.length - 1] //设置填充区域 }); } //这里的数据是用来定义导出的格式类型 //创建二进制对象写入转换好的字节流 let buf=s2ab(xlsxStyle.write(workSheet, { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' })); let exportExcelData = new Blob([buf],{type: ""}); saveAs(exportExcelData, fileName +"."+(type == undefined ? 'xlsx':type)); } //填充Excel每个单元格的内容 function getCellContent(jsonData) { var keyMap = []; //获取keys for (var k in jsonData[0]) { keyMap.push(k); } var cellData=[]; jsonData.map((v, i) => keyMap.map((k, j) => Object.assign({}, { v: v[k], position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1) }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => cellData[v.position] = { v: v.v }); return cellData; } 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; } // 将指定的自然数转换为26进制表示。映射关系:[0-25] -> [A-Z]。 function getCharCol(n) { let temCol = '', s = '', m = 0 while (n > 0) { m = n % 26 + 1 s = String.fromCharCode(m + 64) + s n = (n - m) / 26 } return s } function saveAs(obj, fileName) { var tmpa = document.createElement("a"); tmpa.download = fileName || "下载"; tmpa.href = URL.createObjectURL(obj); tmpa.click(); setTimeout(function () { URL.revokeObjectURL(obj); }, 100); } })(ExpExcel); export default ExpExcel;
6、调用核心 ExpExcel核心的类名 ys对应核心的mergeCell
ExpExcel.downloadExl("牛逼格拉斯",json, "xlsx",ys,_self.setCellStyle,_self.setColRowWH,biaom);