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.

Answer 1

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.

Answer 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.

Answer 3

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?