1. 安装
npm i file-savernpm i xlsxnpm i xlsx-style
如果运行中报错
在\node_modules\xlsx-style\dist\cpexcel.js 807行把 var cpt = require('./cpt' + 'able'); 改成 var cpt = cptable;
2. 创建@/utils/Blob.js文件
/* eslint-disable *//* Blob.js* A Blob implementation.* -05-27** By Eli Grey, * By Devin Samarin, /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 /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 BlobBuildervar BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {varget_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) {vartype = 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 stringif (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {varstr = "", 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 syncthrow 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 stringbb.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));
3. 创建@/utils/Export2Excel.js文件
require('script-loader!file-saver');require('./Blob.js'); require('script-loader!xlsx/dist/xlsx.core.min');import * as XLSX from 'xlsx';import XLSXS from 'xlsx-style';import {saveAs } from 'file-saver';function sheet_from_array_of_arrays(data) {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;}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);}export function export_json_to_excelhb({multiHeader2 = [], // 第一行表头multiHeader = [], // 第二行表头header, // 第三行表头data, //传递的数据filename, //文件名merges = [], // 合并autoWidth = true, //用于设置列宽的bookType = 'xlsx'} = {}) {/* original data */filename = filename || '列表';data = [...data];data.unshift(header);for (let i = multiHeader2.length - 1; i > -1; i--) {data.unshift(multiHeader2[i]);}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);// let borderAll = {// //单元格外侧框线// top: {//style: 'medium'// },// bottom: {//style: 'medium'// },// left: {//style: 'medium'// },// right: {//style: 'medium'// }// };for (let key in ws) {if (ws[key] instanceof Object) {ws[key].s = {// border: borderAll,alignment: {horizontal: 'center', //水平居中对齐vertical: 'center', //垂直居中wrapText: 1 //自动换行},font: {sz: 12 //单元格中字体的样式与颜色设置// color: {// rgb: '495060'// }},bold: true,numFmt: 0};}}ws['I2'] = ws['H2'] = ws['G2'] = ws['F2'] = ws['E2'] = ws['D2'] = ws['C2'] = ws['B2'] = ws['A2']; //用于第二行的单元格的样式设置（如果是合并的第一行，就是1）if (merges.length > 0) {if (!ws['!merges']) ws['!merges'] = [];merges.forEach(item => {ws['!merges'].push(XLSX.utils.decode_range(item));});}if (autoWidth) {let colWidths = [];// 计算每一列的所有单元格宽度// 先遍历行data.forEach(row => {// 列序号let index = 0;// 遍历列for (const key in row) {if (colWidths[index] == null) colWidths[index] = [];switch (typeof row[key]) {case 'string':case 'number':case 'boolean':colWidths[index].push(getCellWidth(row[key]));break;case 'object':case 'function':colWidths[index].push(0);break;}index++;}});ws['!cols'] = [];colWidths.forEach((widths, index) => {// 计算列头的宽度widths.push(getCellWidth(header[index]));// 设置最大值为列宽ws['!cols'].push({wch: Math.max(...widths)});});}console.log(ws);/* add worksheet to workbook */wb.SheetNames.push(ws_name);wb.Sheets[ws_name] = ws;var wbout = XLSXS.write(wb, {bookType: bookType,bookSST: false,type: 'binary'});saveAs(new Blob([s2ab(wbout)], {type: 'application/octet-stream'}),`${filename}.${bookType}`);}export function getCellWidth(value) {if (value == null) {return 12;} else if (value.toString().charCodeAt(0) > 255) {// 判断是否包含中文let length = value.toString().length * 2;if (length > 60) {length = length - 40;//这里的宽度可以自己设定，在前面设置wrapText: 1可以在单元格内换行}return length;} else {return value.toString().length * 1.2;}}
4. 在vue文件中的methods方法中写一个方法
confirmExport () {import('@/utils/Export2Excel').then(excel => {const header = ['问题ID','问题标题','提问时间']; // 表头const filterVal = ['id','title','intime',]; // 表头所对应的字段，这里未填写，请自行填写对应的字段，按实际需求请自行处理该步let rjdata = [];this.multipleSelection.map(ele => {rjdata.push(ele.q_id);});// 调一下接口，获取数据， rjdata 参数doExportHejian({ids: rjdata }).then(res => {// =================================================================================================// 进行所有表头的单元格合并const merges = []; //需要合并的单元格let allList = [];const letter = ['A','B','C'];const list = res.data;let point = 1;// 需要合并的单元格的js ================for (let j = 0; j < list.length; j++) {if (list[j].implementTargetEntities !== null) {for (let i = 0; i < letter.length; i++) {let str = letter[i] + (point + 1) + ':' + letter[i] + (point + list[j].implementTargetEntities.length);merges.push(str);}point += list[j].implementTargetEntities.length;}}// 需要合并的单元格的js ================list.map(ele => {if (ele.implementTargetEntities !== null) {for (let i = 0; i < ele.implementTargetEntities.length; i++) {allList.push({...ele, ...ele.implementTargetEntities[i] });delete allList[allList.length - 1].implementTargetEntities;}} else {allList.push({...ele });delete allList[allList.length - 1].implementTargetEntities;}});allList.map(item => {for (let key in item) {const el = document.createElement('div');el.innerHTML = item[key];item[key] = el.textContent;}});// ============================================================================= 处理我的数据，算一下我哪些需要合并let excelname = 'xxxx'; //导出的excel的名字let data = allList.map(v => filterVal.map(j => v[j])); excel.export_json_to_excelhb({// multiHeader, // 这里是第一行的表头// multiHeader2, // 这里是第二行的表头header: header, // 这里是第三行的表头data,filename: excelname,merges});});});}
