简单点的话直接cope element+admin+vue里面的就行了!
一、简单处理
安装组件
npm install xlsx file-saver -Snpm install script-loader -S -D
1、src下新建utils/Export2Excel.js
/* eslint-disable */import { saveAs } from 'file-saver'import XLSX from 'xlsx'function generateArray(table) { var out = []; var rows = table.querySelectorAll('tr'); var ranges = []; for (var R = 0; R < rows.length; ++R) { var outRow = []; var row = rows[R]; var columns = row.querySelectorAll('td'); for (var C = 0; C < columns.length; ++C) { var cell = columns[C]; var colspan = cell.getAttribute('colspan'); var rowspan = cell.getAttribute('rowspan'); var 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 (var 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 (var k = 0; k < colspan - 1; ++k) outRow.push(null); } out.push(outRow); } return [out, ranges];};function datenum(v, date1904) { if (date1904) v += 1462; var epoch = Date.parse(v); return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);}function sheet_from_array_of_arrays(data, opts) { var ws = {}; var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } }; for (var R = 0; R != data.length; ++R) { for (var 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; var cell = { v: data[R][C] }; if (cell.v == null) continue; var 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._table[14]; cell.v = 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;}function Workbook() { if (!(this instanceof Workbook)) return new Workbook(); this.SheetNames = []; this.Sheets = {};}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;}export function export_table_to_excel(id) { var theTable = document.getElementById(id); var oo = generateArray(theTable); var ranges = oo[1]; /* original data */ var data = oo[0]; var ws_name = "SheetJS"; var wb = new Workbook(), ws = 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; var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' }); saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), "test.xlsx")}export function export_json_to_excel({ multiHeader = [], header, data, filename, merges = [], autoWidth = true, bookType = 'xlsx'} = {}) { /* original data */ filename = filename || 'excel-list' data = [...data] data.unshift(header); for (let i = multiHeader.length - 1; i > -1; i--) { data.unshift(multiHeader[i]) } var ws_name = "SheetJS"; var wb = new Workbook(), ws = 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)) }) } if (autoWidth) { /*设置worksheet每列的最大宽度*/ const colWidth = data.map(row => row.map(val => { /*先判断是否为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 wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: 'binary' }); saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), `${filename}.${bookType}`);}
2、使用方法
import("@/vendor/Export2Excel").then((excel) => { // 表头 const tHeader = [ "Id", "状态", ]; // 数据对应的字段 const filterVal = [ "id", "status", ]; const data = arr; // 数据 excel.export_json_to_excel({ header: tHeader, data, filename: '导出文件', autoWidth: true,// 自动宽度 });})
二:进阶合并单元格
一般而言我们可能需要合并同样数据开头的单元格
而首先excel合并单元格的方法为
excel.export_json_to_excel({ ..... merges: ['A1:A3','B1:B3'], // 合并的单元格 });
是需要每个去指定合并的,这种固定的还好,但是一般而言是动态的,那么就需要我们去生成了!
首先我们需要先合并的行!具体方法可以在本站找到 js 判断数组字符连续出现位置的前后index
// arr 就你自己把数据的那些判断合并行的id 提取出来生成一个数组即可// 找到需要合并的行 var arr = ['a', 'b', 'b', 'c', 'b', 'b', 'b'] arr = arr.sort((a, b) => a - b) // 直接重新排序 let newObj = arr.reduce(function (item, next, index) { if (item[next]) { item[next].end = index; } else { item[next] = {} item[next].name = next item[next].start = index } return item }, {}) // 合并的列 let as=[] const hebingArr = ["A", "B", "C", "I", "J", "K", "L"]; Object.keys(newObj).map((v) => { if (newObj[v].end) { hebingArr.map((s) => { let val = s + (newObj[v].start + 2) + ":" + s + (newObj[v].end + 2); as.push(val); }); } }); return as;
这样我们就能找到全部需要合并的数据了!
三、美化表格
原生的xlsx并不能满足美化表格的左右,只能设置一些宽度!
if (autoWidth) {
具体在上面这部分代码那里可以修改一些宽度!
想要达到美化表格,我们需要先下载插件
npm install xlsx-style --save
然后把Export2Excel.js文件的头部改成
// import XLSX from 'xlsx'import XLSX from 'xlsx-style'
如果引入发生报错:cpexcel.js找不到:
1.在\node_modules\xlsx-style\dist\cpexcel.js 的
2.将 var cpt = require(’./cpt’ + ‘able’);
3.改为 var cpt = cptable;
然后我们找到
export function export_table_to_excel(id) {...ws = sheet_from_array_of_arrays(data);
这个下面添加
ws.A1.s = { font: { sz: 14, bold: true, vertAlign: true }, alignment: { vertical: 'center', horizontal: 'center' }, fill: { bgColor: { rgb: 'E8E8E8' }, fgColor: { rgb: 'E8E8E8' } }, };
没错,你没发现错,这里还是每个都需要单独设置的!
具体可以根据自己的需求去写循环了!
可以和上面合并单元格对应的是
举例1:我们就可以让合并的单元格垂直居中了,
if (merges.length > 0) { if (!ws['!merges']) ws['!merges'] = []; merges.forEach(item => { ws['!merges'].push(XLSX.utils.decode_range(item)) /** 添加部分 */ let l = item.split(':')[0] ws[l].s = { alignment: { vertical: 'center' }, }; /** 添加部分 */ }) }
举例2:我们可以让表头变色
header.map((v, index) => { let obj = ws[('A' + index)] let zimu = String.fromCharCode(65 + index) // 生成 A/B/C/D ws[zimu + 1].s = { font: { sz: 18, bold: true, color: { rgb: 'FFFFFF', }, vertAlign: true }, alignment: { vertical: 'center', horizontal: 'center' }, fill: { bgColor: { rgb: 'ff0000' }, fgColor: { rgb: 'ff0000' }, color: 'ffffff' }, } })
好了其他的就自己玩吧!