Apache POI - Getting a null pointer while reading a cell with value

Question

I'm trying to read some cells in a line, first 5 sheets using the same reading code were ok, but my 6th sheet is returning a null value in a specific cell even when the cell has value in the spreadsheet.

Has anyone seen this one before? How to solve it?

Thanks in advance!

PS.: here's the code I'm using to get the values, works fine 'til it reaches the 6th sheet. I can see that the Excel spreadsheet is filled but still getting a null.

private String getValorTexto(HSSFSheet sheet, int indiceLinha, int indiceColuna) 
{              
    sheet.getRow(indiceLinha).getCell(indiceColuna).setCellType(HSSFCell.CELL_TYPE_STRING);
    if (sheet.getRow(indiceLinha).getCell(indiceColuna).getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return sheet.getRow(indiceLinha).getCell(indiceColuna).getStringCellValue().trim();
    } else {
        return String.valueOf(sheet.getRow(indiceLinha).getCell(indiceColuna).getNumericCellValue());
    }
}

Please post the exception message and stack trace, and indicate on which line the error has occurred. What are the values of indiceLinha and indiceColuna? Indexes into rows and columns are 0-based in Apache POI, so that "A1" corresponds to (0, 0), not (1, 1). — rgettman
– rgettman, Commented Apr 24, 2014 at 21:56
indiceLinha is the row number, I'm iterating through the rows. indiceColuna is the column number, I'm iterating over them too. The problem is that even though this particular cell has a value, getValorTexto is returning null. The other cells are fine, I'm able to retrieve their value properly. — cmlvillage
– cmlvillage, Commented Apr 25, 2014 at 1:52
Share the exception log... Continuing the Rgettman's comment, this is also notable that the sheet, rows, columns etc are zero index based. i.e. starts with zero, not one. so take care of that too. — Sankumarsingh
– Sankumarsingh, Commented Apr 25, 2014 at 5:26
Here's the deal, I'm not getting an exception, everything works fine, I'm just not getting the result from that particular cell of the spreadsheet. The indexes are fine, starting from zero. The content of this cell is '303249833' but I'm getting null every time I try to read this cell. It's a very weird problem. — cmlvillage
– cmlvillage, Commented Apr 25, 2014 at 12:21

Lukasz Wiktor · Accepted Answer · 2014-04-26 05:16:16Z

2

~~I guess that forcing cell type to HSSFCell.CELL_TYPE_STRING might be the reason of getting null.~~

A more robust way to obtain a string value from any type of cell (you might also have dates, formulas, etc.) is to use DataFormatter:

import org.apache.poi.ss.usermodel.DataFormatter;

static DataFormatter dataFormatter = new DataFormatter();

static String getStringValue(Cell cell) {
    return dataFormatter.formatCellValue(cell);
}

It is especially useful when you want to get exactly the same what is displayed in Excel (e.g numbers rounded to given decimal places).

edited Apr 26, 2014 at 5:16

answered Apr 25, 2014 at 11:35

Lukasz Wiktor

20.5k6 gold badges73 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sankumarsingh Over a year ago

If this was the reason, then it must fire the exception on first five sheets as well. But as per the problem, its happening in 6th sheet not in first five sheet.

Lukasz Wiktor Over a year ago

@Sankumarsingh Yes, you're right. Anyway I would suggest to try the way with DataFormatter.

cmlvillage Over a year ago

@LukaszWiktor, it worked perfectly. I totally agree with Sankumarsingh, it should return null in the previous sheets but for some weird reason, it happened only in the 6th one. Anyway, I'm gonna replace my "getValorTexto" method body to use DataFormatter now. Thanks so much for all your help!!

Thomas Williams · Accepted Answer · 2020-12-10 13:35:47Z

0

I have found in recent tests, that the method DateUtil.GetJavaDate(Double value) can throw a NullReferenceException for unknown reasons. This is the method NPOI uses to convert the numeric Excel date when calling cell.DateCellValue(). I recommend instead to use the method DateTime.FromOADate(cell.NumericCellValue) which seem to do the same conversion.

answered Dec 10, 2020 at 13:35

Thomas Williams

2,9412 gold badges21 silver badges11 bronze badges

Collectives™ on Stack Overflow

Apache POI - Getting a null pointer while reading a cell with value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related