CODEWITHSUNDEEP
grailsapache-poi
tom6502
tom6502

Reputation: 190

grails - How to parse POI numeric value in a column

I am using POI to import my books in an Excel file to my domain class. It turns out that Excel marks the columns in a row either as String or Numeric, or Formula, etc.

I have a column that has mix contents with number and string. The contents are actually the ID of the books. All the contents should be treated as String.

This is an example:

Column 1
1234567
8910.0
One2Three
456

Excel marks Row 1, 2 and 4 as Numeric. I use the getCell() method to read the content of the cell.

String bookId = row.getCell(1).toString().trim() // row is a HSSFRow

POI returns "1234567.0", "8910.0", "One2Three", and "456.0".

How do I get rid of the ".0" substring correctly?

"1234567.0" in row 1 should be just "1234567"
"8910.0" in row 2 should be as-is "8910.0"
"One2Three" in row 3 is just "One2Three"
"456.0" in row 4 should be just "456"

Thanks!

Upvotes: 0

Views: 88

Answers (2)

tom6502
tom6502

Reputation: 190

I figured how to make POI to import my book Id correctly now. I set the cell to String before I call the getCell() method. POI gives me the correct content from getCell().toString() method now. I haven't tried a cell with a Link or Formula data type. I am pretty sure this won't work with these other data type in the cell.

row.getCell(1).setCellTypeImpl(CellType.STRING)
bookId = row.getCell(1).toString().trim()

After I set the cell to the STRING cell type, POI returns all the mixed content in column 1 as-is.

Good job me!

Upvotes: 0

Luis Muñiz
Luis Muñiz

Reputation: 4811

Without seeing more code it's hard to know exactly what your problem is, but this is how we do it, to extract an integer value from cell 9 in the row:

row.cells[9].cell.numericCellValue as Integer

Upvotes: 0

Related Questions