一、导入Excel文件
上传excel文件，并获取表格数据；以下有两种方式：①js方式，写在子组件中，引入本页。②结合element-ui中的el-upload，在本页中编写
方法一：js方法
编辑一个子组件，然后只需要在父组件中引入即可使用；
父组件
<template>
<div class="upload-excels">
<h4>①js方式，写在子组件中，引入本页。</h4>
<excel1 v-on:getResult="updateExcel"></excel1>
</div>
</template>
<script>
import excel1 from "@/view/components/upload-excel";
export default {
name:'',
data () {
return {};
},
components: {excel1},
mounted() {},
methods: {
// 导入表格--start
updateExcel(e) {
console.log(e); // 打印出的为导入Excel表格的json数据
},
// 导入表格--end
}
}
</script>
子组件
<template>
<div>
<span>
<input
class="input-file"
type="file"
@change="exportData"
accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
/>
<!-- <button @click="btnClick">导入EXCEL</button> -->
</span>
</div>
</template>
<script>
export default {
name: "upload-excel",
data() {
return {};
},
components: {},
mounted() {},
methods: {
btnClick() {
document.querySelector(".input-file").click();
//
},
exportData(event) {
if (!event.currentTarget.files.length) {
return;
}
const that = this;
// 拿取文件对象
var f = event.currentTarget.files[0];
// 用FileReader来读取
var reader = new FileReader();
// 重写FileReader上的readAsBinaryString方法
FileReader.prototype.readAsBinaryString = function(f) {
var binary = "";
var wb; // 读取完成的数据
var outdata; // 你需要的数据
var reader = new FileReader();
reader.onload = function(e) {
// 读取成Uint8Array，再转换为Unicode编码（Unicode占两个字节）
var bytes = new Uint8Array(reader.result);
var length = bytes.byteLength;
for (var i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
// 接下来就是xlsx了，具体可看api
var XLSX = require("xlsx");
wb = XLSX.read(binary, {
type: "binary"
});
outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
// 自定义方法向父组件传递数据
// console.log('outdata = ' + JSON.stringify(outdata))
console.log(outdata);
that.$emit("getResult", outdata);
};
reader.readAsArrayBuffer(f);
};
reader.readAsBinaryString(f);
}
},
watch: {}
};
</script>
<style lang='' scoped>
</style>
方法二：与element-ui中的upload相结合
代码如下：
<template>
<div class="upload-excels">
<h3>上传excel文件，并获取表格数据；以下有两种方式：①js方式，写在子组件中，引入本页。②结合element-ui中的el-upload，在本页中编写</h3><br><br>
<h4>②结合element-ui中的el-upload，在本页中编写</h4>
<div class="excel2">
<el-upload
class="upload-demo"
action
:on-change="handleChange"
:show-file-list="true"
:on-remove="handleRemove"
:file-list="fileListUpload"
:limit="1"
accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
:auto-upload="false"
>
<el-button size="small" type="primary">选择文件</el-button>
<div slot="tip" class="el-upload__tip">注：只 能 上 传 xlsx / xls 文 件</div>
</el-upload>
</div>
</div>
</template>
<script>
export default {
name:'',
data () {
return {
fileListUpload:[],
tableData2: []
};
},
components: {},
mounted() {},
methods: {
// 导入表格-el-upload--start
// excel表上传
handleChange(file, fileList) {
this.fileTemp = file.raw;
// 判断上传文件格式
if (this.fileTemp) {
if (
this.fileTemp.type ==
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
this.fileTemp.type == "application/vnd.ms-excel"
) {
this.importfxx(this.fileTemp);
} else {
this.$message({
type: "warning",
message: "附件格式错误，请删除后重新上传！"
});
}
} else {
this.$message({
type: "warning",
message: "请上传附件！"
});
}
},
// 移除excel表
handleRemove(file, fileList) {
this.fileTemp = null;
},
importfxx(obj) {
let _this = this;
// 通过DOM取文件数据
this.file = obj;
var rABS = false; //是否将文件读取为二进制字符串
var f = this.file;
var reader = new FileReader();
FileReader.prototype.readAsBinaryString = function(f) {
var binary = "";
var rABS = false; //是否将文件读取为二进制字符串
var pt = this;
var wb; //读取完成的数据
var outdata;
var reader = new FileReader();
reader.onload = function(e) {
var bytes = new Uint8Array(reader.result);
var length = bytes.byteLength;
for (var i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
var XLSX = require("xlsx");
if (rABS) {
wb = XLSX.read(btoa(fixdata(binary)), {
//手动转化
type: "base64"
});
} else {
wb = XLSX.read(binary, {
type: "binary"
});
}
outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]); //outdata就是读取excel内容之后输出的东西
console.log(outdata);
this.da = [...outdata];
let arr = [];
this.da.map(v => {
let obj = {};
obj.ip = v["ip"]; //ip是表的标题
arr.push(obj);
});
//return arr
// 把读取的excel表格中的内容放进tableData2中(这里要改成自己的表的名字)
_this.tableData2 = _this.tableData2.concat(arr);
};
reader.readAsArrayBuffer(f);
};
if (rABS) {
reader.readAsArrayBuffer(f);
} else {
reader.readAsBinaryString(f);
}
},
// 导入表格-el-upload--end
},
watch: {}
}
</script>
<style lang='scss' scoped>
.upload-excels{
width:100%;
height:300px;
.excel2{
width:500px;
height:100px;
border:1px solid red;
}
}
</style>
二、导出Excel表格
1.安装依赖
cnpm install -S file-saver xlsx
cnpm install -D script-loader
2.依赖文件
需要安装依赖文件 Blob.js 与 Export2Excel.js ,在src中创建文件夹 vendor 把这两个文件放入其中，如下：(Blob.js与Export2Excel.js两个文件在文章最后附有)
3.代码如下
<template>
<button @click="getExcel(userList)">导出表格</button>
<button @click="outExe">导出模板</button>
</template>
<script>
export default {
name: "",
data() {
return {
userList: [{
userNum: 100001, //客户编号
userName: '张琳梵', // 客户名字
phone: 158xxxxxxxxx, // 电话
job: '餐饮', // 行业
address: '河南省郑州市中原区xxx号', // 地址
from: '手动录入', //来源
salesman: '李明', // 销售人员
createTime: '2018-09-25' // 入库时间
},
{
userNum: 100005,
userName: '张琳梵',
phone: 158xxxxxxxx,
job: '百货',
address: '河南省郑州市高新区xxx号',
from: '手动录入',
salesman: '李明',
createTime: '2018-09-25'
}]
};
},
components: {},
mounted() {},
methods:{
// 导出模板
outExe() {
require.ensure([], () => {
const { export_json_to_excel } = require("@/vendor/Export2Excel"); //引入文件
const tHeader = ["客户编号","客户名字","电话","行业","地址","来源","销售人员","入库时间"]; //将对应的属性名转换成中文
const data = [];
export_json_to_excel(tHeader, data, "模板");
});
},
// 导出表格
getExcel(res) {
require.ensure([], () => {
const { export_json_to_excel } = require("@/vendor/Export2Excel.js");
const tHeader = ["客户编号","客户名字","电话","行业","地址","来源","销售人员","入库时间"];
const filterVal = ["userNum","userName","phone","job","address","from","salesman","createTime"];
const list = res;
const data = this.formatJson(filterVal, list);
export_json_to_excel(tHeader, data, "导出表格名称");
});
},
formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => v[j]));
},
}
}
</script>
