How to remove empty cells in excel using java and apache poi

Question

Using Apache POI, I am extracting values from an Excel file and writing to another Excel file.

However I am getting empty cells in the output Excel file. How do I:

• detect at the end which rows are empty
• and then remove those rows?

I have added this part of code to remove rows. I am able to detect the rows which are empty but not able to remove it.

Please tell how to implement in the above code I added.

I did by adding this

public class Nlp_health {
private static boolean isRowEmpty;

public static void main(String[] args) throws IOException, ParseException, RowsExceededException, WriteException
{
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Sheet1");
    XSSFSheet sheet2 = wb.createSheet("Sheet2");
    XSSFSheet sheet3 = wb.createSheet("Sheet3");
    XSSFRow row_1 = sheet1.createRow((char)0);
    XSSFRow row_2 = sheet2.createRow((char)0);
    XSSFRow row_3 = sheet3.createRow((char)0);
    row_1.createCell(0).setCellValue("Column1");
    row_1.createCell(1).setCellValue("Column2");

    row_2.createCell(0).setCellValue("Column1");
    row_2.createCell(1).setCellValue("Column2");

    row_3.createCell(0).setCellValue("Column1");
    row_3.createCell(1).setCellValue("Column2");

    FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Master\\Desktop\\Nlp_health3.xlsx");
    wb.write(fileOut);
    fileOut.close();

Xls_Reader datatable = new Xls_Reader("C:\\Users\\Master\\Desktop\\SMB NLP Projects to Update 20140722.xlsx");
Xls_Reader datatable1 = new Xls_Reader("C:\\Users\\Master\\Desktop\\Nlp_health.xlsx");
int rows_count = datatable.getRowCount("Projects View");
System.out.println("Total number of rows = " + rows_count);
System.out.println();
for(int i=4; i<rows_count;i++)
{
    String milestone = datatable.getCellData("Projects View", "SMB Project Health", i);
    String project = datatable.getCellData("Projects View", "Name", i);
{
    //System.out.println(value);
    if (milestone.equals("Yellow")){
        System.out.println("1st step = " + project);

          datatable1.setCellData("Sheet1", "Column1", i, project);
          datatable1.setCellData("Sheet1", "Column2", i, "Yellow");
          //System.out.println(project);
    }
    if(milestone.equals("Red")){
        System.out.println("2nd step = " + project);
        datatable1.setCellData("Sheet1", "Column2", i, "Red");
        datatable1.setCellData("Sheet1", "Column1", i, project);

    }

    }
}

int rows_count_new = datatable1.getRowCount("Sheet1");
System.out.println("Number of rows are" + rows_count_new );
for (int j = 0; j <= rows_count_new; j++){
    String empty_cells = datatable1.getCellData("Sheet1", "Column1", j);
    System.out.println("Empty step1 = " + empty_cells);
     if(sheet1.getRow(j)==null){
            sheet1.shiftRows(j + 1, sheet1.getLastRowNum(), -1);
            j--;
        continue;
        }
        for(int k =0; k<sheet1.getRow(j).getLastCellNum();k++){
            if(sheet1.getRow(j).getCell(k).toString().trim().equals("")){
                isRowEmpty=true;
            }else {
                isRowEmpty=false;
                break;
            }
        }
        if(isRowEmpty==true){
            sheet1.shiftRows(j + 1, sheet1.getLastRowNum(), -1);
            j--;
        }


}





}

}

Answer 1

Assumption: As per your question it seems that you are worried about blank rows, not about blank cells coming within a row having not all cells blank.

A Blank row can be found in two ways.

1. First, the Row is in between the other rows, but never be initialized or created. In this case Sheet.getRow(i) will be null.
2. And Second, the Row was created, its cell may or may not get used but now all of its cells are blank. In this case Sheet.getRow(i) will not be null. (you can check it by using Sheet.getRow(i).getLastCellNum() it will always show you the count same as other rows.)

In general case the second condition occurs. Perhaps in your case, it should be the reason. For this you need to add additional condition to check whether all the cells are blank or not.

    for(int i = 0; i < sheet.getLastRowNum(); i++){
        if(sheet.getRow(i)==null){
           // condition of blank row so do whatever you want to do in this case
           // for example I have done nothing just removed that row.
            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){
         // condition of blank row so do whatever you want to do in this case
         // for example I have done nothing just removed that row.
            sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            i--;
        }
    }

Answer 2

The POI Javadoc clearly documents XSSFSheet#removeRow(), which will remove the entire row from the sheet.

There's also XSSFRow#removeCell(cell) to remove single cells.