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

Spring Boot Excel数据导入数据库实现详解

news 2025/8/12 10:20:54

一、核心实现流程

    @PostMapping("/parsefile/{fileName}")public Result parsefile(@PathVariable String fileName, HttpSession session) {try {// 1. 构建文件路径Path filePath = Paths.get(excelpath, fileName);File excelFile = filePath.toFile();// 2. 读取Excel数据List<List<String>> excelData = ExcelXlsUtil.readXlsFromFile(excelFile);System.out.println("读取到Excel行数: " + excelData.size());// 3. 验证数据格式if (excelData.size() < 3) {return Result.error("Excel格式错误：至少需要3行数据（表头+数据）");}// 4. 解析数据List<Person> persons = new ArrayList<>();Map<String, Integer> communityCache = new HashMap<>();// 列索引映射（根据Excel结构）Map<String, Integer> columnMap = new HashMap<>();columnMap.put("小区名称", 1);columnMap.put("所属楼栋", 2);columnMap.put("房号", 3);columnMap.put("姓名", 4);columnMap.put("性别", 5);columnMap.put("手机号码", 6);columnMap.put("居住性质", 7);columnMap.put("备注", 8);// 获取当前用户User currentUser = (User) session.getAttribute("user");String creater = currentUser != null ? currentUser.getUsername() : "system";// 5. 处理数据行int successCount = 0;int errorCount = 0;for (int i = 2; i < excelData.size(); i++) {List<String> row = excelData.get(i);if (row.isEmpty()) continue;try {// 获取小区名称String communityName = getCellValue(row, columnMap.get("小区名称"));if (StringUtils.isEmpty(communityName)) {System.out.println("第 " + (i+1) + " 行: 小区名称为空，跳过");errorCount++;continue;}// 获取或创建小区IDInteger communityId = communityCache.get(communityName);if (communityId == null) {Community community = communityMapper.selectByName(communityName);if (community == null) {// 创建新小区community = new Community();community.setCommunityName(communityName);community.setTermCount(0);community.setSeq(0);community.setCreater(creater);community.setCreateTime(LocalDateTime.now());communityMapper.insert(community);communityId = community.getCommunityId();System.out.println("创建新小区: " + communityName + ", ID=" + communityId);} else {communityId = community.getCommunityId();}communityCache.put(communityName, communityId);}// 创建人员对象Person person = new Person();person.setCommunityId(communityId);person.setTermName(getCellValue(row, columnMap.get("所属楼栋")));person.setHouseNo(getCellValue(row, columnMap.get("房号")));person.setUserName(getCellValue(row, columnMap.get("姓名")));person.setSex(getCellValue(row, columnMap.get("性别")));person.setMobile(getCellValue(row, columnMap.get("手机号码")));person.setPersonType(getCellValue(row, columnMap.get("居住性质")));person.setRemark(getCellValue(row, columnMap.get("备注")));person.setState(1);person.setCreater(creater);person.setCreateTime(LocalDateTime.now());// 关键修复：设置空值字段person.setFaceUrl("");person.setFaceBase("");persons.add(person);successCount++;} catch (Exception e) {System.err.println("处理第 " + (i+1) + " 行时出错: " + e.getMessage());errorCount++;}}System.out.println("解析完成: 成功 " + successCount + " 条, 失败 " + errorCount + " 条");// 6. 批量保存到数据库if (!persons.isEmpty()) {System.out.println("开始批量插入 " + persons.size() + " 条记录...");personService.saveBatch(persons);System.out.println("批量插入完成");String msg = String.format("导入成功！有效数据: %d条, 失败: %d条", successCount, errorCount);return Result.ok().put("data", msg).put("status", "success");}return Result.error("未找到有效数据");} catch (Exception e) {e.printStackTrace();// 特殊处理数据库约束异常Throwable rootCause = e;while (rootCause.getCause() != null) {rootCause = rootCause.getCause();}if (rootCause instanceof SQLException) {SQLException sqlEx = (SQLException) rootCause;String errorMsg = "数据库操作失败: " + sqlEx.getMessage();// 识别特定错误if (sqlEx.getMessage().contains("doesn't have a default value")) {String field = sqlEx.getMessage().replace("Field ", "").replace(" doesn't have a default value", "").replace("'", "");errorMsg = "数据库字段 '" + field + "' 不能为空，请检查数据或表结构";}return Result.error(errorMsg);}return Result.error("导入失败: " + e.getMessage());}}// 辅助方法：安全获取单元格值private String getCellValue(List<String> row, Integer index) {if (index == null || index >= row.size()) return "";String value = row.get(index);return value != null ? value.trim() : "";}

二、关键技术组件解析

Excel解析工具

// ExcelXlsUtil.java
public static List<List<String>> readXlsFromFile(File file) {List<List<String>> data = new ArrayList<>();try (HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file))) {HSSFSheet sheet = workbook.getSheetAt(0);DataFormatter formatter = new DataFormatter();for (Row row : sheet) {List<String> rowData = new ArrayList<>();for (Cell cell : row) {rowData.add(formatter.formatCellValue(cell));}data.add(rowData);}}return data;
}