java 大量数据excel导出-杰瑞科技汇

Java 大数据量Excel导出解决方案

在Java应用中处理大数据量Excel导出时，直接使用传统方法（如Apache POI）会导致内存溢出或性能问题,以下是几种有效的解决方案：

（图片来源网络，侵删）

使用SXSSFWorkbook（Apache POI推荐方案）

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFRow;
public void exportLargeExcel(HttpServletResponse response) throws IOException {
    // 创建SXSSFWorkbook，-1表示不自动刷新到磁盘
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(-1)) {
        SXSSFSheet sheet = workbook.createSheet("Large Data");
        // 模拟10万行数据
        for (int i = 0; i < 100000; i++) {
            SXSSFRow row = sheet.createRow(i);
            row.createCell(0).setCellValue("ID-" + i);
            row.createCell(1).setCellValue("Name-" + i);
            // 每写入100行刷新一次到磁盘
            if (i % 100 == 0) {
                sheet.flushRows();
            }
        }
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=large_data.xlsx");
        // 写入输出流
        workbook.write(response.getOutputStream());
        workbook.dispose(); // 清理临时文件
    }
}

使用EasyExcel（阿里巴巴开源方案）

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
public void exportLargeExcelWithEasyExcel(HttpServletResponse response) {
    // 设置响应头
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=large_data.xlsx");
    // 使用EasyExcel写Excel
    ExcelWriterBuilder writerBuilder = EasyExcel.write(response.getOutputStream(), YourDataModel.class);
    try (ExcelWriter excelWriter = writerBuilder.build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet("Large Data").build();
        // 分批查询数据并写入
        int pageSize = 10000;
        int pageNum = 1;
        while (true) {
            List<YourDataModel> data = queryDataByPage(pageNum, pageSize);
            if (data.isEmpty()) break;
            excelWriter.write(data, writeSheet);
            pageNum++;
        }
    }
}

使用CSV作为中间格式（适合超大数据量）

public void exportViaCsv(HttpServletResponse response) throws IOException {
    response.setContentType("text/csv");
    response.setHeader("Content-Disposition", "attachment; filename=large_data.csv");
    try (PrintWriter writer = response.getWriter()) {
        // 写入CSV表头
        writer.println("ID,Name,Description");
        // 分批查询并写入
        int pageSize = 10000;
        int pageNum = 1;
        while (true) {
            List<YourDataModel> data = queryDataByPage(pageNum, pageSize);
            if (data.isEmpty()) break;
            for (YourDataModel item : data) {
                writer.printf("%s,%s,%s%n", item.getId(), item.getName(), item.getDescription());
            }
            pageNum++;
        }
    }
}

数据库直接导出（最高效方案）

public void exportFromDatabase(HttpServletResponse response) throws SQLException, IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=large_data.xlsx");
    // 使用数据库游标直接导出
    String sql = "SELECT id, name, description FROM large_table";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql, 
             ResultSet.TYPE_FORWARD_ONLY, 
             ResultSet.CONCUR_READ_ONLY);
         ResultSet rs = ps.executeQuery();
         Workbook workbook = new SXSSFWorkbook(1000); // 缓存1000行
         Sheet sheet = workbook.createSheet("Data")) {
        // 写入表头
        ResultSetMetaData metaData = rs.getMetaData();
        Row headerRow = sheet.createRow(0);
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            headerRow.createCell(i-1).setCellValue(metaData.getColumnName(i));
        }
        // 写入数据
        int rowNum = 1;
        while (rs.next()) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                row.createCell(i-1).setCellValue(rs.getString(i));
            }
            // 定期刷新到磁盘
            if (rowNum % 1000 == 0) {
                ((SXSSFSheet) sheet).flushRows();
            }
        }
        workbook.write(response.getOutputStream());
    }
}