Read large Excel file .xlsx

Question

i'm using library

org.apache.poi 


XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

I'm trying

org.xml.sax

library, but cannot able convert it into workbook

NOTE : at end result i want XSSFWorkbook to be returned

the above code will go out of memory, any help will be appreciated ThankQ in advance

"NOTE : at end result i want XSSFWorkbook to be returned": That's not possible. XSSFWorkbook needs to be fully in memory. Try using SXSSFWorkbook https://poi.apache.org/spreadsheet/how-to.html#sxssf. — Axel Richter, Apr 07 '16 at 11:04
The question talks about reading, not writing, which SXSSFWorkbook is all about. — centic, Apr 07 '16 at 13:59

score 2 · Answer 1 · edited Jul 19 '18 at 13:13

If the input data is too large for the available memory, you have two options.

a) Provide more memory via the -Xmx java command line option

b) Use the Streaming-API of POI.

Option a) will be easy to do if the files eventually fit in memory. If the file is too large for the available physical memory, you will need to take a look at the streaming options, especially the sample XLSX2CSV shows how you can read data from arbitrarily large .xlsx files, albeit with some features that need to access multiple cells not available out of the box.

score 1 · Answer 2 · edited Jan 17 '20 at 05:37

1

InputStream is = new FileInputStream(new File("yourXLSX file path"));
Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(is))
for (Sheet sheet: workbook) {
    //System.out.println(sheet.getSheetName());
    for (Row r: sheet) {
        for (Cell c: r) {
            //System.out.println(c.getStringCellValue());
        }
    }
}

edited Jan 17 '20 at 05:37

Ömer Erden

7,680
5
36
45

answered Jan 17 '20 at 05:28

Pushpender Singh

52
1
5

1

But this has limitations- it throws unsupported exception for getPhysicalRows, getRow etc lots of unsupported methods – Deepa Dhanalakota Dec 07 '21 at 22:52

Read large Excel file .xlsx

2 Answers2

Linked