Search code examples
javaexcelapache-poixssf

XSSFCell causing lock while setting value when running with huge data

I am writing a huge data to Excel sheet. I am using XSSFWorkbook for excel creation.

For 20,000 rows the excel write is fine. But when I try to write a data of more than 1 Million ,the excel sheet is not coming out for more than 3 hours. When I analyzed jstack, I found that there is a lock which is getting created while 

XSSFCell cell = row.createCell( colNum );
cell.setCellValue( value );

setting value to the cell,

The below are the jstacks,

java.lang.Thread.State: RUNNABLE
        at org.apache.xmlbeans.impl.store.Saver$TextSaver.resize(Saver.java:1700)
        at org.apache.xmlbeans.impl.store.Saver$TextSaver.preEmit(Saver.java:1303)
        at org.apache.xmlbeans.impl.store.Saver$TextSaver.emit(Saver.java:1190)
        at org.apache.xmlbeans.impl.store.Saver$TextSaver.emitElement(Saver.java:962)
        at org.apache.xmlbeans.impl.store.Saver.processElement(Saver.java:476)
        at org.apache.xmlbeans.impl.store.Saver.process(Saver.java:307)
        at org.apache.xmlbeans.impl.store.Saver$TextSaver.saveToString(Saver.java:1864)
        at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546)
        at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436)
        **- locked <0x000000076354cdc0> (a org.apache.xmlbeans.impl.store.Locale)**
        at org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1500)
        at org.apache.poi.xssf.model.SharedStringsTable.getKey(SharedStringsTable.java:134)
        at org.apache.poi.xssf.model.SharedStringsTable.addEntry(SharedStringsTable.java:180)
        at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:350)
        at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:320)

How can we handle this. Please help me to resolve this issue.

Solution

  • SXSSFWorkbook might be a good solution (comment above) - if you want to use XSSFWorkbook you could use an XSSFFactory that provides a SharedStringsTable better suited to your needs (see your stacktrace and it is the SharedStringsTable that is causing the locking)

    XSSFWorkbook Constructor that takes an XSSFFactory instance as input

    Custom XSSFFactory example that creates a custom SharedStringsTable. This one uses a temp file for the shared strings data but you could modify it to keep the data in a HashMap.