基于 FastExcel 与消息队列高效生成及导入机构用户数据

🎯 本文档详细介绍了开发机构用户数据导入功能的必要性及实现方法，如针对教育机构如学校场景下提高用户体验和管理效率的需求。文中首先分析了直接对接学生管理系统与平台对接的优势，包括减少人工审核成本、提高身份验证准确性等。接着介绍了FastExcel作为处理Excel文件的高效工具及其在用户数据导入导出中的应用，并展示了如何利用JavaFaker生成测试数据，以及通过消息队列异步处理Excel数据导入以保证效率和稳定性。最后，提供了SQL批量插入语句示例，用于处理用户数据的高效入库。

需求说明

为什么需要开发机构用户数据导入功能？平台不是本身就存在注册功能吗，为啥要多此一举？

场快订采用SaaS模式开发，支持多租户使用，不同租户对其资源的管理方式可能是不同的。

例如，在部分学校中，其内部场馆的预订服务可能仅对本校的教师、学生及工作人员开放。因此，为了准确识别和区分用户身份，确保只有特定机构内的成员能够访问相应的资源和服务，我们需要引入基于机构ID的身份绑定功能。

尽管平台本身已经具备注册功能，但对于类似学校等机构而言，直接利用其现有的学生管理系统与我们的平台进行对接，将极大地提高效率并减少管理成本。如果要求所有学生首先通过上传学生证等方式来完成注册审核流程，不仅会面临伪造证件的风险（如通过P图方式绕过审核），还会因需要大量人工审核而消耗不必要的资源。此外，这种做法对于学生来说也不够友好，增加了他们使用平台的复杂度。

相比之下，如果能够实现从学校的学生管理系统直接导入数据的功能，不仅可以避免上述问题，还能简化学生的登录过程——他们只需使用学号等已有的信息即可快速登录平台，享受便捷的服务。这种方式既提高了验证学生身份的准确性，也提升了整体用户体验，实现了双赢的局面。因此，开发这一功能具有显著的实际意义和应用价值。

FastExcel简介

POI 是 Apache 软件基金会下的一个项目，提供了用于操作各种文档格式的Java API，包括Microsoft Office中的Word、Excel和PowerPoint等格式。特别是对于Excel文件的操作，Apache POI提供了一套完整的解决方案，允许开发者读取、创建和修改Excel文件。然而，随着数据量的增长以及对处理效率要求的提高，使用POI操作Excel文件时遇到了性能瓶颈，尤其是在处理大文件时，内存消耗过大导致效率低下，甚至导致内存溢出。

在这种背景下，EasyExcel应运而生。它是由阿里巴巴开源的一个基于Java的数据处理库，专门用来简化Excel文件的读写操作。EasyExcel采用了不同于POI的实现方式，通过按行读取Excel文件而不是一次性加载整个文件到内存中，大大降低了内存占用，提高了处理效率。此外，EasyExcel还支持多种便捷的功能，如自定义转换器、注解式编程模型等，使得开发者能够更高效地进行Excel文件的操作。

EasyExcel的强大之处主要体现在以下几个方面：

低内存消耗：EasyExcel采用流式读取Excel文件的方式，避免了将整个文件加载到内存中，极大地减少了内存占用。
高效率：由于其优化的内部机制，无论是读取还是写入Excel文件，都比传统的POI方式更快。
易用性：提供了简洁明了的API接口，并支持通过注解配置Excel与Java对象之间的映射关系，极大地方便了开发者的使用。
灵活性：支持自定义转换器，可以根据需要灵活处理不同类型的数据转换问题。
扩展性：易于集成到现有的工程中，并且支持进一步的定制化开发，满足不同场景下的需求。

然而，这么优秀的项目却停止维护了

在这里插入图片描述

但是问题不大，EasyExcel 的原作者开了新的项目 FastExcel ，原本使用了 EasyExcel 的项目，可以平滑过渡到这个项目中，只需要修改一下 Maven 依赖导入和导包路径，即可接着使用。为啥使用 FastExcel ？

原作者通过对底层算法的优化和内存管理的改进，让 FastExcel 的性能更高。
相较于 EasyExcel ，FastExcel 还将持续更新，未来将提供更多的功能。
此外，FastExcel 免费开源，真心感谢大佬造福人类。

感兴趣的朋友可以去关注大佬的微信公众号

在这里插入图片描述

FastExcel项目说明：https://mp.weixin.qq.com/s/XMXLEFaTjaU7QVnSYRFC_A

项目开源仓库地址：https://github.com/CodePhiliaX/fastexcel

用户数据生成

使用 Faker 库虚构数据

Faker 是一个方便的Java库，用于生成各种虚构数据，如姓名、地址、公司信息等，非常适合测试和开发时填充数据库或模拟真实数据场景。通过简单的API调用，它能提供丰富多样的数据类型，并支持多语言和地区设置，帮助开发者高效地创建贴近实际的测试案例，而无需手动编写虚假数据。

依赖

<!-- javafaker 虚构数据 -->
<dependency><groupId>com.github.javafaker</groupId><artifactId>javafaker</artifactId><version>1.0.2</version>
</dependency>

使用

使用方式非常简单，new一个 faker 对象出来之后，直接调用相关方法即可

Faker faker = new Faker(Locale.CHINA);
faker.number().digits(10)

FastExcel 写出 excel 表格

依赖

<dependency><groupId>cn.idev.excel</groupId><artifactId>fastexcel</artifactId><version>1.0.0</version>
</dependency>

实现

首先声明一个实体类，来接收 faker 生成的数据，同时这个实体类也是 FastExcel 的输入

import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;/*** @Author dam* @create 2025/1/12 15:14*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ExcelUserData {// 设置相关列的宽度@ColumnWidth(30)// 设置相关列的名称@ExcelProperty("用户名")private String userName;@ExcelProperty("昵称")private String nickName;@ColumnWidth(20)@ExcelProperty("手机号")private String phoneNumber;@ColumnWidth(30)@ExcelProperty("邮箱")private String email;@ColumnWidth(10)@ExcelProperty("性别")private int gender;@ColumnWidth(20)@ExcelProperty("密码")private String password;
}

生成数据，并导出 excel 表格

package com.vrs;import cn.hutool.core.io.FileUtil;
import cn.idev.excel.EasyExcel;
import cn.idev.excel.util.ListUtils;
import com.github.javafaker.Faker;
import com.vrs.common.entity.ExcelUserData;
import org.junit.Test;import java.io.File;
import java.nio.file.Paths;
import java.util.List;
import java.util.Locale;
import java.util.Random;/*** 模拟用户数据生成** @Author dam* @create 2025/1/12 15:06*/
public class UserDataGenerateTest {/*** 用户数量*/private final int userNum = 10000;private final Faker faker = new Faker(Locale.CHINA);/*** excel地址*/private final String excelPath = Paths.get("").toAbsolutePath().getParent().getParent() + File.separator + "tmp";@Testpublic void generate() {if (!FileUtil.exist(excelPath)) {FileUtil.mkdir(excelPath);}// 数据生成List<ExcelUserData> list = ListUtils.newArrayList();for (int i = 0; i < userNum; i++) {ExcelUserData data = ExcelUserData.builder()// 随机生成10位数字，并拼接成邮箱.email(faker.number().digits(10) + "@qq.com")// 生成一个随机的电话号码.phoneNumber(faker.phoneNumber().cellPhone())// 生成一个随机的用户名，使用 faker 库的 regexify 方法生成 3 到 10 个字母组成的字符串.userName(faker.regexify("[a-zA-Z]{3,10}"))// 生成一个随机的昵称，使用 faker 库的 name 方法生成一个名字作为昵称.nickName(faker.name().firstName())// 示例性别选项.gender(faker.options().option(0, 1))// 生成随机密码.password(faker.internet().password()).build();list.add(data);}// 输出ExcelString fileName = excelPath + File.separator + "机构用户生成.xlsx";EasyExcel.write(fileName, ExcelUserData.class).sheet("用户表").doWrite(list);}
}

不如得感叹，这也太方便了，一句代码就完成了导出

EasyExcel.write(fileName, ExcelUserGenerateData.class).sheet("用户表").doWrite(list);

导出之后的表格如下

在这里插入图片描述

用户数据导入

FastExcel 读入 excel 表格

监听器

监听器需要继承 AnalysisEventListener ，泛型指定为 ExcelUserData 之后，读取的时候会将每一行数据转化为 ExcelUserData 对象

import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;
import com.vrs.common.entity.ExcelUserData;
import lombok.Getter;/*** @Author dam* @create 2025/1/12 17:05*/
public class UserDataRowListener extends AnalysisEventListener<ExcelUserData> {/*** 处理每一行数据* @param excelUserData* @param analysisContext*/@Overridepublic void invoke(ExcelUserData excelUserData, AnalysisContext analysisContext) {}/*** 所有数据解析完成之后的操作* @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}

进行读取

UserDataRowListener listener = new UserDataRowListener();
EasyExcel.read(importUserExcelMqDTO.getExcelPath(), ExcelUserData.class, listener).sheet().doRead();

Controller

/*** 上传excel表，并解析导入机构用户数据*/
@Operation(summary = "机构数据excel导入")
@PostMapping("/importUserExcel")
public Result importUserExcel(MultipartFile file) throws Exception {userService.importUserExcel(file);return Results.success();
}

Service

注意，为了提高接口的效率，使用消息队列异步解析 excel

@Override
public void importUserExcel(MultipartFile file) {/// 数据校验// 校验用户是否有绑定机构Long organizationId = UserContext.getOrganizationId();if (organizationId == null) {throw new ClientException(BaseErrorCode.USER_NOT_SET_ORGANIZATION_ERROR);}// 校验用户权限，是否为机构管理员Integer userType = UserContext.getUserType();UserTypeConstant.validateInstituteManager(userType);// 文件类型校验String absPath;String name = file.getOriginalFilename();if (!name.contains(".")) {// --if-- 如果图片没有正常后缀throw new ClientException(BaseErrorCode.NO_SUFFIX_ERROR);} else if (name.endsWith(".xlsx")) {absPath = EXCEL_TEMP_PATH + File.separator + UUID.randomUUID() + ".xlsx";} else if (name.endsWith(".xls")) {absPath = EXCEL_TEMP_PATH + File.separator + UUID.randomUUID() + ".xls";} else {throw new ClientException(BaseErrorCode.EXCEL_TYPE_ERROR);}/// 暂存文件到服务器本地// 如果不存在目录，创建目录if (!FileUtil.exist(EXCEL_TEMP_PATH)) {FileUtil.mkdir(EXCEL_TEMP_PATH);}// 将输入流中的数据复制到目标文件中try (InputStream is = file.getInputStream()) {File targetFile = new File(absPath);// 将输入流中的数据复制到目标文件中java.nio.file.Files.copy(is, targetFile.toPath(), java.nio.file.StandardCopyOption.REPLACE_EXISTING);} catch (IOException e) {e.printStackTrace();throw new ServiceException(e.getMessage());}/// 发送消息，执行excel数据解析并将用户数据导入数据库SendResult sendResult = importUserExcelProducer.sendMessage(ImportUserExcelMqDTO.builder().organizationId(UserContext.getOrganizationId()).excelPath(absPath).build());if (!sendResult.getSendStatus().equals(SendStatus.SEND_OK)) {log.error("消息发送失败: " + sendResult.getSendStatus());throw new ServiceException(BaseErrorCode.MQ_SEND_ERROR);}// todo 记录导入任务到数据库中
}

【excel读取与数据导入】

/*** 正式执行excel数据解析与导入** @param importUserExcelMqDTO*/
@Override
public void handleImportUserExcel(ImportUserExcelMqDTO importUserExcelMqDTO) {OrganizationDO organizationDO = organizationService.getById(importUserExcelMqDTO.getOrganizationId());UserDataRowListener listener = new UserDataRowListener(this, importUserExcelMqDTO.getOrganizationId(), organizationDO.getMark());EasyExcel.read(importUserExcelMqDTO.getExcelPath(), ExcelUserData.class, listener).sheet().doRead();// 删除暂存的 excel 文件FileUtil.del(importUserExcelMqDTO.getExcelPath());
}

【读取行监听器】

注意：在解析导入用户数据的时候，需要保证导入的效率，又要保证数据尽量都插入成功，如果有的数据插入不成功，需要向管理员反馈，哪些数据导入失败。

为什么会出现导入失败？

username冲突：有可能用户一开始已经将部分用户导入了平台中，后面机构有了新用户，例如学校每年有新学生，此时再导入，会出现 username 重复插入的现象。为了处理这个情况，本文通过使用INSERT IGNORE语句来处理，即如果想数据库中插入一批数据，对于之前还没有插入过的数据，正常执行插入。对于已经插入过的数据，则跳过插入，因为这些用户可能已经使用了平台一段时间
部分字段缺失：可能存在部分数据条目，如用户名等关键字段缺失
数据类型不匹配：如字符串填入到整型字段中
违反非空约束：部分必填的字段为空
数据库连接超时
数据库性能负载过高
网络中断
内存、磁盘资源不足
数据库服务异常

这段代码的作用是读取 Excel 文件里的用户数据，然后把这些数据存到数据库里。为了提高效率，它会先尝试一批一批地插入数据（比如每次插 1000 条）。如果某批数据插入失败了，它会把这批数据拆成更小的批次（比如每次插 250 条）再试一次。如果拆到最小批次（比如 50 条）还是失败，就改成一条一条地插入。最后，如果还有插不进去的数据，就把这些失败的数据保存到一个新的 Excel 文件里，方便以后查看和处理。整个过程既保证了速度，又确保了数据不会丢失。

package com.vrs.service.excel;import cn.idev.excel.EasyExcel;
import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;
import com.vrs.common.entity.ExcelUserData;
import com.vrs.domain.entity.UserDO;
import com.vrs.service.UserService;
import com.vrs.utils.SnowflakeIdUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;/*** @Author dam* @create 2025/1/12 17:05*/
@Slf4j
public class UserDataRowListener extends AnalysisEventListener<ExcelUserData> {private UserService userService;private Long organizationId;private String organizationMark;public UserDataRowListener(UserService userService, Long organizationId, String organizationMark) {this.userService = userService;this.organizationId = organizationId;this.organizationMark = organizationMark;}/*** excel表中的数据行数*/@Getterprivate int rowCount = 0;/*** 一批的数据量*/private final int BATCH_SIZE = 1000;/*** 最小批次大小*/private final int MIN_BATCH_SIZE = 50;public static final String EXCEL_TEMP_PATH = System.getProperty("user.dir") + File.separator + "tmp" + File.separator + "excel";/*** 数据插入缓冲区，满了就存储到数据库中*/private List<UserDO> userDOBuffer = new ArrayList<>();/*** 记录插入失败的数据*/private List<UserDO> failedData = new ArrayList<>();/*** 处理每一行数据** @param excelUserData* @param analysisContext*/@Overridepublic void invoke(ExcelUserData excelUserData, AnalysisContext analysisContext) {UserDO userDO = new UserDO();BeanUtils.copyProperties(excelUserData, userDO);userDO.setOrganizationId(organizationId);userDO.setUserName(organizationMark + "_" + userDO.getUserName());userDO.setId(SnowflakeIdUtil.nextId());userDO.setCreateTime(new Date());userDOBuffer.add(userDO);if (userDOBuffer.size() >= BATCH_SIZE) {processBatch(userDOBuffer, BATCH_SIZE);userDOBuffer.clear();}}/*** 所有数据解析完成之后的操作** @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {if (userDOBuffer.size() > 0) {processBatch(userDOBuffer, BATCH_SIZE);userDOBuffer.clear();}log.info("-------------------------- 机构用户全部导入完毕 ----------------------------");if (failedData.size() > 0) {String fileName = EXCEL_TEMP_PATH + File.separator + "fail " + UUID.randomUUID() + ".xlsx";// todo 如果错误的数据很多的话，之类需要优化，否则占用内存较高EasyExcel.write(fileName, ExcelUserData.class).sheet("导入失败用户").doWrite(failedData);// todo 导出excel之后，将excel请求路径存在到数据库中，供后续被管理员下载}// todo 修改数据库中的导入任务状态为成功或部分失败}/*** 处理批次数据** @param batch     当前批次数据* @param batchSize 当前批次大小*/private void processBatch(List<UserDO> batch, int batchSize) {try {// 尝试批量插入userService.saveBatchIgnore(batch);} catch (Exception e) {log.error("批量插入失败，当前批次大小：{}，尝试拆分批次", batchSize, e);if (batchSize > MIN_BATCH_SIZE) {// 拆分批次为更小的批次int newBatchSize = batchSize / 4;List<List<UserDO>> smallerBatches = splitBatch(batch, newBatchSize);for (List<UserDO> smallerBatch : smallerBatches) {processBatch(smallerBatch, newBatchSize); // 递归处理更小的批次}} else {// 如果批次已经缩小到最小批次，逐条插入processSingleBatch(batch);}}}/*** 将批次拆分为更小的批次** @param batch        原始批次* @param newBatchSize 新批次大小* @return 拆分后的批次列表*/private List<List<UserDO>> splitBatch(List<UserDO> batch, int newBatchSize) {List<List<UserDO>> smallerBatches = new ArrayList<>();for (int i = 0; i < batch.size(); i += newBatchSize) {int end = Math.min(i + newBatchSize, batch.size());smallerBatches.add(batch.subList(i, end));}return smallerBatches;}/*** 逐条插入数据** @param batch 当前批次数据*/private void processSingleBatch(List<UserDO> batch) {for (UserDO userDO : batch) {try {// 逐条插入userService.save(userDO);} catch (Exception e) {log.error("单条插入失败，失败数据：{}", userDO, e);// 记录失败数据failedData.add(userDO);}}}}

MQ

【生产者】

import cn.hutool.core.util.StrUtil;
import com.vrs.constant.RocketMqConstant;
import com.vrs.domain.dto.mq.ImportUserExcelMqDTO;
import com.vrs.templateMethod.AbstractCommonSendProduceTemplate;
import com.vrs.templateMethod.BaseSendExtendDTO;
import com.vrs.templateMethod.MessageWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.rocketmq.common.message.MessageConst;
import org.springframework.messaging.Message;
import org.springframework.messaging.support.MessageBuilder;
import org.springframework.stereotype.Component;import java.util.UUID;/*** 机构用户数据导入 生产者** @Author dam* @create 2024/9/20 16:00*/
@Slf4j
@Component
public class ImportUserExcelProducer extends AbstractCommonSendProduceTemplate<ImportUserExcelMqDTO> {@Overrideprotected BaseSendExtendDTO buildBaseSendExtendParam(ImportUserExcelMqDTO messageSendEvent) {return BaseSendExtendDTO.builder().eventName("机构用户数据导入").keys(String.valueOf(messageSendEvent.getOrganizationId())).topic(RocketMqConstant.ADMIN_TOPIC).tag(RocketMqConstant.IMPORT_USER_EXCEL_TAG).sentTimeout(2000L).build();}@Overrideprotected Message<?> buildMessage(ImportUserExcelMqDTO messageSendEvent, BaseSendExtendDTO requestParam) {String keys = StrUtil.isEmpty(requestParam.getKeys()) ? UUID.randomUUID().toString() : requestParam.getKeys();return MessageBuilder.withPayload(new MessageWrapper(keys, messageSendEvent)).setHeader(MessageConst.PROPERTY_KEYS, keys).setHeader(MessageConst.PROPERTY_TAGS, requestParam.getTag()).build();}
}

【消费者】

package com.vrs.rocketMq.listener;import com.vrs.annotation.Idempotent;
import com.vrs.constant.RocketMqConstant;
import com.vrs.domain.dto.mq.ImportUserExcelMqDTO;
import com.vrs.enums.IdempotentSceneEnum;
import com.vrs.service.UserService;
import com.vrs.templateMethod.MessageWrapper;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.rocketmq.spring.annotation.MessageModel;
import org.apache.rocketmq.spring.annotation.RocketMQMessageListener;
import org.apache.rocketmq.spring.annotation.SelectorType;
import org.apache.rocketmq.spring.core.RocketMQListener;
import org.springframework.stereotype.Component;/*** @Author dam* @create 2024/9/20 21:30*/
@Slf4j(topic = RocketMqConstant.VENUE_TOPIC)
@Component
@RocketMQMessageListener(topic = RocketMqConstant.ADMIN_TOPIC,consumerGroup = RocketMqConstant.ADMIN_CONSUMER_GROUP + "-" + RocketMqConstant.IMPORT_USER_EXCEL_TAG,messageModel = MessageModel.CLUSTERING,// 监听tagselectorType = SelectorType.TAG,selectorExpression = RocketMqConstant.IMPORT_USER_EXCEL_TAG
)
@RequiredArgsConstructor
public class ImportUserExcelListener implements RocketMQListener<MessageWrapper<ImportUserExcelMqDTO>> {private final UserService userService;/*** 消费消息的方法* 方法报错就会拒收消息** @param messageWrapper 消息内容，类型和上面的泛型一致。如果泛型指定了固定的类型，消息体就是我们的参数*/@Idempotent(uniqueKeyPrefix = "import_user_excel:",key = "#messageWrapper.getMessage().getOrganizationId()+''",scene = IdempotentSceneEnum.MQ,keyTimeout = 3600L)@SneakyThrows@Overridepublic void onMessage(MessageWrapper<ImportUserExcelMqDTO> messageWrapper) {// 开头打印日志，平常可 Debug 看任务参数，线上可报平安（比如消息是否消费，重新投递时获取参数等）log.info("[消费者] 机构用户数据导入，机构ID：{}", messageWrapper.getMessage().getOrganizationId());userService.handleImportUserExcel(messageWrapper.getMessage());}
}

SQL

这段SQL是一个用于批量插入数据的语句，具体来说是使用INSERT IGNORE语法向名为user的表中插入多条记录。如果遇到主键冲突或者唯一键冲突，它不会插入那条特定的记录，但会继续尝试插入其他记录，而不是直接中断整个操作。

<insert id="saveBatchIgnore">INSERT IGNORE INTO user (id, user_name, nick_name, user_type, email, phone_number, gender, avatar,avatar_type, password, status, login_ip, login_date, point, organization_id,create_time, update_time) VALUES<foreach collection="userDOBatch" item="userDO" separator=",">(#{userDO.id}, #{userDO.userName}, #{userDO.nickName}, #{userDO.userType},#{userDO.email}, #{userDO.phoneNumber}, #{userDO.gender}, #{userDO.avatar},#{userDO.avatarType}, #{userDO.password}, #{userDO.status}, #{userDO.loginIp},#{userDO.loginDate}, #{userDO.point}, #{userDO.organizationId},#{userDO.createTime}, #{userDO.updateTime})</foreach>
</insert>