Cannot get boolean value from excel cell

Question

I am getting an error when trying to access a boolean value from an Excel file.

final Cell enabledCell = row.getCell(6);
enabledCell.getBooleanCellValue() // this throws an exception

java.lang.IllegalStateException: Cannot get a BOOLEAN value from a STRING cell

In the Excel file, I have just written "TRUE" and "FALSE" as values in the 6th column, but somehow they are perceived as strings and not booleans.

Answer 1

Boolean values in localized Excel applications also are localized. For example in my German Excel boolean values are WAHR and FALSCH. If I put TRUE or FALSE into cells using my German Excel GUI, then this are not boolean values but text strings.

So there is always the need to check CellType before getting the cell value. Or to use DataFormatter to always get string values independent of cell type.

Both is shown in Busy Developers' Guide to HSSF and XSSF Features->Getting the cell contents.

Moreover you might get the wrong cell. In Row.getCell the int cellnum is 0-based. Column A is cellnum 0. So cellnum 6 is column G, which is the 7th column and not the 6th. But that's secondary. Primary is the need to check CellType before getting the cell value or to use DataFormatter.

Answer 2

Enter =TRUE or =FALSE in the cell directly. This will store them as a boolean type in Excel.