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?
Best Answer
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)