El blog de Juan Palómez

5 febrero 2017

Generic POI troubleshooting

Filed under: Java, POI, programming, xlsx — thisisoneball @ 20:45

This is a compilation of things you can check in case you’re having trouble using POI in your Java program, particularly when creating .xlsx files:

  • Slow execution or program crashes with “Could not reserve enough space for object heap” , “java.lang.OutOfMemoryError: Java heap space”, or similar:
    • increase heap size (e.g. -Xmx1000m), for sizes bigger than ~1500MB you must use a 64bit JVM
    • monitor the output of freeMemory() in different parts of your program or inside loops and see the evolution
    • make sure you free up POI objects when no longer used
    • use File objects and not Stream objects for POI stuff, for example for reading your input .xlsx files
    • if allowed by your requirements, split the output into multiple .xlsx files instead of a single file containing multiple tabs
    • make sure you are not creating a style for each cell, for each needed style create just one and apply to multiple cells (*)
    • run program with  -XX:+HeapDumpOnOutOfMemoryError, on crash it will create a big .hprof file, open it with Eclipse MAT (or similar program) and analyze which objects are taking up more resources
    • try a different POI version. You can verify which version is being used at runtime with Workbook.class.getProtectionDomain().getCodeSource().getLocation().getPath()
    • try with both 32bit and 64bit JVM
    • if allowed by your requirements, try with both HSSF and XSSF. Also SXSSF is the most lightweight of the 3, but also has less features
  • Execution is OK but Excel says the generated .xlsx file is corrupt/invalid
    • combined/merged cells may be overlapping. This could be detected at runtime, if not, open the generated .xlsx file as a ZIP file and check the files to see which are the overlapping cells
    • the styles.xml file inside the .xlsx file is not valid or too big, check (*)

Blog de WordPress.com.