1、下载
npm i xlsx --save npm i file-saver --save
2、引入
import FileSaver from 'file-saver'; import XLSX from 'xlsx';
3、使用
**方法一：**
导入：
<template>
<div class="changeElBoCoSt kingChangeHead">
<el-upload action="" :auto-upload="false" :on-change="onChange" :limit="1"><el-button size="mini" type="success">上传文件</el-button></el-upload>
</div>
</template>
<script>
import XLSX from 'xlsx';
export default {
data() {
return {
};
},
methods: {
/* 读取文件 */
readFile(file) {
return new Promise(resolve => {
let reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = ev => {
resolve(ev.target.result);
};
});
},
async onChange(file) {
let dataBinary = await this.readFile(file.raw);
let workBook = XLSX.read(dataBinary, { type: 'binary', cellDates: true });
let workSheet = workBook.Sheets[workBook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(workSheet);
console.log(data);
}
}
};
</script>
<style lang="scss"></style>
**导出：**
<template>
<div class="changeElBoCoSt kingChangeHead">
<el-button @click="output">导出了</el-button>
<el-table id="mytable" :data="tableData1" style="width: 100%">
<el-table-column prop="date" label="日期" width="180"></el-table-column>
<el-table-column prop="name" label="姓名" width="180"></el-table-column>
<el-table-column prop="address" label="地址"></el-table-column>
</el-table>
</div>
</template>
<script>
import FileSaver from 'file-saver';
import XLSX from 'xlsx';
export default {
data() {
return {
tableData1: [
{
date: '2016-05-02',
name: '王小虎',
address: '上海市普陀区金沙江路 1518 弄'
},
{
date: '2016-05-04',
name: '王小虎',
address: '上海市普陀区金沙江路 1517 弄'
},
{
date: '2016-05-01',
name: '王小虎',
address: '上海市普陀区金沙江路 1519 弄'
},
{
date: '2016-05-03',
name: '王小虎',
address: '上海市普陀区金沙江路 1516 弄'
}
]
};
},
methods: {
output() {
// alert(1);
let wb = XLSX.utils.table_to_book(document.querySelector('#mytable')); //mytable为表格的id名
let wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: true,
type: 'array'
});
try {
FileSaver.saveAs(new Blob([wbout], { type: 'application/octet-stream' }), 'sheet.xlsx');
} catch (e) {
if (typeof console !== 'undefined') console.log(e, wbout);
}
return wbout;
}
}
};
</script>
<style lang="scss"></style>
**方法二：(此方法来自于作者：https://juejin.cn/post/6844903944397783054)**
步骤一：首先建立excel.js
import XLSX from 'xlsx';
/**
* @description:
* @param {Object} json 服务端发过来的数据
* @param {String} name 导出Excel文件名字
* @return:
*/
function exportExcel(json, name) {
/* convert state to workbook */
var data = new Array();
var keyArray = new Array();
for (const key1 in json) {
if (json.hasOwnProperty(key1)) {
const element = json[key1];
var rowDataArray = new Array();
for (const key2 in element) {
if (element.hasOwnProperty(key2)) {
const element2 = element[key2];
rowDataArray.push(element2);
if (keyArray.length < getLength(element)) {
keyArray.push(key2);
}
}
}
data.push(rowDataArray);
}
}
data.splice(0, 0, keyArray);
const ws = XLSX.utils.aoa_to_sheet(data);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "SheetJS");
/* generate file and send to client */
XLSX.writeFile(wb, name + ".xlsx");
}
/**
* @description: 导入excel文件并返回数据
* @param {function} 回调函数参数data,dataRef,一个是数据，一个是exce表单的索引
* @return:
*/
function importExcel(callback) {
var inputObj = document.createElement('input')
inputObj.setAttribute('id', 'file');
inputObj.setAttribute('type', 'file');
inputObj.setAttribute('name', 'file');
inputObj.setAttribute("style", 'visibility:hidden');
inputObj.setAttribute("accept", ".xlsx,.xls,.csv")
inputObj.addEventListener('change', (evt) => {
const files = evt.target.files;
if (files && files[0]) _file(files[0], (data, dataRef) => {
callback(data, dataRef)
});
})
document.body.appendChild(inputObj);
inputObj.value;
inputObj.click();
}
/**
* @description: 处理文件
* @param {Object} file 文件对象
* @param {function} callback 回调函数
* @return:
*/
function _file(file, callback) {
const make_cols = refstr => Array(XLSX.utils.decode_range(refstr).e.c + 1).fill(0).map((x, i) => ({
name: XLSX.utils.encode_col(i),
key: i
}));
/* Boilerplate to set up FileReader */
const reader = new FileReader();
reader.onload = (e) => {
/* Parse data */
const bstr = e.target.result;
const wb = XLSX.read(bstr, {
type: 'binary'
});
/* Get first worksheet */
const wsname = wb.SheetNames[0];
const ws = wb.Sheets[wsname];
/* Convert array of arrays */
const data = XLSX.utils.sheet_to_json(ws, {
header: 1
});
/* Update state */
callback(data, make_cols(ws['!ref']))
};
reader.readAsBinaryString(file);
}
/**
* @description: 获取map的长度
* @param {Object} obj map对象
* @return: map的长度
*/
function getLength(obj) {
var count = 0;
for (var i in obj) {
if (obj.hasOwnProperty(i)) {
count++;
}
}
return count;
}
export default {
exportExcel,
importExcel
}
步骤二：
实现：
<template>
<div class="changeElBoCoSt kingChangeHead">
<el-button @click="exportExcel">导出了</el-button>
<div class="changeElBoCoSt kingChangeHead"><el-button @click="importFile">上传文件</el-button></div>
</div>
</template>
<script>
import Excel from './excel.js';
export default {
data() {
return {
tableData1: [
{
date: '2016-05-02',
name: '王小虎',
address: '上海市普陀区金沙江路 1518 弄'
},
{
date: '2016-05-04',
name: '王小虎',
address: '上海市普陀区金沙江路 1517 弄'
},
{
date: '2016-05-01',
name: '王小虎',
address: '上海市普陀区金沙江路 1519 弄'
},
{
date: '2016-05-03',
name: '王小虎',
address: '上海市普陀区金沙江路 1516 弄'
}
]
};
},
methods: {
exportExcel() {
Excel.exportExcel(this.tableData1, '文件');
},
importFile() {
Excel.importExcel((data, dataRef) => {
alert('数据已经打印在控制台');
console.log(data);
console.log(dataRef);
});
}
}
};
</script>
<style lang="scss"></style>
