XSSFCell causing lock while setting value when running with huge data

Question

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.

https://poi.apache.org/components/spreadsheet/ https://stackoverflow.com/questions/29778630/apache-poi-sxssf-and-xssf maybe try using SXSSF when writing instead of XSSF, it handles large files better — scigs, Sep 07 '18 at 17:48

score 0 · Accepted Answer · answered Oct 04 '18 at 19:47

0

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.

answered Oct 04 '18 at 19:47

PJ Fanning

953
5
13

Thanks PJ Fanning .. it worked. I used SXSSFWorkbook workbook = new SXSSFWorkbook( 10000 ); and all XSSF to SXSSF – user2699067 Nov 30 '18 at 18:16
I am facing an exception when I try to download data of more than 1 lakh with SXSSF. Posted here! https://stackoverflow.com/questions/53563035/sxssf-causing-attempting-to-write-a-row23237-in-the-range-0-23272-that-is-al – user2699067 Nov 30 '18 at 18:43

XSSFCell causing lock while setting value when running with huge data

1 Answers1