views:

1279

answers:

7

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?
A: 

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.

Lunatik
I'm no the one who make this .xls files... Users pass them to my programm, and they won't bother to do that.
joycollector
A: 

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

WolfmanDragon
The problem is that if there is 2.0 in A2 I need to get string "2.0", and if 2 then string "2".
joycollector
A: 

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.

Turismo
Have also had the problem
Romain Hippeau
A: 

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.

datatoo
No. That's impossible... Thay create xls themselves...
joycollector
A: 

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.

wil
A: 

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?

Rob
A: 

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.

Simon D