CODEWITHSUNDEEP
javaapache-poi
user1669488
user1669488

Reputation: 227

Null pointer when trying to write empty cell using POI

I'm trying to write an existing excel sheet. however, I'm getting NPE when I run the below code. The code works fine if the target cell has a value. But when it's an empty cell it gives NPE. 

FileInputStream myInput = new FileInputStream(fileName);
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            Workbook myWorkBook = new HSSFWorkbook(myFileSystem);
            Sheet mySheet = myWorkBook.getSheetAt(0);
            myRow = mySheet.getRow(20);
            myCell = myRow.getCell(0);
            //myCell = myRow.createCell(0);
            myCell.setCellValue("praveen");
            FileOutputStream myOutput = new FileOutputStream(fileName);
            myWorkBook.write(myOutput);
            myOutput.close();

Upvotes: 0

Views: 1520

Answers (4)

Darshit
Darshit

Reputation: 361

I have found same issue and resolved it. I would like to share my solution, It can help developers in future.

When there is no text in cell or empty, POI api row object will take it as "NULL". That's why you are getting NPE while processing that cell.

Now its NULL so first we have to initialize it and create a new cell first there and than we can process further.

So, here is some workout than can help.

  1. create new cell there. In your case it will be like

    myRow.createCell(0).setCellValue("Your Value !!")

Hope It will be useful!!

Upvotes: 1

swamy
swamy

Reputation: 1210

//It might help you.

Iterator iterator = mySheet .rowIterator();
 if(iterator.hasNext()){
        Row myRow= mySheet.getRow(20);
        Cell myCell = myRow.getCell(0);
        myCell.setCellValue("praveen");
    }

Upvotes: 0

Fritz
Fritz

Reputation: 10045

If the NPE is being thrown in that line, it means the particular row you're searching for is undefined. Besides checking the overall structure you should do some defensive programming by adding some checks before obtaining a row by validating that the index you search is between the minimum and the maximum indexes.

//assuming a cellIndex int parameter, the code should look like
myRow = mySheet.getRow(20);
if(myRow.getFirstRowNum() < cellIndex && cellIndex < myRow.getLastRowNum())
    myCell = myRow.getCell(0);
else
    //Throw an exception, log something, skip the operation or create a new row.

Upvotes: 0

Sergii Zagriichuk
Sergii Zagriichuk

Reputation: 5399

From javadoc for public HSSFRow getRow(int rowIndex)

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.

Upvotes: 2

Related Questions