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

最新推荐文章于 2024-05-14 12:14:43 发布

2401_84166497

最新推荐文章于 2024-05-14 12:14:43 发布

阅读量163

点赞数 1

分类专栏：程序员文章标签： windows java 开发语言

本文链接：https://blog.csdn.net/2401_84166497/article/details/138450676

版权

程序员专栏收录该内容

183 篇文章 0 订阅

订阅专栏

    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> 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;
}


**第二个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);


![img](https://img-blog.csdnimg.cn/img_convert/23de3453eea2e0dd845ec9a504bfe860.png)
![img](https://img-blog.csdnimg.cn/img_convert/5874ad67ce9c922766ab81a40540fb2c.png)
![img](https://img-blog.csdnimg.cn/img_convert/7076af55fc8f18b503f864e45eb14e62.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**

Workbook(1024);


[外链图片转存中...(img-xK2iXu93-1714828816187)]
[外链图片转存中...(img-TcI1hg99-1714828816188)]
[外链图片转存中...(img-NUoWx6fH-1714828816188)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**