vue-导出excel
需求:导出含有多级表头的Excel表
步骤
1.安装插件--
npm install -S file-saver
npm install -S xlsx
npm install -D script-loader
cnpm install --save xlsx-style --样式
2.创建一个文件夹---创建Bolb.js 、Export2Excel.js
/* eslint-disable */ /* Blob.js * A Blob implementation. * 2014-05-27 * * By Eli Grey, http://eligrey.com * By Devin Samarin, https://github.com/eboyjr * License: X11/MIT * See LICENSE.md */ /*global self, unescape */ /*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true, plusplus: true */ /*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */ (function (view) { "use strict"; view.URL = view.URL || view.webkitURL; if (view.Blob && view.URL) { try { new Blob; return; } catch (e) {} } // Internally we use a BlobBuilder implementation to base Blob off of // in order to support older browsers that only have BlobBuilder var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) { var get_class = function(object) { return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1]; } , FakeBlobBuilder = function BlobBuilder() { this.data = []; } , FakeBlob = function Blob(data, type, encoding) { this.data = data; this.size = data.length; this.type = type; this.encoding = encoding; } , FBB_proto = FakeBlobBuilder.prototype , FB_proto = FakeBlob.prototype , FileReaderSync = view.FileReaderSync , FileException = function(type) { this.code = this[this.name = type]; } , file_ex_codes = ( "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR " + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR" ).split(" ") , file_ex_code = file_ex_codes.length , real_URL = view.URL || view.webkitURL || view , real_create_object_URL = real_URL.createObjectURL , real_revoke_object_URL = real_URL.revokeObjectURL , URL = real_URL , btoa = view.btoa , atob = view.atob , ArrayBuffer = view.ArrayBuffer , Uint8Array = view.Uint8Array ; FakeBlob.fake = FB_proto.fake = true; while (file_ex_code--) { FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1; } if (!real_URL.createObjectURL) { URL = view.URL = {}; } URL.createObjectURL = function(blob) { var type = blob.type , data_URI_header ; if (type === null) { type = "application/octet-stream"; } if (blob instanceof FakeBlob) { data_URI_header = "data:" + type; if (blob.encoding === "base64") { return data_URI_header + ";base64," + blob.data; } else if (blob.encoding === "URI") { return data_URI_header + "," + decodeURIComponent(blob.data); } if (btoa) { return data_URI_header + ";base64," + btoa(blob.data); } else { return data_URI_header + "," + encodeURIComponent(blob.data); } } else if (real_create_object_URL) { return real_create_object_URL.call(real_URL, blob); } }; URL.revokeObjectURL = function(object_URL) { if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) { real_revoke_object_URL.call(real_URL, object_URL); } }; FBB_proto.append = function(data/*, endings*/) { var bb = this.data; // decode data to a binary string if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) { var str = "" , buf = new Uint8Array(data) , i = 0 , buf_len = buf.length ; for (; i < buf_len; i++) { str += String.fromCharCode(buf[i]); } bb.push(str); } else if (get_class(data) === "Blob" || get_class(data) === "File") { if (FileReaderSync) { var fr = new FileReaderSync; bb.push(fr.readAsBinaryString(data)); } else { // async FileReader won't work as BlobBuilder is sync throw new FileException("NOT_READABLE_ERR"); } } else if (data instanceof FakeBlob) { if (data.encoding === "base64" && atob) { bb.push(atob(data.data)); } else if (data.encoding === "URI") { bb.push(decodeURIComponent(data.data)); } else if (data.encoding === "raw") { bb.push(data.data); } } else { if (typeof data !== "string") { data += ""; // convert unsupported types to strings } // decode UTF-16 to binary string bb.push(unescape(encodeURIComponent(data))); } }; FBB_proto.getBlob = function(type) { if (!arguments.length) { type = null; } return new FakeBlob(this.data.join(""), type, "raw"); }; FBB_proto.toString = function() { return "[object BlobBuilder]"; }; FB_proto.slice = function(start, end, type) { var args = arguments.length; if (args < 3) { type = null; } return new FakeBlob( this.data.slice(start, args > 1 ? end : this.data.length) , type , this.encoding ); }; FB_proto.toString = function() { return "[object Blob]"; }; FB_proto.close = function() { this.size = this.data.length = 0; }; return FakeBlobBuilder; }(view)); view.Blob = function Blob(blobParts, options) { var type = options ? (options.type || "") : ""; var builder = new BlobBuilder(); if (blobParts) { for (var i = 0, len = blobParts.length; i < len; i++) { builder.append(blobParts[i]); } } return builder.getBlob(type); }; }(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content || this));
/* eslint-disable */ require('script-loader!file-saver'); require('script-loader!./Blob'); require('script-loader!xlsx/dist/xlsx.core.min'); import XLSX from 'xlsx-style'; 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); console.log('a'); var oo = generateArray(theTable); var ranges = oo[1]; /* original data */ var data = oo[0]; var ws_name = 'SheetJS'; console.log(data); 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'); } function formatJson(jsonData) { console.log(jsonData); } export function export_json_to_excel(th, jsonData, defaultTitle) { /* original data */ var data = jsonData; data.unshift(th); var ws_name = 'SheetJS'; var wb = new Workbook(), ws = sheet_from_array_of_arrays(data); /* 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' }); var title = defaultTitle || '列表'; saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), title + '.xlsx'); } //导出带有多级表头的Excel export function export_json_to_mul_excel({ headerLen = 4, //一共几级表头 multiHeader = [], //第一行表头 multiHeader2 = [], //第二行表头 multiHeader3 = [], //第三行表头 header, data, filename, merges = [], autoWidth = true, bookType = 'xlsx' } = {}) { /* original data */ filename = filename || 'excel-list'; data = [...data]; //此处是第三行行表头 data.unshift(header); if (multiHeader3.length > 0) { for (let i = multiHeader3.length - 1; i > -1; i--) { data.unshift(multiHeader3[i]); } } //此处是第二行表头 if (multiHeader2.length > 0) { for (let i = multiHeader2.length - 1; i > -1; i--) { data.unshift(multiHeader2[i]); } } //此处是第一行行表头 if (multiHeader.length > 0) { for (let i = multiHeader.length - 1; i > -1; i--) { data.unshift(multiHeader[i]); } } // data.unshift(th); 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)); }); } /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; //设置Excel表头和内容的格式 setExcelStyle(wb, multiHeader, headerLen); var wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: 'binary' }); saveAs( new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), filename + '.' + bookType ); } function setExcelStyle(wb, multiHeader, headerLen) { let dataInfo = wb.Sheets[wb.SheetNames[0]]; // console.log('dataInfo', dataInfo); const borderAll = { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }; // 给所有单元格加上边框,内容居中,字体,字号,标题表头特殊格式部分后面替换 for (var i in dataInfo) { if (i == '!ref' || i == '!merges' || i == '!cols' || i == '!rows' || i == 'A1') { } else { dataInfo[i + ''].s = { //border: borderAll, alignment: { horizontal: 'left', vertical: 'center' }, font: { name: '微软雅黑', sz: 10 } }; } } // 设置表格样式 const arrabc_base = [ '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', ]; const arrabc_repeat=['','A','B','C','D'] let arrabc=[] for(let i=0;i<arrabc_repeat.length;i++){ for(let j=0;j<arrabc_base.length;j++){ arrabc.push(arrabc_repeat[i]+arrabc_base[j]) } } const fontName = '微软雅黑'; const rbgColor = 'f2f2f2'; //字体格式 //第一行标题字体 const commonFontHead = { name: fontName, sz: 16, color: { rgb: '666666' }, bold: true }; //第二行标题字体 const commonFontHead2 = { name: fontName, sz: 14, color: { rgb: '666666' }, bold: true }; //第三行标题字体 const commonFontHead3 = { name: fontName, sz: 12, color: { rgb: '666666' }, bold: true }; //普通标题字体 const commonFont = { name: fontName, sz: 10, color: { rgb: '333333' }, bold: true }; //对其格式 const commonAlignment = { horizontal: 'center', vertical: 'center' }; //背景填充格式 const commonFill = { fgColor: { rgb: rbgColor } }; // 给标题、表格描述信息、表头等部分加上特殊格式 arrabc.some(function(v) { for (let j = 1; j < multiHeader.length + headerLen; j++) { const _v = v + j; if (dataInfo[_v]) { dataInfo[_v].s = {}; // 标题部分A1-Z1 if (j == 1) { dataInfo[v + j].s = { border: borderAll, font: commonFontHead, alignment: commonAlignment, fill: commonFill }; } else { // 表头部分,根据表头特殊格式设置 if (multiHeader.length == 0) { // multiHeader.length = 0 时表头没有合并单元格,表头只占1行A2-Z2 const fv = v + (multiHeader.length + 2); dataInfo[fv].s = { border: borderAll, font: commonFontHead2, alignment: commonAlignment, fill: commonFill }; } else if (multiHeader.length == 1) { // multiHeader.length = 0 时表头有合并单元格,表头只占2行A2-Z2,A3-Z3,这是没有描述信息只有表头合并的 dataInfo[v + j].s = { border: borderAll, font: commonFontHead3, alignment: commonAlignment, fill: commonFill }; } else { // multiHeader.length = 0 时表头有合并单元格,表头多行 dataInfo[v + j].s = { border: borderAll, font: commonFont, alignment: { horizontal: 'left', vertical: 'center' } }; } } //第三行的表头格式 if (headerLen > 2) { // multiHeader.length + 2 是表头的最后1行 if( dataInfo[v + (multiHeader.length + 2)]!=null){ dataInfo[v + (multiHeader.length + 2)].s = { border: borderAll, font: commonFont, alignment: commonAlignment, fill: commonFill }; } } //第四行的表头格式 if (headerLen > 3) { if( dataInfo[v + (multiHeader.length + 3)]!=null){ dataInfo[v + (multiHeader.length + 3)].s = { border: borderAll, font: commonFont, alignment: commonAlignment, fill: commonFill }; } } } } }); }
在Export2Excel.js 文件中参考了网上文档添加了可以支持多级表头和表头格式的方法 export_json_to_mul_excel
如果需要对表头进行样式修改,就必须在Export2Excel中添加样式文件引入
import XLSX from 'xlsx-style';
3.导出方法的调用
在需要导出的Vue页面引入导出方法:
import { export_json_to_mul_excel } from '../../../../utils/vendor/Export2Excel.js';
测试用例:
excel2ExcelTest(){ require.ensure([], () => { // const { export_json_to_excel } = require('vendor/Export2Excel'); const multiHeader = [ [ '序号', 'title 1', '', '', '', '', '', '', '', ] ]; const multiHeader2 = [ [ '', 'title 2-1', '', '', '', 'title 2-2', '', '', '', ] ]; const multiHeader3 = [ [ '', 'title 3-1', '', 'title 3-2', '', 'title 3-3', '', 'title 3-4', '', ] ]; const tHeader = [ '', '姓名', '性别', '身高', '体重', '年龄', '电话', '身份证号码', '住址', ]; const filterVal = [ 'index', 'name', 'sex', 'height', 'weight', 'age', 'phone_no', 'id_card_no', 'address', ]; const excelData = [{ index:1, name:'张三', sex:'男', height:'175', weight:'120', age:'30', phone_no:'13612345678', id_card_no:'3123199508081244', address:'上海浦东' }]; const data = this.formatJson(filterVal, excelData); const merges = [ 'A1:A4', 'B1:I1', 'B2:E2', 'F2:I2', 'B3:C3', 'D3:E3', 'F3:G3', 'H3:I3', ]; const headerLen = 4; export_json_to_mul_excel({ headerLen, //一共多少级表头 multiHeader, //这里是第一行的表头 multiHeader2, //这里是第二行的表头 multiHeader3, //这里是第三行的表头 header: tHeader, //这里应该是算第四行的表头 data, filename:'report', merges }); }); },
导出测试用例主要使用的参数为一级、二级、三级、四级表头、需要导出的数据、需要合并的表头数据
导出结果:
附上一份参考文档:
https://blog.csdn.net/weixin_43965143/article/details/106572755