DO NOT REPLY [Bug 48140] New: Cannot get a numeric value from a text cell

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

           Summary: Cannot get a numeric value from a text cell
           Product: POI
           Version: 3.5-FINAL
          Platform: PC
        OS/Version: Windows Vista
            Status: NEW
          Severity: blocker
          Priority: P2
         Component: XSSF
        AssignedTo: dev@...
        ReportedBy: michel.jung89@...


Created an attachment (id=24490)
 --> (https://issues.apache.org/bugzilla/attachment.cgi?id=24490)
The file I try to read

I have a column with a lot of numbers (for instance 1253323, 275527, ... ) and
try to get them using getNumericCellValue(). It throws an exception for each
row:

java.lang.IllegalStateException: Cannot get a numeric value from a text cell

Of course I already checked for the correct value:

Could not convert "2844922" from row 575: java.lang.IllegalStateException:
Cannot get a numeric value from a text cell


There is no problem with getNumericCellValue() in other two rows where I have
values like 0.50625, 0.75, 50 .

I've already tried to set the cell's format to "numeric" (or however it's
called in the english excel). No success.

I hope it's not my fault...

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

--- Comment #1 from Michel Jung <michel.jung89@...> 2009-11-05 08:46:02 UTC ---
Here comes the stack trace:

java.lang.IllegalStateException: Cannot get a numeric value from a text cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:768)
    at
org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:191)
    at xxx.readInstrumentLines(Converter.java:347)
    at xxx.run(Converter.java:412)

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

Nick Burch <nick@...> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|                            |INVALID

--- Comment #2 from Nick Burch <nick@...> 2009-11-05 11:01:53 UTC ---
You need to check the type of the cell. If it's of type numeric, then call
getNumericCellValue(). If it's of type string, call getRichStringCellValue().
The type tells you which one you can call.

Sometimes, excel decides to store a number in a cell of type string. For those
cases, fetch the string value and parse it to a number in java. See the
documentation on the site and/or javadocs for more details

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

Himanshu <himanshu.goyal@...> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|INVALID                     |

--- Comment #3 from Himanshu <himanshu.goyal@...> 2009-11-24 08:25:34 UTC ---
I am having difficulties in reading an excel file which contains data of mix
type.

For ex: If a cell contains value as "444444" with cell type as "General" then
why Apache POI is returning the value as "444444.0" and no way to get the
original value which is displayed when we open the document in MS Excel.

We cannot expect the customer to provide the data with correct cell data type,
Apache POI should return the same data value which is displayed when opening
the document and should additionally provide the string equivalent for numeric
data even. There is no place in your library classes where I see the returned
data consider the settings (2 decimal digits of precision) in Excel document
being taken care while returning the cell value.

I previously used jexcel-Api and recently planned to shift to use Apache POI to
support Excel 2007 files. But even in the initial bit it is a bit disappointing
looking at the data returned and available from your library.

Could you suggest me If there is a way possible to get the data in the required
format present and displayed by MS-Excel.

Hoping for an early response.

Thanks,
Himanshu

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

Nick Burch <nick@...> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|REOPENED                    |RESOLVED
         Resolution|                            |INVALID

--- Comment #4 from Nick Burch <nick@...> 2009-11-24 08:38:05 UTC ---
Please do not ask basic usage questions by re-opening old bugs relating to
something else!

Check the list archives, yours is a common misconception about excel, and all
the answers you seek have come up on the list many times.

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[MSB]

Further to Nick's response, can you please let us have more information. What do you want to do with the values once you have them? If all you want to do is print them onto the screen or onto a report then the easiest technique is simply to use the DataFormatter class, or one of it's subclasses, to convert the cell's contents into a String. DataFormatter actually uses the formatting object applied to the cell by the sheets original creator to ensure that the resulting String looks just as the cell did. However, if you need to recover numeric or date/time values for performing calculations then you need to test the cells type and call the correct method to recover it's contents.

Yours

Mark B

Bugzilla from [e-mail] wrote:

https://issues.apache.org/bugzilla/show_bug.cgi?id=48140

Nick Burch <[e-mail]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|REOPENED                    |RESOLVED
         Resolution|                            |INVALID

--- Comment #4 from Nick Burch <[e-mail]> 2009-11-24 08:38:05 UTC ---
Please do not ask basic usage questions by re-opening old bugs relating to
something else!

Check the list archives, yours is a common misconception about excel, and all
the answers you seek have come up on the list many times.

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]