CODEWITHSUNDEEP
javaexcelapache-poi
Madhusudan
Madhusudan

Reputation: 4815

Is there any limitation on size of excel file that Apache POI can handle?

I am using Apache POI to create a excel sheet and write data into it through java program and reading the same file in different java program. I heard that Apache POI can handle maximum size of 1 MB? Am I right?

If it is, then what is the option for this?

Upvotes: 0

Views: 12112

Answers (3)

vincent
vincent

Reputation: 11

I think there is a limitation for input file size, because I have a problem about reading a xlsx file which size is 200+MB, when I read it with apache poi, then the exception like below

Exception in thread "main" java.io.IOException: MaxLength (100000000) reached - stream seems to be invalid. and when I search the message from source code, it can not be changed, caz it's hard code, this is what I found

source code segment which confuse me

searching from the class that from above pic, there is no way to change the MAX_ENTRY_SIZE.

Upvotes: 1

Midhun Pottammal
Midhun Pottammal

Reputation: 1139

Apache POI can handle more than one MB file ,it uses your memory properly.Apache poi limitation list that not mentioned 

[https://poi.apache.org/spreadsheet/limitations.html][1]

Upvotes: 0

centic
centic

Reputation: 15872

The limits of Excel file formats are defined in class SpreadsheetVersion, as long as you have enough main-memory, you should be able to handle files up to these limits. For huge files using the default POI classes you will need a big amount of memory and that is likely where the "maximum file size" myth comes from.

There are ways to overcome the main-memory limitations if needed:

For writing very huge files, there is SXSSFWorkbook which allows to do a streaming write of data out to files (with certain limitations on what you can do as only parts of the file are held in memory).

For reading very huge files, take a look at the sample XLSX2CSV which shows how you can read a file in streaming fashion (again with some limitations on what information you can read out of the file, but there are ways to get at most of it if necessary)

Upvotes: 3

Related Questions