Java Excel POI - Delete rows with empty cells exception

Question

I'm programing an interface with java and Apache POI Library. I've a problem deleting empty rows. My code is:

public class ExcelDeleteRowsCols {
    final short ROW_START = 0;
    final short COL_START = 0;

    public void deleteRows() {
        try {
            // Open file
            FileInputStream inf = new FileInputStream("in.xls");
            Workbook wb = WorkbookFactory.create(inf);

            // Loop every sheets of workbook
            for (Sheet sheet : wb){

                // Loop every rows of this sheet
                int lastIndex = sheet.getLastRowNum();

                for (int i = ROW_START; i <= lastIndex; i++) {
                    if (sheet.getRow(i) == null || sheet.getRow(i).getCell(COL_START) == null || sheet.getRow(i).getCell(COL_START).toString().equals("")){ 
                        sheet.removeRow(sheet.getRow(i));   //sheet.shiftRows(i, lastIndex, 2);
                    }
                }
            }
            // Save as in another file
            FileOutputStream fileOut = new FileOutputStream("out.xls");
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();

            System.out.println("Finished!");

        } catch (IOException ioe) {
            System.out.println(ioe);
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Exactly the problem is that in a rows with empty cells show an exception message java.lang.NullPointerException. I don't understand it. Excel Example:

"Empty cell" Line2 Line3 Line4 Line5 "Empty cell" Line7 Line8 Line9 Line10 Line11 Line12 Line13

When there aren't empty cells the code is working fine...

Please Could you help me?

Thanks in advance.

I'm analyst the problem and I change the sheet.removeRow by System.out.println. The result is in the empty cells the value is null and the others cells the value is the string object, example: 'org.apache.poi.hssf.usermodel.HSSFRow@2ef5e5e3'. For this issue the cells with value is null can't delete. — ruzD, May 18 '16 at 09:15

Alexis Delahaye · Answer 1 · 2016-05-18T13:47:31.803

2

Of course you can't use

sheet.removeRow(row)

when your row is null

As I saw in your code you want to keep only the row that are not null in the "in.xls" file and write them to "out.xls" , so I would advise you to change the algorithm this way :

Workbook wbOut = new HSSFWorkbook(); // or whatever workbook you'd like to use
for (Sheet sheet : wb){
            Sheet newSheet = wbOut.createSheet();
            int newI = 0;
            // Loop every rows of this sheet
            int lastIndex = sheet.getLastRowNum();

            for (int i = ROW_START; i <= lastIndex; i++) {
                // the exact opposite condition
                if (sheet.getRow(i) != null && 
                    sheet.getRow(i).getCell(COL_START) != null && 
                   !sheet.getRow(i).getCell(COL_START).toString().equals("")){ 
                     // row is not null so you can copy it to a new sheet
                    Row newRow = newSheet.createRow(newI++);
                    newRow = sheet.getRow(i);
                }
            }
        }
      // Save as in another file
        FileOutputStream fileOut = new FileOutputStream("out.xls");
        wbOut.write(fileOut);
        fileOut.flush();
        fileOut.close();

So you'll copy only the not null rows into a new workbook that you can finally write.

edited May 18 '16 at 13:47

answered May 18 '16 at 13:42

Alexis Delahaye

644
5
18

I've tested but in the new file doesn't write the values from the original file. I debug the code and the condition(if) is correct, the newRow is create but after that the out.xls file is empty. – ruzD May 18 '16 at 14:04
Maybe I was too much optmistic with newRow = sheet.getRow(i) ,I think you need to assign value for every cell that compose the row. [Here is a code to get the right value of a cell](http://stackoverflow.com/questions/5578535/get-cell-value-from-excel-sheet-with-apache-poi) Maybe something like : `Row oldRow = sheet.getRow(i); int pos=0; while(oldRow.getCell(pos)!=null){ newRow.createCell(pos).setCellValue( value ); }` where 'value' is the value of the oldRow's cell – Alexis Delahaye May 18 '16 at 14:35
It is even more complex than that because cells have styles, and styles have borders, fills, etc. There is a lot going on to copy just a cell to another workbook, and if formulas are involved, it gets even more complicated because the formula references need to be dealt with. – jmarkmurphy May 19 '16 at 13:34
you're right it's more complex than just copying the value. According to this [post](http://stackoverflow.com/questions/22627691/how-to-copy-a-row-from-existing-excel-sheet-to-a-new-excel-sheet-using-apache-po) a simple affectation between 2 cells seems to copy both value & style but I think like you it won't work with formulas. But maybe the OP has a simple sheet and this solution will be good enough. – Alexis Delahaye May 19 '16 at 13:48

jmarkmurphy · Answer 2 · 2016-05-19T13:19:30.493

So rather than deleting rows, you really want to close up empty rows in your spreadsheet. That is, shift rows containing data up so that there are no blank rows in between.

I have totally changed this answer to take this into consideration.

FileInputStream inf = new FileInputStream("Row_Delete_Test.xlsx");

Workbook wb = WorkbookFactory.create(inf);

for (Sheet sh : wb) {
    int previousIndex = sh.getFirstRowNum();
    if (previousIndex > 0) {
        sh.shiftRows(px, sh.getLastRowNum(), -px);
        previousIndex = 0;
    }

    for (Row row : sh) {
        boolean deleteRow = true;
        for (Cell cell : row) {
            if (!cell.toString().trim().equals("")) {
                deleteRow = false;
                break;
            }
        }

        int currentIndex = row.getRowNum();
        if (deleteRow) {
            sh.removeRow(row);
        } else {
            if (currentIndex > previousIndex + 1) {
                sh.shiftRows(row.getRowNum(), sh.getLastRowNum(), previousIndex - currentIndex + 1);
                currentIndex = previousIndex + 1;
            }
            previousIndex = currentIndex;
        }
    }
}

FileOutputStream fileOut = new FileOutputStream("Row_Delete_Test.xlsx");
wb.write(fileOut);
wb.close();
fileOut.close();

This will have the effect of "deleting" rows from the spreadsheet.

Note: an Excel spreadsheet only really contains the rows with cells, and only contains cells with data. That data can be almost anything, including blanks, so if you also want to "delete" rows where the only cell values are blanks, then you will have to search for that.

Here is some example data (note this is from sheet1.xml inside the Row_Delete_Test.xlsx file)

<sheetData>
    <row r="2" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B2" t="s">
            <v>0</v>
        </c>
    </row>
    <row r="5" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B5" t="s">
            <v>1</v>
        </c>
    </row>
    <row r="6" spans="2:3" x14ac:dyDescent="0.25">
        <c r="C6" t="s">
            <v>4</v>
        </c>
    </row>
    <row r="7" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B7" t="s">
            <v>2</v>
        </c>
    </row>
    <row r="8" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B8" t="s">
            <v>3</v>
        </c>
    </row>
    <row r="9" spans="2:3" x14ac:dyDescent="0.25">
        <c r="C9" t="s">
            <v>4</v>
        </c>
    </row>
</sheetData>

I am going to just tell you, as there is no way you could know other than looking at the shared strings table, that shared string 4, designated by <v>4</v> is just a blank value. The other shared string values are <v>0</v> = 'Row 1', <v>1</v> = 'Row 2', <v>2</v> = 'Row 3', and <v>3</v> = 'Row 4'. So here rows 2, and 5 through 9 are populated, each row has a single cell with data in it. Rows 6 and 9 each have a cell with a blank value in column C.

After running the above code, the sheetData looks like this

<sheetData>
    <row r="1" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B1" t="s">
            <v>0</v>
        </c>
    </row>
    <row r="2" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B2" t="s">
            <v>1</v>
        </c>
    </row>
    <row r="3" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B3" t="s">
            <v>2</v>
        </c>
    </row>
    <row r="4" spans="2:3" x14ac:dyDescent="0.25">
        <c r="B4" t="s">
            <v>3</v>
        </c>
    </row>
</sheetData>

Now only rows 1-4 are in the spreadsheet. Row 2 has been moved to row 1, 5 to 2, 7 to 3, and 8 to 4.

Thanks for your answer but it doesn't work :( Maybe I doesn't understand well. The problem isn't delete the rows with the value is null with your code doesn't delete anything. I put a new answer with more details. — ruzD, May 18 '16 at 13:06
Ok, I misunderstood. The answer is changed to "delete" empty rows. Actually I am detecting a gap in the rows since empty rows (those that have no data) are actually missing from the spreadsheet. So this code will actually shift rows up to "delete" those empty rows. Note that any data in the row will cause it to remain (i.e. a blank value in a cell). This can be detected though and deleted, then the next row shifted up. — jmarkmurphy, May 19 '16 at 12:52
Also, I know you are using `.XLS`. I used `.XLSX` because it is easier to show the internal representation in an understandable manner. Both formats work similarly though. — jmarkmurphy, May 19 '16 at 13:29

score 0 · Accepted Answer · answered May 18 '16 at 13:20

I have this xls document:

My objective is delete every empty rows, for this issue I think that the best option is the OPI in Java. The code is:

public class ExcelDeleteRowsCols {
    final short ROW_START = 0;
    final short COL_START = 0;

    public void deleteRows() {
        try {
            // Open file
            FileInputStream inf = new FileInputStream("in.xls");
            Workbook wb = WorkbookFactory.create(inf);

            // Loop every sheets of workbook
            for (Sheet sheet : wb){

                // Loop every rows of this sheet
                int lastIndex = sheet.getLastRowNum();

                for (int i = ROW_START; i <= lastIndex; i++) {
                    if (sheet.getRow(i) == null || sheet.getRow(i).getCell(COL_START) == null || sheet.getRow(i).getCell(COL_START).toString().equals("")){ 
                        sheet.removeRow(sheet.getRow(i));   
                    }
                }
            }
            // Save as in another file
            FileOutputStream fileOut = new FileOutputStream("out.xls");
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();

            System.out.println("Finished!");

        } catch (IOException ioe) {
            System.out.println(ioe);
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

When the row is empty the code is working fine but the problem is when the row is null(sheet.getRow(i) == null). For example in this xls the row 2, row 12, row 15 and 16 your value is null then it doesn't delete because the command sheet.removeRow(sheet.getRow(i)); throws and exception by NullPointer.

Is there any way to delete a row that the value is null?

Java Excel POI - Delete rows with empty cells exception

3 Answers3