How to load a large xlsx file with Apache POI?

user_1253136 0
Viewed 117911

I have a large .xlsx file (141 MB, containing 293413 lines with 62 columns each) I need to perform some operations within.

I am having problems with loading this file (OutOfMemoryError), as POI has a large memory footprint on XSSF (xlsx) workbooks.

This SO question is similar, and the solution presented is to increase the VM's allocated/maximum memory.

It seems to work for that kind of file-size (9MB), but for me, it just simply doesn't work even if a allocate all available system memory. (Well, it's no surprise considering the file is over 15 times larger)

I'd like to know if there is any way to load the workbook in a way it won't consume all the memory, and yet, without doing the processing based (going into) the XSSF's underlying XML. (In other words, maintaining a puritan POI solution)

If there isn't tough, you are welcome to say it ("There isn't.") and point me the ways to a "XML" solution.

xssf apache-poi out-of-memory memory java
8 Answers

Based on monitorjbl's answer and test suite explored from poi, following worked for me on multi-sheet xlsx file with 200K records (size > 50 MB):

import com.monitorjbl.xlsx.StreamingReader;
. . .
try (
        InputStream is = new FileInputStream(new File("sample.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is);
) {
    DataFormatter dataFormatter = new DataFormatter();
    for (Sheet sheet : workbook) {
        System.out.println("Processing sheet: " + sheet.getSheetName());
        for (Row row : sheet) {
            for (Cell cell : row) {
                String value = dataFormatter.formatCellValue(cell);
            }
        }
    }
}
user_28473300

For latest code use this


InputStream file = new FileInputStream(
                    new File("uploads/" + request.getSession().getAttribute("username") + "/" + userFile));
Workbook workbook = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory
                    .bufferSize(4096) // index of sheet to use (defaults to 0)
                    .open(file); // InputStream or File for XLSX file (required)

Iterator<Row> rowIterator = workbook.getSheetAt(0).rowIterator();
 while (rowIterator.hasNext()) {
     while (cellIterator.hasNext()) {
         Cell cell = cellIterator.next();
        String cellValue = dataFormatter.formatCellValue(cell);
     }}
user_96575400
Related