前端需要导入表格进行数据匹配,并进行表格导出,感谢很多大佬的文章分享,有不对的地方欢迎大家指正。
Element + xlsx
安装命令 npm install xlsx
页面引入 import XLSX from 'xlsx'
一、excel表格导入到表格
<template>
<div class="main">
<div class="main_title">工具</div>
<div class="main_content main_bg">
<div class="table_title" >
<div class="fl left_tbl">
<div>
<el-upload
class="fl upload-demo" action="" ref="upload" accept=".xls,.xlsx"
:file-list="fileList" :on-change="handleChange" :show-file-list="false" :auto-upload="false">
<el-button type="success">导入数据</el-button>
</el-upload>
<div class="fl filename" >{{formName}}</div>
<div class="cf"></div>
</div>
<div class="left_tbl_1">
<el-table :data="tableData" border style="width: 100%" class="tb-edit" height="450" @cell-click="clickCell" :row-class-name="rowClass">
<el-table-column label="序号" type="index" width="50" v-if="tableData.length>0" align="center"></el-table-column>
<template v-for="(item,index) in dataName" v-if="tableData.length>0" align="center">
<el-table-column :label="item" :prop="item" align="center" :key="index"></el-table-column>
</template>
</el-table>
</div>
</div>
</div>
</div>
</div>
</template>
<script>
import XLSX from 'xlsx'
export default {
data(){
return {
// 表格
fileList:[],
tableData: [],
dataName:[],//表格header
formName:'',//导入的表格名
}
},
created(){
},
methods:{
// 导入表格数据
handleChange(file) {
const _this = this
_this.tableData = []
_this.fileName = file.name;
_this.formName = file.name;
const reader = new FileReader();
//提取excel中文件内容
reader.readAsArrayBuffer(file.raw);
reader.onload = function () {
const buffer = reader.result;
const bytes = new Uint8Array(buffer);
const length = bytes.byteLength;
let binary = "";
for (let i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
const XLSX = require("xlsx");
const wb = XLSX.read(binary, {
type: "binary",
});
const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
outdata.forEach((i) => {
_this.tableData.push(i);
});
_this.dataName = Object.keys(_this.tableData[0]);//获取抬头
};
},
}
</script>
<style >
</style>
二、表格数据导出成excel
此处参考Vue中如何进行数据导出与Excel导出?_vue导出excel文件-CSDN博客;
中间一定更要记得引xlsx;
这边的数据是固定的,如果想改成动态的,可以自己写循环替代map方法;
<template>
<div>
<button @click="exportData">导出数据</button>
</div>
</template>
<script>
import XLSX from 'xlsx'
export default {
data() {
return {
tableData: [
{ name: '张三', age: 18, gender: '男' },
{ name: '李四', age: 20, gender: '女' },
{ name: '王五', age: 22, gender: '男' }
]
}
},
methods: {
exportData() {
const XLSX = require("xlsx");
const headers = ['姓名', '年龄', '性别']
const data = this.tableData.map(item => [item.name, item.age, item.gender])
const worksheet = XLSX.utils.aoa_to_sheet([headers, ...data])
const workbook = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
XLSX.writeFile(workbook, 'data.xlsx')
}
}
}
</script>