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--;
        }


}





}

}

Are you asking how to remove rows in general? Or how to detect which rows are empty, so that you can then remove them? — Jean-François Corbett
– Jean-François Corbett, Commented Aug 8, 2014 at 7:57
Yes, once I get the output in the second excel file, first I need to detect at the end which rows are lying empty and then I need to remove those rows or cells. — testing
– testing, Commented Aug 8, 2014 at 8:08
Then say so in your question, and leave out all the extraneous detail. Did it for you this time. — Jean-François Corbett
– Jean-François Corbett, Commented Aug 8, 2014 at 8:18

Jean-François Corbett · Accepted Answer · 2014-08-08 08:18:44Z

3

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.

edited Aug 8, 2014 at 8:18

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Aug 8, 2014 at 7:50

Jim Garrison

87k20 gold badges162 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

testing Over a year ago

Can you give an example, not able to implement this.

Sankumarsingh · Accepted Answer · 2014-08-08 09:43:06Z

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.

First, the Row is in between the other rows, but never be initialized or created. In this case Sheet.getRow(i) will be null.
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--;
        }
    }

answered Aug 8, 2014 at 9:43

Sankumarsingh

10.1k11 gold badges54 silver badges75 bronze badges

2 Comments

testing Over a year ago

Getting this error: Exception in thread "main" java.lang.IllegalArgumentException: firstMovedIndex, lastMovedIndex out of order

Sankumarsingh Over a year ago

The row counts are zero index based... it may be possible that you have pointed a wrong index number. Check it on your end.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related