Delete Excel rows programatically using Java

Question

Finding last column cell values. if cell value is "Not Valid" or "Not Applicable", delete the entire row.

The code I have written so far is as follows:

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<=lastIndex; i++)
    {
      row=sheet.getRow(i);
      if(row.getCell(getLastCell)!=null && (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();
    }
}

}

With the above code, using row.setZeroHeight(true); method I can able to hide the rows.

With the above code, using sheet.removeRow(row); method I can able to empty all the cells in that particular row.

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?

Answer 1

Try this one...

 SVTableModel model = new SVTableModel(sheet);
 int lastIndex = model.getRowCount();
 for (int i=1; i<=lastIndex; i++)
   {
  row=sheet.getRow(i);
  if((row.getCell(getLastCell)!=null) && (row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid") ||  row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable")))
  {
      row.setZeroHeight(true);
      sheet.removeRow(row);
  }

Answer 2

Perhaps using a VBA macro would be more helpful in this situation? VBA macros can be embedded into your spreadsheet, so running them could be a lot easier.

Here is a good place to get started with VBA:

http://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

Answer 3

Instead of sheet.removeRow(row); you can use the shiftRows(int startRow, int endRow, int n) instruction, that seems you have tried, that shifts rows between startRow and endRow n number of rows.

In your case it will be

// ... code before    ...
for (int i=0; i<=lastIndex; i++)
        {
          row=sheet.getRow(i);
          if(row.getCell(getLastCell)!=null && (row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid") || row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable")))
              {
               // This should shift up by 1 row all the rows below
               sheet.shiftRows(i+1, lastIndex, -1);
               i--; // Since you move row at i+1 to i you have to recompute the i-th row
            }
        }
// ... code after ...

Check org.apache.poi.hssf.usermodel.HSSFSheet Documentation for more information