Apache POI, CREATE_NULL_AS_BLANK is causing errors

Question

Right now I'm following a tutorial for writing Data Driven Tests in Java. My IDE is IntelliJ Community Edition and following is my code

package utility;

import config.Constants;
import executionEngine.DriverScript;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelUtils {
    private static XSSFSheet ExcelWSheet;
    private static XSSFWorkbook ExcelWBook;
    private static XSSFCell Cell;
    private static XSSFRow Row;

    //This method is to set the File path and to open the Excel File
    //Pass Excel Path and Sheet Name as Arguments to this method
    public static void setExcelFile(String Path) throws Exception {
        try {
            FileInputStream ExcelFile = new FileInputStream(Path);
            ExcelWBook = new XSSFWorkbook(ExcelFile);
        }catch(Exception e){
            Log.error("Class Utils | Method setExcelFile | Exception desc: " + e.getMessage());
            DriverScript.bResult = false;
        }
    }

    @SuppressWarnings("static-access")
    //This method is used to write value in the excel sheet
    //This method accepts four arguments (Result, Row Number, Column Number, Sheet Name)
    public static void setCellData(String Result, int RowNum, int ColNum, String SheetName) throws Exception{
        try{
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
            Row = ExcelWSheet.getRow(RowNum);

            // CHECK IF ERRORS HAPPEN AFTER THIS!!!!!!!!!!!!!!!!
            Cell = Row.getCell(ColNum, Row.CREATE_NULL_AS_BLANK);


            if(Cell == null){
                Cell = Row.createCell(ColNum);
                Cell.setCellValue(Result);
            }else{
                Cell.setCellValue(Result);
            }
            //Constant variables Test Data path and Test Data file name
            FileOutputStream fileOut = new FileOutputStream(Constants.Path_TestData);
            ExcelWBook.write(fileOut);
            //fielOut.flush();
            fileOut.close();
            ExcelWBook = new XSSFWorkbook(new FileInputStream(Constants.Path_TestData));
        }catch(Exception e){
            DriverScript.bResult = false;
        }
    }

    //This method is to read the test data from the Excel cell
    //In this we are passing Arguments as Row Num, Col Num & Sheet Name
    public static String getCellData(int RowNum, int ColNum, String SheetName) throws Exception{
        try{
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            String CellData = Cell.getStringCellValue();
            return CellData;
        }catch(Exception e){
            Log.error("Class Utils | Method getCellData | Exception desc: " + e.getMessage());
            DriverScript.bResult = false;
            return "";
        }
    }

    //This method id to get the row count used of the excel sheet
    public static int getRowCount(String SheetName){
        int iNumber = 0;
        try {
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
            iNumber = ExcelWSheet.getLastRowNum()+1;
        }catch(Exception e){
            Log.error("Class Utils | Method getRowCount | Exception desc: " + e.getMessage());
            DriverScript.bResult = false;
        }
        return iNumber;
    }

    //This method is to get the Row number of the test case
    //This method takes three arguments(Test Case Name, Column Number & Sheet name)
    public static int getRowContains(String sTestCaseName, int colNum, String SheetName) throws Exception{
        int iRowNum = 0;
        try{
            //ExcelWSheet = ExcelWBook.getSheet(SheetName);
            int rowCount = ExcelUtils.getRowCount(SheetName);
            for(; iRowNum<rowCount; iRowNum++){
                if(ExcelUtils.getCellData(iRowNum, colNum, SheetName).equalsIgnoreCase(sTestCaseName)){
                    break;
                }
            }
        }catch(Exception e){
            Log.error("Class Utils | Method getRowContains | Exception desc: " + e.getMessage());
            DriverScript.bResult = false;
        }
        return iRowNum;
    }

    //This method is to get the count of the test steps of test case
    //This method takes three arguments (Sheet name, Test Case ID & Test case row number)
    public static int getTestStepsCount(String SheetName, String sTestCaseID, int iTestCaseStart) throws Exception{
        try{
            for(int i=iTestCaseStart; i<=ExcelUtils.getRowCount(SheetName); i++){
                if(!sTestCaseID.equals(ExcelUtils.getCellData(i, Constants.Col_TestCaseID, SheetName))){
                    int number = i;
                    return number;
                }
            }
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
            int number = ExcelWSheet.getLastRowNum()+1;
            return number;
        }catch(Exception e){
            Log.error("Class Utils | Method getTestStepsCount | Exception desc: " + e.getMessage());
            DriverScript.bResult = false;
            return 0;
        }
    }
}

My problem now is the following part:

public static void setCellData(String Result, int RowNum, int ColNum, String SheetName) throws Exception{
    try{
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
        Row = ExcelWSheet.getRow(RowNum);

        // CHECK IF ERRORS HAPPEN AFTER THIS!!!!!!!!!!!!!!!!
        Cell = Row.getCell(ColNum, Row.CREATE_NULL_AS_BLANK);

I don't know what to do now ... CREATE_NULL_AS_BLANK is marked red and says "Cannot resolve symbol" and every step from my data driven framework is failing.

I tried to use it with

org.apache.poi.ss.usermodel.Row.MissingCellPolicy.CREATE_NULL_AS_BLANK;

With this code I can execute my code, but my Framework does only the first step before crushing

Here is my Log

2017-10-16 13:19:50,093 INFO  [Log] **************************************************************************************** 
2017-10-16 13:19:50,093 INFO  [Log] **************************************************************************************** 
2017-10-16 13:19:50,093 INFO  [Log] $$$$$$$$$$$$$$$$$$$$$                 Wikipedia_01       $$$$$$$$$$$$$$$$$$$$$$$$$ 
2017-10-16 13:19:50,093 INFO  [Log] **************************************************************************************** 
2017-10-16 13:19:50,093 INFO  [Log] **************************************************************************************** 
2017-10-16 13:19:50,094 ERROR [Log] Class Utils | Method getCellData | Exception desc: null 
2017-10-16 13:19:50,094 INFO  [Log] Opening Browser 
2017-10-16 13:19:53,388 INFO  [Log] Closing the browser 
2017-10-16 13:19:54,069 INFO  [Log] XXXXXXXXXXXXXXXXXXXXXXX             -E---N---D-             XXXXXXXXXXXXXXXXXXXXXX 
2017-10-16 13:19:54,069 INFO  [Log] X 
2017-10-16 13:19:54,069 INFO  [Log] X 
2017-10-16 13:19:54,069 INFO  [Log] X 
2017-10-16 13:19:54,069 INFO  [Log] X 

I hope someone can help me solve this problem, cause it is driving me nuts.

Edit: Before I added the "setCellData" part, the framework worked totally fine and every step was executed flawlessly. The error happend directly after that

Answer 1

Replace your Row.CREATE_NULL_AS_BLANK with Row.MissingCellPolicy.CREATE_NULL_AS_BLANK.

And use your "regular" Row import (for example: import org.apache.poi.ss.usermodel.Row;)

I was using v3.14, updated it to v3.17 and got the same error. Somewhere between these versions the CREATE_NULL_AS_BLANK was packed inside a MissingCellPolicy enum, hence the error.