delete all empty rows in excel file with JAVA

Question

can someone tell me how to iterate and delete all the empty rows in excel file using apache poi? I am calling those 2 functions with row indexes that i wish to delete, but the second one (shift) is always giving me Minumum row index is 0 exceltion.

 public static void removeRow(HSSFSheet sheet, int rowIndex) {

   HSSFRow removingRow = sheet.getRow(rowIndex);
   if (removingRow != null) {
       sheet.removeRow(removingRow);
   }
 }

 public static void shiftRow(HSSFSheet sheet, int rowIndex) {
   int lastRowNum = sheet.getLastRowNum();
   if (rowIndex >= 0 && rowIndex < lastRowNum) {
       sheet.shiftRows(rowIndex+1, rowIndex+1, -1);
   }
 }

Thanks

score 0 · Accepted Answer · answered Dec 19 '12 at 10:13

0

The problem was in columns that were merged in the excel file.

answered Dec 19 '12 at 10:13

gospodin

1,133
4
22
42

score 0 · Answer 2 · answered Mar 08 '17 at 09:27

sheet.removeRow(row)

will delete the row, but it will create a white space(hole), or an empty row in your data, hence will make it inconsistent. What you can do is after deleting you can shift your rows, which means moving all the remaining rows one step up using this:

int rowToDeleteIndex = // The row number you want to delete
int lastRowIndex = worksheet.getLastRowNum();
worksheet.shiftRows(rowToDeleteIndex + 1, lastIndex, -1);

delete all empty rows in excel file with JAVA

2 Answers2