poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Bradley <martsbrad...@btinternet.com>
Subject Re: Reading leading zero formatted numbers
Date Tue, 20 Jun 2006 21:28:13 GMT
Folks,

Thanks for the replies.  I have went with Amirs suggestion of grabbing 
the format from the cell and trying to use that with DecimalFormat class.

And it works perfectly for my example of a format string of 000000.   It 
works perfectly with this format because this format in POI and Excel 
correspond to the same thing.  However it does not for other formats 
such as exponential numbers.

I'm hoping not to encounter the other formats, :-)

Thanks,
Martin.



Amir Khan wrote:

> Why not read it the way are doing and then (in Java) use number format 
> to re-create the leading zeros?
>
> http://java.sun.com/docs/books/tutorial/java/data/decimalFormat.html
>
> e.g.
>
> |value| |pattern output|
> 123.78 000000.000 000123.780
>
> Thanks
>
> Amir
>
> Chaudhary, Harsh wrote:
>
>> This is probably too simplistic but try to get the value of the Excel
>> cells as String by getStringCellValue(). But this will probably throw a
>> runtime exception about the format of the cell data. I also encountered
>> similar problems with my workbooks. After working with work arounds for
>> a while, I got tired of it.
>> So, I wrote a Visual Basic sub (a glorified macro essentially) which
>> would take the original workbook and paste the relevant data into a new
>> workbook. Basic data pre-processing. That's where I got rid of the
>> annoying stuff like $ and ',' symbols; percentage conversions from e.g.
>> 60% to .6 etc. A favorable side effect was reduction in the file size of
>> the workbooks I had to read as the new workbooks were only about 20KB as
>> opposed to the 830KB filesize of the original workbooks. Huge
>> performance gains there.
>>
>> This is something you might want to look into if nothing else works.
>> Harsh.
>>
>> -----Original Message-----
>> From: Martin Bradley [mailto:martsbradley@btinternet.com] Sent: 
>> Monday, June 19, 2006 2:22 PM
>> To: poi-user@jakarta.apache.org
>> Subject: Reading leading zero formatted numbers
>>
>>
>> Folks,
>>
>> I've been using POI to read values from excel spreadsheets, but I have a
>> problem.
>>
>> My spreadsheets can contain a column with the following codes shown in
>> the table.  The formatting of the cells is set up as Custom 0000000
>>
>> 0003322
>> 0047345
>> 7088449
>> 7145456
>> 2380398
>> 0234400
>>
>> I do know that the column should be set to Text, since that is what it
>> contains, these are not numbers but codes.  However I have no control
>> over this since I have not created the data.
>>
>> Whenever I read the values out of the cells I get the following doubles.
>>
>> 3322.0
>> 47345.0
>> 7088449.0
>> 7145456.0
>> 2380398.0
>> 234400.0
>>
>> POI is reading the correct value from the sheet, the values read are the
>> same as the ones shown in the Formula bar whenever the cell is selected.
>> The Excel application is taking the Formatting preference and displaying
>> the leading zeros to the user.
>>
>> How can I get POI to read the values that are shown in the table after
>> the formatting has been applied.  I want the String 0003322 for the 
>> first cell for example.
>>
>> I'm sure that this is not the first time this problem came up, I've
>> searched the mailing list archive but only seen people trying to
>> preserve the leading zero when writing Excel sheets.
>>
>> thanks for your time,
>> Martin.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>>
>>
>>
>>  
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Mime
View raw message