【从前端到后端导入excel资料实现批量导入-笔记模仿芋道源码的《系统管理-用户管理-导入-批量导入》】
前端
场馆列表
该列表进入出现的是这样的,这儿是列表操作
<el-table-column label="操作" align="center" width="220px">
<template #default="scope">
<el-button
link
type="primary"
@
click="openForm('update', scope.row.id, scope.row.name)"
v-hasPermi="['sports:venue:update']"
>
编辑
<
/el-button>
<el-button
link
type="primary"
@
click="openImportForm('update', scope.row.id, scope.row.name, scope.row.capacity)"
v-hasPermi="['sports:venue:update']"
>
批量导入预约
<
/el-button>
<el-button
link
type="danger"
@
click="handleDelete(scope.row.id)"
v-hasPermi="['sports:venue:delete']"
>
删除
<
/el-button>
<
/template>
<
/el-table-column>批量导入操作在这儿
<el-button
link
type="primary"
@
click="openImportForm('update', scope.row.id, scope.row.name, scope.row.capacity)"
v-hasPermi="['sports:venue:update']"
>
批量导入预约
<
/el-button><
!-- 批量导入 -->
<UserImportForm ref="importFormRef" @
success="getList" />
import UserImportForm from './UserImportForm.vue'
// 批量导入
const importFormRef = ref(
)
const openImportForm = (type: string
, id?: number
, name?: string
, capacity: number
) =>
{
if (!importFormRef.value) {
console.error('importFormRef 未正确绑定'
)
return
}
importFormRef.value.open(type, id, name)
}这是导入的弹窗
<template>
<Dialog v-model="dialogVisible" title="批量导入场馆预约" width="480">
<el-form-item class="form-item">
<el-date-picker
v-model="bookingDates"
type="date"
value-format="YYYY-MM-DD"
placeholder="请选择预约的日期"
:disabled-date="disabledDate"
:clearable="true"
class="custom-date-picker"
/>
<
/el-form-item>
<el-upload
ref="uploadRef"
v-model:file-list="fileList"
:action="importUrl"
:auto-upload="false"
:disabled="formLoading"
:headers="uploadHeaders"
:limit="1"
:on-error="submitFormError"
:on-exceed="handleExceed"
:on-success="submitFormSuccess"
accept=".xlsx, .xls"
drag
class="upload-area"
>
<Icon icon="ep:upload" />
<div class="el-upload__text">将文件拖到此处，或<em>点击上传<
/em>
<
/div>
<template #tip>
<div class="el-upload__tip text-center">
<span>仅允许导入 xls、xlsx 格式文件。<
/span>
<el-link
:underline="false"
style="font-size: 12px; vertical-align: baseline"
type="primary"
@
click="importTemplate"
>
下载模板
<
/el-link>
<
/div>
<
/template>
<
/el-upload>
<template #footer>
<el-button :disabled="formLoading" type="primary" @
click="submitForm"
class="submit-button">
确 定
<
/el-button>
<el-button @
click="dialogVisible = false"
class="cancel-button">取 消<
/el-button>
<
/template>
<
/Dialog>
<
/template>
<script lang="ts" setup>
import {
ref, nextTick, defineExpose
}
from 'vue'
import axios from 'axios'
import {
getAccessToken, getTenantId
}
from '@/utils/auth'
import *
as VenueBookingApi from '@/api/sports/venuebooking'
import download from '@/utils/download'
import {
getUserProfile
}
from '@/api/system/user/profile'
defineOptions({
name: 'VenueBookingImportForm'
}
)
const message = useMessage(
)
const dialogVisible = ref(false
)
const formLoading = ref(false
)
const uploadRef = ref(
)
const importUrl =
import.meta.env.VITE_BASE_URL +
import.meta.env.VITE_API_URL + '/sports/venue-booking/import'
const uploadHeaders = ref(
)
const fileList = ref([]
)
const updateSupport = ref(0
)
const bookingDates = ref([]
) // 日期范围
const venueId = ref(
null
) // 用于存储场馆ID
const venueName = ref(''
) // 用于存储场馆名称
// 用于打开导入对话框并传入参数
const open = (type: string
, id: number
, name: string
) =>
{
dialogVisible.value = true
venueId.value = id // 设置 venueId
venueName.value = name // 设置 venueName
updateSupport.value = 0
fileList.value = []
bookingDates.value = [] // 重置日期范围
resetForm(
)
}
defineExpose({
open
}
)
// 限制选择日期的函数（例如，不能选择过去的日期）
const disabledDate = (date: Date) =>
{
return date.getTime(
) < Date.now(
) // 禁止选择过去的日期
}
// 提交表单
const submitForm =
async (
) =>
{
if (!validateForm(
)
)
return
uploadHeaders.value = {
Authorization: 'Bearer ' + getAccessToken(
)
,
'tenant-id': getTenantId(
)
}
formLoading.value = true
// 创建 FormData 对象，包含文件和其他参数
const formData =
new FormData(
)
formData.append('file'
, fileList.value[0].raw)
formData.append('venueId'
, venueId.value) // 场馆 ID
formData.append('venueName'
, venueName.value) // 场馆名称
formData.append('bookingTime'
, bookingDates.value) // 预约日期
formData.append('updateSupport'
, updateSupport.value ? 'true' : 'false'
)
// 获取用户资料并添加到 formData 中
// 发起请求
try {
const res =
await getUserProfile(
)
;
// 假设 getUserProfile 是异步函数
formData.append('agent'
, res.nickname)
;
// 将 nickname 作为 agent 添加到 FormData 中
formData.append('agentId'
, res.id)
;
const response =
await axios.post(importUrl, formData, {
headers: uploadHeaders.value
}
)
// 检查接口返回的 response 格式，如果是包含 code 的结构
if (response.data && response.data.code === 0
) {
// 上传成功
message.alertSuccess('批量导入成功！！'
)
submitFormSuccess(response.data)
}
else {
// 上传失败，显示错误信息
submitFormError(response.data)
}
}
catch (error) {
submitFormError(error) // 请求失败
}
}
// 失败回调
const submitFormError = (error: any
):
void =>
{
if (error && error.msg) {
message.error(error.msg || '上传失败，请重新上传！'
)
}
else {
message.error('上传失败，请重新上传！'
)
}
formLoading.value = false
}
// 成功回调
const submitFormSuccess = (response: any
) =>
{
if (response.code !== 0
) {
message.error(response.msg || '上传失败'
)
formLoading.value = false
return
}
// 完成后恢复状态
formLoading.value = false
dialogVisible.value = false
}
// 重置表单
const resetForm =
async (
): Promise<
void>
=>
{
formLoading.value = false
await nextTick(
)
uploadRef.value?.clearFiles(
)
}
// 处理文件超出限制
const handleExceed = (
):
void =>
{
message.error('最多只能上传一个文件！'
)
}
// 下载模板
const importTemplate =
async (
) =>
{
const res =
await VenueBookingApi.importUserTemplate(
)
download.excel(res, '用户导入模版.xls'
)
}
// 验证表单输入
const validateForm = (
) =>
{
if (!fileList.value.length) {
message.error('请上传文件'
)
return false
}
if (!bookingDates.value || bookingDates.value.length === 0
) {
message.error('请先选择预约日期'
)
return false
}
return true
}
<
/script>
<style scoped>
.form-item {
margin-left: 110px;
}
<
/style>// 下载用户导入模板
export
const importUserTemplate = (
) =>
{
return request.download({
url: '/sports/venue-booking/get-import-template'
}
)
}下载用户模板插件
const download0 = (data: Blob, fileName: string
, mineType: string
) =>
{
// 创建 blob
const blob =
new Blob([data]
, {
type: mineType
}
)
// 创建 href 超链接，点击进行下载
window.URL = window.URL || window.webkitURL
const href = URL.createObjectURL(blob)
const downA = document.createElement('a'
)
downA.href = href
downA.download = fileName
downA.click(
)
// 销毁超连接
window.URL.revokeObjectURL(href)
}
const download = {
// 下载 Excel 方法
excel: (data: Blob, fileName: string
) =>
{
download0(data, fileName, 'application/vnd.ms-excel'
)
}
,
// 下载 Word 方法
word: (data: Blob, fileName: string
) =>
{
download0(data, fileName, 'application/msword'
)
}
,
// 下载 Zip 方法
zip: (data: Blob, fileName: string
) =>
{
download0(data, fileName, 'application/zip'
)
}
,
// 下载 Html 方法
html: (data: Blob, fileName: string
) =>
{
download0(data, fileName, 'text/html'
)
}
,
// 下载 Markdown 方法
markdown: (data: Blob, fileName: string
) =>
{
download0(data, fileName, 'text/markdown'
)
}
,
// 下载图片（允许跨域）
image: ({
url,
canvasWidth,
canvasHeight,
drawWithImageSize = true
}: {
url: string
canvasWidth?: number // 指定画布宽度
canvasHeight?: number // 指定画布高度
drawWithImageSize?: boolean // 将图片绘制在画布上时带上图片的宽高值, 默认是要带上的
}
) =>
{
const image =
new Image(
)
// image.setAttribute('crossOrigin', 'anonymous')
image.src = url
image.onload = (
) =>
{
const canvas = document.createElement('canvas'
)
canvas.width = canvasWidth || image.width
canvas.height = canvasHeight || image.height
const ctx = canvas.getContext('2d'
)
as CanvasRenderingContext2D
ctx?.clearRect(0
, 0
, canvas.width, canvas.height)
if (drawWithImageSize) {
ctx.drawImage(image, 0
, 0
, image.width, image.height)
}
else {
ctx.drawImage(image, 0
, 0
)
}
const url = canvas.toDataURL('image/png'
)
const a = document.createElement('a'
)
a.href = url
a.download = 'image.png'
a.click(
)
}
}
}
export
default download后端
用户模板下载
@GetMapping("/get-import-template"
)
@Operation(summary = "获得导入用户模板"
)
public
void importTemplate(HttpServletResponse response) throws IOException {
// 手动创建导出 demo
List<UserImportDemoExcelVO> list = Arrays.asList(
UserImportDemoExcelVO.builder(
).registrant("张三"
).phone("15601691300"
).idCard("522324198805060010"
).build(
)
)
;
// 输出
ExcelUtils.write(response, "预约用户导入模板.xls"
, "预约用户列表"
, UserImportDemoExcelVO.class, list)
;
}@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false
) // 设置 chain = false，避免用户导入有问题
public
class UserImportDemoExcelVO {
// @ExcelProperty("场馆id")
// private Long venueId;
//
// @ExcelProperty("场馆名称")
// private String venueName;
@ExcelProperty("预定人姓名"
)
private String registrant;
@ExcelProperty("预定人联系方式"
)
private String phone;
@ExcelProperty("预定人身份证号"
)
private String idCard;
// @ExcelProperty("预约时间")
// private LocalDateTime bookingTime;
}导入模板
@PostMapping("/import"
)
@Operation(summary = "导入场馆预约数据"
)
@Parameters({
@Parameter(name = "file"
, description = "Excel 文件"
, required = true
)
,
@Parameter(name = "venueId"
, description = "场馆 ID"
, required = true
)
,
@Parameter(name = "venueName"
, description = "场馆名称"
, required = true
)
,
@Parameter(name = "bookingTime"
, description = "预约时间"
, required = true
)
,
@Parameter(name = "agent"
, description = "代理预约人"
, required = true
)
,
@Parameter(name = "agentId"
, description = "代理id"
, required = true
)
,
@Parameter(name = "updateSupport"
, description = "是否支持更新，默认为 false"
, example = "false"
)
}
)
@PreAuthorize("@ss.hasPermission('sports:venue:booking:import')"
)
public CommonResult<VenueBookingRespVO>
importExcel(@RequestParam("file"
) MultipartFile file,
@RequestParam("venueId"
) Long venueId,
@RequestParam("agentId"
) Long agentId,
@RequestParam("venueName"
) String venueName,
@RequestParam("bookingTime"
) String bookingTime,
@RequestParam("agent"
) String agent,
@RequestParam(value = "updateSupport"
, required = false
, defaultValue = "false"
) Boolean updateSupport) throws Exception {
// 解析 Excel 文件为对象列表
List<UserImportExcelVO> list = ExcelUtils.read(file, UserImportExcelVO.class)
;
// 调用 service 层进行导入，获取导入结果
CommonResult<VenueBookingRespVO> result = venueBookingService.importVenueBookingList(list, venueId, venueName,agent,agentId, bookingTime, updateSupport)
;
// 返回服务层的响应
return result;
}@Schema(description = "管理后台 - 观赛预约记录 Response VO"
)
@Data
@ExcelIgnoreUnannotated
public
class VenueBookingRespVO {
@ExcelProperty("代理预定人id"
)
private Long id;
@Schema(description = "预定人"
)
@ExcelProperty("预定人"
)
private String registrant;
@Schema(description = "预定人"
)
@ExcelProperty("代理预定人"
)
private String agent;
@Schema(description = "代理预定人id"
, example = "19070"
)
@ExcelProperty("代理预定人id"
)
private Long agentId;
@Schema(description = "预定人员电话号码"
)
@ExcelProperty("预定人员电话号码"
)
private String phone;
@Schema(description = "预定人员身份证"
)
@ExcelProperty("预定人员身份证"
)
private String idCard;
@Schema(description = "预约时间"
)
@ExcelProperty("预约时间"
)
private LocalDateTime bookingTime;
@Schema(description = "场馆id"
, example = "19070"
)
@ExcelProperty("场馆id"
)
private Long venueId;
@Schema(description = "场馆名称"
)
@ExcelProperty("场馆名称"
)
private String venueName;
@Schema(description = "创建时间"
, requiredMode = Schema.RequiredMode.REQUIRED
)
@ExcelProperty("创建时间"
)
private LocalDateTime createTime;
private String message;
// 带有多个字段的构造方法
public VenueBookingRespVO(String message, String registrant, String phone, String idCard,
LocalDateTime bookingTime, Long venueId, String venueName, LocalDateTime createTime
) {
this.message = message;
this.registrant = registrant;
this.phone = phone;
this.idCard = idCard;
this.bookingTime = bookingTime;
this.venueId = venueId;
this.venueName = venueName;
this.createTime = createTime;
}
}/**
* 用户 Excel 导入 VO
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false
) // 设置 chain = false，避免用户导入有问题
public
class UserImportExcelVO {
@ExcelProperty("场馆id"
)
private Long venueId;
@ExcelProperty("场馆名称"
)
private String venueName;
@ExcelProperty("预定人姓名"
)
private String registrant;
@ExcelProperty("预定人联系方式"
)
private String phone;
@ExcelProperty("预定人身份证号"
)
private String idCard;
@ExcelProperty("预约时间"
)
private LocalDateTime bookingTime;
}@Override
@Transactional(rollbackFor = Exception.class)
public CommonResult<VenueBookingRespVO>
importVenueBookingList(List<UserImportExcelVO> list, Long venueId, String venueName, String agent, Long agentId, String bookingTime, Boolean updateSupport
) {
if (list ==
null || list.isEmpty(
)
) {
return CommonResult.error(400
, "导入的数据不能为空！"
)
;
}
List<String> errorMessages =
new ArrayList<
>
(
)
;
LocalDateTime bookingDateTime = LocalDateTime.parse(bookingTime + "T00:00:00"
)
;
// 身份证号和手机号验证正则表达式
String idCardRegex = "^[1-9]\\d{5}(?:18|19|20)\\d{2}(?:0[1-9]|10|11|12)(?:0[1-9]|[1-2]\\d|30|31)\\d{3}[\\dXx]$"
;
Pattern idCardPattern = Pattern.compile(idCardRegex)
;
String phoneRegex = "^1[3-9]\\d{9}$"
;
Pattern phonePattern = Pattern.compile(phoneRegex)
;
List<VenueBookingDO> venueBookingList =
new ArrayList<
>
(
)
;
Set<String> idCardSet =
new HashSet<
>
(
)
;
// 用于存储已存在的身份证号，查重
for (int i = 0
; i < list.size(
)
; i++
) {
UserImportExcelVO excelVO = list.get(i)
;
errorMessages.clear(
)
;
// 清空错误信息列表
// 验证身份证号格式
Matcher idCardMatcher = idCardPattern.matcher(excelVO.getIdCard(
)
)
;
if (!idCardMatcher.matches(
)
) {
errorMessages.add("第" + (i + 1
) + "条记录：身份证号格式不正确"
)
;
}
// 验证手机号格式
Matcher phoneMatcher = phonePattern.matcher(excelVO.getPhone(
)
)
;
if (!phoneMatcher.matches(
)
) {
errorMessages.add("第" + (i + 1
) + "条记录：手机号格式不正确"
)
;
}
// 检查身份证号是否重复
if (idCardSet.contains(excelVO.getIdCard(
)
)
) {
errorMessages.add("第" + (i + 1
) + "条记录：身份数据重复"
)
;
}
else {
idCardSet.add(excelVO.getIdCard(
)
)
;
// 加入已存在身份证号集合
}
// 如果有错误，返回错误信息并终止插入
if (!errorMessages.isEmpty(
)
) {
return CommonResult.error(400
, String.join("，"
, errorMessages)
)
;
// 使用 error 方法返回错误信息
}
// 对身份证号进行加密处理
String encryptedIdCard = Sm2Util.signMd5(excelVO.getIdCard(
)
)
;
excelVO.setVenueId(venueId)
;
excelVO.setVenueName(venueName)
;
excelVO.setBookingTime(bookingDateTime)
;
excelVO.setIdCard(encryptedIdCard)
;
VenueBookingDO venueBookingDO =
new VenueBookingDO(
)
;
venueBookingDO.setVenueId(excelVO.getVenueId(
)
)
;
venueBookingDO.setVenueName(excelVO.getVenueName(
)
)
;
venueBookingDO.setBookingTime(excelVO.getBookingTime(
)
)
;
venueBookingDO.setIdCard(excelVO.getIdCard(
)
)
;
// 设置加密后的身份证号
venueBookingDO.setPhone(excelVO.getPhone(
)
)
;
venueBookingDO.setRegistrant(excelVO.getRegistrant(
)
)
;
venueBookingDO.setAgent(agent)
;
venueBookingDO.setAgentId(agentId)
;
venueBookingDO.setBookingStatus(0
)
;
venueBookingList.add(venueBookingDO)
;
}
// 批量插入数据
if (!venueBookingList.isEmpty(
)
) {
venueBookingMapper.insertBatch(venueBookingList)
;
}
// 返回成功的响应，填充所有字段
return CommonResult.success(
new VenueBookingRespVO("导入成功"
, "registrantData"
, "12345678901"
, "123456789012345678"
, bookingDateTime, venueId, venueName, LocalDateTime.now(
)
)
)
;
}
浙公网安备 33010602011771号