title: Delete Excel rows programmatically using Java
body: <p>Finding last column cell values. if cell value is "Not Valid" or "Not Applicable", delete the entire row.</p>
  
  <p>The code I have written so far is as follows:</p>
  
  <pre><code>import java.io.FileInputStream;
  import java.io.FileNotFoundException;
  import java.io.FileOutputStream;
  import java.io.IOException;
  
  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  import org.apache.poi.ss.usermodel.Row;
  import org.apache.poi.ss.usermodel.Sheet;
  import org.apache.poi.ss.usermodel.WorkbookFactory;
  import org.apache.poi.ss.usermodel.Workbook;
  
  public class Column
  {
      public static void main(String[] args) throws InvalidFormatException, FileNotFoundException, IOException
      {
          try
          {
              Workbook wb = WorkbookFactory.create(new FileInputStream("C:/Users/Excel1.xlsx"));
              Sheet sheet = wb.getSheet("Retail-All");
  
              Workbook wb2 = new HSSFWorkbook();
              wb2 = wb;
  
              Row row;
              row = sheet.getRow(0);
              int getLastCell=row.getLastCellNum()-1;
              int lastIndex = sheet.getLastRowNum();
  
              for (int i=0; i&lt;=lastIndex; i++)
              {
                  row=sheet.getRow(i);
                  if(row.getCell(getLastCell)!=null &amp;&amp; (row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid) || row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable")))
                  {
                      sheet.removeRow(row);
                      //sheet.shiftRows(i, lastIndex, -1);
                  }
              }
              FileOutputStream fileOut = new FileOutputStream("C:/Users/shiftRows.xlsx");
              wb2.write(fileOut);
              fileOut.close();
          }
          catch (Exception e)
          {
              e.printStackTrace();
          }
      }
  }
  </code></pre>
  
  <p>With the above code, using row.setZeroHeight(true); method I can able to hide the rows. </p>
  
  <p>With the above code, using sheet.removeRow(row); method I can able to empty all the cells in that particular row.</p>
  
  <p>I found some code in net, but none of them are deleting the rows permanently.My requirement is to delete the rows permanently. How to code to meet my requirement?</p>