别再手动复制粘贴了！用EasyExcel 2.1.1 + SpringBoot 5分钟搞定数据库数据导出Excel

5分钟极速导出ExcelSpringBootEasyExcel实战指南每次产品经理甩来一句把用户数据导出成Excel时你是不是还在手忙脚乱地复制粘贴作为Java开发者我们完全可以用技术手段优雅解决这个问题。今天要介绍的EasyExcel能让数据库到Excel的转换变得像喝咖啡一样简单——从配置到运行真正只需5分钟。1. 环境准备与依赖配置1.1 初始化SpringBoot项目首先确保你已经有一个基础的SpringBoot项目。如果还没有可以通过Spring Initializr快速生成curl https://start.spring.io/starter.zip -d dependenciesweb,mysql,mybatis -d typemaven-project -o excel-export-demo.zip解压后导入到你喜欢的IDE中。我习惯用IntelliJ IDEA它的自动补全功能对后续开发很有帮助。1.2 添加EasyExcel依赖在pom.xml中加入以下依赖注意版本号dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.1.1/version /dependency提示最新稳定版已经迭代到3.x系列相比2.x版本在内存占用和性能上有显著提升2. 数据准备与模型定义2.1 数据库表设计示例假设我们要导出用户数据对应的MySQL表结构如下CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, username varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, email varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, create_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;2.2 定义Excel映射模型创建一个UserExcelVO类来定义Excel的列结构Data public class UserExcelVO { ExcelProperty(用户ID) private Long id; ExcelProperty(用户名) private String username; ExcelProperty(value 邮箱, converter CustomStringConverter.class) private String email; ExcelProperty(value 注册时间, format yyyy-MM-dd HH:mm:ss) private Date createTime; }这里用到了几个关键注解ExcelProperty定义列名和顺序format日期格式化converter自定义数据转换器后面会介绍3. 核心导出逻辑实现3.1 基础导出控制器创建一个REST控制器处理导出请求RestController RequestMapping(/export) public class ExcelExportController { Autowired private UserService userService; GetMapping(/users) public void exportUsers(HttpServletResponse response) throws IOException { // 设置响应头 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); // 查询数据并导出 ListUserExcelVO data userService.getAllUsersForExport(); EasyExcel.write(response.getOutputStream(), UserExcelVO.class) .sheet(用户数据) .doWrite(data); } }3.2 服务层数据转换在UserService中实现数据转换逻辑Service public class UserServiceImpl implements UserService { Autowired private UserMapper userMapper; Override public ListUserExcelVO getAllUsersForExport() { ListUser users userMapper.selectAll(); return users.stream().map(user - { UserExcelVO vo new UserExcelVO(); BeanUtils.copyProperties(user, vo); return vo; }).collect(Collectors.toList()); } }4. 高级功能扩展4.1 自定义数据转换器当需要特殊处理某些字段时比如加密邮箱可以创建自定义转换器public class CustomStringConverter implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public String convertToJavaData(ReadConverterContext? context) { return context.getReadCellData().getStringValue(); } Override public WriteCellData? convertToExcelData(WriteConverterContextString context) { // 对邮箱进行部分脱敏处理 String email context.getValue(); if(email ! null email.contains()) { String[] parts email.split(); if(parts[0].length() 3) { email parts[0].substring(0, 3) *** parts[1]; } } return new WriteCellData(email); } }4.2 大数据量分片导出当数据量超过万条时建议使用分片查询导出public void exportLargeData(HttpServletResponse response) throws IOException { // ...响应头设置同上... ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), UserExcelVO.class).build(); int pageSize 5000; int page 1; while(true) { ListUserExcelVO data userService.getUsersByPage(page, pageSize); if(data.isEmpty()) break; WriteSheet writeSheet EasyExcel.writerSheet(page-1, 第page页).build(); excelWriter.write(data, writeSheet); page; } excelWriter.finish(); }4.3 动态列导出有时需要根据用户选择动态决定导出哪些列public void dynamicExport(HttpServletResponse response, SetString includeFields) throws IOException { // ...响应头设置... ExcelWriterBuilder writerBuilder EasyExcel.write(response.getOutputStream()); // 动态设置包含的字段 if(includeFields.contains(username)) { writerBuilder.includeColumnFieldNames(Collections.singletonList(username)); } // 其他字段同理... writerBuilder.head(UserExcelVO.class) .sheet(动态数据) .doWrite(userService.getAllUsersForExport()); }5. 常见问题解决方案5.1 内存溢出问题即使使用EasyExcel处理超大文件时仍需注意对于导出使用分页查询分片写入如4.2所示对于导入使用ReadListener逐行处理// 安全导入示例 public void safeImport(MultipartFile file) { EasyExcel.read(file.getInputStream(), UserExcelVO.class, new UserDataListener(userService)) .sheet() .headRowNumber(1) // 跳过标题行 .doRead(); }5.2 样式定制技巧通过拦截器自定义单元格样式EasyExcel.write(outputStream, UserExcelVO.class) .registerWriteHandler(new CellStyleStrategy()) .sheet(带样式的数据) .doWrite(data); // 自定义样式策略 public class CellStyleStrategy implements CellWriteHandler { Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, ListCellData cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if(isHead) { // 设置标题样式 CellStyle style writeSheetHolder.getSheet().getWorkbook().createCellStyle(); Font font writeSheetHolder.getSheet().getWorkbook().createFont(); font.setBold(true); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell.setCellStyle(style); } } }5.3 性能优化参数通过调整这些参数可以显著提升导出速度参数推荐值说明autoCloseStreamtrue自动关闭输出流useDefaultStylefalse禁用默认样式减少内存compressionLevel5ZIP压缩级别(1-9)writeCacheSize8192写缓存大小(字节)配置示例EasyExcel.write(response.getOutputStream(), UserExcelVO.class) .excelType(ExcelTypeEnum.XLSX) .autoCloseStream(true) .compressionLevel(5) .sheet(优化后的导出) .doWrite(data);6. 完整项目结构参考一个标准的导出功能项目结构如下src/main/java ├── com.example.exceldemo │ ├── config │ ├── controller │ │ └── ExcelExportController.java │ ├── converter │ │ └── CustomStringConverter.java │ ├── entity │ │ ├── User.java │ │ └── UserExcelVO.java │ ├── listener │ │ └── UserDataListener.java │ ├── mapper │ │ └── UserMapper.java │ ├── service │ │ ├── impl │ │ │ └── UserServiceImpl.java │ │ └── UserService.java │ └── ExcelDemoApplication.java在实际项目中我发现最容易出问题的环节是响应头的设置——特别是当需要支持多浏览器下载时。经过多次测试最可靠的响应头组合是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);