一、创建一个VUE项目
项目环境版本:
npm 8.5.5
node 16.15.0
vue-cli 4
引入相关的依赖
npm install --save
引入Element-plus和XLSX和File-saver相关的依赖
npm install element-plus
npm install xlsx
npm install file-saver
查看package.json
{
"name": "xlsx-demo",
"private": true,
"version": "0.0.0",
"scripts": {
"dev": "vite",
"build": "vite build",
"preview": "vite preview"
},
"dependencies": {
"element-plus": "^2.2.12",
"file-saver": "^2.0.2",
"vue": "^3.2.25",
"xlsx": "^0.16.0"
},
"devDependencies": {
"@vitejs/plugin-vue": "^2.3.3",
"vite": "^2.9.9"
}
}
二、写一个table表格文件 并为表格填充对应的值
<template>
<div>
<el-table :data="tableData" border style="width: 100%">
<el-table-column type="index" label="序号" width="180" />
<el-table-column prop="name" label="姓名" width="180" />
<el-table-column prop="age" label="年龄" width="180" />
<el-table-column prop="address" label="住址" />
</el-table>
</div>
</template>
<script setup>
import { reactive, ref } from "vue";
let tableData = reactive([
{
name: "张三",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三222",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三333",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三444",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三555",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三666",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三777",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三888",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三999",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
{
name: "张三000",
age: 18,
address: "贵州贵阳云岩区XXX路街道",
},
]);
</script>
<style></style>
在APP中引入组件
<template>
<div style="width: 1200px; margin: 50px auto">
<el-button type="success">导出</el-button>
<Table />
</div>
</template>
<script setup>
import Table from "./components/Table.vue";
</script>
<style></style>
示例图:
三、完成导出导入的业务逻辑
<template>
<div style="width: 1200px; margin: 50px auto">
<el-button type="success" @click="exportClick">导出</el-button>
<Table />
</div>
</template>
<script setup>
import Table from "./components/Table.vue";
//引入XLSX
import FileSaver from "file-saver";
import XLSX from "xlsx";
import { ElMessage } from "element-plus";
const exportClick = () => {
//时候按照表格的原样导出 不对表格数据进行处理
let xlsxParams = { row: true };
//根据表格导出 可以根据json数据导出 json_to_sheet
let wb = XLSX.utils.table_to_book(
document.querySelector("#table"),
xlsxParams
);
//写入
let wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: true,
type: "array",
});
try {
FileSaver.saveAs(
new Blob([wbout], { type: "application/octet-stream" }),
"数据导出.xlsx"
);
} catch (e) {
if (typeof consloe !== undefined) {
ElMessage.error({
message: e.wbout,
type: "error",
});
}
}
return wbout;
};
</script>
数据导出完成 示意图如下:
四、数据导入功能
需要使用上传组件 需要引入icon字体图片
npm install @element-plus/icons-vue
编写导入功能代码
<template>
<div style="width: 1200px; margin: 50px auto">
<el-button type="success" @click="exportClick">导出</el-button>
<el-button type="primary" @click="importClick">导入</el-button>
<Table />
<!-- 数据上传的功能 -->
<el-dialog v-model="dialogFormVisible" title="数据导入">
<el-upload
class="upload-demo"
drag
action="/"
:auto-upload="false"
:on-change="handleSuccess"
>
<el-icon class="el-icon--upload"><upload-filled /></el-icon>
<div class="el-upload__text">将文件点击或者拖拽 <em>上传</em></div>
</el-upload>
</el-dialog>
</div>
</template>
<script setup>
import { reactive, ref } from "vue";
import Table from "./components/Table.vue";
//引入XLSX
import FileSaver from "file-saver";
import XLSX from "xlsx";
import { ElMessage } from "element-plus";
import { UploadFilled } from "@element-plus/icons-vue";
//数据导出
const exportClick = () => {
//时候按照表格的原样导出 不对表格数据进行处理
let xlsxParams = { row: true };
//根据表格导出 可以根据json数据导出 json_to_sheet
let wb = XLSX.utils.table_to_book(
document.querySelector("#table"),
xlsxParams
);
//写入
let wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: true,
type: "array",
});
try {
FileSaver.saveAs(
new Blob([wbout], { type: "application/octet-stream" }),
"数据导出.xlsx"
);
} catch (e) {
if (typeof consloe !== undefined) {
ElMessage.error({
message: e.wbout,
type: "error",
});
}
}
return wbout;
};
//数据导入
let dialogFormVisible = ref(false);
const importClick = () => {
dialogFormVisible.value = !dialogFormVisible.value;
};
//文件上传成功
let tabelData = reactive([]);
let fileData = ref("");
function handleSuccess(file) {
fileData = file;
readExcel();
}
function readExcel() {
const files = fileData;
if (!files) {
// 没有文件
return false;
} else if (!/\.(xls|xlsx)$/.test(files.name.toLowerCase())) {
Error("请上传xls或者xlsx文件");
return false;
}
const fileReader = new FileReader();
fileReader.onload = (e) => {
try {
const data = e.target.result;
const workbook = XLSX.read(data, {
type: "binary",
});
if (workbook.SheetNames.length >= 1) {
ElMessage.success("导入成功");
}
// 取第一张表
const wsname = workbook.SheetNames[0];
// 生成json表格内容
const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]);
console.log(ws);
batcnUserList.length = 0;
batchLength.value = ws.length;
ws.map((item) => {
batcnUserList.push(item);
});
} catch (e) {
return false;
}
};
fileReader.readAsBinaryString(files.raw);
}
</script>
导入功能完成 示例图如下