groovyapache-poixssf

Why do I get this NullPointerException with getCellType() if getCellType() is contained within a check for null?


I have a groovy script that is designed to export an Excel table to a markdown file. I am having an issue where getCellType() is throwing a null pointer exception.

I have tried testing the cell to see if it is null in two places - both before the cell is passed to the method in which getCellType() is called, and also within the method itself. I've looked over my logic again and again and I can't seem to find what it is I'm missing.

I have tested it to make sure that the functionality is working up until the point where a null cell is found, and it seems to work fine. It cycles through the table precisely how I want it to, but then throws the null pointer exception at the end.

Note: The first for loop prints the first column followed by the second for loop which prints all the other columns underneath it, in case the purpose of this was unclear.

    #!/usr/bin/env groovy

    @Grab(group = 'org.apache.poi', module = 'poi', version = '4.1.0')
    @Grab(group = 'org.apache.poi', module = 'poi-ooxml', version = '4.1.0')

    import org.apache.poi.xssf.usermodel.XSSFWorkbook
    import org.apache.poi.xssf.usermodel.*
    import org.apache.poi.ss.usermodel.*

    Workbook wb = new XSSFWorkbook(new File(this.args[0]))

    DataFormatter formatter = new DataFormatter()
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator()

    PrintStream out = new PrintStream(new FileOutputStream(this.args[1]), true, "UTF-8")

    Sheet sheet = wb.getSheetAt(0)
    int lastRow = sheet.getLastRowNum()

    static private void handleCell(Cell cell, FormulaEvaluator evaluator, DataFormatter formatter, PrintStream out){

        if(cell != null){
            CellValue cellValue = evaluator.evaluate(cell);

            switch (cellValue.getCellType()) {
                case CellType.BOOLEAN:
                    out.print("Cell: " + cellValue.getBooleanValue() + " Type: Boolean")
                    break;
                case CellType.NUMERIC:
                    out.print("Cell: " + cellValue.getNumberValue() + " Type: Numeric")
                    break;
                case CellType.STRING:
                    out.print("Cell: " + cellValue.getStringValue() + " Type: String")
                    break;
                case CellType.BLANK:
                    out.print("Cell: BLANK Type: Blank")
                    break;
                case CellType.ERROR:

                    break;

                // CELL_TYPE_FORMULA will never happen
                case CellType.FORMULA:
                    break;

            }
        }
    }

    for ( int r = 0 ; r <= lastRow ; r++ ){
        cell = sheet.getRow(r).getCell(0)
        if(cell.getCellType() != CellType.BLANK){
            out.println(cell)
        }
    }
    for ( int r = 0 ; r <= lastRow ; r++ ) {
        boolean firstCell = true
        int lastCol = sheet.getRow(r).getLastCellNum()
        if ( r == 1){
            for ( int c = 1 ; c <= lastCol ; c++){
                if(firstCell){
                    out.print("| ")
                }
                out.print(" --- |")
                firstCell = false
            }
            out.println()
        }

        firstCell = true
        for( int c = 1 ; c <= lastCol ; c++ ){
            Cell cell = sheet.getRow(r).getCell(c)

            if(firstCell){
                out.print("| ")
            }
            if(cell != null){
                handleCell(cell, evaluator, formatter, out)
            }
            out.print(" | ")
            firstCell = false
        }
        out.println()
    }

Error Message:

Caught: java.lang.NullPointerException: Cannot invoke method getCellType() on null object
java.lang.NullPointerException: Cannot invoke method getCellType() on null object
    at org.apache.poi.ss.usermodel.CellValue$getCellType.call(Unknown Source)
    at excel2md.handleCell(excel2md.groovy:25)
    at excel2md.run(excel2md.groovy:82)

Solution

  • Both Axel Richter and tim_yates found the issue as per the comments above. Blank cells were returning a null CellValue which was the object throwing the null pointer exception. I changed the test condition within the method to if(cell.getCellType() != CellType.BLANK) and the problem was solved.