vue导出excel并修改表头样式

vue导出excel并修改样式

本文参考CSDN蓝色的落叶（侵删）

DEMO下载

安装插件
npm install --save xlsx(修改样式需要下载npm install --save xlsx-style)
npm install -S file-saver
npm install -D script-loader
安装npm install --save xlsx-style会出现找不到cpexcel.js的错误
解决方案：创建自定义node_moduls文件夹替换插件内文件（vue修改插件源码）或直接修改源码
\node_modules\xlsx-style\dist\cpexcel.js 807行的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;
下载Blob.js文件
新建一个Export2Excel.js（摘自CSDN蓝色的落叶）

//这是从网上的，改了一点点 Export2Excel.js(此内容转自https://blog.csdn.net/qq_38502227/article/details/101702819）
require("script-loader!file-saver");
import XLSX from "xlsx-style";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;
}function export_json_to_excel({title,multiHeader = [],header,data,filename,merges = [],autoWidth = true,bookType = "xlsx"} = {}) {// const merges = [`A1:${getCharCol(list.length - 2)}1`]; //合并单元格/* original data */filename = filename || "excel-list";data = [...data];data.unshift(header);if (title) {data.unshift(title);}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: 13};} else if (val.toString().charCodeAt(0) > 255) {/*再判断是否为中文*/return {wch: val.toString().length * 2};} else {return {wch: val.toString().length};}}));// console.log(colWidth);/*以第一行为初始值*/let result = colWidth[0];colWidth[0][0]["wch"] = 13;// console.log(colWidth[0][0]["wch"]);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 dataInfo = wb.Sheets[wb.SheetNames[0]];// 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 == 'A1') {//   } else {//     dataInfo[i + ''].s = {//       border: borderAll//     }//   }// }// 标题行let arr = ["A1","B1","C1","D1","E1","F1","G1","H1","I1","J1","K1","L1","M1","N1","O1","P1","Q1","R1","S1","T1","U1","V1","W1","X1","Y1","Z1"];// arr.some(function(v) {//   let a = merges[0].split(":");//   if (v == a[1]) {//     dataInfo[v].s = {};//     return true;//   } else {//     dataInfo[v].s = {};//   }// });//设置主标题样式let style = {font: {// name: "宋体",// sz: 18,color: { rgb: "000000" },bold: true// italic: false,// underline: false},alignment: {horizontal: "center",vertical: "center"}// fill: {//   fgColor: {rgb: "008000"},// },};// dataInfo["B1"].s = style;// dataInfo["A1"].s = style;//excel标题样式for (var i = 0; i < header.length; i++) {dataInfo[arr[i]].s = style;}var wbout = XLSX.write(wb, {bookType: bookType,bookSST: false,type: "binary"});saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}),`${filename}.${bookType}`);
}
function toExportExcel(tHeader, data, filename) {import("@/utils/excel/Export2Excel").then(excel => {//表头对应字段excel.export_json_to_excel({header: tHeader,data: data,filename: filename,autoWidth: true,bookType: "xlsx"});});
}
export { toExportExcel, export_json_to_excel };

将Blob.js和Export2Excel.js建立一个excel.js文件（摘自CSDN蓝色的落叶）

(此内容转自https://blog.csdn.net/qq_38502227/article/details/101702819）
//util.js
import "@/utils/excel/Blob";export { json2excel, getCharCol, formatJson };// function parseTime(time, cFormat) {
//   if (arguments.length === 0) {
//     return null;
//   }
//   const format = cFormat || "{y}-{m}-{d} {h}:{i}:{s}";
//   let date;
//   if (typeof time === "object") {
//     date = time;
//   } else {
//     if (typeof time === "string" && /^[0-9]+$/.test(time)) {
//       time = parseInt(time);
//     }
//     if (typeof time === "number" && time.toString().length === 10) {
//       time = time * 1000;
//     }
//     date = new Date(time);
//   }
//   const formatObj = {
//     y: date.getFullYear(),
//     m: date.getMonth() + 1,
//     d: date.getDate(),
//     h: date.getHours(),
//     i: date.getMinutes(),
//     s: date.getSeconds(),
//     a: date.getDay()
//   };
//   // eslint-disable-next-line
//   const time_str = format.replace(/{(y|m|d|h|i|s|a)+}/g, (result, key) => {
//     let value = formatObj[key];
//     // Note: getDay() returns 0 on Sunday
//     if (key === "a") {
//       return ["日", "一", "二", "三", "四", "五", "六"][value];
//     }
//     if (result.length > 0 && value < 10) {
//       value = "0" + value;
//     }
//     return value || 0;
//   });
//   // eslint-disable-next-line
//   return time_str;
// }function json2excel(tableJson, filenames, autowidth, bookTypes) {import("@/utils/excel/Export2Excel").then(excel => {var tHeader = [];var dataArr = [];var sheetnames = [];for (var i in tableJson) {tHeader.push(tableJson[i].tHeader);dataArr.push(formatJson(tableJson[i].filterVal, tableJson[i].tableDatas));sheetnames.push(tableJson[i].sheetName);}excel.export_json_to_excel({header: tHeader,data: dataArr,sheetname: sheetnames,filename: filenames,autoWidth: autowidth,bookType: bookTypes});});
}
// 数据过滤，时间过滤
function formatJson(filterVal, jsonData) {return jsonData.map(v =>filterVal.map(j => {if (j === "timestamp") {return parseTime(v[j]);} else {return v[j];}}));
}// 获取26个英文字母用来表示excel的列
function getCharCol(n) {for (var i = 0; i < this.list.length; i++) {this.list[i].showActive = false;if (index == i) {this.list[index].showActive = true;}}let temCol = "",s = "",m = 0;while (n > 0) {m = (n % 26) + 1;s = String.fromCharCode(m + 64) + s;n = (n - m) / 26;}return s;
}

5.使用

<template><div class="home"><!--    <img alt="Vue logo" src="../assets/logo.png">--><button @click="exportData">Excel导出</button></div>
</template><script>
// @ is an alias to /src
// import HelloWorld from '@/components/HelloWorld.vue'
import {formatJson} from '@/utils/excel/excel'
import {toExportExcel} from '@/utils/excel/Export2Excel'export default {name: 'Home',components: {// HelloWorld},methods: {exportData() {const list = [{name: '小明',age: 16,},{name: '小红',age: 15,},{name: '小刚',age: 16,},]const tHeader = ['姓名', '年龄'] //表头const filterVal = ['name', 'age']//对应list'name',获取对应'value'const data = formatJson(filterVal, list);const filename = "测试";toExportExcel(tHeader, data, filename);},}
}
</script>