How to Delete or Remove empty rows in Excel sheet using java

Question

Input Excel file.....

25010082512 25002207512 1044 1044 NGN NGN

36620841728 36617009228 1066 1066 NGN NGN

import java.io.File; 
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class RemoveEmptyCellInExcel {

    //shifting empty columns
    public static void shift(File f){
        File F=f;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet=null;
        Boolean isRowEmpty=false;
        try{
            FileInputStream is=new FileInputStream(F);

             workbook= new HSSFWorkbook(is);
             sheet = workbook.getSheetAt(0);
             //sheet.setDisplayGridlines(false);

              for(int i = 3; i < sheet.getLastRowNum(); i++){
                  if(sheet.getRow(i)==null){
                      sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                      i--;
                      continue;
                      }
              for(int j=0; j<sheet.getRow(i).getLastCellNum();j++){

              if(sheet.getRow(i).getCell(j).toString().trim().equals("")) 
                {
                   isRowEmpty=true;
              }else { 
              isRowEmpty=false;
              break;
              }
          }
          if(isRowEmpty==true){
              sheet.shiftRows(i+ 1, sheet.getLastRowNum(), -1);
              i--;
              } 
          }

          //Writing output to the same file. 
         FileOutputStream fileOut = new FileOutputStream("--------"); 
         workbook.write(fileOut); 
         fileOut.close();
         System.out.println("Successfully wrote the content in the file");
    }
    catch(Exception e){
        e.printStackTrace();
    }
}


public static void main(String args[]) {
    //Input file path
        File f=new File("------------");
        RemoveEmptyCellInExcel.shift(f);
}

}

I need following output.....

25010082512 25002207512 1044 1044 NGN NGN
36620841728 36617009228 1066 1066 NGN NGN

Answer 1

your code is over complicated try to simplifie try using apach csv :

public static void shift(File file){
final FileReader fr = new FileReader(file.getPath());
final File out = new File(path);
try(CSVPrinter printer = new CSVPrinter(out, CSVFormat.DEFAULT
  .withDelimiter(' ')) {
     final Iterable<CSVRecord> records =  
     CSVFormat.DEFAULT.withDelimiter(' ').parse(fr);
     for (final CSVRecord record : records) { 
        printer.printRecord(record.get(0),record.get(1), ..., 
        record.get(record.size()-1));
     }

Answer 2

Try to use this code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class RemoveEmptyCellInExcel {

    //shifting empty columns
    public static void shift(File f){
        HSSFWorkbook workbook;
        HSSFSheet sheet;
        int firstColumn;
        int endColumn;
        boolean isRowEmpty = true;
        try{
            FileInputStream is=new FileInputStream(f);

            workbook= new HSSFWorkbook(is);
            sheet = workbook.getSheetAt(0);
            //sheet.setDisplayGridlines(false);

            //block to set column bounds
            Iterator<Row> iter = sheet.rowIterator();
            firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
            endColumn = 0;
            while (iter.hasNext()) {
                Row row = iter.next();
                short firstCell = row.getFirstCellNum();
                if (firstCell >= 0) {
                    firstColumn = Math.min(firstColumn, firstCell);
                    endColumn = Math.max(endColumn, row.getLastCellNum());
                }
            }

            // main logic block
            for (int i = 0; i< sheet.getLastRowNum(); i++) {
                if (sheet.getRow(i) != null) {
                    isRowEmpty = true;
                    Row row = sheet.getRow(i);
                    for (int j = firstColumn; j < endColumn; j++) {
                        if (j >= row.getFirstCellNum() && j < row.getLastCellNum()) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {
                                if (!cell.getStringCellValue().equals("")) {
                                    isRowEmpty = false;
                                    break;
                                }
                            }
                        }
                    }
                    //if empty
                    if (isRowEmpty) {
                        System.out.println("Found empty row on: " + row.getRowNum());
                        sheet.shiftRows(row.getRowNum() + 1, sheet.getLastRowNum(), -1);
                        i--;
                    }
                }
                // if row is null
                else{
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                    i--;
                }
            }
            //Writing output to the same file.
            FileOutputStream fileOut = new FileOutputStream("Test.xls");
            workbook.write(fileOut);
            fileOut.close();
            System.out.println("Successfully wrote the content in the file");
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {
        //Input file path
        File f=new File("Test.xls");
        RemoveEmptyCellInExcel.shift(f);
    }
}