Apache poi method to write data to an existing workbook

Question

This is my class to read and write existing excel files. I have been calling these functions in the main class by passing the filePath and fileName.

   public class NewExcelFile {

    Workbook workbook;

    /******* Methods *******/
    // returns a workbook on giving the excel file's path and name
    public Workbook readExcel(String filePath, String fileName) {
        // Create object of File class to open xlsx file
        File file = new File(filePath + "\\" + fileName);
        // Create an object of FileInputStream class to read excel file
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            System.out.println("Error: Unable to find " + fileName + " in "
                    + filePath);
            e.printStackTrace();
        }
        Workbook workbook = null;
        // Find the file extension by spliting file name in substring and
        // getting only extension name
        String fileExtensionName = fileName.substring(fileName.indexOf("."));
        // Check condition if the file is xlsx file
        if (fileExtensionName.equals(".xlsx")) {
            // If it is xlsx file then create object of XSSFWorkbook class
            try {
                workbook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        // Check condition if the file is xls file
        else if (fileExtensionName.equals(".xls")) {
            // If it is xls file then create object of XSSFWorkbook class
            try {
                workbook = new HSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        this.workbook = workbook;
        return workbook;

    }

    public void writeExcel(String filePath, String fileName, String sheetName,
            String dataToWrite, int rowno) {

        System.out.println("WriteExcel" + filePath + " " + fileName + " "
                + sheetName + " " + dataToWrite + " " + rowno);

        Workbook newWorkbook = readExcel(filePath, fileName);
        Sheet sheet = newWorkbook.getSheet(sheetName);
        System.out.println("Sheet: " + sheet.getSheetName());

        Cell resultcell;

        ******resultcell = sheet.getRow(rowno).createCell(8);
        resultcell.setCellType(Cell.CELL_TYPE_STRING);
        resultcell.setCellValue(dataToWrite);

        CellStyle style = workbook.createCellStyle();
        if (dataToWrite == "P") {
            style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
            style.setFillPattern(CellStyle.ALIGN_FILL);
            resultcell.setCellStyle(style);
        } else if (dataToWrite == "F") {
            style.setFillBackgroundColor(IndexedColors.RED.getIndex());
            style.setFillPattern(CellStyle.ALIGN_FILL);
            resultcell.setCellStyle(style);
        }
        // Create an object of FileOutputStream class to create write data in
        // excel file
        File file = new File(filePath + "\\" + fileName);
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(file);
        } catch (FileNotFoundException e) {
            System.out.println("File not found");
            e.printStackTrace();
        }

        // write data in the excel file and close output stream
        try {
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            System.out.println("Error in writing to file");
            e.printStackTrace();
        }

    }

When I get a workbook in the main using readExcel and call this function:

Row row = testScriptsSheet.getRow(24);

I get the correct row and am able to call all functions on this row.But for the exact same row in the exact same sheet in the writeExcel(), I get a null pointer exception(the line preceded by *** in the code above). getRow() gives me null here. What am I doing wrong here?

Also, should I keep workbook as a data member and domyNewExcelFile.workbook whenever I need it or keep it as a variable returned from the readExcel in the main class? Also I was wondering what is happening now that I am not closing the inputStream at the end of the readExcel function. I get the same error whether I close the inputStream or not.

EDIT - Adding the main function

public class NewDriver {

public static void main(String[] args) {
    System.out.println("Starting the framework");
    // initialise the workbook
    NewExcelFile testExecution = new NewExcelFile();
    testExecution.readExcel(System.getProperty("user.dir") + "\\",
            "abc.xlsx");

    // initialise sheets of workbook
    Sheet testSuiteSheet = testExecution.workbook.getSheet("TestSuite");
    Sheet testScriptsSheet = testExecution.workbook.getSheet("TestCases");
    Row row = testScriptsSheet.getRow(24);//gives the correct row

    //calling writeExcel gives npe in that line


        }
    }
}

}

Answer 1

As per my understanding it seems a conceptual error. Before calling WriteExcel() method, all the changes that you have made in main method are in buffer, not written in the excel sheet/workbook present in your harddisk. But in WriteExcel() method you are not passing the sheet or workbook you kept in buffer, but the address of the one physically present in the hard drive. So any change you have done in main function is not there, hence showing null pointer exception.

e.g. I have one workbook in say my D Drive, having value 1 in A0. Now I have programmatically made it 2 but not perform the write operation, and put the execution on hold. Meanwhile I went to my D drive and open the sheet there will be 1 not 2, as the updated value is in buffer, till I have perform write operation on that workbook.

Suggestion: Instead of passing the address of the workbook, why not you just pass the workbook you have used in main method.

UPDATE: Not the main method but readExcel(String filePath, String fileName) method actually.

Answer 2

From the docs the getRow(int) method:

Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet.

So when a row is not defined, you must first create the row and then create the cell.