Java中大量数据Excel导入导出的实现方案

一、原理解析

1. Excel文件格式解析

• XLS格式：基于二进制格式，使用Apache POI的HSSF组件处理
• XLSX格式：基于XML的压缩格式，使用Apache POI的XSSF组件处理

2. 技术选型

• Apache POI：最常用的Java Excel处理库
• EasyExcel：阿里巴巴开源，适合大数据量处理，内存占用低
• JExcelAPI：轻量级，但功能较少

3. 大数据量处理策略

• 流式读取：逐行处理，避免内存溢出
• 分批次处理：分页读取，批量入库
• 异步处理：后台线程处理，不阻塞主线程

二、实际应用场景

1. 导入场景

• 用户数据批量导入
• 产品目录更新
• 订单数据同步
• 财务报表导入

2. 导出场景

• 数据报表生成
• 数据备份
• 数据交换
• 批量打印

三、完整实现方案

项目结构

excel-demo/ ├── pom.xml ├── src/ │ ├── main/ │ │ ├── java/ │ │ │ └── com/ │ │ │ └── example/ │ │ │ ├── ExcelApplication.java │ │ │ ├── controller/ │ │ │ │ └── ExcelController.java │ │ │ ├── service/ │ │ │ │ ├── ExcelService.java │ │ │ │ └── impl/ │ │ │ │ └── ExcelServiceImpl.java │ │ │ ├── dao/ │ │ │ │ └── UserDao.java │ │ │ ├── entity/ │ │ │ │ └── User.java │ │ │ ├── dto/ │ │ │ │ ├── ExcelImportResult.java │ │ │ │ └── ImportUserDTO.java │ │ │ └── listener/ │ │ │ └── UserExcelListener.java │ │ └── resources/ │ │ └── application.properties 

1. Maven依赖 (pom.xml)

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.example</groupId><artifactId>excel-demo</artifactId><version>1.0.0</version><packaging>jar</packaging><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.10</version></parent><properties><java.version>1.8</java.version></properties><dependencies><!-- Spring Boot --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- Apache POI --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency><!-- EasyExcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><!-- Lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- MySQL --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- MyBatis Plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.1</version></dependency><!-- 验证依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

2. 实体类 (User.java)

packagecom.example.entity;importcom.baomidou.mybatisplus.annotation.*;importlombok.Data;importjava.util.Date;@Data@TableName("t_user")publicclassUser{@TableId(type =IdType.AUTO)privateLong id;@TableField("user_name")privateString userName;@TableField("email")privateString email;@TableField("phone")privateString phone;@TableField("age")privateInteger age;@TableField("department")privateString department;@TableField("salary")privateDouble salary;@TableField(value ="create_time", fill =FieldFill.INSERT)privateDate createTime;@TableField(value ="update_time", fill =FieldFill.INSERT_UPDATE)privateDate updateTime;}

3. 导入DTO (ImportUserDTO.java)

packagecom.example.dto;importcom.alibaba.excel.annotation.ExcelProperty;importlombok.Data;importjavax.validation.constraints.*;@DatapublicclassImportUserDTO{@ExcelProperty("用户名")@NotBlank(message ="用户名不能为空")@Size(min =2, max =20, message ="用户名长度必须在2-20之间")privateString userName;@ExcelProperty("邮箱")@NotBlank(message ="邮箱不能为空")@Email(message ="邮箱格式不正确")privateString email;@ExcelProperty("手机号")@NotBlank(message ="手机号不能为空")@Pattern(regexp ="^1[3-9]\\d{9}$", message ="手机号格式不正确")privateString phone;@ExcelProperty("年龄")@NotNull(message ="年龄不能为空")@Min(value =18, message ="年龄必须大于等于18岁")@Max(value =65, message ="年龄必须小于等于65岁")privateInteger age;@ExcelProperty("部门")@NotBlank(message ="部门不能为空")privateString department;@ExcelProperty("薪资")@NotNull(message ="薪资不能为空")@DecimalMin(value ="0.0", message ="薪资必须大于等于0")privateDouble salary;// 行号，用于错误定位privateInteger rowIndex;// 导入结果信息privateString errorMsg;// 是否验证通过privateBoolean valid =true;}

4. 导入结果类 (ExcelImportResult.java)

packagecom.example.dto;importlombok.Data;importjava.util.List;@DatapublicclassExcelImportResult<T>{// 成功数量privateint successCount;// 失败数量privateint failCount;// 总数量privateint totalCount;// 失败数据列表privateList<T> failDataList;// 错误文件下载路径privateString errorFileUrl;// 导入耗时（毫秒）privateLong costTime;// 错误信息汇总privateString errorSummary;publicExcelImportResult(){this.costTime =0L;}publicvoidcalculate(){this.totalCount = successCount + failCount;}}

5. Excel监听器 (UserExcelListener.java)

packagecom.example.listener;importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.read.listener.ReadListener;importcom.alibaba.excel.util.ListUtils;importcom.example.dto.ImportUserDTO;importcom.example.service.ExcelService;importlombok.extern.slf4j.Slf4j;importjavax.validation.ConstraintViolation;importjavax.validation.Validation;importjavax.validation.Validator;importjava.util.ArrayList;importjava.util.List;importjava.util.Set;@Slf4jpublicclassUserExcelListenerimplementsReadListener<ImportUserDTO>{// 每隔1000条存储数据库，然后清理list，方便内存回收privatestaticfinalint BATCH_COUNT =1000;// 缓存的数据privateList<ImportUserDTO> cachedDataList =ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);privatefinalExcelService excelService;// 验证器privatefinalValidator validator =Validation.buildDefaultValidatorFactory().getValidator();// 错误数据列表privatefinalList<ImportUserDTO> errorDataList =newArrayList<>();// 成功计数privateint successCount =0;publicUserExcelListener(ExcelService excelService){this.excelService = excelService;}@Overridepublicvoidinvoke(ImportUserDTO data,AnalysisContext context){// 设置行号（从0开始，通常第0行是标题）Integer rowIndex = context.readRowHolder().getRowIndex()+1; data.setRowIndex(rowIndex);// 数据验证validateData(data);if(data.getValid()){ cachedDataList.add(data);// 达到BATCH_COUNT，存储一次数据库if(cachedDataList.size()>= BATCH_COUNT){saveData(); cachedDataList =ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}else{ errorDataList.add(data);}}@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext context){// 处理最后一批数据if(!cachedDataList.isEmpty()){saveData();} log.info("Excel解析完成，成功导入{}条数据，失败{}条数据", successCount, errorDataList.size());}privatevoidvalidateData(ImportUserDTO data){Set<ConstraintViolation<ImportUserDTO>> violations = validator.validate(data);if(!violations.isEmpty()){ data.setValid(false);StringBuilder errorMsg =newStringBuilder();for(ConstraintViolation<ImportUserDTO> violation : violations){ errorMsg.append(violation.getMessage()).append("; ");} data.setErrorMsg(errorMsg.toString());}}privatevoidsaveData(){try{// 批量保存数据int batchSuccessCount = excelService.batchSaveUsers(cachedDataList); successCount += batchSuccessCount; log.info("批量保存{}条数据成功", batchSuccessCount);}catch(Exception e){ log.error("批量保存数据失败", e);// 记录失败的数据for(ImportUserDTO data : cachedDataList){ data.setValid(false); data.setErrorMsg("系统错误: "+ e.getMessage()); errorDataList.add(data);}}}publicList<ImportUserDTO>getErrorDataList(){return errorDataList;}publicintgetSuccessCount(){return successCount;}}

6. Service层 (ExcelService.java & ExcelServiceImpl.java)

packagecom.example.service;importcom.example.dto.ExcelImportResult;importcom.example.dto.ImportUserDTO;importcom.example.entity.User;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.util.List;publicinterfaceExcelService{/** * 导入用户数据 */ExcelImportResult<ImportUserDTO>importUsers(MultipartFile file);/** * 批量保存用户 */intbatchSaveUsers(List<ImportUserDTO> userList);/** * 导出用户数据 */voidexportUsers(HttpServletResponse response);/** * 导出错误数据 */voidexportErrorData(List<ImportUserDTO> errorDataList,HttpServletResponse response);}

packagecom.example.service.impl;importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;importcom.example.dao.UserDao;importcom.example.dto.ExcelImportResult;importcom.example.dto.ImportUserDTO;importcom.example.entity.User;importcom.example.listener.UserExcelListener;importcom.example.service.ExcelService;importcom.baomidou.mybatisplus.core.conditions.query.QueryWrapper;importlombok.extern.slf4j.Slf4j;importorg.springframework.beans.BeanUtils;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.scheduling.annotation.Async;importorg.springframework.stereotype.Service;importorg.springframework.transaction.annotation.Transactional;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.net.URLEncoder;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importjava.util.concurrent.CompletableFuture;@Service@Slf4jpublicclassExcelServiceImplimplementsExcelService{@AutowiredprivateUserDao userDao;@Override@Transactional(rollbackFor =Exception.class)publicExcelImportResult<ImportUserDTO>importUsers(MultipartFile file){long startTime =System.currentTimeMillis();ExcelImportResult<ImportUserDTO> result =newExcelImportResult<>();try{// 创建监听器UserExcelListener listener =newUserExcelListener(this);// 读取ExcelEasyExcel.read(file.getInputStream(),ImportUserDTO.class, listener).sheet().headRowNumber(1)// 跳过标题行.doRead();// 设置结果 result.setSuccessCount(listener.getSuccessCount()); result.setFailCount(listener.getErrorDataList().size()); result.setFailDataList(listener.getErrorDataList()); result.calculate();// 如果有错误数据，生成错误报告if(!listener.getErrorDataList().isEmpty()){String errorSummary =generateErrorSummary(listener.getErrorDataList()); result.setErrorSummary(errorSummary);}}catch(IOException e){ log.error("读取Excel文件失败", e); result.setErrorSummary("文件读取失败: "+ e.getMessage());}long endTime =System.currentTimeMillis(); result.setCostTime(endTime - startTime);return result;}@Override@Transactional(rollbackFor =Exception.class)publicintbatchSaveUsers(List<ImportUserDTO> userList){if(userList ==null|| userList.isEmpty()){return0;}List<User> users =newArrayList<>();for(ImportUserDTO dto : userList){User user =newUser();BeanUtils.copyProperties(dto, user); user.setCreateTime(newDate()); user.setUpdateTime(newDate()); users.add(user);}// 批量插入int count = userDao.batchInsert(users); log.info("批量插入{}条用户数据", count);return count;}@OverridepublicvoidexportUsers(HttpServletResponse response){try{// 查询数据List<User> userList = userDao.selectList(newQueryWrapper<>());// 转换为DTOList<ImportUserDTO> exportData =newArrayList<>();for(User user : userList){ImportUserDTO dto =newImportUserDTO();BeanUtils.copyProperties(user, dto); exportData.add(dto);}// 设置响应头 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8");String fileName =URLEncoder.encode("用户数据导出","UTF-8").replaceAll("\\+","%20"); response.setHeader("Content-disposition","attachment;filename*=utf-8''"+ fileName +".xlsx");// 写入ExcelEasyExcel.write(response.getOutputStream(),ImportUserDTO.class).registerWriteHandler(newLongestMatchColumnWidthStyleStrategy())// 自动列宽.sheet("用户数据").doWrite(exportData);}catch(IOException e){ log.error("导出Excel失败", e);thrownewRuntimeException("导出失败");}}@OverridepublicvoidexportErrorData(List<ImportUserDTO> errorDataList,HttpServletResponse response){try{ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8");String fileName =URLEncoder.encode("导入错误数据","UTF-8").replaceAll("\\+","%20"); response.setHeader("Content-disposition","attachment;filename*=utf-8''"+ fileName +".xlsx");EasyExcel.write(response.getOutputStream(),ImportUserDTO.class).registerWriteHandler(newLongestMatchColumnWidthStyleStrategy()).sheet("错误数据").doWrite(errorDataList);}catch(IOException e){ log.error("导出错误数据失败", e);thrownewRuntimeException("导出失败");}}/** * 异步导入方法 */@AsyncpublicCompletableFuture<ExcelImportResult<ImportUserDTO>>importUsersAsync(MultipartFile file){returnCompletableFuture.completedFuture(importUsers(file));}privateStringgenerateErrorSummary(List<ImportUserDTO> errorDataList){StringBuilder summary =newStringBuilder(); summary.append("共发现").append(errorDataList.size()).append("条错误数据：\n");for(ImportUserDTO data : errorDataList){ summary.append("第").append(data.getRowIndex()).append("行: ").append(data.getErrorMsg()).append("\n");}return summary.toString();}}

7. DAO层 (UserDao.java)

packagecom.example.dao;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.example.entity.User;importorg.apache.ibatis.annotations.Insert;importorg.apache.ibatis.annotations.Mapper;importorg.apache.ibatis.annotations.Param;importjava.util.List;@MapperpublicinterfaceUserDaoextendsBaseMapper<User>{/** * 批量插入用户 */@Insert({"<script>","INSERT INTO t_user(user_name, email, phone, age, department, salary, create_time, update_time) VALUES ","<foreach collection='list' item='item' separator=','>","(#{item.userName}, #{item.email}, #{item.phone}, #{item.age}, ","#{item.department}, #{item.salary}, #{item.createTime}, #{item.updateTime})","</foreach>","</script>"})intbatchInsert(@Param("list")List<User> userList);}

8. Controller层 (ExcelController.java)

packagecom.example.controller;importcom.example.dto.ExcelImportResult;importcom.example.dto.ImportUserDTO;importcom.example.service.ExcelService;importio.swagger.annotations.Api;importio.swagger.annotations.ApiOperation;importlombok.extern.slf4j.Slf4j;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.http.ResponseEntity;importorg.springframework.web.bind.annotation.*;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.util.HashMap;importjava.util.Map;@RestController@RequestMapping("/api/excel")@Api(tags ="Excel导入导出管理")@Slf4jpublicclassExcelController{@AutowiredprivateExcelService excelService;@PostMapping("/import/users")@ApiOperation("导入用户数据")publicResponseEntity<Map<String,Object>>importUsers(@RequestParam("file")MultipartFile file){Map<String,Object> result =newHashMap<>();try{// 检查文件if(file.isEmpty()){ result.put("code",400); result.put("message","文件不能为空");returnResponseEntity.badRequest().body(result);}// 检查文件类型String fileName = file.getOriginalFilename();if(fileName ==null||(!fileName.toLowerCase().endsWith(".xls")&&!fileName.toLowerCase().endsWith(".xlsx"))){ result.put("code",400); result.put("message","只支持Excel文件");returnResponseEntity.badRequest().body(result);}// 执行导入ExcelImportResult<ImportUserDTO> importResult = excelService.importUsers(file); result.put("code",200); result.put("message","导入完成"); result.put("data", importResult);returnResponseEntity.ok(result);}catch(Exception e){ log.error("导入用户数据失败", e); result.put("code",500); result.put("message","导入失败: "+ e.getMessage());returnResponseEntity.internalServerError().body(result);}}@GetMapping("/export/users")@ApiOperation("导出用户数据")publicvoidexportUsers(HttpServletResponse response){try{ excelService.exportUsers(response);}catch(Exception e){ log.error("导出用户数据失败", e); response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);}}@PostMapping("/export/errors")@ApiOperation("导出错误数据")publicvoidexportErrorData(@RequestBodyExcelImportResult<ImportUserDTO> importResult,HttpServletResponse response){try{ excelService.exportErrorData(importResult.getFailDataList(), response);}catch(Exception e){ log.error("导出错误数据失败", e); response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);}}@GetMapping("/template/download")@ApiOperation("下载导入模板")publicvoiddownloadTemplate(HttpServletResponse response){try{// 这里可以提供一个空模板或者带说明的模板 excelService.exportUsers(response);// 暂时用导出方法，实际应创建专门的模板}catch(Exception e){ log.error("下载模板失败", e); response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);}}}

9. 启动类 (ExcelApplication.java)

packagecom.example;importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importorg.springframework.scheduling.annotation.EnableAsync;@SpringBootApplication@EnableAsyncpublicclassExcelApplication{publicstaticvoidmain(String[] args){SpringApplication.run(ExcelApplication.class, args);}}

10. 配置文件 (application.properties)

# 服务器配置 server.port=8080 server.servlet.context-path=/excel-demo # 数据库配置 spring.datasource.url=jdbc:mysql://localhost:3306/excel_demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai spring.datasource.username=root spring.datasource.password=your_password spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # MyBatis配置 mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml mybatis-plus.type-aliases-package=com.example.entity mybatis-plus.configuration.map-underscore-to-camel-case=true mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl # 上传文件大小限制 spring.servlet.multipart.max-file-size=100MB spring.servlet.multipart.max-request-size=100MB # 异步线程池配置 spring.task.execution.pool.core-size=10 spring.task.execution.pool.max-size=20 spring.task.execution.pool.queue-capacity=500 spring.task.execution.thread-name-prefix=excel-async- # 日志配置 logging.level.com.example=DEBUG 

11. SQL建表语句

-- 创建数据库CREATEDATABASEIFNOTEXISTS excel_demo DEFAULTCHARACTERSET utf8mb4;USE excel_demo;-- 用户表CREATETABLEIFNOTEXISTS t_user ( id BIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'主键ID', user_name VARCHAR(50)NOTNULLCOMMENT'用户名', email VARCHAR(100)NOTNULLCOMMENT'邮箱', phone VARCHAR(20)NOTNULLCOMMENT'手机号', age INTCOMMENT'年龄', department VARCHAR(100)COMMENT'部门', salary DECIMAL(10,2)COMMENT'薪资', create_time DATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', update_time DATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',INDEX idx_user_name (user_name),INDEX idx_email (email),INDEX idx_create_time (create_time))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='用户表';-- 导入日志表（可选）CREATETABLEIFNOTEXISTS t_import_log ( id BIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'主键ID', file_name VARCHAR(255)COMMENT'文件名', file_size BIGINTCOMMENT'文件大小', total_count INTCOMMENT'总记录数', success_count INTCOMMENT'成功数', fail_count INTCOMMENT'失败数', import_time DATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'导入时间', operator VARCHAR(50)COMMENT'操作人',statusTINYINTDEFAULT1COMMENT'状态：1-成功，0-失败', error_message TEXTCOMMENT'错误信息')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='导入日志表';

四、使用示例

1. 启动应用

mvn spring-boot:run 

2. 导入Excel

• 使用Postman或前端上传Excel文件
• 文件格式要求：包含userName, email, phone, age, department, salary列
• 接口地址：POST http://localhost:8080/excel-demo/api/excel/import/users

3. 导出Excel

• 接口地址：GET http://localhost:8080/excel-demo/api/excel/export/users

五、优化建议

1. 性能优化

• 使用数据库连接池
• 调整批量提交大小
• 使用多线程并行处理
• 添加Redis缓存

2. 功能增强

• 支持多种Excel格式
• 添加数据清洗功能
• 支持模板自定义
• 添加导入进度查询

3. 安全性考虑

• 文件类型验证
• 文件大小限制
• 病毒扫描
• 访问权限控制

六、注意事项

1. 内存管理：大数据量导入时，注意JVM内存设置
2. 事务控制：批量导入要考虑事务边界
3. 异常处理：完善的异常处理和日志记录
4. 数据验证：前端和后端双重验证
5. 用户体验：提供进度反馈和错误报告