性能爆炸！SXSSFWorkbook原文件上追加写入&分页导出

使用SXSSF大数据导出，SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，SXSSFWorkbook专门处理大数据，对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

网上教程很多，下面是我跑出来的版本

项目依赖包及版本

<commons.compress.version>1.18</commons.compress.version>
<poi.version>4.0.1</poi.version>
 
<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>
<version>${ poi.version }</version> 

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>
<version>${ poi.version }</version>

</dependency>
<dependency>

   <groupId>org.apache.commons</groupId>

   <artifactId>commons-compress</artifactId>

   <version>${commons.compress.version}</version>

</dependency>

工具类

import dbox.tbox.lang.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Stream;

/**
 * @Author ping
 * @Date 2023/12/4 17:04
 * excel工具类 用于service服务的导出，不使用export模式
 */
public class ExcelUtil {
    // 读取 Excel 文件并将其映射为具体的 Java 对象
    public static <T> List<T> readExcel(String filePath, Class<T> clazz) throws Exception {
        List<T> objects = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 选择工作表

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                T obj = clazz.getDeclaredConstructor().newInstance();

                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    String fieldName = sheet.getRow(0).getCell(j).getStringCellValue();
                    Field field = clazz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    //跳过静态属性
                    if (Modifier.isStatic(field.getModifiers())){
                        continue;
                    }
                    //单元格为空的情况
                    if (cell == null){
                        continue;
                    }
                    if (cell.getCellType() == CellType.NUMERIC) {
                        double numericValue = cell.getNumericCellValue();
                        if (field.getType() == BigDecimal.class){
                            field.set(obj, BigDecimal.valueOf(numericValue));
                        }else if (field.getType() == Long.class){
                            field.set(obj, (long) numericValue);
                        }else if (field.getType() == Integer.class){
                            field.set(obj, (int) numericValue);
                        }else {
                            field.set(obj, numericValue); // 设置为Double类型
                        }
                    } else if (cell.getCellType() == CellType.STRING) {
                        if (field.getType() == Date.class){
                            field.set(obj, DateUtils.strToDate(cell.getStringCellValue(),"yyyy-MM-dd HH:mm:ss"));
                        }else if (field.getType() == String.class){
                        field.set(obj, cell.getStringCellValue());
                        }
                    }
                }
                objects.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return objects;
    }

    /**
     * 创建表头 MAP 格式为 filed，表头名
     * @param objects
     * @param filePath
     * @param titleMap
     * @param append
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(List<T> objects, String filePath, Map<String, String> titleMap, boolean append) throws IOException {
        // 确保工作簿以指定模式打开
        Workbook workbook;
        if (append) {
            try (FileInputStream fis = new FileInputStream(filePath)) {
                workbook = WorkbookFactory.create(fis);
            }
        } else {
            workbook = new XSSFWorkbook();
        }

        // 获取或创建Sheet
        Sheet sheet = workbook.getSheet("Sheet1");
        if (sheet == null) {
            sheet = workbook.createSheet("Sheet1");
        }

        // 创建表头
        List<String> titles = new ArrayList<>();
        titleMap.forEach((k, v) -> titles.add(v));
        if (!append) {
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < titles.size(); i++) {
                headerRow.createCell(i).setCellValue(titles.get(i));
            }
        }

        // 写入数据
        int lastRowNum = sheet.getPhysicalNumberOfRows();
        int currentRow = lastRowNum == 0 ? 0 : lastRowNum + 1;
        for (T object : objects) {
            Row row = sheet.createRow(currentRow++);
            for (String fieldName : titleMap.keySet()) {
                Cell cell = row.createCell(row.getLastCellNum());
                cell.setCellValue((String) getCellValue(object, fieldName));
            }
        }

        // 写入文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }
    }

    /**
     * 只需要列名--不创建表头
     * @param objects
     * @param filePath
     * @param titleList
     * @param append
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(List<T> objects, String filePath, List<String> titleList, boolean append) throws IOException {
        // 确保工作簿以指定模式打开
        Workbook workbook;
        if (append) {
            try (FileInputStream fis = new FileInputStream(filePath)) {
                workbook = WorkbookFactory.create(fis);
            }
        } else {
            workbook = new XSSFWorkbook();
        }

        // 获取或创建Sheet
        Sheet sheet = workbook.getSheet("Sheet1");
        if (sheet == null) {
            sheet = workbook.createSheet("Sheet1");
        }

        // 写入数据
        int lastRowNum = sheet.getPhysicalNumberOfRows();
        int currentRow = lastRowNum == 0 ? 0 : lastRowNum + 1;

        for (T object : objects) {
            Row row = sheet.createRow(currentRow++);
            for (String fieldName : titleList) {
                Cell cell = row.createCell(row.getLastCellNum());
                cell.setCellValue((String) getCellValue(object, fieldName));
            }
        }

        // 写入文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }
    }


    public static  void writeExcel(List<List<String>> objects, String filePath, boolean append,List<Integer> rigthList) throws IOException {
        // 确保工作簿以指定模式打开
        Workbook workbook;
        if (append) {
            try (FileInputStream fis = new FileInputStream(filePath)) {
                workbook = WorkbookFactory.create(fis);
            }
        } else {
            workbook = new XSSFWorkbook();
        }

        // 获取或创建Sheet
        Sheet sheet = workbook.getSheet("Sheet1");
        if (sheet == null) {
            sheet = workbook.createSheet("Sheet1");
        }

        // 写入数据
        int lastRowNum = sheet.getPhysicalNumberOfRows();
        int currentRow = lastRowNum == 0 ? 0 : lastRowNum;

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cellStyle.setWrapText(true);

        for (List<String> objectList : objects) {
            Row row = sheet.createRow(currentRow++);
            for (int i =0;i<objectList.size();i++) {
                Cell cell = row.createCell(i);
                //右对齐
                if (rigthList.contains(i)){

                    cell.setCellStyle(cellStyle);
                }
                if (objectList.get(i) == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(objectList.get(i));
                }
            }
        }

        // 写入文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } finally {
            // 确保工作簿被关闭以释放资源
            if (workbook != null) {
                workbook.close();
            }
        }
    }

    private static  <T> Object getCellValue(T object, String columnName) {
        // 这里业务对象有对应属性，且属性名与columnNam匹配
        Field field = getDeclaredField(object.getClass(), columnName);
        try {
            Object value = field.get(object);
            if (value instanceof Number) {
                return ((Number) value).doubleValue();
            } else if (value instanceof String) {
                return value;
            } else if (value instanceof Date){
                return DateUtils.dateToStr((Date)value,"yyyy-MM-dd HH:mm:ss");
            }
            return "";
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static Field getDeclaredField(Class<?> clazz, String fieldName) {
        try {
            return clazz.getDeclaredField(fieldName);
        } catch (NoSuchFieldException e) {
            // 递归查找父类
            Field parentField = getDeclaredField(clazz.getSuperclass(), fieldName);
            if (parentField != null) {
                return parentField;
            }
        }
        return null;
    }

    public static void writeExcelBySXSSF(List<List<String>> newData,String filePath,  boolean append,List<Integer> rigthList) throws IOException {
        if (append) {
            // 打开现有的Excel文件
            FileInputStream fis = new FileInputStream(filePath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);

            // 创建SXSSFWorkbook实例
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1024);

            // 获取原有工作簿的工作表
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); // 假设数据在第一个工作表
            SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet(xssfSheet.getSheetName());

            // 复制原有工作表的数据和样式到SXSSFWorkbook
            copySheet(xssfSheet, sxssfSheet);

            CellStyle cellStyle = sxssfWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            cellStyle.setWrapText(true);

            // 追加新数据到SXSSFWorkbook的工作表
            int rowIndex = sxssfSheet.getLastRowNum() + 1;
            for (List<String> row : newData) {
                Row sxssfRow = sxssfSheet.createRow(rowIndex++);
                for (int i = 0; i < row.size(); i++) {
                    Cell cell = sxssfRow.createCell(i);
                    cell.setCellValue(row.get(i));
                    if (rigthList != null && rigthList.contains(i)){
                        cell.setCellStyle(cellStyle);
                    }
                }
            }

            // 将SXSSFWorkbook写入文件，覆盖原有文件
            FileOutputStream os = new FileOutputStream(filePath);
            sxssfWorkbook.write(os);
            os.close();

            // 关闭工作簿和文件输入流
            sxssfWorkbook.close();
            xssfWorkbook.close();
            fis.close();
        } else {
            // 如果不追加，创建新的SXSSFWorkbook并写入数据的逻辑
        }
    }


    private static void copySheet(XSSFSheet xssfSheet, SXSSFSheet sxssfSheet) {
        for (int rowIndex = 0; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            Row sxssfRow = sxssfSheet.createRow(rowIndex);
            for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum(); cellIndex++) {
                Cell xssfCell = xssfRow.getCell(cellIndex);
                Cell sxssfCell = sxssfRow.createCell(cellIndex);
                if (xssfCell != null) {
                    // 复制单元格的值
                    sxssfCell.setCellValue(xssfCell.toString());
                    // 复制单元格的样式
                    /*CellStyle xssfCellStyle = xssfCell.getCellStyle();
                    if (xssfCellStyle != null) {
                        sxssfCell.setCellStyle(xssfCellStyle);
                        // 复制其他样式属性，如字体、边框等
                    }*/
                }
            }
        }
    }


}

以上工具类里提供的都是一次性导出的方法。那如果说需要多次导出咋整呢。说实话，支付系统里头最头痛的就是导出大数据量，性能瓶颈第一卡在数据库资源，第二卡在poi性能。

做了一系列优化后，还是觉得分页最靠谱。分页查询分页导出，于是就会有这种场景，第一次写完的excel，第二次读取后再追加写入。但我们又知道SXSSFWorkbook类是专门给写定制的，压根就没法直接读取原excel文件。只能由XSSFWorkbook类读取。所以TM实现起来真难啊。只能先读，后复制再写。给两个方法自行体会。

第一个XSSFWorkbook类追加写入，只是参考模式，具体参数自己玩

public static  void writeExcel(List<List<String>> objects, String filePath, boolean append,List<Integer> rigthList) throws IOException {
        // 确保工作簿以指定模式打开
        Workbook workbook;
        if (append) {
            try (FileInputStream fis = new FileInputStream(filePath)) {
                workbook = WorkbookFactory.create(fis);
            }
        } else {
            workbook = new XSSFWorkbook();
        }

        // 获取或创建Sheet
        Sheet sheet = workbook.getSheet("Sheet1");
        if (sheet == null) {
            sheet = workbook.createSheet("Sheet1");
        }

        // 写入数据
        int lastRowNum = sheet.getPhysicalNumberOfRows();
        int currentRow = lastRowNum == 0 ? 0 : lastRowNum;

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cellStyle.setWrapText(true);

        for (List<String> objectList : objects) {
            Row row = sheet.createRow(currentRow++);
            for (int i =0;i<objectList.size();i++) {
                Cell cell = row.createCell(i);
                //右对齐
                if (rigthList.contains(i)){

                    cell.setCellStyle(cellStyle);
                }
                if (objectList.get(i) == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(objectList.get(i));
                }
            }
        }

        // 写入文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } finally {
            // 确保工作簿被关闭以释放资源
            if (workbook != null) {
                workbook.close();
            }
        }
    }

  private static  <T> Object getCellValue(T object, String columnName) {
        // 这里业务对象有对应属性，且属性名与columnNam匹配
        Field field = getDeclaredField(object.getClass(), columnName);
        try {
            Object value = field.get(object);
            if (value instanceof Number) {
                return ((Number) value).doubleValue();
            } else if (value instanceof String) {
                return value;
            } else if (value instanceof Date){
                return DateUtils.dateToStr((Date)value,"yyyy-MM-dd HH:mm:ss");
            }
            return "";
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static Field getDeclaredField(Class<?> clazz, String fieldName) {
        try {
            return clazz.getDeclaredField(fieldName);
        } catch (NoSuchFieldException e) {
            // 递归查找父类
            Field parentField = getDeclaredField(clazz.getSuperclass(), fieldName);
            if (parentField != null) {
                return parentField;
            }
        }
        return null;
    }

第二个SXSSFWorkbook类追加写入

public static void writeExcelBySXSSF(List<List<String>> newData,String filePath,  boolean append,List<Integer> rigthList) throws IOException {
        if (append) {
            // 打开现有的Excel文件
            FileInputStream fis = new FileInputStream(filePath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);

            // 创建SXSSFWorkbook实例
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1024);

            // 获取原有工作簿的工作表
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); // 假设数据在第一个工作表
            SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet(xssfSheet.getSheetName());

            // 复制原有工作表的数据和样式到SXSSFWorkbook
            copySheet(xssfSheet, sxssfSheet);

            CellStyle cellStyle = sxssfWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            cellStyle.setWrapText(true);

            // 追加新数据到SXSSFWorkbook的工作表
            int rowIndex = sxssfSheet.getLastRowNum() + 1;
            for (List<String> row : newData) {
                Row sxssfRow = sxssfSheet.createRow(rowIndex++);
                for (int i = 0; i < row.size(); i++) {
                    Cell cell = sxssfRow.createCell(i);
                    cell.setCellValue(row.get(i));
                    if (rigthList != null && rigthList.contains(i)){
                        cell.setCellStyle(cellStyle);
                    }
                }
            }

            // 将SXSSFWorkbook写入文件，覆盖原有文件
            FileOutputStream os = new FileOutputStream(filePath);
            sxssfWorkbook.write(os);
            os.close();

            // 关闭工作簿和文件输入流
            sxssfWorkbook.close();
            xssfWorkbook.close();
            fis.close();
        } else {
            // 如果不追加，创建新的SXSSFWorkbook并写入数据的逻辑
        }
    }


    private static void copySheet(XSSFSheet xssfSheet, SXSSFSheet sxssfSheet) {
        for (int rowIndex = 0; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            Row sxssfRow = sxssfSheet.createRow(rowIndex);
            for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum(); cellIndex++) {
                Cell xssfCell = xssfRow.getCell(cellIndex);
                Cell sxssfCell = sxssfRow.createCell(cellIndex);
                if (xssfCell != null) {
                    // 复制单元格的值
                    sxssfCell.setCellValue(xssfCell.toString());
                    // 复制单元格的样式
                    /*CellStyle xssfCellStyle = xssfCell.getCellStyle();
                    if (xssfCellStyle != null) {
                        sxssfCell.setCellStyle(xssfCellStyle);
                        // 复制其他样式属性，如字体、边框等
                    }*/
                }
            }
        }
    }

以上方法都是经过系统测试的，用于记录自己遇到的问题，如果你也遇到并且有疑问欢迎留言解答。