CODEWITHSUNDEEP
javaexcel
suman tipparapu
suman tipparapu

Reputation: 90

Taking long time to generate excel file using POI

In my project i want to generate a Excel file with 85 columns and rows will increase day by day.

Right now i have around 6K records. while generating file its always giving me Java Heap space exception.

My heap space setting is

-Xmx2048M

-Xmx6144M

My code is

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FIELD OPERATIONS TRACKER");

for (int i=0; i < list.size(); i++)
{
 HSSFRow rowHeader = sheet.createRow(i);

 HSSFCell cell = rowHeader.createCell(0);
 cell.setCellValue(list.get(i).getName());

 cell = rowHeader.createCell(1);
 cell.setCellValue(list.get(i).getSeason());

//like this  I have 85 columns here
}

Please help me to improve performnce

Upvotes: 2

Views: 3813

Answers (2)

Vijay Kumar Chauhan
Vijay Kumar Chauhan

Reputation: 329

Constant.MAX_ROW_IN_MEMORY=1000; // A constant set to 1000

SXSSFWorkbook workBook = new SXSSFWorkbook(Constant.MAX_ROW_IN_MEMORY); workBook.setCompressTempFiles(true);

Try creating the excel file with the one argument constructor.. and setCompressTempFiles to true.. Successfully wrote 2 Lakh records in single excel with less than 1 GB of memory.

Upvotes: 0

rjdkolb
rjdkolb

Reputation: 11828

If you want to create .xlsx file and not .xls files, simply swap out your new HSSFWorkbook() with a new SXSSFWorkbook()

Workbook wb = new SXSSFWorkbook();

The guys at Apache POI did a good job of keeping things about the same. Just use the interfaces and not specific classes like HSSFCell and HSSFRow.

You will need two dependencies in your project : 

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>${apache.poi.version}</version>
</dependency>

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>${apache.poi.version}</version>
</dependency>

Upvotes: 1

Related Questions