Numeric Cell value like zip code

["Tom Holmes Jr." <to...@tomholmes.net>]

I'm reading in an Excel file perfectly, and I can get all the data I need.

However, columns like zip code and phone numbers are coming back with a 
numeric value as a double.
I really want these values to come back as a string and not a double.
Sure, I can convert from double to string ... but a zip code like 12345 
comes back as a double like 12345.0
and the string is also '12345.0'

Likewise, we have some phone numbers as:  1112223333 or 12223334444
This is also seen as a numeric value and the same problem happens. 
The numeric value as a double like 1.11222333E9 and then when it 
converts to String ... the value is the same, which is useless to me.

Anyone find a way to work around this?    Thanks!
                                                                                       
Tom

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Numeric Cell value like zip code

[Nick Burch <ni...@torchbox.com>]

On Tue, 29 Apr 2008, Tom Holmes Jr. wrote:
> Or, is there a way to use POI to programmatically convert the xls file 
> to a csv file?  Then everything would be text, and I already have the 
> code to read in a csv file as well.

There's XLS2CSVmra, which is powered by the event api:
http://poi.apache.org/hssf/how-to.html#record_aware_event_api
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Numeric Cell value like zip code

["Tom Holmes Jr." <to...@tomholmes.net>]

Thanks for the information.  That does explain a lot.

For my users, I might have to have them take more responsibility for 
what is in the Excel file and making sure the data is correct.

Or, is there a way to use POI to programmatically convert the xls file 
to a csv file?  
Then everything would be text, and I already have the code to read in a 
csv file as well.

Nick Burch wrote:
> On Mon, 28 Apr 2008, Tom Holmes Jr. wrote:
>   
>> However, columns like zip code and phone numbers are coming back with a
>> numeric value as a double.
>>     
>
> This indicates that excel is really storing them as a number, and not as a
> string. If you want to force excel to store them as a number, prefix them
> with a ' when entering them
>
> If you have stored them as numbers, you'll need to format them back to
> strings if that's what you want. As the cell style for the data format
> string, and that'll tell you how excel thinks it should be formatted
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>   


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Numeric Cell value like zip code

[Nick Burch <ni...@torchbox.com>]

On Mon, 28 Apr 2008, Tom Holmes Jr. wrote:
> However, columns like zip code and phone numbers are coming back with a
> numeric value as a double.

This indicates that excel is really storing them as a number, and not as a
string. If you want to force excel to store them as a number, prefix them
with a ' when entering them

If you have stored them as numbers, you'll need to format them back to
strings if that's what you want. As the cell style for the data format
string, and that'll tell you how excel thinks it should be formatted

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Numeric Cell value like zip code

[Martin Schöffler <ma...@gmail.com>]

When converting to String you have to use a java.text.NumberFormat to
control the conversion.

For integer-like values (like zip codes) I usually use something like this:
double numericValue = cell.getNumericCellValue();
if (((long) numericValue) == numericValue) {
    NumberFormat numberFormat = NumberFormat.getIntegerInstance();
    numberFormat.setGroupingUsed(false);
    stringValue = numberFormat.format(numericValue);
}

Martin

On Mon, Apr 28, 2008 at 9:35 PM, Tom Holmes Jr. <to...@tomholmes.net> wrote:
>
>  Likewise, we have some phone numbers as:  1112223333 or 12223334444
>  This is also seen as a numeric value and the same problem happens. The
> numeric value as a double like 1.11222333E9 and then when it converts to
> String ... the value is the same, which is useless to me.
>
>  Anyone find a way to work around this?    Thanks!

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org