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.body
  console.log(body);
  const insertedData = await caseprogressModel.insertMany(body)
  res.send({code:"200",message:'madd ok',data: insertedData})
});

最终效果展示：