Number as it is from excel file using Apache POI

0
votes

I am using Apache POI (version 3.9) framework for dealing with excel files, i.e. reading excel file.

I am having trouble with number types. User can write a number in excel file with comma or dot as decimal separator, i.e. 12.34 or 12,34, for various reasons.

Now, I would like to get that value as it is (i.e. if it is 12.34, then I would like to get 12.34, or if it is 12,34, then I would like to get 12,34), but instead POI Cell preliminary gives me a double with dot as decimal separator. So, if a cell value was 12,34, POI Cell would give double of value 12.34.

This behaviour is not what I would like to be. I would like to get the value that was entered (12,34).

How to avoid/solve this?

I have searched Stackoverflow for similar problems and tried to use solution given in thread How can I read numeric strings in Excel cells as string (not numbers) with Apache POI? and also tried using alternative org.apache.poi.ss.usermodel.DataFormatter class, and it works, but it does not work when the the value is 12,34 and type of cell is Number defined in Excel file itself.

1
javaexcelapache-poi
The data you are looking for isn't stored in the cell value. The value is just a number. You'll have to look at the cell formatting and then format the number as a string yourself. – Gary Forbis
Hi Gary, thank you for your reply. I didn't emphasize that while reading an excel file (which user provided) I need to do some validation. And I would formatted it, if only I would get a string and not an already modified double. Do you have any comments on this? – mismas
If you are validating the numbers, I would suggest that you might have more luck converting your validation string into a number to compare with the value in the cell. There is no information stored in the spreadsheet about the decimal/thousands separator. That is based on the locale set in the application or OS and is display-only - it is not stored at all. So you will have to manually format the number as a String. – Gary Forbis
Thank you Gary for this valuable information :) Just, it is quite unbelievable that this framework can't just give value as it is written in the cell. I can't understand it, because I'm sure they know it. Anyway, thanks a lot again! – mismas
Could you please post your comment as an answer so I can give you credit for answering it. Thanks! – mismas

1 Answers

1
votes

If you are validating the numbers, I would suggest that you might have more luck converting your validation string into a number to compare with the value in the cell. There is no information stored in the spreadsheet about the decimal/thousands separator. That is based on the locale set in the application or OS and is display-only - it is not stored at all. So you will have to manually format the number as a String.