You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Matthew Keene <df...@yahoo.com.au> on 2005/01/31 07:38:09 UTC
Precision of numbers formatted as General
I'm trying to read numbers from an Excel spreadsheet.
The cells containing the numbers are formatted as
General, and have no specification of the number of
decimal places, yet when I read the cell using
cell.getNumericCellValue(), I get a different number
of decimal places than what is shown in Excel. For
instance, one cell contains the value 30.69677419 in
Excel, yet displays as 30.69677419354839 when
retrieved using POI.
Here's the code that I'm using to retrieve the cell
values:
// Allow for the biggest possible number we might
ever get
DecimalFormat df = new
DecimalFormat("####################.####################")
;
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
String cellValue =
cell.getStringCellValue().trim() ;
}
else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_NUMERIC) {
rowValues.add(df.format(cell.getNumericCellValue())) ;
}
Can anyone explain why the differences are occurring,
and what we can do to display the data in the way it
is shown in Excel (which is what the users expect) ?
I know that I could round it to a specific number of
decimal places, but we want the users to be able to
put in an arbitrary number of decimal places in a cell
in the spreadsheet and have the program retrieve them
as entered.
Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com
---------------------------------------------------------------------
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/
Re: Precision of numbers formatted as General
Posted by Matthew Keene <df...@yahoo.com.au>.
--- Avik Sengupta <av...@itellix.com> wrote:
> Excel stores all ALL numbers internally as binary
> floating point
> numbers. Based on the formatting, it decides how
> many decimals etc to
> display. Note that the formatting is stored
> separately, and the
> representation is only decided at runtime.. it is
> NOT stored in the
> file.
I understand what you're saying, but I guess I'm
confused about how Excel knows how many decimal points
were entered in the original entry and knows how to
replicate them when redisplaying the file unless it
keeps some information about them in the file.
>
> For 'general' formats, excel
> does some
> heuristics, so you might have to do that as well.
>
Are there any details about the 'heuristics' ? Is
there any documentation that you can point me to that
might describe them ?
Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com
---------------------------------------------------------------------
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/
Re: Precision of numbers formatted as General
Posted by Avik Sengupta <av...@itellix.com>.
Quick answer: to replicate what the user sees in excel, you will, of
course, have to replicate excel functionality!
Excel stores all ALL numbers internally as binary floating point
numbers. Based on the formatting, it decides how many decimals etc to
display. Note that the formatting is stored separately, and the
representation is only decided at runtime.. it is NOT stored in the
file.
POI provides you only what is stored in the file. Beyond that is
application territory! You should get the format for the cell, and write
your logic accordingly. For 'general' formats, excel does some
heuristics, so you might have to do that as well.
Regards
-
Avik
On Mon, 2005-01-31 at 17:38 +1100, Matthew Keene wrote:
> I'm trying to read numbers from an Excel spreadsheet.
> The cells containing the numbers are formatted as
> General, and have no specification of the number of
> decimal places, yet when I read the cell using
> cell.getNumericCellValue(), I get a different number
> of decimal places than what is shown in Excel. For
> instance, one cell contains the value 30.69677419 in
> Excel, yet displays as 30.69677419354839 when
> retrieved using POI.
>
> Here's the code that I'm using to retrieve the cell
> values:
>
> // Allow for the biggest possible number we might
> ever get
> DecimalFormat df = new
> DecimalFormat("####################.####################")
> ;
> if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
> {
> String cellValue =
> cell.getStringCellValue().trim() ;
> }
> else if (cell.getCellType() ==
> HSSFCell.CELL_TYPE_NUMERIC) {
>
> rowValues.add(df.format(cell.getNumericCellValue())) ;
> }
>
> Can anyone explain why the differences are occurring,
> and what we can do to display the data in the way it
> is shown in Excel (which is what the users expect) ?
> I know that I could round it to a specific number of
> decimal places, but we want the users to be able to
> put in an arbitrary number of decimal places in a cell
> in the spreadsheet and have the program retrieve them
> as entered.
>
>
> Find local movie times and trailers on Yahoo! Movies.
> http://au.movies.yahoo.com
>
> ---------------------------------------------------------------------
> 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/