超大Excel文件高效写入方案-优快云博客

背景

接上一篇文章，Apache POI 的OOXML 包有XSSFReader，却没有提供对应的Writer，找了一圈好像POI的包就没有提供直接xml流写出的方式，不得已上网转了一圈，貌似也没有类似的实现。只能自己动手，看能不能找到可行的方案；

实现思路

1.使用XSSFWorkBook生成基本的结构，需要用createSheet()生成xml，这块也可能自己实现，但是需要通读底层代码；

2.拿到XSSFWorkBook父类的私有变量pkg，这个指向的就是OPCPackage；

3.设法拿到前面新增Sheet对于的xml数据流，这块没有直接的API可以调用，而且不能使用POI的API，因为底层是DOM实现；

4.建立XMLStreamWriter，对上面的xml数据流直接写入XML，跳过POI中的Cell 和相关对象构建；

遇到的问题

1.OPCPackage openPart打开的PackagePart只能用于READ，得到OutputStream，直接报closed错误；

2.OPCPackage createPart方法，xml存在也会报错，因此sheet不能new出来，new了createPart报错；

3.如果不调用createSheet，直接调createPart，则关系relationship丢失，也不行；

最终可行的方案

1.仍然需要createSheet，这块逻辑和关联要找出来比较难，不能省；

2.想办法把新增sheet对应的xml从OPCPackage中移走，这样createPart才能不报错；

3.调用PackagePart的getOutputStream，流直接向中间写入

4.同时要注意CellStyle，这块依然要从workbook 新建，并注意复用；

5.写入完成先关闭XMLStreamWriter，再关闭PackagePart上的outputstream，然后关闭PackagePart;

6.workbook.getSheetAt(0)获取到Sheet，（不能用前面createSheet生成的，因为第二步已经把这个sheet remove了。）对应column 进行autoSizeColumn

7.关闭workbook。

修改于2025-4-26

以上方法经测试，速度还可以，但是在WorkBook save时会有很大的JVM波动，在并发多任务时会有很大几率OOM，如果是多任务导出的时候，这种方法不是很适合；

提供一下另一个思路

1.Workbook生成后，添加上需要的CellType后，OPCPackage remove sheet1 后直接保存到ByteArraryOutputStream；

2.另外新建一个ZipOutputStream，新增一个ZipInputStream，传入上一步的ByteArrayOutputStream的byteArray，然后遍历ZipOutput Stream，写入到ZipOutputStream

原理参看

https://codingtechroom.com/question/how-to-add-entries-to-existing-zip-file-in-java

3.用BufferedOutputstream 包装下ZipOutputStream,缓冲区大小按照系统和使用需要设置；

4.XMLStreamWriter 传入参数改为BufferedOutputstream ,不再使用workbook的save方法；

5.完成关闭相应的资源，记得先ZipOutputStream.closeEntity，再关闭BufferedOutputstream；

经过以上改造，JVM内存消耗显著下降，速度比Workbook保存还稍快；

以上两种方案都经过测试

具体实现

https://github.com/robinhood-jim/JavaFramework/blob/develop/common/src/main/java/com/robin/core/fileaccess/writer/XmlFileWriter.java

代码片段

public class XlsxFileWriter extends TextBasedFileWriter{
    private XMLOutputFactory factory;
    private XMLStreamWriter streamWriter;
    private XSSFWorkbook workbook;
    private OPCPackage opcPackage;
    private PackagePart part;
    private OutputStream xlsxOutputStream;
    private static final char CHARA = 'A';
    private Map<String,CellStyle> cellStyleMap=new HashMap<>();
    ......
    @Override
    public void beginWrite() throws IOException {
        super.beginWrite();
        try {
            factory=XMLOutputFactory.newFactory();
            workbook = new XSSFWorkbook();
            Field field = workbook.getClass().getSuperclass().getDeclaredField("pkg");
            field.setAccessible(true);
            opcPackage = (OPCPackage) field.get(workbook);
            workbook.createSheet("sheet1");
            PackagePartName packagePartName = PackagingURIHelper.createPartName("/xl/worksheets/sheet1.xml");
            opcPackage.removePart(packagePartName);
            ......
            }catch (Exception ex){
            ex.printStackTrace();
            }
    }
    ......
}

测试用例

https://github.com/robinhood-jim/JavaFramework/blob/develop/common/src/test/java/com/robin/comm/test/TestExcelOperation.java

testWriteStAX方法