POI get the original value from a numeric excel cell

Question

I am triing to read exact string values from an excel sheet using the POI library. The problem is that, it is not acceptable to require the user to set the cell type explicitly to text, so excel automatically converts numeric input into a numeric value.

When I'm triing to get the value back as a string, the following problem occurs:

1. I'm triing to use the Cell.getStringValue() function. It throws an exception, since my field is implicitly set to a numeric type field.

2. I'm checking for the field type, and if I notice this happens to be a numeric field, I'm converting it into a string with either Double.toString, or Cell.toString(), my field is converted by using decimals, even if it was a round value. For example I write the number '1' into the field. It automatically converts to the 1 numberic value. When I try to get back the string value, I get back the "1.0" string value, wich is problematic.

Is there a way to get back the original string in this scenario? The only solution I can come up after triing this is to forbid using decimals in the user input.

Answer 1

I think ms excel is lossy in this regard. For instance if you type 00123 in a cell it will automatically be converted to 123. This happens before reading the data with POI so unfortunately POI doesn't have access to the original value.

I think the only option is to set the cell formatting to string prior to entering data into the cell.

Very annoying!