Writing to Excel with POI

[fayevalentine]

Hello,

I am trying to generate an excel file with POI Library, so far here is a piece of code, I get about 150,000 registers between 2 dates

FileInputStream fis = new FileInputStream(new File(my_template));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet sheet = workbook.getSheetAt(0);

FileOutputStream fos1 =new FileOutputStream(new File(path_to_report));

while (date1 <= date2 )
{ ...
row1 = sheet.createRow(ln_excel);
ln_excel++;
row1.createCell(0).setCellValue(rs.getString(2));
... (about 15 columns)
fos1.flush();
}
fos1.close();


my process takes too much time,

Any hint to improve my code?

Thank in advance.

 

[Diancecht]

The only improvement I can think of is putting the flush out of the loop. How much time is is taking? Is this the code that consumes all the time or the database query is also slow?

Cheers,
Dian

 

[fayevalentine]

Hello Dian

My query is also very slow because I make several UNIONs it takes between 30 and 40 minutes, I make JDBC connection but I think the result is not that big, The code above sometimes give me OutOfMemory Error when it is creating excel file but sometimes not.

Right now I am trying with this:

Workbook wb = new SXSSFWorkbook(500) instead of XSSFWorkbook workbook = new XSSFWorkbook (fis);

but it is said that it is only used to write, I cannot update my file this way.