智慧社区：居民信息Excel导入数据库

目录

1.技术选型

Apache POI

EasyExcel（阿里开源流式方案）

Easy-POI（注解驱动方案）

JXL（轻量级旧版方案）

前后端协作方案（SpreadJS + 后端）

对比表

2.业务逻辑

3.代码实现

1.技术选型

excel的导入导出主要有5种：

Apache POI

核心特点：

• 官方底层库，支持.xls（HSSF）和.xlsx（XSSF/SXSSF）格式34

• 提供完整API（单元格操作、公式计算、样式控制）

• 缺点：

  • 内存消耗大（XSSF全内存加载；SXSSF流式写入可缓解）

  • 代码量较大（需手动处理行列和类型转换）

适用场景：大数据量导入导出（>10万行）、Spring Boot项目快速集成

EasyExcel（阿里开源流式方案）

核心优化：

• 解决POI内存溢出：基于事件模型逐行解析/写入26

• 注解驱动：@ExcelProperty映射字段与列

• 监听器机制：分批处理数据（如每1000条入库一次）

适用场景：导出含子表格的报表（如订单+商品明细）、需要表头校验的场景

Easy-POI（注解驱动方案）

扩展功能：

• 基于POI封装，简化复杂结构导出（如一对多嵌套表）4

• 注解配置：@Excel定义列名/格式，@ExcelCollection处理嵌套集合

• 校验支持：集成Hibernate Validator

JXL（轻量级旧版方案）

特点与限制：

• 仅支持旧版.xls格式（最大65536行）57

• API简洁但功能弱（不支持公式、条件格式等）

• 适合小数据量快速操作

适用场景：遗留系统维护、无需新Excel格式的简单导出

前后端协作方案（SpreadJS + 后端）

实现模式：

• 前端：SpreadJS库实现Excel渲染/编辑8

• 后端：仅负责文件存储和传输（无需解析内容）

• 流程：

  1. 前端导出JSON → 后端存为Excel

  2. 后端返回Excel二进制流 → 前端解析渲染

优势：复杂表格交互（如合并单元格、图表）的在线编辑。

对比表

我们使用Apache POI，剩下的方式后面会一一总结。

2.业务逻辑

上传模板文件personInfo.xls

模板文件如图：

导入成功后：

1791为最新的ID

3.代码实现

上传excel的代码：

/** * excel上传 * @param uploadExcel * @return * @throws IOException */ @PostMapping("/excelUpload") public Result excelUpload(MultipartFile uploadExcel) throws IOException { String originalFilename = uploadExcel.getOriginalFilename(); if(originalFilename==null || originalFilename.equals("")){ return Result.error("上传文件为空"); }else{ UUID uuid = UUID.randomUUID(); String fileExtension = originalFilename.substring(originalFilename.lastIndexOf(".") + 1); String fileName = uuid.toString() + "." + fileExtension; File file = new File(excel,fileName); //如果目录不存在就创建一个 if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } uploadExcel.transferTo(file); return Result.ok().put("data",fileName); } }

导入的接口：

/** * 数据导入操作 * @param fileName Excel文件名 * @return 导入结果 */ @PostMapping("/parsefile/{fileName}") public Result parseFile(HttpSession session,@PathVariable String fileName) throws Exception { // 第一部分：初始化POI对象 POIFSFileSystem fs = null; // POI文件系统对象，用于处理Excel文件 HSSFWorkbook wb = null; // Excel工作簿对象 try { String basePath = excel + fileName; // 构建Excel文件的完整路径 // 创建文件系统对象并打开Excel文件 fs = new POIFSFileSystem(new FileInputStream(basePath)); // 基于文件系统创建工作簿 wb = new HSSFWorkbook(fs); } catch (Exception e) { e.printStackTrace(); // 文件读取异常处理 } // 第二部分：读取Excel数据到二维数组 HSSFSheet sheet = wb.getSheetAt(0); // 获取第一个工作表 Object[][] data = null; // 声明二维数组用于存储Excel数据 int r = sheet.getLastRowNum()+1; // 获取总行数（索引从0开始，所以+1） int c = sheet.getRow(0).getLastCellNum(); // 获取第一行的总列数 int headRow = 2; // 表头行数（跳过前2行） data = new Object[r - headRow][c]; // 创建二维数组，大小为（总行数-表头行数）×列数 // 遍历Excel行和列，读取数据 for (int i = headRow; i < r; i++) { // 从第3行开始遍历（跳过表头） HSSFRow row = sheet.getRow(i); // 获取当前行对象 for (int j = 0; j < c; j++) { // 遍历当前行的每一列 HSSFCell cell = null; // 单元格变量 try { cell = row.getCell(j); // 获取当前单元格 try { // 使用DataFormatter将单元格值统一转为字符串格式 DataFormatter dataFormater = new DataFormatter(); String a = dataFormater.formatCellValue(cell); // 格式化单元格值为字符串 data[i - headRow][j] = a; // 存储到二维数组对应位置 } catch (Exception e) { // 格式化异常处理 data[i-headRow][j] = ""; // 先设置为空字符串 // 特殊处理第一列（ID列） if(j==0){ try { // 尝试获取数值型单元格的值 double d = cell.getNumericCellValue(); data[i - headRow][j] = (int)d + ""; // 转换为整数字符串 }catch(Exception ex){ data[i-headRow][j] = ""; // 转换失败保持为空字符串 } } } } catch (Exception e) { // 单元格读取异常 System.out.println("i="+i+";j="+j+":"+e.getMessage()); // 打印异常信息 } } } // 第三部分：数据验证和导入到数据库 int row = data.length; // 获取数据行数 int col = 0; // 列计数器 String errinfo = ""; // 错误信息字符串 headRow = 3; // 调整表头行数为3（用于错误提示） // 从session中获取当前用户信息 User user =(User) session.getAttribute("user"); errinfo = ""; // 重置错误信息 // 遍历二维数组中的每一行数据 for (int i = 0; i < row; i++) { Person single = new Person(); // 创建人员实体对象 single.setPersonId(0); // 设置ID为0（数据库自增） single.setState(1); // 设置状态为1（启用） single.setFaceUrl(""); // 设置头像URL为空 try { col=1; // 从第2列开始（跳过Excel中的ID列） // 验证第2列：小区名称是否存在 String communityName = data[i][col++].toString(); // 获取小区名称并移动列指针 QueryWrapper<Community> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("community_name", communityName); // 构建查询条件 Community community = this.communityService.getOne(queryWrapper); // 查询小区 if( community == null){ // 小区不存在 errinfo += "Excel文件第" + (i + headRow) + "行小区名称不存在！"; return Result.ok().put("status", "fail").put("data", errinfo); // 返回错误 } single.setCommunityId(community.getCommunityId()); // 设置小区ID // 设置第3列：楼栋名称 single.setTermName(data[i][col++].toString()); // 设置第4列：房号 single.setHouseNo(data[i][col++].toString()); // 设置第5列：姓名 single.setUserName(data[i][col++].toString()); // 设置第6列：性别 single.setSex(data[i][col++].toString()); // 设置第7列：手机号码 single.setMobile(data[i][col++].toString()); // 设置第8列：人员类型 single.setPersonType(data[i][col++].toString()); // 设置第9列：备注 single.setRemark(data[i][col++].toString()); // 设置创建信息 single.setCreater(user.getUsername()); // 创建人 single.setCreateTime(new Date()); // 创建时间 // 保存到数据库 this.personService.save(single); } catch (Exception e) { e.printStackTrace(); // 单行数据处理异常 } } // 第四部分：返回导入成功结果 return Result.ok().put("status", "success").put("data","数据导入完成！"); }

debug走一遍：

导入之前会先把xls文件上传：

首先上传时会先用uuid对文件重新命名：

然后找到存放该文件的目录：

目录的值在yaml配置文件中配置了，通过@Value注入：

上传完毕之后进入导入逻辑：

首先初始化好HSSFWorkbook和POIFSDileSystem对象

//POIFSFileSystem 是 Apache POI 库中的核心类 //专门用于处理 OLE 2 Compound Document Format（微软复合文档格式） //是 Java 开发中处理传统 Microsoft Office 文档（如 .xls, .doc, .ppt）的基础组件 //HSSFWorkbook 是 Apache POI 库中处理 Microsoft Excel 格式（.xls 文件）的核心类 // 作为 Horrible SpreadSheet Format 的缩写，它提供了完整的 API 来创建、读取和修改传统 Excel // 二进制文件。

然后拼接好路径：

然后根据这个路径打开excel文件和基于文件系统创建工作簿对象

第一步准备工作做完之后，就可以开始读取excel数据了

这部分是用一个二维数组读取表格

// 第二部分：读取Excel数据到二维数组 HSSFSheet sheet = wb.getSheetAt(0); // 获取第一个工作表 Object[][] data = null; // 声明二维数组用于存储Excel数据 int r = sheet.getLastRowNum()+1; // 获取总行数（索引从0开始，所以+1） int c = sheet.getRow(0).getLastCellNum(); // 获取第一行的总列数 int headRow = 2; // 表头行数（跳过前2行） data = new Object[r - headRow][c]; // 创建二维数组，大小为（总行数-表头行数）×列数 // 遍历Excel行和列，读取数据 for (int i = headRow; i < r; i++) { // 从第3行开始遍历（跳过表头） HSSFRow row = sheet.getRow(i); // 获取当前行对象 for (int j = 0; j < c; j++) { // 遍历当前行的每一列 HSSFCell cell = null; // 单元格变量 try { cell = row.getCell(j); // 获取当前单元格 try { // 使用DataFormatter将单元格值统一转为字符串格式 DataFormatter dataFormater = new DataFormatter(); String a = dataFormater.formatCellValue(cell); // 格式化单元格值为字符串 data[i - headRow][j] = a; // 存储到二维数组对应位置 } catch (Exception e) { // 格式化异常处理 data[i-headRow][j] = ""; // 先设置为空字符串 // 特殊处理第一列（ID列） if(j==0){ try { // 尝试获取数值型单元格的值 double d = cell.getNumericCellValue(); data[i - headRow][j] = (int)d + ""; // 转换为整数字符串 }catch(Exception ex){ data[i-headRow][j] = ""; // 转换失败保持为空字符串 } } } } catch (Exception e) { // 单元格读取异常 System.out.println("i="+i+";j="+j+":"+e.getMessage()); // 打印异常信息 } } }

getSheetAt(0)的原因是因为我们的表在sheet1是工作簿的第零个

获取好总行数和总列数之后声明一个二维数组读取数据

要从第三行开始，因为数据部分在第三行。

读取完之后就是第三部分：

用person类对象single去读取二维数组的数据，然后保存到数据库。

要注意的点是，要查询小区名字，看看该小区是否存在，如果不存在，直接返回错误

// 第三部分：数据验证和导入到数据库 int row = data.length; // 获取数据行数 int col = 0; // 列计数器 String errinfo = ""; // 错误信息字符串 headRow = 3; // 调整表头行数为3（用于错误提示） // 从session中获取当前用户信息 User user =(User) session.getAttribute("user"); errinfo = ""; // 重置错误信息 // 遍历二维数组中的每一行数据 for (int i = 0; i < row; i++) { Person single = new Person(); // 创建人员实体对象 single.setPersonId(0); // 设置ID为0（数据库自增） single.setState(1); // 设置状态为1（启用） single.setFaceUrl(""); // 设置头像URL为空 try { col=1; // 从第2列开始（跳过Excel中的ID列） // 验证第2列：小区名称是否存在 String communityName = data[i][col++].toString(); // 获取小区名称并移动列指针 QueryWrapper<Community> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("community_name", communityName); // 构建查询条件 Community community = this.communityService.getOne(queryWrapper); // 查询小区 if( community == null){ // 小区不存在 errinfo += "Excel文件第" + (i + headRow) + "行小区名称不存在！"; return Result.ok().put("status", "fail").put("data", errinfo); // 返回错误 } single.setCommunityId(community.getCommunityId()); // 设置小区ID // 设置第3列：楼栋名称 single.setTermName(data[i][col++].toString()); // 设置第4列：房号 single.setHouseNo(data[i][col++].toString()); // 设置第5列：姓名 single.setUserName(data[i][col++].toString()); // 设置第6列：性别 single.setSex(data[i][col++].toString()); // 设置第7列：手机号码 single.setMobile(data[i][col++].toString()); // 设置第8列：人员类型 single.setPersonType(data[i][col++].toString()); // 设置第9列：备注 single.setRemark(data[i][col++].toString()); // 设置创建信息 single.setCreater(user.getUsername()); // 创建人 single.setCreateTime(new Date()); // 创建时间 // 保存到数据库 this.personService.save(single); } catch (Exception e) { e.printStackTrace(); // 单行数据处理异常 } }