性能爆炸！SXSSFWorkbook原文件上追加写入&分页导出，2024年最新掌握这个提升路径import ja

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 List readExcel(String filePath, Class clazz) throws Exception { List 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
@throws IOException */ public static void writeExcel(List 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 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
@throws IOException */ public static void writeExcel(List objects, String filePath, List 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> objects, String filePath, boolean append,List 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 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> newData,String filePath, boolean append,List 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类追加写入**，**只是参考模式，具体参数自己玩**

    // 获取或创建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();
        }
    }
}

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

性能爆炸！SXSSFWorkbook原文件上追加写入&amp;分页导出，2024年最新掌握这个提升路径

性能爆炸！SXSSFWorkbook原文件上追加写入&分页导出，2024年最新掌握这个提升路径