本例子实现了纯前端导出excel的功能，由于表格的列太多，我是从后端拿到fieldsData再展示出该用户所需要的列，导出的时候是先选择复选框，然后把选中的数据导出到表格里（只导出该用户能看到的列） 例子还有另外一个没有用到的导出代码，该代码导出的表格数据是不带样式的
1,安装依赖，我的node版本是16.20.2
npm install --save xlsx-style //导出excel后表格的样式需要用到这个库，我的版本是："xlsx": "^0.18.5",
npm install --save xlsx //声明工作簿，创建文件，塞入数据要用到这个库，我的版本是："xlsx-style": "^0.8.13"
2，在局部引用依赖，代码如下
<template>
<div class="out-body">
<div class="inner-body">
<div class="tool">
<el-button size="mini" icon="el-icon-download" @click="bulkExport">导出</el-button>
</div>
<el-table
:data="dataSource"
style="width: 100%"
@selection-change="handleSelectionChange"
>
<el-table-column type="selection"/>
<el-table-column
v-for="(item,index) in fieldsData"
:prop="item.prop"
:label="item.fieldCn"
:key="index"
width="180">
</el-table-column>
</el-table>
</div>
</div>
</template>
<script>
import * as XLSX from "xlsx";
import XLSXStyle from 'xlsx-style'
import request from "@/utils/request.js"
const titleStyle = {
font: {
name: '黑体', sz: 18, bold: false,
color: {
rgb: '000000'
}
},
alignment: {horizontal: 'center', vertical: 'center', wrapText: false}
}
const headerStyle = {
font: {
name: '宋体', sz: 11, bold: false,
color: {
rgb: '000000'
}
},
alignment: {horizontal: 'center', vertical: 'center', wrapText: false},
border: {
top: {style: 'thin'},
bottom: {style: 'thin'},
left: {style: 'thin'},
right: {style: 'thin'}
}
// fill: {
// fgColor: { rgb: 'ebebeb' }// 设置标题单元格的背景颜色
// }
}
export default {
name: 'test',
data() {
return {
dataSource: [],
fieldsData: [],
selectData: [],
queryParams:{},
}
},
methods: {
handleSelectionChange(val) {
this.selectData = val
},
testEvent() {
if (this.selectData.length <= 0) {
this.$message.warning('请选择数据！');
return
}
let tableData = [
this.fieldsData.map(ele => ele.fieldCn),
] // 表格表头
this.selectData.forEach((item, index) => {
let rowData = []
//导出内容的字段，对内容做格式化处理，如金额格式化千分位和日期格式化
rowData = this.fieldsData.map(ele => {
if (ele.fieldFmType == 1) {
return formatMoney(item[ele.fieldEn])
} else if (ele.fieldFmType == 2) {
return parseIntDate(item[ele.fieldEn])
} else {
return item[ele.fieldEn]
}
})
tableData.push(rowData)
})
let workSheet = XLSX.utils.aoa_to_sheet(tableData);
// // 通过工作表的每一列数据计算宽度
// const columnWidths = tableData[0].map((_col, colIndex) => {
// const column = tableData.map((row) => row[colIndex]);
// return Math.max(...column.map((cell) => ('' + cell).length)) * 1.2; // 1.2是一个调整系数，可以根据实际情况调整
// });
// // 设置工作表的列宽
// workSheet['!cols'] = columnWidths.map((width) => ({ wch: width }));
this.format2(workSheet)
let bookNew = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(bookNew, workSheet, 'test信息') // 工作簿名称
XLSXStyle.write(bookNew, {bookType: '', bookSST: false, type: 'binary'})
let name = 'test信息表' + this.timeFormat() + '.xlsx'
XLSX.writeFile(bookNew, name) // 保存的文件名
},
format2(sheet) {
for (const key in sheet) {
const k1 = (Number(key.slice(1)))
console.log('打印key', k1)
if (k1 == 1) { //设置第一行【表头】样式
sheet[key].s = this.titleStyle; //<====设置xlsx单元格样式
}
}
},
timeFormat() {
let time = new Date();
let year = time.getFullYear();
let month = time.getMonth() + 1;
let date = time.getDate();
let hours = time.getHours();
let minutes = time.getMinutes();
let seconds = time.getSeconds();
return year + '-' + this.addZero(month) + '-' + this.addZero(date) + ' ' + this.addZero(hours) + ':' + this.addZero(minutes) + ':' + this.addZero(seconds);
},
addZero(num) {
return num < 10 ? '0' + num : num
},
async getDataSource() {
this.loading = true;
request({url: '/test', method: 'get', params: this.queryParams}).then((res) => {
this.dataSource = res.data
}).finally(() => {
this.loading = false
})
},
// 导出主方法（点击事件绑定此方法）
async bulkExport() {
if (this.selectData.length <= 0) {
this.$message.warning('请选择数据！');
return
}
const loadingInstance = trustLoading.service({text: '导出数据中，请稍等~', gif: 'download'})
await this.$nextTick()
setTimeout(()=>{
try {
let tableData = [
this.fieldsData.map(ele => ele.fieldCn), //fieldCn 是
] // 表格表头
this.selectData.forEach((item, index) => {
let rowData = []
//导出内容的字段
rowData = this.fieldsData.map(ele => {
const d1 = item[ele.fieldEn]
if (d1 === null || d1 === undefined) {
return ''
} else {
if (ele.fieldFmType == 1) {
return this.helpers.formatMoney(item[ele.fieldEn])
} else if (ele.fieldFmType == 2) {
return this.helpers.parseIntDate(item[ele.fieldEn])
} else {
return item[ele.fieldEn]
}
}
})
tableData.push(rowData)
})
var sheet = XLSX.utils.json_to_sheet(tableData, {
skipHeader: true,
});
for (const key in sheet) {
// 所有单元格居中
if (key.indexOf("!") === -1) {
sheet[key].s = this.headerStyle
}
// 表头加颜色加边框
if (key.replace(/[^0-9]/ig, '') === '1') {
sheet[key].s = {
fill: { //背景色
fgColor: {rgb: 'C0C0C0'}
},
font: {//字体
name: '宋体',
sz: 12,
bold: true
},
border: {//边框
bottom: {
style: 'thin',
color: 'FF000000'
},
top: {
style: 'thin',
},
left: {
style: 'thin',
},
right: {
style: 'thin',
},
},
alignment: {
horizontal: 'center' //水平居中
}
}
}
// 通过工作表的每一列数据计算宽度
const columnWidths = tableData[0].map((_col, colIndex) => {
const column = tableData.map((row) => row[colIndex]);
return Math.max(...column.map((cell) => ('' + cell).length)) * 1.6; // 1.2是一个调整系数，可以根据实际情况调整
});
// 设置工作表的列宽
sheet['!cols'] = columnWidths.map((width) => ({wch: width}));
}
// 根据业务需求修改sheet名称和excle导出的名称
this.openDownload(this.sheet2blob(sheet, 'test信息表'), 'test信息表.xlsx');
loadingInstance.close()
} catch (err) {
loadingInstance.close()
}
},500)
},
// 处理excle文件
sheet2blob(sheet, sheetName) {
let wb = XLSX.utils.book_new();
wb.SheetNames.push(sheetName)
wb.Sheets[sheetName] = sheet;
var wbout = XLSXStyle.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'})
var blob = new Blob([s2ab(wbout)], {type: ""}, sheetName);
// 字符串转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;
},
// 下载excle文件
openDownload(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);
},
},
created() {
this.getDataSource()
},
}
</script>
