Apache POI shift row to last vs shift rows up

0
votes

I am using Apache POI to process excel files. I want to delete the specified line, and then found that the first method not wort, I mean the deleted row should be moved to the end of this sheet, but it still has not moved.

Method1:

sheet.shiftRows(rowNum, rowNum, sheet.getLastRowNum() - rowNum, true, false);

Method2:

sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, false);

Anyone can help me?

UPDATE

After test, I found other prombel Apache POI delete row and append row

1
javaexcelapache-poi

1 Answers

1
votes

Using what apache poi version is method 1 even working? The logic is not the one for deleting a row. It only shifts the row rowNum to the position of the last row. So last row gets overwritten and row rowNum gets empty. This is not what I would call deleting a row. Furthermore this always leads to corrupt Excel files when XSSF (*.xlsx) is used. So clearly method 2.

But this one-liner will not always work. It only works if there are filled rows after the to delete row. If that is not the case, rowNum + 1 will be greater than sheet.getLastRowNum() and it fails. In that case we should shift the empty rowNum + 1 one up.

 void deleteRow(Sheet sheet, int rowNum) {
  if (rowNum > sheet.getLastRowNum()) return; // rowNum is outside the filled rows

  if (rowNum + 1 < sheet.getLastRowNum()) { // there are filled rows after the to delete row 
   sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, false);
  } else { // there are no filled rows after the to delete row, so shift rowNum + 1 one up
   sheet.shiftRows(rowNum + 1, rowNum + 1, -1, true, false);
  }
 }