前言
应用插件xlsx、xlsx-style导出带有款式的表格，两者都能够实现创立excel，然而xlsx免费版仅仅只能做单元格合并，免费版能力设置单元格款式，所以须要用到xlsx-style插件实现表格款式的批改
一、应用步骤
1. 装置环境
- vue: 2.5.22
- xlsx: 0.18.5
- xlsx-style: 0.8.13
2. 引入xlsx库
npm i xlsx xlsx-style -S3. 批改js文件
这里咱们须要两个文件：
- node_modules/xlsx/dist里的xlsx.extendscript.js
node_modules/xlsx/dist里的xlsx.js
批改xlsx.extendscript.js里所有XLSX变量为XLSX2，防止和xlsx.js里的变量抵触
var XLSX = {};...批改xlsx.js里的办法，使导出反对自定义行高性能
// 减少行高设置var DEF_PPI = 96, PPI = DEF_PPI;function px2pt(px) { return px * 96 / PPI; }function pt2px(pt) { return pt * PPI / 96; }function write_ws_xml_data(ws, opts, idx, wb) { var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows']; var dense = Array.isArray(ws); var params = ({r:rr}), row, height = -1; for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C); for(R = range.s.r; R <= range.e.r; ++R) { r = []; rr = encode_row(R); for(C = range.s.c; C <= range.e.c; ++C) { ref = cols[C] + rr; var _cell = dense ? (ws[R]||[])[C]: ws[ref]; if(_cell === undefined) continue; if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell); } if(r.length > 0 || (rows && rows[R])) { params = ({r:rr}); if(rows && rows[R]) { row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } } o[o.length] = (writextag('row', r.join(""), params)); } } if(rows) for(; R < rows.length; ++R) { if(rows && rows[R]) { params = ({r:R+1}); row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } o[o.length] = (writextag('row', "", params)); } } return o.join("");}创立自定义的js文件export2Excel.js 内容如下
// 将一个sheet转成最终的excel文件的blob对象，而后利用URL.createObjectURL下载function sheet2blob(sheet, sheetName) { sheetName = sheetName || 'sheet1'; var workbook = { SheetNames: [sheetName], Sheets: {} }; workbook.Sheets[sheetName] = sheet; // 生成excel的配置项 var wopts = { bookType: 'xlsx', // 要生成的文件类型 bookSST: false, // 是否生成Shared String Table，官网解释是，如果开启生成速度会降落，但在低版本IOS设施上有更好的兼容性 type: 'binary' }; var wbout = XLSX.write(workbook, wopts); var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" }); // 字符串转ArrayBuffer 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; } return blob;} // 将workbook装化成blob对象,可反对多个sheetfunction workbook2blob(workbook) { // 生成excel的配置项 var wopts = { // 要生成的文件类型 bookType: "xlsx", // 是否生成Shared String Table，官网解释是，如果开启生成速度会降落，但在低版本IOS设施上有更好的兼容性 bookSST: false, type: "binary" }; var wbout = XLSX.write(workbook, wopts); // 将字符串转ArrayBuffer 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; } var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" }); return blob;}/** * 通用的关上下载对话框办法 * @param url 下载地址，也能够是一个blob对象，必选 * @param saveName 放弃文件名，可选 */function openDownloadDialog(url, saveName) { if (typeof url == 'object' && url instanceof Blob) { // 创立blob地址 url = URL.createObjectURL(url); } var aLink = document.createElement('a'); aLink.href = url; // HTML5新增的属性，指定保留文件名，能够不要后缀，留神，file:///模式下不会失效 aLink.download = saveName || ''; var event; if (window.MouseEvent) event = new MouseEvent('click'); else { event = document.createEvent('MouseEvents'); event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null); } aLink.dispatchEvent(event);}// 为合并项增加边框function addRangeBorder(range, ws) { let arr = ["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"]; range.forEach(item => { let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r); let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c); const test = ws[arr[startRowNumber] + (startColNumber + 1)]; for (let col = startColNumber; col <= endColNumber; col++) { for (let row = startRowNumber; row <= endRowNumber; row++) { ws[arr[row] + (col + 1)] = test; } } }) return ws;}4. index.html引入
<script src="xlsx.js"></script><script src="export2Excel.js"></script><script src="xlsx.extendscript.js"></script>5. vue代码
<template> <div class="config-form"> <el-button @click="exportExcel">下载文档</el-button> </div></template><script> export default { data() { return { value: [20, 50], excelAoa: [ ['XXXXXXXXXX记录表', null, null, null, null, null, null], ['与会者', null, null, null, null, null, null] ['序号', '姓名', '性别', '年龄', '批改人', '批改日期', '提交日期'] ] } }, methods: { exportExcel() { const aoa = [ ...this.excelAoa ]; //这个就是批改格局的代码 sheet["A1"].s = { font: { sz: 14, bold: true, color: { rgb: "0000FF" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } }; //<====设置xlsx单元格款式 const sheet = this.xlsxAddStyle(aoa); openDownloadDialog(sheet2blob(sheet), '导出.xlsx'); }, // 表格款式设置 xlsxAddStyle(aoa) { // 留神XLSX2变量 const sheet = XLSX2.utils.aoa_to_sheet(aoa); // 合并单元格 s 开始坐标 e完结坐标 c-x轴 r-y轴 const mergeArr = [ {s: {c: 0, r: 0}, e: {c: 6, r: 0}}, {s: {c: 1, r: 1}, e: {c: 6, r: 1}} ] const rowH = []; // 表格每列高度 // 单元格外侧有框线 const borderAll = { top: { style: "thin" }, bottom: { style: "thin" }, left: { style: "thin" }, right: { style: "thin" }, }; // 单元格外侧无框线 const noBorder = { top: { style: "" }, bottom: { style: "" }, left: { style: "" }, right: { style: "" }, }; for (const key in sheet) { if (Object.hasOwnProperty.call(sheet, key)) { const element = sheet[key]; if (typeof element === "object") { const index = Number(key.slice(1)) - 1; rowH[index] = { hpx: 30 }; element.s = { alignment: { horizontal: "center", // 所有单元格右对齐 vertical: "center", // 所有单元格垂直居中 wrapText: true // 换行 }, font: { name: "微软雅黑", sz: 10, italic: false, underline: false, }, border: borderAll, fill: { fgColor: { rgb: "FFFFFFFF" }, }, }; // 题目的款式 if (['A1'].includes(key)) { element.s.alignment.horizontal = 'center'; element.s.font.bold = true; element.s.font.sz = 15; element.s.fill.border = noBorder; } } } } // 单元格的列宽 sheet["!cols"] = [ { wpx: 60 }, { wpx: 100 }, { wpx: 120 }, { wpx: 120 }, { wpx: 100 }, { wpx: 100 }, { wpx: 100 } ]; sheet["!rows"] = rowH; sheet["!merges"] = mergeArr; // 合并项增加边框 const sheetNew = addRangeBorder(sheet['!merges'], sheet); return sheetNew } } }</script>