Java POI 通用Excel导出工具支持大数据流式导出、PO/VO适配、解决列错位问题一、项目简介Excel导出是后端开发高频刚需场景普通POI一次性加载全量数据容易引发内存溢出OOM。本文封装一套高兼容、高性能、可通用的Excel导出工具完美适配2003(.xls) / 2007(.xlsx)格式支持普通List集合导出、MyBatis Cursor大数据流式导出同时兼容数据库PO实体、自定义导出VO彻底解决日常导出列错位、数据为空、内存溢出等常见问题。核心优势双格式兼容支持2003低版本、2007高版本Excel文件导出大数据友好基于SXSSF实现流式导出、MyBatis Cursor游标查询百万级数据无OOM风险高性能反射缓存实体Getter方法避免重复反射大幅提升导出效率全类型适配自动兼容数字、日期、布尔、字符串等字段类型布尔值自动转换「是/否」场景全覆盖支持PO实体直接导出、自定义VO联查导出适配多表联查导出场景二、Maven依赖引入采用稳定经典的POI 3.10-FINAL版本适配绝大多数SpringBoot项目无版本冲突问题dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version3.10-FINAL/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version3.10-FINAL/version /dependency三、通用Excel导出工具类完整版工具类封装2003、2007双版本导出逻辑内置方法缓存、数据类型自动适配、统一单元格样式、流式临时文件自动释放开箱即用无需重复改造。package com.skm.utils; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; /** * 导出Excel支持 SXSSF 流式导出适合大数据量 * * param T */ public class ExportExcelUtilT { // 2007 版本以上 最大支持1048576行 public final static String EXCEl_FILE_2007 2007; // 2003 版本 最大支持65536 行 public final static String EXCEL_FILE_2003 2003; // SXSSF 内存中保留的行数窗口默认 1000可根据内存调整 private static final int DEFAULT_STREAM_WINDOW_SIZE 1000; // 反射方法缓存避免每行都重复 getMethod private static final MapClass?, Method[] METHOD_CACHE new ConcurrentHashMap(); // 数字字符串正则修复了原代码 //d 的 bug private static final Pattern NUMBER_PATTERN Pattern.compile(^-?\\d(\\.\\d)?$); /** * 设置 SXSSF 流式窗口大小。值越小内存占用越低但写入速度可能略有下降。 */ private int streamWindowSize DEFAULT_STREAM_WINDOW_SIZE; public void setStreamWindowSize(int streamWindowSize) { this.streamWindowSize streamWindowSize; } /** * p * 导出无头部标题行Excel br * 时间格式默认yyyy-MM-dd HH:mm:ss br * /p * * param title 表格标题 * param dataset 数据集合 * param out 输出流 * param version 2003 或者 2007不传时默认生成2003版本 */ public void exportExcel(String title, CollectionT dataset, OutputStream out, String version) { if (StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())) { exportExcel2003(title, null, dataset, out, yyyy-MM-dd HH:mm:ss); } else { exportExcel2007(title, null, dataset, out, yyyy-MM-dd HH:mm:ss); } } /** * p * 导出带有头部标题行的Excel br * 时间格式默认yyyy-MM-dd HH:mm:ss br * /p * * param title 表格标题 * param headers 头部标题集合 * param dataset 数据集合 * param out 输出流 * param version 2003 或者 2007不传时默认生成2003版本 */ public void exportExcel(String title, String[] headers, CollectionT dataset, OutputStream out, String version) { if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) { exportExcel2003(title, headers, dataset, out, yyyy-MM-dd HH:mm:ss); } else { exportExcel2007(title, headers, dataset null ? null : dataset.iterator(), out, yyyy-MM-dd HH:mm:ss); } } /** * 流式导出入口支持 Iterator适合 MyBatis Cursor 等流式数据源。 */ public void exportExcel(String title, String[] headers, IteratorT dataIt, OutputStream out, String version) { if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) { exportExcel2003(title, headers, dataIt, out, yyyy-MM-dd HH:mm:ss); } else { exportExcel2007(title, headers, dataIt, out, yyyy-MM-dd HH:mm:ss); } } /** * 流式导出 2007 版本 Excelxlsx适合大数据量百万级。 * * param title 表格标题名 * param headers 表格头部标题集合 * param dataset 需要显示的数据集合集合中放置符合 JavaBean 风格的对象 * param out 与输出设备关联的流对象 * param pattern 时间数据输出格式默认为 yyyy-MM-dd HH:mm:ss */ SuppressWarnings({unchecked, rawtypes}) public void exportExcel2007(String title, String[] headers, CollectionT dataset, OutputStream out, String pattern) { exportExcel2007(title, headers, dataset null ? null : dataset.iterator(), out, pattern); } /** * 流式导出 2007 版本 Excelxlsx—— Iterator 版本。 */ SuppressWarnings({unchecked, rawtypes}) public void exportExcel2007(String title, String[] headers, IteratorT dataIt, OutputStream out, String pattern) { if (dataIt null || !dataIt.hasNext()) { return; } // 使用 SXSSFWorkbook 进行流式写入 SXSSFWorkbook workbook new SXSSFWorkbook(streamWindowSize); workbook.setCompressTempFiles(true); // 压缩临时文件降低磁盘占用 try { Sheet sheet workbook.createSheet(title); sheet.setDefaultColumnWidth(20); // 头部样式SXSSFWorkbook 实际创建的是 XSSFCellStyle XSSFCellStyle headerStyle (XSSFCellStyle) workbook.createCellStyle(); headerStyle.setFillForegroundColor(new XSSFColor(java.awt.Color.gray)); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); XSSFFont headerFont (XSSFFont) workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(宋体); headerFont.setColor(new XSSFColor(java.awt.Color.BLACK)); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); // 数据样式 XSSFCellStyle dataStyle (XSSFCellStyle) workbook.createCellStyle(); dataStyle.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE)); dataStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); dataStyle.setBorderBottom(CellStyle.BORDER_THIN); dataStyle.setBorderLeft(CellStyle.BORDER_THIN); dataStyle.setBorderRight(CellStyle.BORDER_THIN); dataStyle.setBorderTop(CellStyle.BORDER_THIN); dataStyle.setAlignment(CellStyle.ALIGN_CENTER); dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); XSSFFont dataFont (XSSFFont) workbook.createFont(); dataFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); dataStyle.setFont(dataFont); // 写入表头 if (headers ! null headers.length 0) { Row headerRow sheet.createRow(0); for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(new XSSFRichTextString(headers[i])); } } // 写入数据行 SimpleDateFormat sdf new SimpleDateFormat(pattern); int index (headers null || headers.length 0) ? 0 : 1; while (dataIt.hasNext()) { T t (T) dataIt.next(); Row row sheet.createRow(index); writeRow2007(row, t, dataStyle, sdf); } workbook.write(out); out.flush(); } catch (IOException e) { throw new RuntimeException(导出 Excel 失败, e); } finally { // 必须 dispose否则临时文件不会删除POI 3.10 SXSSFWorkbook 没有 close() 方法 workbook.dispose(); } } /** * 写入一行数据SXSSF */ private void writeRow2007(Row row, T t, XSSFCellStyle dataStyle, SimpleDateFormat sdf) { Method[] methods getGetterMethods(t.getClass()); for (int i 0; i methods.length; i) { Cell cell row.createCell(i); cell.setCellStyle(dataStyle); setCellValue(cell, invokeGetter(methods[i], t), sdf); } } /** * 通用导出方法生成 2003 版本 xls仅适合 65536 行以内。 */ SuppressWarnings({unchecked, rawtypes}) public void exportExcel2003(String title, String[] headers, CollectionT dataset, OutputStream out, String pattern) { exportExcel2003(title, headers, dataset null ? null : dataset.iterator(), out, pattern); } /** * 通用导出方法生成 2003 版本 xls仅适合 65536 行以内—— Iterator 版本。 */ SuppressWarnings({unchecked, rawtypes}) public void exportExcel2003(String title, String[] headers, IteratorT dataIt, OutputStream out, String pattern) { if (dataIt null || !dataIt.hasNext()) { return; } HSSFWorkbook workbook new HSSFWorkbook(); try { HSSFSheet sheet workbook.createSheet(title); sheet.setDefaultColumnWidth(20); HSSFCellStyle style workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName(宋体); font.setColor(HSSFColor.WHITE.index); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFCellStyle style2 workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font2 workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); style2.setFont(font2); if (headers ! null headers.length 0) { HSSFRow row sheet.createRow(0); for (int i 0; i headers.length; i) { HSSFCell cell row.createCell(i); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString(headers[i])); } } SimpleDateFormat sdf new SimpleDateFormat(pattern); int index (headers null || headers.length 0) ? 0 : 1; while (dataIt.hasNext()) { T t (T) dataIt.next(); HSSFRow row sheet.createRow(index); Method[] methods getGetterMethods(t.getClass()); for (int i 0; i methods.length; i) { HSSFCell cell row.createCell(i); cell.setCellStyle(style2); setCellValue(cell, invokeGetter(methods[i], t), sdf); } } workbook.write(out); out.flush(); } catch (IOException e) { throw new RuntimeException(导出 Excel 失败, e); } // POI 3.10 的 HSSFWorkbook 没有 close() 方法无需关闭 } /** * 获取并缓存指定类的所有 getter 方法按字段声明顺序排列。 */ private Method[] getGetterMethods(Class? clazz) { return METHOD_CACHE.computeIfAbsent(clazz, k - { java.lang.reflect.Field[] fields k.getDeclaredFields(); Method[] methods new Method[fields.length]; for (int i 0; i fields.length; i) { String fieldName fields[i].getName(); String getMethodName get fieldName.substring(0, 1).toUpperCase() fieldName.substring(1); try { methods[i] k.getMethod(getMethodName); } catch (NoSuchMethodException e) { // 如果没有对应的 getter则尝试 isXxxBoolean 常见 if (fields[i].getType() boolean.class || fields[i].getType() Boolean.class) { try { methods[i] k.getMethod(is fieldName.substring(0, 1).toUpperCase() fieldName.substring(1)); } catch (NoSuchMethodException ex) { throw new RuntimeException(字段 fieldName 缺少 getter 方法, ex); } } else { throw new RuntimeException(字段 fieldName 缺少 getter 方法, e); } } } return methods; }); } /** * 调用 getter 方法获取值。 */ private Object invokeGetter(Method method, Object target) { try { return method.invoke(target); } catch (IllegalAccessException | InvocationTargetException e) { throw new RuntimeException(调用 method.getName() 失败, e); } } /** * 根据值类型设置单元格内容。 */ private void setCellValue(Cell cell, Object value, SimpleDateFormat sdf) { if (value null) { return; } if (value instanceof Number) { // Integer / Long / Float / Double / BigDecimal 等直接写为数字 cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value ? 是 : 否); } else if (value instanceof Date) { cell.setCellValue(sdf.format((Date) value)); } else { String textValue value.toString(); Matcher matcher NUMBER_PATTERN.matcher(textValue); if (matcher.matches()) { cell.setCellValue(Double.parseDouble(textValue)); } else { cell.setCellValue(textValue); } } } }四、实际业务导出示例提供两种生产常用导出方案大数据Cursor流式导出PO实体、多表联查VO导出适配不同业务场景。4.1 大数据量流式导出MyBatis Cursor PO实体适用于单表大批量数据导出基于游标分批读取数据内存零压力百万级数据不OOM。Override Transactional // Cursor 需要保持连接打开 public void exportAll(HttpServletResponse response) { ExportExcelUtilChild util new ExportExcelUtil(); util.setStreamWindowSize(1000); String[] headers {Id,姓名,性别, 年级, 班级, 出生日期, 入园日期, 过敏史, 饮食禁忌, 家长手机号}; response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment; filenamechildren.xlsx); try (CursorChild cursor childMapper.selectAllCursor()) { util.exportExcel(儿童信息, headers, cursor.iterator(), response.getOutputStream(), 2007); } catch (IOException e) { throw new BusinessException(导出儿童信息失败, e); } }4.2 多表联查VO导出最常用适用于多表联查、需要整合扩展字段的导出场景如关联班级名称使用自定义VO接收联查数据。Override public void exportChild(HttpServletResponse response) { ExportExcelUtilChildExcelVo util new ExportExcelUtil(); //child_id,name,grade,gender,birth_date,enroll_date,current_class_id,allergy_info,diet_restrictions,parent_phone,create_time,update_time,status // 核心重点表头顺序、VO字段声明顺序、数据查询顺序 三者必须完全一致否则列数据错位 String[] headers {Id,姓名, 年级,性别, 出生日期, 入园日期,班级, 过敏史, 饮食禁忌, 家长手机号,创建时间,更新时间,状态}; response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment; filenamechildren.xlsx); ListChildExcelVo childList childMapper.selectExcelList(new ChildQuery()); try { util.exportExcel(儿童信息, headers, childList, response.getOutputStream(), 2007); } catch (IOException e) { throw new BusinessException(导出儿童信息失败, e); } }五、核心避坑注意事项重点5.1 VO导出列错位方案本工具不根据SQL查询顺序、表头顺序赋值而是通过反射VO字段声明顺序依次填充单元格。三者必须严格对齐缺一不可VO实体字段从上到下的声明顺序核心依据Java表头数组headers的顺序SQL查询字段顺序建议对齐避免冗余字段错误场景VO第1个字段是姓名表头第1列是ID → 整列数据全部错位。5.2 数据为空/映射失败问题开启MyBatis下划线转驼峰map-underscore-to-camel-case: true否则数据库下划线字段无法映射VO驼峰字段注意字段拼写错误会直接导致字段值为nullMapper.xml查询VO必须指定resultTypeVO全类名否则MyBatis无法封装数据5.3 大数据导出优化规则万级以上数据必须使用SXSSF流式导出 MyBatis Cursor禁止一次性查询全量List导出完成必须执行workbook.dispose()释放POI临时文件避免磁盘爆满合理设置streamWindowSize内存充足可设2000内存紧张设500