java - Unable to delete the Empty Rows Excel File using Apache POI

Question

I have been trying my code to delete the empty rows inside my excel file! my code is:

private void shift(File f){
    File F=f;
    HSSFWorkbook wb = null;
    HSSFSheet sheet=null;
    try{
        FileInputStream is=new FileInputStream(F);

         wb= new HSSFWorkbook(is);
         sheet = wb.getSheetAt(0);
         for(int i = 0; i < sheet.getLastRowNum(); i++){
if(sheet.getRow(i)==null){
    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
    i--;
}
}

FileOutputStream fileOut = new FileOutputStream("C:/juni1.xls");
wb.write(fileOut);
fileOut.close();
        //Here I want to write the new update file without empty rows! 
    }
    catch(Exception e){
        System.out.print("SERRO "+e);
    }

}

The code has no effect at all. Can any body tell me what is the problem please help me i have been trying to do the thing since last 10 hours. thanks in advance!

Answer 1

There will be two cases when any row is blank.

1. First, the Row is in between the other rows, but never be initialized or created. In this case Sheet.getRow(i) will be null.
2. And Second, the Row was created, its cell may or may not get used but now all of its cells are blank. In this case Sheet.getRow(i) will not be null. (you can check it by using Sheet.getRow(i).getLastCellNum() it will always show you the count same as other rows.)

In general case the second condition occurs. Perhaps in your case, it should be the reason. For this you need to add additional condition to check whether all the cells are blank or not.

    for(int i = 0; i < sheet.getLastRowNum(); i++){
        if(sheet.getRow(i)==null){
            sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            i--;
        continue;
        }
        for(int j =0; j<sheet.getRow(i).getLastCellNum();j++){
            if(sheet.getRow(i).getCell(j).toString().trim().equals("")){
                isRowEmpty=true;
            }else {
                isRowEmpty=false;
                break;
            }
        }
        if(isRowEmpty==true){
            sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            i--;
        }
    }