How to load a large xlsx file with Apache POI?

by

I was in a similar situation with a webserver environment. The typical size of the uploads were ~150k rows and it wouldn’t have been good to consume a ton of memory from a single request. The Apache POI Streaming API works well for this, but it requires a total redesign of your read logic. I already had a bunch of read logic using the standard API that I didn’t want to have to redo, so I wrote this instead: https://github.com/monitorjbl/excel-streaming-reader

It’s not entirely a drop-in replacement for the standard XSSFWorkbook class, but if you’re just iterating through rows it behaves similarly:

import com.monitorjbl.xlsx.StreamingReader;

InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx"));
StreamingReader reader = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .sheetIndex(0)        // index of sheet to use (defaults to 0)
        .read(is);            // InputStream or File for XLSX file (required)

for (Row r : reader) {
  for (Cell c : r) {
    System.out.println(c.getStringCellValue());
  }
}

There are some caveats to using it; due to the way XLSX sheets are structured, not all data is available in the current window of the stream. However, if you’re just trying to read simple data out from the cells, it works pretty well for that.

More Related Contents:

  1. Processing large xlsx file
  2. Apache POI error loading XSSFWorkbook class
  3. Excel Cell Style issue
  4. Error While Reading Large Excel Files (xlsx) Via Apache POI
  5. Is the garbage collector guaranteed to run before Out of Memory Error?
  6. Java POI the supplied data appears to be in the Office 2007+ XML
  7. Limitation while generating excel drop down list with Apache POI
  8. Reading Specific rows of an excel [closed]
  9. In Java, what is the best way to determine the size of an object?
  10. Error java.lang.OutOfMemoryError: GC overhead limit exceeded
  11. “java.lang.OutOfMemoryError : unable to create new native Thread”
  12. Growing resident memory usage (RSS) of Java Process
  13. java.lang.OutOfMemoryError: Java heap space in Maven
  14. Unable to execute dex: GC overhead limit exceeded in Eclipse
  15. How much memory does a string use in Java 8?
  16. Apache POI add a Series name into LineChart
  17. Java: Unable to create new native thread
  18. PySpark: java.lang.OutofMemoryError: Java heap space
  19. Java – shutting down on Out of Memory Error
  20. Merging cells in Excel using Apache POI
  21. org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException:
  22. how to read exact cell content of excel file in apache POI
  23. How many string objects will be created in memory? [duplicate]
  24. Can’t get past 2542 Threads in Java on 4GB iMac OSX 10.6.3 Snow Leopard (32bit)
  25. Export to Excel JSF and PrimeFaces
  26. Create a password protected Excel file using Apache POI?
  27. Apache POI Line Chart colors
  28. “java.lang.OutOfMemoryError: PermGen space” in Maven build [duplicate]
  29. Reading data from xlsx with Apache POI’s SXSSFSheet
  30. How to avoid OOM (Out of memory) error when retrieving all records from huge table?

Leave a Comment