Unable to delete the Empty Rows Excel File using A

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--;
        }
    }

Answer 2

Okay I'm not sure exactly why you're having a problem but I think it's due to the way in which you are accessing the Workbook. Let's assume that the file you send down file is the location of the XLS workbook that you want to work with. The first thing you need to check is if that workbook exists because the POI's handling of existing v nonexistent workbooks is differnt. That is accomplished as such:

HSSFWorkbook wb;
HSSFSheet sheet;
if(file.exists()) {//The workbook has been created already
    wb = (HSSFWorkbook) WorkbookFactory.create(new FileInputStream(file));//Line 1
    sheet = wb.getSheetAt(0);
} else {//No workbook exists at the location the "file" specifies
    wb = new HSSFWorkbook();
    sheet = wb.createSheet();
}

A few notes: Line 1 throws 2 exceptions IOException from java.io.IOException and InvalidFormatException from org.apache.poi.openxml4j.exceptions.InvalidFormatException. So either throw the exceptions or surround with try-catch as you prefer.

Now if file always exists, than the if-else statement isn't really needed. However, to properly open the desired workbook I would use the WorkbookFactory.

As a final word, you can simplify your file saving by simply putting:

wb.write(new FileOutputStream("C:/juni1.xls");

Notice that you are also writing the saved file to a different location. Therefore your original workbook is untouched while the corrected one is in a different place.