一、js-xlsx简介
用于各种电子表格格式的Arser和Writer。纯JS洁净室实现，来自官方规范、相关文档和测试文件。强调分析和编写健壮性、与统一JS表示的跨格式功能兼容性，以及与IE6的ES3/ES5浏览器兼容性。
组件：js-xlsx
https://www.npmjs.com/package/xlsx
二、组件安装
npm install xlsx --save
npm install -S file-saver
npm install -D script-loader
三、导入 Excel
- 新建文件importExcel.vue
<template>
<span style="margin-right:10px">
<input
class="input-file"
type="file"
@change="exportData"
accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
/>
<el-button type="primary" size="mini" icon="el-icon-upload" @click="btnClick">上传Excel表格数据</el-button>
</span>
</template>
<script>
// 引入组件
import XLSX from "xlsx";
export default {
data() {
return {};
},
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() {
// 读取成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
wb = XLSX.read(binary, {
type: "binary"
});
outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
// 自定义方法向父组件传递数据
that.$emit("getResult", outdata);
};
reader.readAsArrayBuffer(f);
};
reader.readAsBinaryString(f);
}
}
};
</script>
<style>
.input-file {
display: none;
}
</style>
- 组件调用Excel.vue
<template>
<import-excel @getResult="getMyExcelData" />
</template>
<script>
import importExcel from "@/components/excel/importExcel";
export default {
data() {
return {};
},
components: {
importExcel
},
methods: {
getMyExcelData(data) {
// data 为读取的excel数据，在这里进行处理该数据
console.log(data);
}
}
};
</script>
-
测试结果
excel
导入结果
四、导出Excel
- 新建exportExcel.vue文件
<template>
<el-button size="middle" type="primary" v-on:click="downloadMater()">下载Excel</el-button>
</template>
<script>
import XLSX from "xlsx";
import FileSaver from "file-saver";
export default {
data() {
return {
exportList: [
{
name: "韩版设计时尚风衣大",
number: "MPM00112",
salePrice: "￥999.00",
stocknums: 3423,
salesnums: 3423,
sharenums: 3423
},
{
name: "韩版设计时尚风衣大",
number: "MPM00112",
salePrice: "￥999.00",
stocknums: 3423,
salesnums: 3423,
sharenums: 3423
}
]
};
},
methods: {
downloadMater() {
const defaultCellStyle = {
font: { name: "Verdana", sz: 11, color: "FF00FF88" },
fill: { fgColor: { rgb: "FFFFAA00" } }
};
const wopts = {
bookType: "xlsx",
bookSST: false,
type: "binary",
defaultCellStyle: defaultCellStyle,
showGridLines: false
};
const wb = { SheetNames: ["Sheet1"], Sheets: {}, Props: {} };
let data = this.exportList;
wb.Sheets["Sheet1"] = XLSX.utils.json_to_sheet(data);
//创建二进制对象写入转换好的字节流
let tmpDown = new Blob([this.s2ab(XLSX.write(wb, wopts))], {
type: "application/octet-stream"
});
// 保存文件
FileSaver.saveAs(tmpDown, "hello world.xls");
},
// 字符串转字符流
s2ab(s) {
if (typeof ArrayBuffer !== "undefined") {
let buf = new ArrayBuffer(s.length);
let view = new Uint8Array(buf);
for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
return buf;
} else {
let buf = new Array(s.length);
for (let j = 0; j != s.length; ++j) buf[j] = s.charCodeAt(j) & 0xff;
return buf;
}
}
}
};
</script>
-
测试结果
测试结果
excel文件
