当前位置： 首页 > news >正文

08_Excel 导入 - 用户信息批量导入

news 2025/7/3 13:23:39

08_Excel 导入 - 用户信息批量导入

1. VO 类

java复制编辑@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserInfoBatch4ExcelReq {@ExcelProperty(value = "用户姓名")@Schema(description = "用户姓名")private String userName;@ExcelProperty(value = "用户性别（男/女）")@Schema(description = "用户性别")private String gender;@ExcelProperty(value = "出生日期")@Schema(description = "出生日期")private String birthDate;@ExcelProperty(value = "电子邮箱")@Schema(description = "电子邮箱")private String email;@ExcelProperty(value = "联系电话")@Schema(description = "联系电话")private String phoneNumber;@ExcelProperty(value = "地址")@Schema(description = "地址")private String address;@ExcelProperty(value = "职位")@Schema(description = "职位")private String jobTitle;@ExcelProperty(value = "部门")@Schema(description = "部门")private String department;@ExcelProperty(value = "入职日期")@Schema(description = "入职日期")private String joinDate;@ExcelProperty(value = "是否为管理员（是/否）")@Schema(description = "是否为管理员")private String isAdmin;@ExcelIgnore@Schema(description = "用户账号")private String account;@ExcelIgnore@Schema(description = "用户密码")private String password;
}

说明：

  • @ExcelProperty：Excel 表格中列的标题。
  • @Schema：为 Swagger 或其他文档生成工具提供字段描述。

2. DO 类

java复制编辑@EqualsAndHashCode(callSuper = true)
@Data
@TableName(value = "user_info", autoResultMap = true)
@NoArgsConstructor
@AllArgsConstructor
public class UserInfoVo extends BaseVo {@ExcelProperty(value = "序号", index = 0)@TableId(value = "id", type = IdType.AUTO)@Schema(description = "主键id")private Long id;@ExcelProperty(value = "用户姓名", index = 1)@Schema(description = "用户姓名")private String userName;@ExcelProperty(value = "用户性别", index = 2)@Schema(description = "用户性别")private String gender;@ExcelProperty(value = "出生日期", index = 3)@Schema(description = "出生日期")private String birthDate;@ExcelProperty(value = "电子邮箱", index = 4)@Schema(description = "电子邮箱")private String email;@ExcelProperty(value = "联系电话", index = 5)@Schema(description = "联系电话")private String phoneNumber;@ExcelProperty(value = "地址", index = 6)@Schema(description = "地址")private String address;@ExcelProperty(value = "职位", index = 7)@Schema(description = "职位")private String jobTitle;@ExcelProperty(value = "部门", index = 8)@Schema(description = "部门")private String department;@ExcelProperty(value = "入职日期", index = 9)@Schema(description = "入职日期")private String joinDate;@ExcelProperty(value = "是否为管理员", index = 10)@Schema(description = "是否为管理员")private String isAdmin;@ExcelIgnore@Schema(description = "用户账号")private String account;@ExcelIgnore@Schema(description = "用户密码")private String password;@ExcelIgnore@Schema(description = "是否删除")private Boolean isDeleted;
}

说明：

  • 该类用于将导入的 Excel 数据转换为持久化存储对象（UserInfoVo）进行数据库操作。

3. Controller 层

java复制编辑@Tag(name = "用户管理")
@RestController
@RequestMapping("/user")
@RequiredArgsConstructor
public class UserController {private final UserInfoService userInfoService;@Operation(summary = "批量用户信息导入")@RequestMapping(value = "/import", method = RequestMethod.POST)public CommonResult<Long> userInfoBatchInsert4Excel(@RequestParam("file") MultipartFile file) {return CommonResult.success(userInfoService.userInfoBatchInsert4Excel(file));}
}

说明：

  • @RequestMapping 用于映射 HTTP 请求。
  • @Operation 描述接口功能。
  • MultipartFile 用于接收上传的 Excel 文件。

4. Service 接口

java复制编辑public interface UserInfoService {Long userInfoBatchInsert4Excel(MultipartFile file);
}

说明：

  • 定义了一个方法用于批量导入用户信息。

5. Service 实现类

java复制编辑@Service
@RequiredArgsConstructor
public class UserInfoServiceImpl implements UserInfoService {private final UserMapper userMapper;private final FileService fileService;private final ExcelImageUtil excelImageUtil;private final ThreadPoolTaskExecutor threadPoolTaskExecutor;@Overridepublic Long userInfoBatchInsert4Excel(MultipartFile file) {try {if (file.getSize() > 52428800) {throw new BusinessException(ResultCode.FAILED, "导入失败，用户批量导入Excel文件大小最多为50MB");}byte[] excelBytes = file.getBytes();Map<String, byte[]> imageData = excelImageUtil.extractImages(excelBytes);UserInfoExcelListener listener = new UserInfoExcelListener(threadPoolTaskExecutor,userMapper,imageData,fileService);FastExcel.read(new ByteArrayInputStream(excelBytes), UserInfoBatch4ExcelReq.class, listener).sheet().doRead();return listener.getSuccessCount();} catch (Exception e) {throw new BusinessException(ResultCode.FAILED, "导入失败，请检查 Excel 文件是否正确！");}}
}

说明：

  • 通过 FastExcel 读取并解析 Excel 文件。
  • 进行图片提取并处理。
  • 使用自定义的 UserInfoExcelListener 进行批量导入。

6. Listener 监听类

@Slf4j
@RequiredArgsConstructor
@Getter
public class UserInfoExcelListener extends AnalysisEventListener<UserInfoBatch4ExcelReq> {private final ThreadPoolTaskExecutor threadPoolTaskExecutor;private final UserMapper userMapper;private final FileService fileService;private final Map<String, byte[]> imageData;private Integer rowIndex = 1;private Long successCount = 0L;@Overridepublic void invoke(UserInfoBatch4ExcelReq data, AnalysisContext context) {rowIndex = context.readRowHolder().getRowIndex();// 校验数据并进行转换validateAndConvert(data);try {// 如果包含图片字段，解析图片if (data.getProfileImage() != null && !data.getProfileImage().isEmpty()) {String imageUrl = resolveImageUrlFromDispImg(data.getProfileImage());data.setProfileImage(imageUrl); // 将解析后的图片URL赋值回去}// 没问题，插入数据UserInfoVo userInfoVo = new UserInfoVo();BeanUtils.copyProperties(data, userInfoVo);// 插入用户信息userMapper.insert(userInfoVo);successCount++;} catch (Exception e) {String msg = e.getMessage();if (msg != null && msg.contains("Duplicate entry")) {// 解析出 "xx用户id-用户名-1（是否参与联合唯一索引校验）"Pattern pattern = Pattern.compile("Duplicate entry '(.+?)' for key");Matcher matcher = pattern.matcher(msg);if (matcher.find()) {String dupKey = matcher.group(1);String[] parts = dupKey.split("-", 3);if (parts.length == 3) {String userIdStr = parts[0];String userName = parts[1];// 构造具体的错误信息String errorMessage = String.format("当前用户存在相同用户信息，重复用户名：《%s》，请移除后再重新提交！",userName);throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, errorMessage, null));}}}throw new BusinessException(ResultCode.FAILED, "导入失败，请检查格式是否正确，如尝试多次仍然报此错误，请联系管理员！");}}private void validateAndConvert(UserInfoBatch4ExcelReq data) {// ========== 基础校验 ============================if (StringUtils.isNullOrEmpty(data.getUserName())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "用户姓名不能为空", null));}if (StringUtils.isNullOrEmpty(data.getGender())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "用户性别不能为空", null));}if (StringUtils.isNullOrEmpty(data.getBirthDate())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "出生日期不能为空", null));}if (StringUtils.isNullOrEmpty(data.getEmail())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "电子邮箱不能为空", null));}if (StringUtils.isNullOrEmpty(data.getPhoneNumber())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "联系电话不能为空", null));}if (StringUtils.isNullOrEmpty(data.getAddress())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "地址不能为空", null));}if (StringUtils.isNullOrEmpty(data.getJobTitle())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "职位不能为空", null));}if (StringUtils.isNullOrEmpty(data.getDepartment())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "部门不能为空", null));}if (StringUtils.isNullOrEmpty(data.getJoinDate())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "入职日期不能为空", null));}if (StringUtils.isNullOrEmpty(data.getIsAdmin())) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "是否为管理员不能为空", null));}}private String buildImportErrorMessage(int rowIndex, String reason, String correctFormat) {StringBuilder sb = new StringBuilder();sb.append("导入失败，已成功导入 ").append(successCount).append(" 条记录。");sb.append("异常位置：第").append(rowIndex).append("行数据处理异常。");sb.append("异常原因：").append(reason);if (!StringUtils.isNullOrEmpty(correctFormat)) {sb.append("。正确格式：").append(correctFormat).append("。");}return sb.toString();}/*** 如果字段是类似 =DISPIMG("ID_293ECFFE4A5D4C11996580C8502E1816",1)* 则尝试提取ID，从imageData里取图片上传返回url* 否则直接返回原字符串*/private String resolveImageUrlFromDispImg(String fieldValue) {if (fieldValue == null || !fieldValue.contains("DISPIMG")) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "图片解析失败，请检查 Excel 中插入的图片", null));}String imgId = extractDispImgId(fieldValue);if (imgId == null || !imageData.containsKey(imgId)) {throw new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "图片解析失败，请检查 Excel 中插入的图片", null));}return uploadImage(imageData.get(imgId));}private String extractDispImgId(String formula) {if (formula != null) {formula = formula.trim();if (formula.startsWith("=")) {formula = formula.substring(1); // 去掉前面等号}if (formula.startsWith("DISPIMG")) {int start = formula.indexOf("\"");int end = formula.lastIndexOf("\"");if (start >= 0 && end > start) {return formula.substring(start + 1, end);}}}return null;}private String uploadImage(byte[] imgBytes) {if (imgBytes.length > 2097152) { // 最大 2MBthrow new BusinessException(ResultCode.FAILED, buildImportErrorMessage(rowIndex, "图片大小不能超过2MB", null));}String extension = FileTypeUtil.getImageExtension(imgBytes);String fileName = UUID.randomUUID() + extension;MultipartFile multipartFile = new MockMultipartFile(fileName, fileName, "image/" + extension.replace(".", ""), imgBytes);return fileService.uploadFile(multipartFile, null);}
}

说明：

  • 监听器负责逐行处理 Excel 数据，进行字段校验、数据转换并保存。
  • 支持图片字段的解析和上传。

7. WPS excel 图片提取工具类

Component
public class ExcelImageUtil {/*** 提取 WPS 表格中的内嵌图片（=DISPIMG(...)）。** @param excelData Excel 文件的字节数组* @return 图片映射，键为图片 ID，值为图片字节数组* @throws IOException 如果读取失败*/public Map<String, byte[]> extractImages(byte[] excelData) throws IOException {Map<String, String> idToRidMap = new HashMap<>();Map<String, String> ridToTargetMap = new HashMap<>();Map<String, byte[]> imageMap = new HashMap<>();// 1. 解析 cellimages.xml 和其 rels 文件Map<String, byte[]> zipEntryMap = new HashMap<>();try (ZipInputStream zipInputStream = new ZipInputStream(new ByteArrayInputStream(excelData))) {ZipEntry zipEntry;while ((zipEntry = zipInputStream.getNextEntry()) != null) {String entryName = zipEntry.getName();if (entryName.equals("xl/cellimages.xml") || entryName.equals("xl/_rels/cellimages.xml.rels") || entryName.startsWith("xl/media/")) {byte[] entryData = IOUtils.toByteArray(zipInputStream);zipEntryMap.put(entryName, entryData);}zipInputStream.closeEntry();}}// 2. 解析 cellimages.xmlbyte[] cellImagesData = zipEntryMap.get("xl/cellimages.xml");if (cellImagesData != null) {JSONObject json = XML.toJSONObject(new String(cellImagesData, "UTF-8"));JSONObject cellImages = json.getJSONObject("etc:cellImages");if (cellImages != null) {Object cellImageObj = cellImages.get("etc:cellImage");JSONArray cellImageArray = new JSONArray();if (cellImageObj instanceof JSONArray) {cellImageArray = (JSONArray) cellImageObj;} else if (cellImageObj instanceof JSONObject) {cellImageArray.add(cellImageObj);}for (int i = 0; i < cellImageArray.size(); i++) {JSONObject cellImage = cellImageArray.getJSONObject(i);JSONObject pic = cellImage.getJSONObject("xdr:pic");if (pic != null) {JSONObject nvPicPr = pic.getJSONObject("xdr:nvPicPr");if (nvPicPr != null) {JSONObject cNvPr = nvPicPr.getJSONObject("xdr:cNvPr");String name = cNvPr.getStr("name");JSONObject blipFill = pic.getJSONObject("xdr:blipFill");if (blipFill != null) {JSONObject blip = blipFill.getJSONObject("a:blip");String embed = blip.getStr("r:embed");idToRidMap.put(name, embed);}}}}}}// 3. 解析 cellimages.xml.relsbyte[] relsData = zipEntryMap.get("xl/_rels/cellimages.xml.rels");if (relsData != null) {JSONObject json = XML.toJSONObject(new String(relsData, "UTF-8"));JSONObject relationships = json.getJSONObject("Relationships");if (relationships != null) {Object relationshipObj = relationships.get("Relationship");JSONArray relationshipArray = new JSONArray();if (relationshipObj instanceof JSONArray) {relationshipArray = (JSONArray) relationshipObj;} else if (relationshipObj instanceof JSONObject) {relationshipArray.add(relationshipObj);}for (int i = 0; i < relationshipArray.size(); i++) {JSONObject rel = relationshipArray.getJSONObject(i);String id = rel.getStr("Id");String target = rel.getStr("Target");ridToTargetMap.put(id, target);}}}// 4. 根据 rId -> target 映射找到 media 文件for (Map.Entry<String, String> entry : idToRidMap.entrySet()) {String dispImgId = entry.getKey();String rid = entry.getValue();String targetPath = "xl/" + ridToTargetMap.getOrDefault(rid, "");byte[] imageData = zipEntryMap.get(targetPath);if (imageData != null) {imageMap.put(dispImgId, imageData);}}return imageMap;}
}
http://www.lryc.cn/news/579137.html

相关文章：

  • 【Erdas实验教程】024：遥感图像辐射增强（亮度反转Brightness Inversion）
  • 优雅草蜻蜓R实时音视频会议系统技术规划与全球RTC开源技术全景分析·优雅草卓伊凡|麻子|贝贝|clam
  • 远程问诊选声网视频通话，画面不卡音质更清楚
  • iOS重构期调试实战：架构升级中的性能与数据保障策略
  • 在mac下手动编译迁移的android版webrtc组件
  • Docker Dify安装 完整版本
  • 「Java流程控制」跳转语句
  • 【数据结构】排序算法：冒泡与快速
  • OWASP Top 10 是什么？
  • 胡兵创立时尚生活频道《HUBING SELECTS胡兵智选》担任主编深耕智选生活
  • java实现发送短信
  • QT6 源（147）模型视图架构里的表格窗体 QTableWidget 的范例代码举例，以及其条目 QTableWidgetItem 类型的源代码。
  • 【嵌入式电机控制#6】编码器原理与内部构造
  • HTTP 协议深入理解
  • Django 安装使用教程
  • Day3.常见音频场景
  • 动手学Dify：自定义工具与沙盒
  • 澳鹏重磅发布MediGo医疗大模型数据开发平台 破解医疗AI数据瓶颈
  • 【docker部署】在服务器上使用docker
  • 【深度学习-Day 34】CNN实战：从零构建CIFAR-10图像分类器（PyTorch）
  • CISSP知识点汇总-安全与风险管理
  • 智能学号抽取系统 V3.7.5 —— 一个基于 Vue.js 的交互式网页应用
  • 小架构step系列02：搭建工程
  • 智能检测原理和架构
  • STM32WB55VGY6TR 蓝牙OTA升级
  • ZED相机与Foxglove集成：加速机器人视觉调试效率的实用方案
  • 观测云 × AWS SSO：权限治理可观测实践
  • 计算机组成笔记：缓存替换算法
  • [202106][凤凰架构][构建可靠的大型分布式系统][周志明][著]
  • 车载软件架构 -- SOA服务分层设计原则