Problem with Numeric/String using Apache POI...

Question

Hi!

1. I have excel file with such contents:

A1: SomeString

A2: 2

All fields are set to String format.

1. When I read file in java using poi it tells that B1 is numeric cell format.
2. The problem is that the value in A1 can be 2 or 2.0 so I can't just use .toString(). Can anyone help please?

Answer 1

Can you prepend an apostrophe to the strings in Excel? They won't display on the worksheet, but force the cell format to text and can be identified and stripped/coerced during your import.

Answer 2

cast to an int then do a .toString(). It is ugly but it works.

Answer 3

We had the same problem and forced our users to format the cells as 'text' before entering the value. That way Excel correctly stores even numbers as text. If the format is changed afterwards Excel only changes the way the value is displayed but does not change the way the value is stored unless the value is entered again (e.g. by pressing return when in the cell).

Whether or not Excel correctly stored the value as text is indicated by the little green triangle that Excel displays in the left upper corner of the cell if it thinks the cell contains a number but is formated as text.

Answer 4

Do you control the excel worksheet in anyway? Is there a template the users have for giving you the input? If so, you can have code format the input cells for you.

Answer 5

had same problems, did cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value which solved the problem regardless of how the user formatted the cell.

Answer 6

Did anyone uncover an answer here? The cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value had no effect for me and I am still getting the string "2" inserted into my DB as "2.0" as a string!?!?

Anyone with a solution?

Answer 7

It looks like this can't be done in the current version of POI, based on the fact that this bug:

https://issues.apache.org/bugzilla/show_bug.cgi?id=46136

is still outstanding.