How to use POI SXSSF to read a large spreadsheet

Question

I am trying to read an xls file by using SXSSF. I have read about SXSSF, but do not understandexactly how to use it. So I am running into some problems.

Can anybody help me with the java code for reading large xls files (some 100,000 rows and 7-8 sheets).

(Edit from the comments)

Here is what I have tried:

Workbook workBook = new SXSSFWorkbook(200); 
workBook = WorkbookFactory.create(inputStream); 
Sheet sheet = workBook.getSheetAt(0); 
int totalRows = sheet.getPhysicalNumberOfRows(); 

for (int i=0; i<totalRows; i++) { 
    Row row = sheet.getRow(i); 
    int totalCols = row.getPhysicalNumberOfCells(); 
    for(int j=0; j<totalCols; j++) { 
        Cell cell = row.getCell(j); 
    } 
 }

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) [This](http://stackoverflow.com/questions/4752456/memory-efficient-java-library-to-read-excel-files) or [That](http://stackoverflow.com/questions/4085517/which-is-the-best-api-to-read-large-sized-excel-files-in-java) just for instance? — nobeh, Apr 07 '12 at 09:50
Workbook workBook = new SXSSFWorkbook(200); workBook = WorkbookFactory.create(inputStream); Sheet sheet = workBook.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); for(int i=0; i — abhi, Apr 07 '12 at 09:54
Try first running provided examples of API you are using, then try with simple scenarios and only then try your case, so when problem arise, you know where to look. — d1e, Apr 07 '12 at 11:01
SXSSF is write only. Please check this one [previous issue][1] [1]: http://stackoverflow.com/questions/12513981/reading-data-from-xlsx-sxssfsheet-with-apache-poi-java — glassfish, Apr 02 '13 at 14:01

score 23 · Answer 1 · edited Sep 12 '20 at 14:18

23

SXSSF is only to write large excel files (xlsx) and not to read.

To read large excel files, please refer to SAX parsing based event handling API of Apache POI at: https://poi.apache.org/components/spreadsheet/how-to.html.

A very good working example is present at: https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java

edited Sep 12 '20 at 14:18

gws

459
1
7
16

answered Jul 01 '15 at 17:07

vijayinani

2,548
2
26
48

The example has moved to https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java – Matt Wallis Jan 18 '22 at 16:36

score 1 · Answer 2 · answered Apr 02 '20 at 12:56

1

SXSSF (since 3.8-beta3) – is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced

As you can see, it is clearly specified SXSSF is used when large spreadsheets are to be produced. You can use XSSF. It is similar and also is good enough.

answered Apr 02 '20 at 12:56

Chetan Oswal

430
9
21

Unfortunately, XSSF when reading small excel file, sometimes, consumes all the server memory and throws OutOfException :/ – Muflix Mar 27 '22 at 11:42

How to use POI SXSSF to read a large spreadsheet

2 Answers2

Linked