Creating multiple sheets using Apache poi and servlets

Question

When i am creating multiple sheets using Apache poi and servlets. It is creating the sheet but not writing the data to file. I am trying to write the first 1000 records to sheet1 and next 1000 to sheet2 through below code, but not working

private void writeDataToExcelFile(String string,
        ArrayList<ArrayList<String>> excelData, OutputStream outputStream) {
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    String sheetName = "";
    sheetName = "Document-" + 0;
    HSSFSheet mySheet = myWorkBook.createSheet();
    HSSFRow myRow = null;
    HSSFCell myCell = null;
    for (int rowNum = 0; rowNum < excelData.size(); rowNum++) {
        ArrayList<String> rowData = excelData.get(rowNum);
        if(rowNum>0 && rowNum%1000 == 0)
        {
            sheetName = "Document-" + (rowNum/1000);
            mySheet = myWorkBook.createSheet();
        }
        myRow = mySheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < rowData.size(); cellNum++) {
            myCell = myRow.createCell(cellNum);
            myCell.setCellValue(rowData.get(cellNum));
        }
    }
    System.out.println("Last row:" + mySheet.getLastRowNum());
    System.out.println("Row number:" + mySheet.rowIterator().next().getRowNum());
    try {
        myWorkBook.write(outputStream);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

What is wrong with my logic.Please do the needful help. Thanks

Solution

When you loop through the dataset, you are wanting to split at row 1000 to start a new sheet, which is fine, however when you start the new sheet, the next row you create is row 1001 (the outer loop index variable)

myRow = mySheet.createRow(rowNum);

To get the effect you wish, change the loop to be something like this:

int currentRow = 0;
for (int rowNum = 0; rowNum < excelData.size(); rowNum++) 
{
  ArrayList<String> rowData = excelData.get(rowNum);

  if(currentRow == 1000)
  {
    sheetName = "Document-" + (rowNum/1000);
    mySheet = myWorkBook.createSheet();
    currentRow = 0;
  }
  myRow = mySheet.createRow(currentRow);
  for (int cellNum = 0; cellNum < rowData.size(); cellNum++) 
  {
    myCell = myRow.createCell(cellNum);
    myCell.setCellValue(rowData.get(cellNum));
  }

  currentRow++;
}

I haven't compiled this, so I don't know if it'll work right away, but it should point you in the right direction.

HTH

Edit
Thinking about this further, you could get the same effect from making a 1 line change to the original application (albeit losing a little bit of clarity):

myRow = mySheet.createRow(rowNum%1000);

Comments

[spt]

No this is giving error java.lang.IllegalArgumentException: Invalid row number (-999) outside allowable range (0..65535)

[Crollster]

Sorry, it's been a long day. Have updated last line - this time it should work.

[spt]

It is working fine, but the header(columns names) are not adding to new sheet

[Crollster]

Every time you create a new sheet, add the excelData.get(0) row at row 0. Easy :-) you already have the code there to do it.

[spt]

I want to remove the sheet, if there was no data. I am doing like this int index=0; if(sheet.getLastRowNum()==1) { index = myWorkBook.getSheetIndex(sheet); myWorkBook.removeSheetAt(index); } this is correct approach

[Crollster]

Probably. Looks reasonable to me, however I've never actually used Apache POI before. Why don't you ask a new question on this matter?

[spt]

How to reduce the download time in servlets. when i am downloading a excel from servlet it is taking much time.Actually after adding the cellstyle it is taking much time

[Crollster]

Faster internet connection? Have you determined the time taken to generate the file, compared to the time taken to download it? ie. are you sure it's the downloading that is taking the longer time?

[Crollster]

Are you running this on an AWS micro instance? This might cause you problems, since these instances can have their CPU usage throttled if it goes too high for too long.