Removing a row from an Excel sheet with Apache POI HSSF

Question

I'm using the Apache POi HSSF library to import info into my application. The problem is that the files have some extra/empty rows that need to be removed first before parsing.

There's not a HSSFSheet.removeRow( int rowNum ) method. Only removeRow( HSSFRow row ). The problem with this it that empty rows can't be removed. For example:

sheet.removeRow( sheet.getRow(rowNum) );

gives a NullPointerException on empty rows because getRow() returns null. Also, as I read on forums, removeRow() only erases the cell contents but the row is still there as an empty row.

Is there a way of removing rows (empty or not) without creating a whole new sheet without the rows that I want to remove?

Answer 1

 /**
 * Remove a row by its index
 * @param sheet a Excel sheet
 * @param rowIndex a 0 based index of removing row
 */
public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum=sheet.getLastRowNum();
    if(rowIndex>=0&&rowIndex<lastRowNum){
        sheet.shiftRows(rowIndex+1,lastRowNum, -1);
    }
    if(rowIndex==lastRowNum){
        HSSFRow removingRow=sheet.getRow(rowIndex);
        if(removingRow!=null){
            sheet.removeRow(removingRow);
        }
    }
}

Answer 2

I know, this is a 3 year old question, but I had to solve the same problem recently, and I had to do it in C#. And here is the function I'm using with NPOI, .Net 4.0

    public static void DeleteRow(this ISheet sheet, IRow row)
    {
        sheet.RemoveRow(row);   // this only deletes all the cell values

        int rowIndex = row.RowNum;

        int lastRowNum = sheet.LastRowNum;

        if (rowIndex >= 0 && rowIndex < lastRowNum)
        {
            sheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
        }
    }

Answer 3

Something along the lines of

int newrownum=0;
for (int i=0; i<=sheet.getLastRowNum(); i++) {
  HSSFRow row=sheet.getRow(i);
  if (row) row.setRowNum(newrownum++);
}

should do the trick.

Answer 4

The HSSFRow has a method called setRowNum(int rowIndex).

When you have to "delete" a row, you put that index in a List. Then, when you get to the next row non-empty, you take an index from that list and set it calling setRowNum(), and remove the index from that list. (Or you can use a queue)

Answer 5

My special case (it worked for me):

    //Various times to delete all the rows without units
    for (int j=0;j<7;j++) {
      //Follow all the rows to delete lines without units (and look for the TOTAL row)
      for (int i=1;i<sheet.getLastRowNum();i++) {
        //Starting on the 2nd row, ignoring first one
        row = sheet.getRow(i);
        cell = row.getCell(garMACode);
        if (cell != null) 
        {
          //Ignore empty rows (they have a "." on first column)
          if (cell.getStringCellValue().compareTo(".") != 0) {  
            if (cell.getStringCellValue().compareTo("TOTAL") == 0) {
              cell = row.getCell(garMAUnits+1);
              cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
              cell.setCellFormula("SUM(BB1" + ":BB" + (i - 1) + ")");
            } else {
              cell = row.getCell(garMAUnits);
              if (cell != null) {
                int valor = (int)(cell.getNumericCellValue());
                if (valor == 0 ) {
                  //sheet.removeRow(row);
                  removeRow(sheet,i);
                }
              }
            }
          }
        }
      }
    }

Answer 6

This answer is an extension over AndreAY's answer, Giving you complete function on deleting a row.

public boolean deleteRow(String sheetName, String excelPath, int rowNo) throws IOException {

    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;

    try {
        FileInputStream file = new FileInputStream(new File(excelPath));
        workbook = new XSSFWorkbook(file);
        sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            return false;
        }
        int lastRowNum = sheet.getLastRowNum();
        if (rowNo >= 0 && rowNo < lastRowNum) {
            sheet.shiftRows(rowNo + 1, lastRowNum, -1);
        }
        if (rowNo == lastRowNum) {
            XSSFRow removingRow=sheet.getRow(rowNo);
            if(removingRow != null) {
                sheet.removeRow(removingRow);
            }
        }
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File(excelPath));
        workbook.write(outFile);
        outFile.close();


    } catch(Exception e) {
        throw e;
    } finally {
        if(workbook != null)
            workbook.close();
    }
    return false;
}

Answer 7

I'm trying to reach back into the depths of my brain for my POI-related experience from a year or two ago, but my first question would be: why do the rows need to be removed before parsing? Why don't you just catch the null result from the sheet.getRow(rowNum) call and move on?