在日常开发中，导入导出excel是非常常见的，特别是在一些政企类的项目中。那么下面将介绍几种常见的导入导出的方式，仅供参考
导出--方式一：vue + elementui 导出excel
下载依赖： npm install vue-json-excel -S
main.js
import Vue from 'vue'
import App from './App.vue'
import router from './router'
import store from './store'
// 第一种导出方法插件
import JsonExcel from 'vue-json-excel'
Vue.component('downloadExcel', JsonExcel)
//引入UI库
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';
Vue.use(ElementUI);
Vue.config.productionTip = false
new Vue({
router,
store,
render: h => h(App)
}).$mount('#app')
在页面中使用插件：
<template>
<div class="home">
<download-excel
class="export-excel-wrapper"
:data="DetailsForm"
:fields="json_fields"
:header="title"
name="表格名字.xls"
>
<!-- 上面可以自定义样式，也可以引用其他组件button -->
<el-button type="success">导出</el-button>
</download-excel>
</div>
</template>
<script>
export default {
name: "HomeView",
data() {
return {
title: "xxxxx表格",
json_fields: {
序号: "num",
姓名: "name",
年龄: "age",
爱好: "like",
毕业时间: "graduationTime",
所学专业: "specialized",
毕业学校: "graduationSchool",
身份证号: "idnum",
},
DetailsForm: [
{
num: "1",
name: "张三",
age: "20",
like: "唱跳rap",
graduationTime: "2023-06-06",
specialized: "计算机科学与技术",
graduationSchool: "xxx理工大学",
idnum: "111111222233334455",
},
{
num: "1",
name: "张三",
age: "20",
like: "唱跳rap",
graduationTime: "2023-06-06",
specialized: "计算机科学与技术",
graduationSchool: "xxx理工大学",
idnum: "111111222233334455",
},
{
num: "1",
name: "张三",
age: "20",
like: "唱跳rap",
graduationTime: "2023-06-06",
specialized: "计算机科学与技术",
graduationSchool: "xxx理工大学",
idnum: "111111222233334455",
},
{
num: "1",
name: "张三",
age: "20",
like: "唱跳rap",
graduationTime: "2023-06-06",
specialized: "计算机科学与技术",
graduationSchool: "xxx理工大学",
idnum: "111111222233334455",
},
{
num: "1",
name: "张三",
age: "20",
like: "唱跳rap",
graduationTime: "2023-06-06",
specialized: "计算机科学与技术",
graduationSchool: "xxx理工大学",
idnum: "111111222233334455",
},
],
};
},
};
</script>
导出--方式二
下载依赖并引入： npm install file-saver -S npm install xlsx -S
<template>
<div>
<!--导出按钮-->
<el-button type="primary" style="margin: 20px" @click="exportExcelSelect"
>导出Excel</el-button
>
<!--原始表格-->
<el-table :data="tableData" @selection-change="handleSelectionChange">
<el-table-column type="selection"> </el-table-column>
<el-table-column prop="date" label="日期"> </el-table-column>
<el-table-column prop="name" label="姓名"> </el-table-column>
<el-table-column label="详细地址">
<el-table-column prop="province" label="省份"> </el-table-column>
<el-table-column prop="city" label="市区"> </el-table-column>
<el-table-column prop="address" label="地址"> </el-table-column>
<el-table-column prop="email" label="邮编"> </el-table-column>
</el-table-column>
<el-table-column fixed="right" label="操作">
<template>
<el-button type="text" size="small">查看</el-button>
<el-button type="text" size="small">编辑</el-button>
</template>
</el-table-column>
</el-table>
<!--预览弹窗表格-->
<el-dialog title="表格保存预览" width="70%" :visible.sync="selectWindow">
<el-table :data="selectData" id="selectTable" height="380px">
<el-table-column prop="date" label="日期"> </el-table-column>
<el-table-column prop="name" label="姓名"> </el-table-column>
<el-table-column label="详细地址">
<el-table-column prop="province" label="省份"> </el-table-column>
<el-table-column prop="city" label="市区"> </el-table-column>
<el-table-column prop="address" label="地址"> </el-table-column>
<el-table-column prop="email" label="邮编"> </el-table-column>
</el-table-column>
</el-table>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="exportExcel">确定保存</el-button>
</div>
</el-dialog>
</div>
</template>
<script>
import FileSaver from "file-saver";
import XLSX from "xlsx";
export default {
data() {
return {
//表格数据
tableData: [
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
{
date: "2023-03-31",
name: "表格导出",
province: "北京",
city: "朝阳区",
address: "北京市朝阳区红海路123号",
email: 123456,
},
],
//表格中选中的数据
selectData: [],
selectWindow: false,
};
},
methods: {
//格式化数据
getExcel(dom, title = "默认标题") {
var excelTitle = title;
var wb = XLSX.utils.table_to_book(document.querySelector(dom));
/* 获取二进制字符串作为输出 */
var wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: true,
type: "array",
});
try {
FileSaver.saveAs(
new Blob([wbout], { type: "application/octet-stream" }),
excelTitle + ".xlsx"
);
} catch (e) {
if (typeof console !== "undefined") console.log(e, wbout);
}
return wbout;
},
//导出
exportExcel() {
this.getExcel("#selectTable", "导出的自定义标题");
},
//显示预览弹窗
exportExcelSelect() {
if (this.selectData.length < 1) {
this.$message.error("请选择要导出的内容！");
return false;
}
this.selectWindow = true;
},
//选中数据
handleSelectionChange(val) {
this.selectData = val;
},
},
};
</script>
导入--方式一：ElementUI文件上传进行表格导入
安装Excel表格解析插件： npm i xlsx -S
main.js
import Vue from 'vue'
import App from './App.vue'
import router from './router'
import store from './store'
// 第一种导出方法插件
import JsonExcel from 'vue-json-excel'
Vue.component('downloadExcel', JsonExcel)
//引入UI库
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';
Vue.use(ElementUI);
Vue.config.productionTip = false
new Vue({
router,
store,
render: h => h(App)
}).$mount('#app')
<template>
<div class="app">
<el-upload action="https://jsonplaceholder.typicode.com/posts/" :on-success="handleChange" :file-list="fileList"
class="el-upload">
<el-button size="small" type="primary" class="el-btn">点击上传</el-button>
<div slot="tip" class="el-upload-tip">
只能上传xlsx文件，且不超过5MB
</div>
</el-upload>
<el-table v-if="tableHead.length" :data="tableData[0]" style="width: 100%">
<el-table-column v-for="(data, key) in tableHead" :prop="data" :label="data" :key="key" width="180">
</el-table-column>
</el-table>
</div>
</template>
<script>
export default {
data() {
return {
fileList: [], //上传文件列表
tableHead: [], //表头
tableData: [] // 表数据
};
},
methods: {
// 导入成功时执行
handleChange(res, file, fileList) {
// 将文件放入
for (let i = 0; i < fileList.length; i++) {
if (file.name != fileList[i].name) {
this.fileList.push({
name: file.name,
url: "",
uid: file.uid
});
}
}
const files = { 0: file };
this.readExcel(files);
},
readExcel(file) {
const fileReader = new FileReader();
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = read(data, { type: "binary" });
const params = [];
// 取对应表生成json表格内容
workbook.SheetNames.forEach(item => {
this.tableData.push(utils.sheet_to_json(workbook.Sheets[item]));
});
// 该算法仅针对表头无合并的情况
if (this.tableData.length > 0) {
// 获取excel中第一个表格数据tableData[0][0]，并且将表头提取出来
for (const key in this.tableData[0][0]) {
this.tableHead.push(key);
}
}
// 重写数据
} catch (e) {
console.log("error:" + e);
return false;
}
};
fileReader.readAsBinaryString(file[0].raw);
}
}
}
</script>
导入--方式二：利用自带的input做文件上传
<template>
<div class="app">
<div class="flex-display">
<div class="left-box">文件上传(input)：</div>
<input type="file" v-on:change="onChange" class="file-ipt" />
</div>
</div>
</template>
<script>
import { read, utils } from "xlsx";
export default {
data() {
return {
fileList: [], //上传文件列表
tableHead: [], //表头
tableData: [] // 表数据
};
},
methods: {
onChange(e) {
const file = e.target.files[0];
const fileReader = new FileReader();
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = read(data, { type: "binary" });
const params = [];
// 取对应表生成json表格内容
workbook.SheetNames.forEach(item => {
params.push({
name: item,
dataList: utils.sheet_to_json(workbook.Sheets[item])
});
this.tableData.push(utils.sheet_to_json(workbook.Sheets[item]));
});
// 该算法仅针对表头无合并的情况
if (this.tableData.length > 0) {
// 获取excel中第一个表格数据tableData[0][0]，并且将表头提取出来
for (const key in this.tableData[0][0]) {
this.tableHead.push(key);
}
}
return params;
// 重写数据
} catch (e) {
console.log("error:" + e);
return false;
}
};
fileReader.readAsBinaryString(file);
},
}
}
</script>
以上两种导入方式完整代码：
<template>
<div class="app">
<el-upload action="https://jsonplaceholder.typicode.com/posts/" :on-success="handleChange" :file-list="fileList"
class="el-upload">
<el-button size="small" type="primary" class="el-btn">点击上传</el-button>
<div slot="tip" class="el-upload-tip">
只能上传xlsx文件，且不超过5MB
</div>
</el-upload>
<el-table v-if="tableHead.length" :data="tableData[0]" style="width: 100%">
<el-table-column v-for="(data, key) in tableHead" :prop="data" :label="data" :key="key" width="180">
</el-table-column>
</el-table>
<div class="flex-display">
<div class="left-box">文件上传(input)：</div>
<input type="file" v-on:change="onChange" class="file-ipt" />
</div>
</div>
</template>
<script>
import { read, utils } from "xlsx";
export default {
data() {
return {
fileList: [], //上传文件列表
tableHead: [], //表头
tableData: [] // 表数据
};
},
methods: {
onChange(e) {
const file = e.target.files[0];
const fileReader = new FileReader();
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = read(data, { type: "binary" });
const params = [];
// 取对应表生成json表格内容
workbook.SheetNames.forEach(item => {
params.push({
name: item,
dataList: utils.sheet_to_json(workbook.Sheets[item])
});
this.tableData.push(utils.sheet_to_json(workbook.Sheets[item]));
});
// 该算法仅针对表头无合并的情况
if (this.tableData.length > 0) {
// 获取excel中第一个表格数据tableData[0][0]，并且将表头提取出来
for (const key in this.tableData[0][0]) {
this.tableHead.push(key);
}
}
return params;
// 重写数据
} catch (e) {
console.log("error:" + e);
return false;
}
};
fileReader.readAsBinaryString(file);
},
// 导入成功时执行
handleChange(res, file, fileList) {
// 将文件放入
for (let i = 0; i < fileList.length; i++) {
if (file.name != fileList[i].name) {
this.fileList.push({
name: file.name,
url: "",
uid: file.uid
});
}
}
const files = { 0: file };
this.readExcel(files);
},
readExcel(file) {
const fileReader = new FileReader();
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = read(data, { type: "binary" });
const params = [];
// 取对应表生成json表格内容
workbook.SheetNames.forEach(item => {
this.tableData.push(utils.sheet_to_json(workbook.Sheets[item]));
});
// 该算法仅针对表头无合并的情况
if (this.tableData.length > 0) {
// 获取excel中第一个表格数据tableData[0][0]，并且将表头提取出来
for (const key in this.tableData[0][0]) {
this.tableHead.push(key);
}
}
// 重写数据
} catch (e) {
console.log("error:" + e);
return false;
}
};
fileReader.readAsBinaryString(file[0].raw);
}
}
}
</script>
导入导出方式三：利用vxe-table 实现导入导出
这里具体可参考官方文档：vxetable.cn/v3/#/table/…
