I need to open, alter and write an excel file. As the altering process is a bit more complex I'll leave it out, as the issue does not lie within it. The issue I am running into is while writing the file to disk. The issue starts with files at around 7MB size (doesn't matter if its formula or values).
I prepared following MVCE:
public static void main(String[] args) throws EncryptedDocumentException, IOException, InvalidFormatException {
String filePath = "C:\\temp";
String outputFilePath = "C:\\temp\\test";
ZipSecureFile.setMinInflateRatio(0);
File f = new File(filePath, "Test.xlsx");
try (XSSFWorkbook workBook = new XSSFWorkbook(f)) {
System.out.println("writing file");
File outputFile = new File(outputFilePath, f.getName());
try (FileOutputStream fos = new FileOutputStream(outputFile)) {
workBook.write(fos);
}
workBook.close();
}
System.out.println("fin");
}
This code already causes the issue I am running into. The exact stacktrace is:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:3236)
at java.io.ByteArrayOutputStream.toByteArray(ByteArrayOutputStream.java:191)
at org.apache.poi.openxml4j.opc.internal.MemoryPackagePartOutputStream.flush(MemoryPackagePartOutputStream.java:76)
at org.apache.poi.openxml4j.opc.internal.MemoryPackagePartOutputStream.close(MemoryPackagePartOutputStream.java:51)
at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:3575)
at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:462)
at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:467)
at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:236)
at test.TestWriteOriginalWorkbook.main(TestWriteOriginalWorkbook.java:25)
Though the trace itself varies from file to file, the exception itself stays the same all the time.
As far as I know, the only way to solves this, would be to increase the available memory for the application. Though if possible, I would like to avoid that. I just checked for my MaxHeapSize which defaults to around 260MB which seems quite low. So an increase to 1GB would be acceptable if necessary.
I ran this code with -Xmx1g and get the same exception with a different cause:
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3045)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3065)
at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3198)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:509)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:374)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2784)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(XMLNSDocumentScannerImpl.java:112)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:505)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:841)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:770)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1213)
at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:643)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3414)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1272)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1259)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:227)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:219)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.parseSheet(XSSFWorkbook.java:452)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:417)
at org.apache.poi.ooxml.POIXMLDocument.load(POIXMLDocument.java:184)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:286)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:323)
at test.TestWriteOriginalWorkbook.main(TestWriteOriginalWorkbook.java:21)
Cleaning up unclosed ZipFile for archive C:\temp\Test.xlsx
To summarize the question: What can I do to improve the performance of workBook.write(fos); to handle files above 7MB size (preferably up to 15MB)?
Test.xlsx is an excel file with values only. I created it with 1 in the whole first column as well as the second column until row 443269.
