Writing a large resultset to an Excel file using POI
Oh. I think you're writing the workbook out 944,000 times. Your wb.write(bos) call is in the inner loop. I'm not sure this is quite consistent with the semantics of the Workbook class? From what I can tell in the Javadocs of that class, that method writes out the entire workbook to the output stream specified. And it's gonna write out every row you've added so far once for every row as the thing grows.
This explains why you're seeing exactly 1 row, too. The first workbook (with one row) to be written out to the file is all that is being displayed - and then 7GB of junk thereafter.
Writing a large Excel sheet with 60+ columns using Apache POI
Without further information I can only make a guess what´s your real problem. But I can tell you that apache poi can create excel sheets with more than 1000 columns and over 20k rows with colors, styles and stuff (done already).
Make sure you use the streaming api of apache.poi API
org.apache.poi.xssf.streaming
Here is the demo of apache
Big Grid Demo
UPDATE
As it states in the demo I linked to you should maybe use the new SXSSF user model (I used that if I remember correct) because it handels all the streaming stuff for you ;-)
SXSSF (Streaming Usermodel API)
How to load a large xlsx file with Apache POI?
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.
Related Topics
Pass a Local File in to Url in Java
Moving Items Around in an Arraylist
Encrypt and Decrypt with Aes and Base64 Encoding
How to Create a Hashmap with Two Keys (Key-Pair, Value)
When Are Java Strings Interned
How to Get Whole and Fractional Parts from Double in Jsp/Java
Should a "Static Final Logger" Be Declared in Upper-Case
Spring - Redirect After Post (Even with Validation Errors)
Passing Pointers Between C and Java Through Jni
Make a File/Folder Hidden on Windows with Java
Modifying Local Variable from Inside Lambda
Output an Image File from a Servlet
Is Polymorphism Possible Without Inheritance