CODEWITHSUNDEEP
javaapache-poixls
Akshay
Akshay

Reputation: 177

Export to XLS using POI

I want to export more than 200000 rows with 60 Columns from database to the client machine by using browser. I am using Servlet and POI 3 .8 version jars and below code.

ServletOutputStream servletOutputStreamObj = response.getOutputStream();
HSSFWorkbook workBook =  new HSSFWorkbook ();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=\"" + strFileName + ".xls\"");
workBook.write(servletOutputStreamObj);

This code is working fine up to 65535 rows. How to make it for more number of records. (xls/xlsx both format are fine).

Can anyone help me?

Upvotes: 1

Views: 3153

Answers (2)

jmcnamara
jmcnamara

Reputation: 41524

You are using a HSSFWorkbook object which creates an Excel xls file which is limited to 65,535 rows.

Instead you should use a XSSFWorkbook which creates an Excel xlsx file which in turn supports 1,048,576 rows. You should also change your ContentType and the file extension in that case.

And if you run out of memory using XSSFWorkbook try SXSSFWorkbook.

Here is the background to the 3 classes from the Apache POI website.

Upvotes: 2

UVM
UVM

Reputation: 9914

Probably you can take a look at this example for your need.This sample code tries to create excel document upto 100000 records.

Demonstrates a workaround you can use to generate large workbooks and avoid OutOfMemory exception

Upvotes: 1

Related Questions