[alibaba/easyexcel]请教大佬，我这种大批量数据写入为什么会卡死在close阶段

异常代码

public void easyExcel() {
        File templatefile1 = new File("templatef1");
        File templatefile2 = new File("templatef2");
        File templatefile3 = new File("templatef3");
        File templatefile4 = new File("templatef4");
        File templatefile5 = new File("templatef5");
        File templatefile6 = new File("templatef6");
        File templatefile7 = new File("templatef7");
        File templatefile8 = new File("templatef8");

        File file1 = new File("f1");
        File file2 = new File("f2");
        File file3 = new File("f3");
        File file4 = new File("f4");
        File file5 = new File("f5");
        File file6 = new File("f6");
        File file7 = new File("f7");
        File file8 = new File("f8");

        ExcelWriter excelWriter1 = EasyExcel.write(file1).withTemplate(templatefile1).build();
        ExcelWriter excelWriter2 = EasyExcel.write(file2).withTemplate(templatefile2).build();
        ExcelWriter excelWriter3 = EasyExcel.write(file3).withTemplate(templatefile3).build();
        ExcelWriter excelWriter4 = EasyExcel.write(file4).withTemplate(templatefile4).build();
        ExcelWriter excelWriter5 = EasyExcel.write(file5).withTemplate(templatefile5).build();
        ExcelWriter excelWriter6 = EasyExcel.write(file6).withTemplate(templatefile6).build();
        ExcelWriter excelWriter7 = EasyExcel.write(file7).withTemplate(templatefile7).build();
        ExcelWriter excelWriter8 = EasyExcel.write(file8).withTemplate(templatefile8).build();

        WriteSheet writeSheet1 = EasyExcel.writerSheet("sheet1").head(Pojo1.class).build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet("sheet2").head(Pojo2.class).build();
        WriteSheet writeSheet3 = EasyExcel.writerSheet("sheet3").head(Pojo3.class).build();
        WriteSheet writeSheet4 = EasyExcel.writerSheet("sheet4").head(Pojo4.class).build();
        WriteSheet writeSheet5 = EasyExcel.writerSheet("sheet5").head(Pojo5.class).build();
        WriteSheet writeSheet6 = EasyExcel.writerSheet("sheet6").head(Pojo6.class).build();
        WriteSheet writeSheet7 = EasyExcel.writerSheet("sheet7").head(Pojo7.class).build();
        WriteSheet writeSheet8 = EasyExcel.writerSheet("sheet8").head(Pojo8.class).build();
        WriteSheet writeSheet9 = EasyExcel.writerSheet("sheet9").head(Pojo9.class).build();
        WriteSheet writeSheet10 = EasyExcel.writerSheet("sheet10").head(Pojo10.class).build();

        // 循环5000条数据
        for (int i = 0; i < 5000; i++) {
            // 查询数据库 每个数据可以查回来50条记录
            for (int j = 0; j < 50; j++) {
                // 每次写一条记录,每个excelWriter都需要写多个sheet
                excelWriter1.write(List.of(new Pojo1()), writeSheet1);
                excelWriter1.write(List.of(new Pojo2()), writeSheet2);
                excelWriter1.write(List.of(new Pojo3()), writeSheet3);
                excelWriter1.write(List.of(new Pojo4()), writeSheet4);
                excelWriter1.write(List.of(new Pojo5()), writeSheet5);
                excelWriter1.write(List.of(new Pojo6()), writeSheet6);
                excelWriter1.write(List.of(new Pojo7()), writeSheet7);
                excelWriter1.write(List.of(new Pojo8()), writeSheet8);
                excelWriter1.write(List.of(new Pojo9()), writeSheet8);
                excelWriter1.write(List.of(new Pojo10()), writeSheet8);

                // 每个excelWriter都需要写入所有sheet页，
                .
                .
                .
                excelWriter8.write(List.of(new Pojo1()), writeSheet1);
                excelWriter8.write(List.of(new Pojo2()), writeSheet2);
                excelWriter8.write(List.of(new Pojo3()), writeSheet3);
                excelWriter8.write(List.of(new Pojo4()), writeSheet4);
                excelWriter8.write(List.of(new Pojo5()), writeSheet5);
                excelWriter8.write(List.of(new Pojo6()), writeSheet6);
                excelWriter8.write(List.of(new Pojo7()), writeSheet7);
                excelWriter8.write(List.of(new Pojo8()), writeSheet8);
                excelWriter8.write(List.of(new Pojo9()), writeSheet8);
                excelWriter8.write(List.of(new Pojo10()), writeSheet8);

            }
        }

        // 所有数据写完准备填充一下模板 并关闭流
        excelWriter1.fill(new PojoF(), writeSheet1);
        excelWriter1.close();

        excelWriter2.fill(new PojoF(), writeSheet1);
        excelWriter2.close();

        excelWriter3.fill(new PojoF(), writeSheet1);
        excelWriter3.close();

        excelWriter4.fill(new PojoF(), writeSheet1);
        excelWriter4.close();

        excelWriter5.fill(new PojoF(), writeSheet1);
        excelWriter5.close();

        excelWriter6.fill(new PojoF(), writeSheet1);
        excelWriter6.close();

        excelWriter7.fill(new PojoF(), writeSheet1);
        excelWriter7.close();

        excelWriter8.fill(new PojoF(), writeSheet1);
        excelWriter8.close();
    }

异常提示 jstat -gc

S0C S1C S0U S1U EC EU OC OU MC MU CCSC CCSU YGC YGCT FGC FGCT GCT 381440.0 382464.0 0.0 365747.5 4825600.0 3717913.6 11185152.0 2646498.6 172544.0 160786.2 20480.0 18335.6 191 17.709 5 1.060 18.769

jstack

"org.springframework.kafka.KafkaListenerEndpointContainer#3-0-C-1" #118 prio=5 os_prio=0 tid=0x00007fd6403f2000 nid=0x85 runnable [0x00007fd650af4000] java.lang.Thread.State: RUNNABLE at org.apache.xmlbeans.impl.store.Locale.findNthChildElem(Locale.java:1318) at org.apache.xmlbeans.impl.store.Xobj.insert_element_user(Xobj.java:2029) at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:373) at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:311) at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontsImpl.setFontArray(CTFontsImpl.java:89) at org.apache.poi.xssf.model.StylesTable.writeTo(StylesTable.java:668) at org.apache.poi.xssf.model.StylesTable.commit(StylesTable.java:740) at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:467) at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:472) at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:221) at org.apache.poi.xssf.streaming.SXSSFWorkbook.write(SXSSFWorkbook.java:969) at com.alibaba.excel.context.WriteContextImpl.finish(WriteContextImpl.java:381) at com.alibaba.excel.write.ExcelBuilderImpl.finish(ExcelBuilderImpl.java:99) at com.alibaba.excel.ExcelWriter.finish(ExcelWriter.java:140) at com.alibaba.excel.ExcelWriter.close(ExcelWriter.java:155) at com.c.e.pojo.bo.EntInfoExcelBatchBO.closeAllExcelWriter(EntInfoExcelBatchBO.java:109) at com.c.e.report.entInfo.batch.ReportEntInfoBatch.generateExcel(ReportEntInfoBatch.java:188) at com.c.e.report.entInfo.batch.ReportEntInfoBatch.generateBatch(ReportEntInfoBatch.java:125) at com.c.e.service.ReportService.reportBatchGenerate(ReportService.java:391) at com.c.e.kafka.KafkaConsumer.reportBatchGenerateConsumer(KafkaConsumer.java:262) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:171) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:120) at org.springframework.kafka.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:48) at org.springframework.kafka.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:330) at org.springframework.kafka.listener.adapter.RecordMessagingMessageListenerAdapter.onMessage(RecordMessagingMessageListenerAdapter.java:86) at org.springframework.kafka.listener.adapter.RecordMessagingMessageListenerAdapter.onMessage(RecordMessagingMessageListenerAdapter.java:51) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeOnMessage(KafkaMessageListenerContainer.java:2069) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeOnMessage(KafkaMessageListenerContainer.java:2051) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeRecordListener(KafkaMessageListenerContainer.java:1988) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeWithRecords(KafkaMessageListenerContainer.java:1928) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeRecordListener(KafkaMessageListenerContainer.java:1814) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeListener(KafkaMessageListenerContainer.java:1531) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.pollAndInvoke(KafkaMessageListenerContainer.java:1178) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.run(KafkaMessageListenerContainer.java:1075) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.lang.Thread.run(Thread.java:750)

问题描述

由于项目太大只能写示例代码模拟我们的场景，实际场景生成8个文件，每个文件10个左右的sheet页，8个文件一共50个sheet页其中一些sheet页还包含图片列。
每个sheet页有一些特殊样式的表头所以表头是使用模板填充的
8个文件对应8个excelWriter所有数据写完一起调用close方法关闭，就是在阶段 close方法会一直卡死，可以看上面jstack状态

bajie-git

2023-03-03

我想知道是我使用的姿势不对么。为什么会出现这种情况，我尝试debug跟踪只是发现close时执行org.apache.xmlbeans.impl.store.Locale.findNthChildElem方法一直在查找xobj这个链表，链表中很多对象都是null

bajie-git

2023-03-03

业务上可以改成分开close吗

gongxuanzhang

2023-03-04

业务上可以改成分开close吗

是的，我正有这个打算，但分开不一定会解决问题，我想知道为什么我会出现这种情况，close方法为什么会执行的那么缓慢

bajie-git

2023-03-04

close其实是finish方法，包含真正的写入，不仅仅是一个关闭流的操作

gongxuanzhang

2023-03-05

close其实是finish方法，包含真正的写入，不仅仅是一个关闭流的操作

嗯，我理解这个但我觉得事情应该不会这么简。因为这解释不了多个excelWriter进行关闭为什么会耗时这么久，足足有6个小时，，，

bajie-git

2023-03-05

分享一下最后解决得办法及原因

这几天反复得看日志和堆栈信息最后在jstack中看到这么一条 at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontsImpl.setFontArray(CTFontsImpl.java:89) 也就是最后close得时候一直在处理和字体有关得事情，突然想起来我们代码中有个继承AbstractCellStyleStrategy的handler用来处理自定义表头和内容的样式

@Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            if ("目录".equals(sheet.getSheetName())) {
                return;
            }
            Cell cell = context.getCell();
            CellStyle cellStyle = cell.getCellStyle();

            Workbook workbook = sheet.getWorkbook();
            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            cellStyle.setFont(font);

            cellStyle.setBorderLeft(BorderStyle.MEDIUM);
            cellStyle.setLeftBorderColor(color);
            cellStyle.setBorderTop(BorderStyle.MEDIUM);
            cellStyle.setTopBorderColor(color);
            cellStyle.setBorderRight(BorderStyle.MEDIUM);
            cellStyle.setRightBorderColor(color);
            cellStyle.setBorderBottom(BorderStyle.MEDIUM);
            cellStyle.setBottomBorderColor(color);
    }

原因就在createFont上，，，，创建了太多字体当达到一定量级以后就会出现这种情况

bajie-git

2023-03-07

感谢记录

gongxuanzhang

2023-03-07

[alibaba/easyexcel]请教大佬，我这种大批量数据写入为什么会卡死在close阶段

回答

相关问题