vue3表格导入导出.xlsx

在这次使用时恰好整出来了，希望大家也能学习到，特此分享出来

使用前确保安装以下模块，最好全局配置element-plus

### 展示一下

###

###导出选项

###

###导入de数据

###

安装的模块

npm install js-table2excel // 安装js-table2excel
npm install xlsx // 安装xlsx
npm install dayjs // 安装dayjs
npm install axios // 安装axios

element-plus全局配置地址：快速开始 | Element Plus

依次根据官网步骤进行引入即可，不在过多介绍

接口文件配置，例如utlis/api.js

// api.js
import axios from 'axios'const api = axios.create({baseURL: 'http://localhost:3000', // 后端接口地址timeout: 5000,
})export default api;

vue页面的构局/前端

样式部分

<template><div><el-card class="box-card1"><div style="display: flex; align-items: center;margin: 15px;"><el-upload action="#" :show-file-list="false" :before-upload="importBefore" accept=".xls,.xlsx"style="margin: 0 12px;display: flex;align-items: center;"><el-button type="success" plain>表格导入</el-button></el-upload><el-button type="success" plain @click="userExport">导出表格</el-button></div><div style="margin-left:1.875rem; margin-top: 2.125rem;"><el-table :data="tableData" style="width:100%" ref="tableRef"><el-table-column type="selection" width="50" align="center" /><el-table-column width="100"><template #default="scope"><!-- {{scope.row.name}} --><template v-if="scope.row.level === '1'"><el-button type="danger" round size="small">重大</el-button></template><template v-else-if="scope.row.level === '2'"><el-button type="success" round size="small">非重大</el-button></template></template></el-table-column><el-table-column width="280"><template #default="scope"><div><h4>案件编码:{{ scope.row.anjbm }}</h4></div><div><span>案件名称:{{ scope.row.name }}</span></div></template></el-table-column><el-table-column width="280"><template #default="scope"><div><span>我方地位:{{ scope.row.mypos }}</span></div><div><span>提交时间:{{ dayjs(scope.row.date).format("YYYY-MM-DD hh:mm:ss") }}</span></div></template></el-table-column><el-table-column width="200"><template #default="scope"><div><span>案件类型:{{ scope.row.style }}</span></div><template v-if="scope.row.status === 1"><div><span>案件进展:进展中</span></div></template><template v-else-if="scope.row.status === 2"><div><span>案件进展:暂无进展</span></div></template><template v-else-if="scope.row.status === 3"><div><span>案件进展:已审理结案</span></div></template><!-- <div><span>状态:{{ scope.row.status == 1 ? '进展中' : scope.row.status == 2 ? '暂无进展' : '已审理结案' }}</span></div> --></template></el-table-column><el-table-column width="200"><template #default="scope"><el-progress :percentage="scope.row.schedule" /></template></el-table-column><el-table-column width="280"><template #default="scope"><div><template v-if="scope.row.flag === 1"><el-button size="small" link>新增执行</el-button><el-button size="small" text>修改执行</el-button><el-button size="small" text>执行结果</el-button></template><template v-else-if="scope.row.flag === 2"><el-button size="small" link>新增执行</el-button></template><template v-else-if="scope.row.flag === 3"></template></div></template></el-table-column></el-table></div></el-card>
</template>

功能部分

<script setup>
import { ElMessage, ElButton, ElLoading } from 'element-plus'
import table2Excel from 'js-table2excel'
import * as XLSX from "xlsx"
import api from '../utils/api'
import dayjs from "dayjs";
import { ref, onMounted, reactive, toRefs, h } from 'vue'
import axios from 'axios'
import { ElMessageBox } from 'element-plus'
const dialogVisible = ref(false)const state = reactive({tableData: [// {// level:'1',// anjbm:'A202311111009',// name:'某某酒驾撞人案件',// mypos:'被告',// date:'2023-11-19 09:42:09',// style:'劳动争议案件',// status:1// schedule:80// flag:1// }],  //模拟请求数据exportConfig: [  //导出Excel表格配置{title: '案件级别',key: 'level',type: 'text'},{title: '案件编码',key: 'anjbm',type: 'text'},{title: '案件名称',key: 'name',type: 'text'},{title: '我方地位',key: 'mypos',type: 'text'},{title: '提交时间',key: 'date',type: 'text'},{title: '案件类型',key: 'style',type: 'text'},{title: '案件进展',key: 'status',type: 'text'},{title: '案件进度',key: 'schedule',type: 'text'},{title: '功能区',key: 'flag',type: 'text'},//  图片配置// {//   title: '头像',//   key: 'imgs',//   type: 'image'// },],formatColumns: [ // 导出特殊字段处理{prop: 'status',option: {'1': '进展中','2': '暂无进展','3': '已审理结案'},},{prop: 'level',option: {'1': '重大','2': '非重大','3': '已结案'},},]
})
const { tableData, exportConfig, formatColumns } = toRefs(state)const tableRef = ref()// 表格导出
const userExport = () => {ElMessageBox({title: '导出Excel表格',draggable: true,showCancelButton: true,showConfirmButton: false,message: h('div', null, [ // 这里用到了h函数h(ElButton, { text: true, type: 'primary', innerHTML: '导出选中数据', onClick: assignExport }),h(ElButton, { text: true, type: 'success', innerHTML: '导出所有数据', onClick: allExport })]),cancelButtonText: '取消',}).then((res) => { }).catch((res) => { })
}// 选中数据导出
const assignExport = () => {// getSelectionRows  Element Plus table表格组件方法，获取当前选中的数据let arr = tableRef.value.getSelectionRows()if (!arr.length) {return ElMessage({message: '请选择需要导出的数据',type: 'warning',})}ElMessageBox.close() // 关闭弹出框const loading = ElLoading.service({ // 打开遮罩层lock: true,text: '请稍等...',background: 'rgba(255, 255, 255, 0.5)',})let list = JSON.stringify(tableRef.value.getSelectionRows())list = formatExportData(JSON.parse(list))table2Excel(state.exportConfig, list, '案件进展批量导出')loading.close() // 关闭遮罩层
}// 所有数据导出
const allExport = () => {ElMessageBox.close() // 关闭弹出框const loading = ElLoading.service({ // 打开遮罩层lock: true,text: '请稍等...',background: 'rgba(255, 255, 255, 0.5)',})let list = JSON.stringify(state.tableData) // 用定义的数据list = formatExportData(JSON.parse(list))table2Excel(state.exportConfig, list, '案件进展全部导出')loading.close() // 关闭遮罩层
}const formatExportData = (list) => {// 处理特殊字段list.forEach((item) => {state.formatColumns.forEach((i) => {item[i.prop] = i.option[item[i.prop]]})for (let key in item) {if (!item[key] && item[key] == null) {item[key] = ""}}});return list
}// 表格导入
const importBefore = (file) => {const reader = new FileReader();reader.onload = (e) => {const data = e.target.result;const workbook = XLSX.read(data, { type: "array" });const firstSheetName = workbook.SheetNames[0];const worksheet = workbook.Sheets[firstSheetName];const results = XLSX.utils.sheet_to_json(worksheet);importAdd(results)};reader.readAsArrayBuffer(file);
}const importAdd = (list) => {// 处理上传时excel中特殊字段list.forEach((item) => {state.exportConfig.forEach((i) => {item[i.key] = item[i.title]delete item[i.title]})for (let key in item) {if (key == "date") {item[key] = ExcelDateToJSDate(item[key])}}})list = convertImportData(list)// 调用后台接口进行批量添加api.post('/l/madd', list).then(response => {getdata()console.log('res', response.data);return response.data}).catch(error => {throw new Error(error)})
}// 处理日期时间
const ExcelDateToJSDate = (serial) => {// 原始的// var utc_days = Math.floor(serial - 25569);// var utc_value = utc_days * 86400;// var date_info = new Date(utc_value * 1000);// var fractional_day = serial - Math.floor(serial) + 0.0000001;// var total_seconds = Math.floor(86400 * fractional_day);// var seconds = total_seconds % 60;// total_seconds -= seconds;// var hours = Math.floor(total_seconds / (60 * 60));// var minutes = Math.floor(total_seconds / 60) % 60;// return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);// 更改后的，引入dayjs包后做的改进return dayjs(serial).format("YYYY-MM-DD hh:mm:ss")
}// 返回上传的Excel文件
const convertImportData = (list) => {console.log(list);list.forEach((item) => {state.formatColumns.forEach((i) => {for (let key in i.option) {if (item[i.prop] == i.option[key]) {item[i.prop] = key}}})for (let key in item) {if (!item[key] && item[key] == undefined) {item[key] = ""}}});return list
}// 渲染数据的vue3钩子函数,并不会刷新页面
const getdata = onMounted(async () => {const { data } = await axios.get('/l/case/show')state.tableData = data.data
})
</script>

样式自己搭建即可，不再演示辣

vue后端接口的布局

之前已经讲解过数据库的搭建，mongoose搭建步骤 <-----查看创建步骤

也可根据mongoose官网：Mongoose.js中文网

以下是各接口的简单搭建

后端展示数据接口

// 数据全部展示
router.get("/case/show",async function(req,res){let data=await caseprogressModel.find()res.send({code:200,message:'caseshow ok',data})
})

后端批量添加接口

// 批量添加接口
router.post('/madd',async function(req, res, next) {const body= req.bodyconsole.log(body);const insertedData = await caseprogressModel.insertMany(body)res.send({code:"200",message:'madd ok',data: insertedData})
});

最终效果展示：