Check if cell value is empty or null with Apache POI?

Question

I have the following method in java

/*-------------IMPORT Rutas----------*/
    public String processImportRutas(MultipartFile fileDocument, long loggedUserId) {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("error", "");
        ArrayList<RutasImportDto> arrayList = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(fileDocument.getInputStream());
            XSSFSheet sheet = workbook.getSheetAt(0);
            DataFormatter formater = new DataFormatter();
            int rowCount = 1;
            RutasImportDto dto;

            for (Row row : sheet) {
                dto = new RutasImportDto();
                if (rowCount < 4) {
                    rowCount++;
                    continue;
                }
                if (rowCount == 4) {
                    Cell cell = row.getCell(0);
                    if (cell == null) {
                        continue;
                    }
                    if (formater.formatCellValue(cell).equalsIgnoreCase("Nº DE PLANTA")) {
                        // si la el encabezado interpreta que está en la fila 4
                        // aumentamos el rowCount para ajustar los números de filas
                        rowCount++;
                        continue;// for Row...
                    }

                }
                for (int i = 0; i < 4; i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    boolean isBlankCell = CellType.BLANK == cell.getCellTypeEnum();
                    boolean isEmptyStringCell = CellType.STRING == cell.getCellTypeEnum() && cell.getStringCellValue().trim().isEmpty(); 
                    //condition is ignored
                    if (cell == null || isBlankCell || isEmptyStringCell) {
                        continue;
                    }

                    //log.info("celda valor: {}",cell);
                    // if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
                    //     continue;
                    // }
                    switch (i) {
                        case 0:
                            dto.setPlantNumber(formater.formatCellValue(cell));
                            break;
                        case 1:
                            dto.setUserId(formater.formatCellValue(cell));
                            break;
                        case 2:
                            dto.setStartDay(formater.formatCellValue(cell));
                            break;
                        case 3:
                            dto.setHour(formater.formatCellValue(cell));
                            break;
                        default:
                            break;
                    }
                }
                dto.setRow(rowCount);
                arrayList.add(dto);
                rowCount++;
            }
            jsonObject = persistImportRutas(arrayList, loggedUserId);
            // log.info("***** arrayList "+ arrayList);
        } catch (Throwable th) {
            jsonObject.put("error", th.getMessage()).toString();
            log.error("processImportRutas : " + th.getMessage());
            log.debug("processImportRutas : " + th.getMessage(), th);
        }
        // log.info("***** jsonObject.toString "+ jsonObject.toString());
        return jsonObject.toString();
    }

My code reads 4 columns

1- plantnumber

2- userId

3- startDay

4- Hour

If all 4 columns are empty the code should ignore the row and read the next one, if they have value then it should store values in an ArrayList for further validations/instructions.

I'm trying to make it so that when I upload my excel file, it ignores rows that are empty or null and continue with the loop but it completely ignores my condition and returns a bunch of errors messages

I followed the answers from this other question but nothing in there seems to work for me

Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
boolean isBlankCell = CellType.BLANK == cell.getCellTypeEnum();
boolean isEmptyStringCell = CellType.STRING == cell.getCellTypeEnum() && cell.getStringCellValue().trim().isEmpty(); 
//condition is ignored
if (cell == null || isBlankCell || isEmptyStringCell) {
   continue;
}

In this method persistImportRutas is where I make other validations with the data I got from the cells and return error msgs if data is null/empty or not valid and insert in my DB the ones that are valid but the problem is that my code is not ignoring the rows that are empty or null and it tries to validate the cell values and returns error cuz of it. My Apache POI version is 3.17

I don't think you're setting the boolean values correctly. You have to use an if statement and inside the if statement you have to set the boolean values. If the cell is empty then you set the variable to true or false. — Alias Cartellano, Mar 21 '23 at 20:57
I just realized after making a few more test that my condition actually works and it reads the `continue;` line inside the if statement but the code inserts the null data inside my `arrayList` anyway which is why I'm having issues — Chris G, Mar 22 '23 at 11:51

score 0 · Answer 1 · answered Mar 22 '23 at 13:53

Fixed my issue, posting the answer in case it helps someone else. I noticed that my condition was ok and that it went inside my if statement but after the continue it kept saving null values in my arrayList

/*-------------IMPORT Rutas----------*/
    public String processImportRutas(MultipartFile fileDocument, long loggedUserId) {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("error", "");
        ArrayList<RutasImportDto> arrayList = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(fileDocument.getInputStream());
            XSSFSheet sheet = workbook.getSheetAt(0);
            DataFormatter formater = new DataFormatter();
            int rowCount = 1;
            RutasImportDto dto;

            for (Row row : sheet) {
                dto = new RutasImportDto();
                if (rowCount < 4) {
                    rowCount++;
                    continue;
                }
                if (rowCount == 4) {
                    Cell cell = row.getCell(0);
                    if (cell == null) {
                        continue;
                    }
                    if (formater.formatCellValue(cell).equalsIgnoreCase("Nº DE PLANTA")) {
                        // si la el encabezado interpreta que está en la fila 4
                        // aumentamos el rowCount para ajustar los números de filas
                        rowCount++;
                        continue;// for Row...
                    }

                }
                int nullCounter = 0;//se inicializa null counter
                for (int i = 0; i < 4; i++) {
                    //obtener valor de celda y variables para validar si vienen con datos vacios
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    boolean isBlankCell = CellType.BLANK == cell.getCellTypeEnum();
                    boolean isEmptyStringCell = CellType.STRING == cell.getCellTypeEnum() && cell.getStringCellValue().trim().isEmpty(); 
                    
                    //si viene vacio, aumentar contador y pasar a sgte celda
                    if (cell == null || isBlankCell || isEmptyStringCell) {
                        nullCounter++;
                        //si celda esta vacia, continuar con la iteracion
                        continue;
                    } 
                    //si no, guardar datos para luego almacenar en arraylist
                    switch (i) {
                        case 0:
                            dto.setPlantNumber(formater.formatCellValue(cell));
                        break;
                        case 1:
                            dto.setUserId(formater.formatCellValue(cell));
                        break;
                        case 2:
                            dto.setStartDay(formater.formatCellValue(cell));
                        break;
                        case 3:
                            dto.setHour(formater.formatCellValue(cell));
                        break;
                        default:
                        break;
                    }
                    
                }
                //si las 4 columnas de la fila son null, no guardar datos en arraylist
                if(nullCounter==4){
                    //log.info("fila completamente vacia no se agregan datos a arrayList en fila: {}", rowCount);
                } else {
                    dto.setRow(rowCount);
                    arrayList.add(dto);
                }
                rowCount++;
            }
            jsonObject = persistImportRutas(arrayList, loggedUserId);
            // log.info("***** arrayList "+ arrayList);
        } catch (Throwable th) {
            jsonObject.put("error", th.getMessage()).toString();
            log.error("processImportRutas : " + th.getMessage());
            log.debug("processImportRutas : " + th.getMessage(), th);
        }
        // log.info("***** jsonObject.toString "+ jsonObject.toString());
        return jsonObject.toString();
    }

So I added this int nullCounter = 0; a null counter that resets every time it iterates thru my 4 columns and goes for the next row. Everytime my condition is true and the cell values are empty or null I increase that counter then continue;.

if (cell == null || isBlankCell || isEmptyStringCell) {
    nullCounter++;
    //si celda esta vacia, continuar con la iteracion
    continue;
}

The max number that counter can reach before it resets its 4, if it reaches 4 then that means that all 4 columns in that row were null or empty so instead of just adding the data to my arrayList I make another condition and check if that counter equals 4. If the condition is true then ignores the data and continues iterating, else, add the data to my arrayList arrayList.add(dto);

if(nullCounter==4){
     //log.info("fila completamente vacia no se agregan datos a arrayList en fila: {}", rowCount);
} else {
     dto.setRow(rowCount);
     arrayList.add(dto);
}
rowCount++;

and That finally solved the problem

score 0 · Accepted Answer · answered Mar 31 '23 at 17:52

Make a counter, increase inside condition. Then if counter is 4 do nothing else save data in array

if(nullCounter==4){
     //log.info("fila completamente vacia no se agregan datos a arrayList en fila: {}", rowCount);
} else {
     dto.setRow(rowCount);
     arrayList.add(dto);
}
rowCount++;

Check if cell value is empty or null with Apache POI?

2 Answers2