1.下载依赖
npm install xlsx --save
npm install file-saver --save
npm install xlsx-style --save
2.先修改xlsx-style的源码，一旦引入xlsx-style则会报错
xlsx-style使用中常见问题及解决办法：
xlsx-style使用中常见问题及解决办法-CSDN博客
在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;
但是如果每次打包之前去修改包里的代码很麻烦所以我去node_modules包里找到了这个文件
把这个xlsx.full.min.js复制一份出来，粘贴到项目内，我是放到了public的index里面如图：
3.在页面使用，这里是使用element表格，用id绑定后，再用dom获取该节点，然后导出数据添加样式
<template>
<div>
<el-table :id="'mytable'" :data="tableData" style="width: 100%">
<el-table-column prop="date" label="日期" width="150"> </el-table-column>
<el-table-column label="配送信息">
<el-table-column prop="name" label="姓名" width="120">
</el-table-column>
<el-table-column label="地址">
<el-table-column prop="province" label="省份" width="120">
</el-table-column>
<el-table-column prop="city" label="市区" width="120">
</el-table-column>
<el-table-column prop="address" label="地址" width="300">
</el-table-column>
<el-table-column prop="zip" label="邮编" width="120">
</el-table-column>
</el-table-column>
</el-table-column>
</el-table>
<button @click="setExport2Excel">按钮</button>
</div>
</template>
<script>
import { utils } from "xlsx";
export default {
data() {
return {
tableData: [
{
date: "2016-05-03",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-02",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-04",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-01",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-08",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-06",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
{
date: "2016-05-07",
name: "王小虎",
province: "上海",
city: "普陀区",
address: "上海市普陀区金沙江路 1518 弄",
zip: 200333,
},
],
};
},
methods: {
setExport2Excel() {
var xlsxParam = { raw: true }; //这个保证表格只进行解析 不做运算
var wb = utils.table_to_sheet(
document.querySelector("#mytable"),
xlsxParam
);
for (var i = 0; i < 11; i++) {
wb["!cols"][i] = { wpx: 130 };
}
// 样式的文档地址
// https://www.npmjs.com/package/xlsx-style
for (const key in wb) {
if (key.indexOf("!") === -1 && wb[key].v) {
wb[key].s = {
font: {
//字体设置
sz: 13,
bold: false,
color: {
rgb: "000000", //十六进制，不带#
},
},
alignment: {
//文字居中
horizontal: "center",
vertical: "center",
wrap_text: true,
},
border: {
// 设置边框
top: { style: "thin" },
bottom: { style: "thin" },
left: { style: "thin" },
right: { style: "thin" },
},
};
}
}
var data = this.addRangeBorder(wb["!merges"], wb) //合并项添加边框
var filedata = this.sheet2blob(data)
this.openDownloadDialog(filedata,this.todayTimeString + "-xxx报表.xlsx")
},
//为合并项添加边框
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",
];
if (range) {
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;
},
//将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
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;
},
openDownloadDialog(url, saveName) {
if (typeof url == "object" && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement("a");
aLink.href = url;
aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
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);
},
},
};
</script>
