最近在工作上遇到了实现从Excel中导入信息的需求问题,解决以后发到csdn上来记录一下,也欢迎大家来讨论,直接上源码。
前端导入:
导入按钮:
<el-button
type="success"
plain
@click="handleImport"
>
<Icon icon="ep:upload" class="mr-5px" /> 导入
</el-button>
弹窗:
<!-- 用户导入对话框 -->
<BeliefImportForm ref="importFormRef" @success="getList" />
导入:
import BeliefImportForm from './BeliefImportForm.vue'
/** 用户导入 */
const importFormRef = ref()
const handleImport = () => {
importFormRef.value.open()
}
BeliefImportForm.Vue:
<template>
<Dialog v-model="dialogVisible" title="住户导入" width="400">
<el-upload
ref="uploadRef"
v-model:file-list="fileList"
:action="importUrl + '?updateSupport=' + updateSupport"
:auto-upload="false"
:disabled="formLoading"
:headers="uploadHeaders"
:limit="1"
:on-error="submitFormError"
:on-exceed="handleExceed"
:on-success="submitFormSuccess"
accept=".xlsx, .xls"
drag
>
<Icon icon="ep:upload" />
<div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div>
<template #tip>
<div class="el-upload__tip text-center">
<div class="el-upload__tip">
<el-checkbox v-model="updateSupport" />
是否更新已经存在的用户数据
</div>
<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">确 定</el-button>
<el-button @click="dialogVisible = false">取 消</el-button>
</template>
</Dialog>
</template>
<script lang="ts" setup>
import * as BeliefApi from '@/api/basicInfo/belief'
import { getAccessToken, getTenantId } from '@/utils/auth'
import download from '@/utils/download'
defineOptions({ name: 'Belief' })
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 + '/basicInfo/belief/import'
const uploadHeaders = ref() // 上传 Header 头
const fileList = ref([]) // 文件列表
const updateSupport = ref(0) // 是否更新已经存在的用户数据
/** 打开弹窗 */
const open = () => {
dialogVisible.value = true
fileList.value = []
resetForm()
}
defineExpose({ open }) // 提供 open 方法,用于打开弹窗
/** 提交表单 */
const submitForm = async () => {
if (fileList.value.length == 0) {
message.error('请上传文件')
return
}
// 提交请求
uploadHeaders.value = {
Authorization: 'Bearer ' + getAccessToken(),
'tenant-id': getTenantId()
}
formLoading.value = true
uploadRef.value!.submit()
}
/** 文件上传成功 */
const emits = defineEmits(['success'])
const submitFormSuccess = (response: any) => {
if (response.code !== 0) {
message.error(response.msg)
formLoading.value = false
return
}
// 拼接提示语
const data = response.data
let text = '上传成功数量:' + data.createUsernames.length + ';'
for (let username of data.createUsernames) {
text += '< ' + username + ' >'
}
text += '更新成功数量:' + data.updateUsernames.length + ';'
for (const username of data.updateUsernames) {
text += '< ' + username + ' >'
}
text += '更新失败数量:' + Object.keys(data.failureUsernames).length + ';'
for (const username in data.failureUsernames) {
text += '< ' + username + ': ' + data.failureUsernames[username] + ' >'
}
message.alert(text)
// 发送操作成功的事件
emits('success')
}
/** 上传错误提示 */
const submitFormError = (): void => {
message.error('上传失败,请您重新上传!')
formLoading.value = false
}
/** 重置表单 */
const resetForm = () => {
// 重置上传状态和文件
formLoading.value = false
uploadRef.value?.clearFiles()
}
/** 文件数超出提示 */
const handleExceed = (): void => {
message.error('最多只能上传一个文件!')
}
/** 下载模板操作 */
const importTemplate = async () => {
const res = await BeliefApi.importUserTemplate()
download.excel(res, '用户导入模版.xls')
}
</script>
后端导入:
先来看看我的实体类BeliefDo有哪些字段:
package com.todod.backend.module.basicInfo.dal.dataobject.belief;
import lombok.*;
import java.util.*;
import java.time.LocalDateTime;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.*;
import com.todod.backend.framework.mybatis.core.dataobject.BaseDO;
/**
* 宗教信仰 DO
*
* @author 平台管理员
*/
@TableName("religious_belief")
@KeySequence("religious_belief_seq") // 用于 Oracle、PostgreSQL、Kingbase、DB2、H2 数据库的主键自增。如果是 MySQL 等数据库,可不写。
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class BeliefDO extends BaseDO {
/**
* 主键id
*/
@TableId
private Long id;
/**
* 所属小区
*/
private String religionCommunity;
/**
* 居住房屋
*/
private String religionHouse;
/**
* 姓名
*/
private String religionName;
/**
* 身份证号
*/
private String religionIdCard;
/**
* 联系电话
*/
private String religionPhone;
/**
* 性别
*
* 枚举 {@link TODO system_user_sex 对应的类}
*/
private Integer religionSex;
/**
* 民族
*
* 枚举 {@link TODO nation 对应的类}
*/
private Integer religionNation;
/**
* 国籍
*/
private Integer religionNationality;
/**
* 信仰
*
* 枚举 {@link TODO religion_belief 对应的类}
*/
private Integer religionBelief;
/**
* 宗教团体
*/
private String religionTeam;
/**
* 学历
*/
private Integer religionEducationalBack;
/**
* 备注
*/
private String religionRemark;
/**
* 删除标志位
*/
private String delFlag;
/**
* 导入批次号
*/
private String batchNo;
}
接下来我们在BeliefController层中加入import方法用于导入Excel文件:
@PostMapping("/import")
@Operation(summary = "导入宗教信仰 Excel")
@Parameters({
@Parameter(name = "file", description = "Excel 文件", required = true),
@Parameter(name = "updateSupport", description = "是否支持更新,默认为 false", example = "true")
})
// @PreAuthorize("@ss.hasPermission('basicInfo:belief:import')")
public CommonResult<BeliefImportRespVO> importExcel(@RequestParam("file") MultipartFile file,
@RequestParam(value = "updateSupport", required = false, defaultValue = "false") Boolean updateSupport) throws Exception {
List<BeliefImportExcelVO> list = ExcelUtils.read(file, BeliefImportExcelVO.class);
return success(beliefService.importBeliefList(list, updateSupport));
}
@GetMapping("/get-import-template")
@Operation(summary = "获得导入用户模板")
public void importTemplate(HttpServletResponse response) throws IOException {
// 手动创建导出 demo
List<BeliefImportExcelVO> list = Arrays.asList(
BeliefImportExcelVO.builder().id(1L).religionCommunity("爱情缤纷里").religionHouse("A-1-304")
.religionName("小宋").religionSex(1).religionNation(1).religionNationality(1).religionIdCard("456546546354")
.religionPhone("546456456546").religionTeam("中国").religionEducationalBack(2).religionRemark("太好了").build(),
BeliefImportExcelVO.builder().id(2L).religionCommunity("爱情缤纷里").religionHouse("A-1-202").religionName("小李")
.religionSex(1).religionNation(1).religionNationality(1).religionIdCard("46546546464")
.religionPhone("545456156").religionTeam("中国").religionEducationalBack(1).religionRemark("太好了")
.build()
);
// 输出
ExcelUtils.write(response, "宗教信仰信息导入模板.xls", "住户列表", BeliefImportExcelVO.class, list);
}
注意,此时我们一定要再多建两个文件
原因是Excel的导入需要借助阿里云的EasyExcel,EasyExcel中使用了cglib,而cglib读取链接调用方法存在问题,所以要建这两个类
BeliefImportExcelVO:这里面用来放Excel中对应的字段
package com.todod.backend.module.basicInfo.controller.admin.belief.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.todod.backend.framework.excel.core.annotations.DictFormat;
import com.todod.backend.framework.excel.core.convert.DictConvert;
import com.todod.backend.module.system.enums.DictTypeConstants;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
/**
* 用户 Excel 导入 VO
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false,避免用户导入有问题
public class BeliefImportExcelVO {
@ExcelProperty("id")
private Long id;
@ExcelProperty("所属小区")
private String religionCommunity;
@ExcelProperty("居住房屋")
private String religionHouse;
@ExcelProperty("姓名")
private String religionName;
@ExcelProperty("身份证号")
private String religionIdCard;
@ExcelProperty("联系电话")
private String religionPhone;
@ExcelProperty(value = "性别")
@DictFormat(DictTypeConstants.USER_SEX)
private Integer religionSex;
@ExcelProperty(value = "民族")
@DictFormat(DictTypeConstants.RELIGION_NATION)
private Integer religionNation;
@ExcelProperty(value = "国籍")
@DictFormat(DictTypeConstants.RELIGION_NATIONALITY)
private Integer religionNationality;
@ExcelProperty(value = "信仰")
@DictFormat(DictTypeConstants.RELIGION_BELIEF)
private Integer religionBelief;
@ExcelProperty("宗教团体")
private String religionTeam;
@ExcelProperty(value = "学历")
@DictFormat(DictTypeConstants.EDUCATIONALBACK)
private Integer religionEducationalBack;
@ExcelProperty("备注")
private String religionRemark;
}
@DictFormat(DictTypeConstants.USER_SEX)这个注解是为了让Excel中的信息与数据字典对应,要在DictTypeConstants类中添加数据字典的信息
// ========== BASICINFO 模块 ==========
String RELIGION_NATION = "nation"; // 民族
String RELIGION_NATIONALITY = "nationality"; // 国籍
String RELIGION_BELIEF = "religion_belief"; // 信仰
String EDUCATIONALBACK = "educationalBack";
BeliefImportRespVO类:
package com.todod.backend.module.basicInfo.controller.admin.belief.vo;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Builder;
import lombok.Data;
import java.util.List;
import java.util.Map;
@Schema(description = "管理后台 - 用户导入 Response VO")
@Data
@Builder
public class BeliefImportRespVO {
@Schema(description = "创建成功的用户名数组", requiredMode = Schema.RequiredMode.REQUIRED)
private List<String> createUsernames;
@Schema(description = "更新成功的用户名数组", requiredMode = Schema.RequiredMode.REQUIRED)
private List<String> updateUsernames;
@Schema(description = "导入失败的用户集合,key 为用户名,value 为失败原因", requiredMode = Schema.RequiredMode.REQUIRED)
private Map<String, String> failureUsernames;
}
Service层:
/**
* 批量导入用户
*
* @param importUsers 导入用户列表
* @param isUpdateSupport 是否支持更新
* @return 导入结果
*/
BeliefImportRespVO importBeliefList(List<BeliefImportExcelVO> importUsers, boolean isUpdateSupport);
Mapper层:
package com.todod.backend.module.basicInfo.dal.mysql.belief;
import java.util.*;
import com.todod.backend.framework.common.pojo.PageResult;
import com.todod.backend.framework.mybatis.core.query.LambdaQueryWrapperX;
import com.todod.backend.framework.mybatis.core.mapper.BaseMapperX;
import com.todod.backend.module.basicInfo.dal.dataobject.belief.BeliefDO;
import org.apache.ibatis.annotations.Mapper;
import com.todod.backend.module.basicInfo.controller.admin.belief.vo.*;
/**
* 宗教信仰 Mapper
*
* @author 平台管理员
*/
@Mapper
public interface BeliefMapper extends BaseMapperX<BeliefDO> {
default PageResult<BeliefDO> selectPage(BeliefPageReqVO reqVO) {
return selectPage(reqVO, new LambdaQueryWrapperX<BeliefDO>()
.eqIfPresent(BeliefDO::getReligionCommunity, reqVO.getReligionCommunity())
.likeIfPresent(BeliefDO::getReligionName, reqVO.getReligionName())
.orderByDesc(BeliefDO::getId));
}
default BeliefDO selectByUsername(String religionName) {
return selectOne(BeliefDO::getReligionName, religionName);
}
default BeliefDO selectByIdCard(String religionIdCard) {
return selectOne(BeliefDO::getReligionIdCard, religionIdCard);
}
}
实现类:
@Override
@Transactional(rollbackFor = Exception.class) // 添加事务,异常则回滚所有导入
public BeliefImportRespVO importBeliefList(List<BeliefImportExcelVO> importUsers, boolean isUpdateSupport) {
if (CollUtil.isEmpty(importUsers)) {
throw exception(USER_IMPORT_LIST_IS_EMPTY);
}
BeliefImportRespVO respVO = BeliefImportRespVO.builder().createUsernames(new ArrayList<>())
.updateUsernames(new ArrayList<>()).failureUsernames(new LinkedHashMap<>()).build();
importUsers.forEach(importUser -> {
// 校验,判断是否有不符合的原因
try {
validateUserForCreateOrUpdate( null,importUser.getReligionName(), importUser.getReligionIdCard());
} catch (ServiceException ex) {
respVO.getFailureUsernames().put(importUser.getReligionName(), ex.getMessage());
return;
}
// 判断如果不存在,在进行插入
BeliefDO existUser = beliefMapper.selectByUsername(importUser.getReligionName());
if (existUser == null) {
beliefMapper.insert(BeanUtils.toBean(importUser, BeliefDO.class));
respVO.getCreateUsernames().add(importUser.getReligionName());
return;
}
// 如果存在,判断是否允许更新
if (!isUpdateSupport) {
respVO.getFailureUsernames().put(importUser.getReligionName(), USER_USERNAME_EXISTS.getMsg());
return;
}
BeliefDO updateUser = BeanUtils.toBean(importUser, BeliefDO.class);
updateUser.setId(existUser.getId());
beliefMapper.updateById(updateUser);
respVO.getUpdateUsernames().add(importUser.getReligionName());
});
return respVO;
}
private BeliefDO validateUserForCreateOrUpdate(Long id,String religionName,String religionIdCard) {
// 关闭数据权限,避免因为没有数据权限,查询不到数据,进而导致唯一校验不正确
return DataPermissionUtils.executeIgnore(() -> {
// 校验用户存在
BeliefDO beliefDO = validateUserExists(id);
// 校验姓名唯一
validateUsernameUnique(id, religionName);
// // 校验身份证号唯一
// validateIdCardUnique(id, religionIdCard);
return beliefDO;
});
}
@VisibleForTesting
void validateUsernameUnique(Long id, String religionName) {
if (StrUtil.isBlank(religionName)) {
return;
}
BeliefDO beliefDO = beliefMapper.selectByUsername(religionName);
if (beliefDO == null) {
return;
}
// 如果 id 为空,说明不用比较是否为相同 id 的用户
if (id == null) {
throw exception(USER_USERNAME_EXISTS);
}
if (!beliefDO.getId().equals(id)) {
throw exception(USER_USERNAME_EXISTS);
}
}
// @VisibleForTesting
// void validateIdCardUnique(Long id, String religionIdCard) {
// if (StrUtil.isBlank(religionIdCard)) {
// return;
// }
// BeliefDO beliefDO = beliefMapper.selectByIdCard(religionIdCard);
// if (beliefDO == null) {
// return;
// }
// // 如果 id 为空,说明不用比较是否为相同 id 的用户
// if (id == null) {
// throw exception(USER_MOBILE_EXISTS);
// }
// if (!beliefDO.getId().equals(id)) {
// throw exception(USER_MOBILE_EXISTS);
// }
// }
@VisibleForTesting
BeliefDO validateUserExists(Long id) {
if (id == null) {
return null;
}
BeliefDO beliefDO = beliefMapper.selectById(id);
if (beliefDO == null) {
throw exception(USER_NOT_EXISTS);
}
return beliefDO;
}
欢迎大家交流、学习、指正!