I want to write data into excel(.xlsx file) using Apache poi

Question

I want to write data into excel(.xlsx file) using Apache poi. but getting some error while writing a data. I have followed this video " How to read/write data from Excel file using Apache POI API in Selenium || Latest POI Version", I m able to read data but while writing I m getting this error " Cannot invoke "org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue()" because the return value of "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" is null ", basically nullpointerexception.

enter code here

String resourceGroupNameElement = driver.findElement(By.xpath(FrameworkValidator_Constants.Constants.RESOURCE_GROUP_NAME_XPATH)).getText();

        String   expectedResult = reader.getCellData("RG",6,2);
             
             if( resourceGroupNameElement== expectedResult) {
            String  status= "pass";
            System.out.println(status);
             }
             else {
                String status="fail";
                System.out.println(status);
             }
             //reader.setCellData("RG", "STATUS/PASS/FAIL", 2, status);
             System.out.println(status);
             reader.setCellData("RG","ACTUAL  RESULT" , 2, resourceGroupNameElement);
             Assert.assertEquals(resourceGroupNameElement, expectedResult);

####### It is showing error in this section

public String setCellData(String sheetName, String colName, int rowNum, String data) {
    try {
        fis = new FileInputStream(path);
        workbook = new XSSFWorkbook(fis);

        if (rowNum <= 0)
            return "";

        int index = workbook.getSheetIndex(sheetName);
        int colNum = -1;
        if (index == -1)
            return "";

        sheet = workbook.getSheetAt(index);

        row = sheet.getRow(0);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            // System.out.println(row.getCell(i).getStringCellValue().trim());
            if (row.getCell(i).getStringCellValue().trim().equals(colName))
                colNum = i;
        }
        if (colNum == -1)
            return "";

        sheet.autoSizeColumn(colNum);
        row = sheet.getRow(rowNum - 1);
        if (row == null)
            row = sheet.createRow(rowNum - 1);

        cell = row.getCell(colNum);
        if (cell == null)
            cell = row.createCell(colNum);

        // cell style
        // CellStyle cs = workbook.createCellStyle();
        // cs.setWrapText(true);
        // cell.setCellStyle(cs);
        cell.setCellValue(data);

        fileOut = new FileOutputStream(path);

        workbook.write(fileOut);

        fileOut.close();

    } catch (Exception e) {
        e.printStackTrace();
        return "";
    }
    return "";
}

so can anybody tell me where I m going wrong.

Answer 1

Have a look at the HOWTO and examples. You will notice that there are calls for creating a row or creating a cell. Unless you do so, the row/cell does not exist and your getCell() function will return null.