CODEWITHSUNDEEP
javaspringapachehibernateapache-poi
John
John

Reputation: 708

New Sheet is not getting created when i do Apache POI on a loop

I am facing an issue when i write huge set of data to a Excel file with multiple sheets. I am using apache POI for the excel export.

    File file = new File("../path/file.xls");
    FileOutputStream fout = new FileOutputStream(file);
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    int limit = 100000,offset=0,count=0,sheetIndex=0;
    XSSFWorkbook workbook = new XSSFWorkbook();
    do{

        XSSFSheet sheet = null;
        if (file.exists() && sheetIndex > 0) {
            try {
                workbook = (XSSFWorkbook)WorkbookFactory.create(file);
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
            sheet = workbook.createSheet("Sheet-"+sheetIndex);
        }else{
            workbook = new XSSFWorkbook();
            sheet = workbook.createSheet("Sheet-"+sheetIndex);
        }
        Row header = sheet.createRow(0);
        //...Header row creation...
        List<DataType> result = query(criteria,offset,limit);
        offset = offset + limit;
        count = results.size();
        sheetIndex++;
        int rowCount = 1;
        for(DataType rowData : results){
            Row row =  sheet.createRow(rowCount++);
            //row creation....
        }
        try {
            workbook.write(outputStream);
            outputStream.writeTo(fout);
        } finally {
            outputStream.flush();
        }

    }while(count == limit);

    workbook.write(outputStream);
    outputStream.writeTo(fout);
    outputStream.close();
    fout.close();

In the loop i am fetching 100k records from DB and writing it to the excel, and each 100k i am creating a new Sheet until there are no more records from the DB.

This code have 2 issues 1. I am facing issues in opening the file, the excel file alert me that it has issues when i try to open, eventually when i say ok it loads the data.

  1. I can see there are only 1 sheet with 100k data though my DB contains 240M records. I also can see the loop is looping for number of times.

How can i get these issues resolved? really stucked!

Thanks in advance.

Upvotes: 0

Views: 1508

Answers (2)

John
John

Reputation: 708

I have changed WorkBook type to SXSSFWorkbook and set the flush limit to 100 and it worked. The performance has increased 5 times better than the XSSFWorkbook.

Upvotes: 0

Antot
Antot

Reputation: 3964

The XSSFWorkbook workbook is created multiple times and it overwrites the one created on previous loop. The workbook needs to be created only once.

I suggest changing the loop entry to the following:

XSSFWorkbook workbook = new XSSFWorkbook();
do {
    XSSFSheet sheet = workbook.createSheet("Sheet-"+sheetIndex);
    Row header = sheet.createRow(0);
    //...Header row creation...
    // remaining code

Upvotes: 4

Related Questions