How to load a large xlsx file with Apache POI?
Solution 1
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.
Solution 2
A improvement in memory usage can be done by using a File instead of a Stream. (It is better to use a streaming API, but the Streaming API's have limitations, see http://poi.apache.org/spreadsheet/index.html)
So instead of
Workbook workbook = WorkbookFactory.create(inputStream);
do
Workbook workbook = WorkbookFactory.create(new File("yourfile.xlsx"));
This is according to : http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream
Files vs InputStreams
"When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx XSSFWorkbook, the Workbook can be loaded from either a File or an InputStream. Using a File object allows for lower memory consumption, while an InputStream requires more memory as it has to buffer the whole file."
Solution 3
The Excel support in Apache POI, HSSF and XSSF, supports 3 different modes.
One is a full, DOM-Like in-memory "UserModel", which supports both reading and writing. Using the common SS (SpreadSheet) interfaces, you can code for both HSSF (.xls) and XSSF (.xlsx) basically transparently. However, it needs lots of memory.
POI also supports a streaming read-only way to process the files, the EventModel. This is much more low-level than the UserModel, and gets you very close to the file format. For HSSF (.xls) you get a stream of records, and optionally some help with handling them (missing cells, format tracking etc). For XSSF (.xlsx) you get streams of SAX events from the different parts of the file, with help to get the right part of the file and also easy processing of common but small bits of the file.
For XSSF (.xlsx) only, POI also supports a write-only streaming write, suitable for low level but low memory writing. It largely just supports new files though (certain kinds of append are possible). There is no HSSF equivalent, and due to back-and-forth byte offsets and index offsets in many records it would be pretty hard to do...
For your specific case, as described in your clarifying comments, I think you'll want to use the XSSF EventModel code. See the POI documentation to get started, then try looking at these three classes in POI and Tika which use it for more details.
Solution 4
POI now includes an API for these cases. SXSSF http://poi.apache.org/spreadsheet/index.html It does not load everything on memory so it could allow you to handle such file.
Note: I have read that SXSSF works as a writing API. Loading should be done using XSSF without inputstream'ing the file (to avoid a full load of it in memory)
Solution 5
Check this post. I show how to use SAX parser to process an XLSX file.
https://stackoverflow.com/a/44969009/4587961
In short, I extended org.xml.sax.helpers.DefaultHandler
whih processes XML structure for XLSX filez. t is event parser - SAX.
class SheetHandler extends DefaultHandler {
private static final String ROW_EVENT = "row";
private static final String CELL_EVENT = "c";
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private List<String> cellCache = new LinkedList<>();
private List<String[]> rowCache = new LinkedList<>();
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if (CELL_EVENT.equals(name)) {
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
} else if (ROW_EVENT.equals(name)) {
if (!cellCache.isEmpty()) {
rowCache.add(cellCache.toArray(new String[cellCache.size()]));
}
cellCache.clear();
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
// Output after we've seen the string contents
if(name.equals("v")) {
cellCache.add(lastContents);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
public List<String[]> getRowCache() {
return rowCache;
}
}
And then I parse the XML presending XLSX file
private List<String []> processFirstSheet(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
SheetHandler handler = new SheetHandler(sst);
XMLReader parser = fetchSheetParser(handler);
Iterator<InputStream> sheetIterator = r.getSheetsData();
if (!sheetIterator.hasNext()) {
return Collections.emptyList();
}
InputStream sheetInputStream = sheetIterator.next();
BufferedInputStream bisSheet = new BufferedInputStream(sheetInputStream);
InputSource sheetSource = new InputSource(bisSheet);
parser.parse(sheetSource);
List<String []> res = handler.getRowCache();
bisSheet.close();
return res;
}
public XMLReader fetchSheetParser(ContentHandler handler) throws SAXException {
XMLReader parser = new SAXParser();
parser.setContentHandler(handler);
return parser;
}
CosmicGiant
Programmer and Indie Game-developer. Most knowledge in Java and C#, but also have some on Python, C, C++, HTML, CSS, JavaScript and Pascal.
Updated on July 09, 2022Comments
-
CosmicGiant almost 2 years
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.
-
CosmicGiant almost 12 yearsCan you give me any sources on a way to implement loading an existing file? All i see on that link is for creating purposes, which is not of use in my case.
-
Alfabravo almost 12 yearsI have read that SXSSF works as a writing API. Loading should be done using XSSF without inputstream'ing the file (to avoid a full load of it in memory)
-
CosmicGiant almost 12 yearsHow can I load it without ImputStream'ing the file then? The load (read) is the problem here, not the save (write).
-
Alfabravo almost 12 yearsThat could be solved reading this thread. Hopefully stackoverflow.com/questions/11154678/…
-
CosmicGiant almost 12 yearsNope. The following code still results in
OutOfMemoryError
with even with-Xmx2048m
:OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath()); workbook = new XSSFWorkbook(opcPackage);
--- There is still the event-based API (XSSF_SAX
) to try, but I'm unsure how to do it. -
rjdkolb almost 10 yearsAs I recall .create 'creates' a workbook and not the actual file. Correct me if I am wrong.
-
rjdkolb almost 10 yearsIt's a deceiving API name :)
-
Alfabravo over 8 years@Panther yes, I added that to a comment right here, some time ago. Might add it to the actual answer to make it clearer. Thanks.
-
darkman97i over 8 yearsGoes right except for empty cell, which are not returned and that caused to me a problem because I'm not able to identify which column was really missing on each row ( only I know have been returned less than expected )
-
Anthony Ruffino almost 8 yearsThis solution is great. On the git page there is a new usage defined:
InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx")); Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).sheetIndex(0).open(is);
If all you need to do is iterate over the rows, this allows you to treat the worksheet exactly the same as if it was loaded in memory. -
themenace over 7 years@darkman97i:
Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK)
is now supported and resolves the issue. See here -
Arthur bauer about 7 yearsThis solution work well for reading the file content but does not work if you want to get the cell styles from the workbook.
-
abr over 6 yearswhere is your fetchSheetParser?
-
Yan Khonski over 6 years@abr Edited, check again.
-
abr over 6 yearsthanks, working as expected. Question: I'm using your code to read an extremly long xlsx file and happens that it gas 60 columns. However, each line gets returned 48 and some 59 (aparently it's ignoring the empty values on cells and not placing them in the array list). Tried to figure out how to output it on the startelement but so far, no success. Have any idea?
-
Yan Khonski over 6 yearsCurrently, it ignores empty cell. This is a homework for you - to fix this bug. When I have time, I will fix it myself and update the code.
-
abr over 6 yearsWill try, if I manage the work-around, I'll send an edit request to your post :) tag me if you manage it before I do
-
nguyenngoc101 about 5 yearsIt does not support .xls files when using the library. Do you have any suggestions? Thank you!
-
Umesh Sonawane over 4 yearsBut how to get cell formula while reding using handler if using your code can you suggest me @YanKhonski
-
DFW almost 4 yearsHow does this work with Formula cells? I'm not sure if the result of the formula is stored in the cell's data in addition to the formula, or if it's calculated as you read through them.
-
John almost 4 yearsWhere is the Streaming Reader class coming from (I don't have it in the version of poi I'm using).
-
Justin N over 3 yearsIt looks like it comes from: github.com/monitorjbl/excel-streaming-reader
-
saran3h over 3 yearsYou just saved my job (kidding!). It works so well. I tried everything and nothing worked. Just wished formula cells could be handled as well. For now i've requested clients not to upload files with formulas.
-
DaviM about 3 yearsThe answer in the link of this post has been updated to handle all rows and empty cells.