When generating Excel Sheets it is sometimes necessary to remove rows which match a given criteria afterwards. The first idea for this would be to use removeRow(Row row) but this has the problem that it deletes all the row contents and leaves the empty row in your sheet which is probably not what you want. To remove the rows as a whole the shiftRows(int startRow, int endRow, int n) is needed.
Lets say we have a xls file with column A being the title which is always filled but we want to remove every row in which the data column B is not filled. For this case the following snippet would be appropriate

[java]
/**
* Removes rows where the column B is empty.
* @param sheet the sheet where rows should be removed
*/
private int removeEmptyRows(final Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
boolean isRowEmpty;
if (sheet.getRow(i)==null) {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
i–;
continue;
}
final Row actualRow = sheet.getRow(i);
isRowEmpty =
actualRow.getCell(1).toString().trim().equals(“”);
if (isRowEmpty) {
if (i == sheet.getLastRowNum()) {
sheet.removeRow(actualRow);
} else {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
i–;
}
}
}
[/java]
We define in the for loop to get through all available rows and check within the first if clause if we have a row which is completely null and shift it if yes. The shiftRows takes the start- and the end-row as parameters and shift the so selected row block as is defined by the last parameter int n. This means when we use the -1 in the example all the selected rows will be shifted one up. If we would take a positive 1 as parameter for int n they would be shifted one down.
After that we get the actual row and perform our check on it to determine if the column B is empty. Column B in POI ist the cell 1 as counting of the cells starts at 0.
The next if clause checks if there was an empty row found and if yes the row gets shifted up again. The additional inner if clause is needed in the case the last row contains an empty column B because this can’t be shifted as we cannot select the next row after the last row.