org.apache.poi.ss.util
Class NumberToTextConverter
java.lang.Object
org.apache.poi.ss.util.NumberToTextConverter
public final class NumberToTextConverter
- extends java.lang.Object
Excel converts numbers to text with different rules to those of java, so
Double.toString(value) won't do.
- No more than 15 significant figures are output (java does 18).
- The sign char for the exponent is included even if positive
- Special values (NaN and Infinity) get rendered like the ordinary
number that the bit pattern represents.
- Denormalised values (between ±2-1074 and ±2-1022
are displayed as "0"
IEEE 64-bit Double Rendering Comparison
| Raw bits | Java | Excel |
|---|
| 0x0000000000000000L | 0.0 | 0 |
| 0x3FF0000000000000L | 1.0 | 1 |
| 0x3FF00068DB8BAC71L | 1.0001 | 1.0001 |
| 0x4087A00000000000L | 756.0 | 756 |
| 0x401E3D70A3D70A3DL | 7.56 | 7.56 |
| 0x405EDD3C07FB4C99L | 123.45678901234568 | 123.456789012346 |
| 0x4132D687E3DF2180L | 1234567.8901234567 | 1234567.89012346 |
| 0x3EE9E409302678BAL | 1.2345678901234568E-5 | 1.23456789012346E-05 |
| 0x3F202E85BE180B74L | 1.2345678901234567E-4 | 0.000123456789012346 |
| 0x3F543A272D9E0E51L | 0.0012345678901234567 | 0.00123456789012346 |
| 0x3F8948B0F90591E6L | 0.012345678901234568 | 0.0123456789012346 |
| 0x3EE9E409301B5A02L | 1.23456789E-5 | 0.0000123456789 |
| 0x3E6E7D05BDABDE50L | 5.6789012345E-8 | 0.000000056789012345 |
| 0x3E6E7D05BDAD407EL | 5.67890123456E-8 | 5.67890123456E-08 |
| 0x3E6E7D06029F18BEL | 5.678902E-8 | 0.00000005678902 |
| 0x2BCB5733CB32AE6EL | 9.999999999999123E-98 | 9.99999999999912E-98 |
| 0x2B617F7D4ED8C59EL | 1.0000000000001235E-99 | 1.0000000000001E-99 |
| 0x0036319916D67853L | 1.2345678901234578E-307 | 1.2345678901235E-307 |
| 0x359DEE7A4AD4B81FL | 2.0E-50 | 2E-50 |
| 0x41678C29DCD6E9E0L | 1.2345678901234567E7 | 12345678.9012346 |
| 0x42A674E79C5FE523L | 1.2345678901234568E13 | 12345678901234.6 |
| 0x42DC12218377DE6BL | 1.2345678901234567E14 | 123456789012346 |
| 0x43118B54F22AEB03L | 1.2345678901234568E15 | 1234567890123460 |
| 0x43E56A95319D63E1L | 1.2345678901234567E19 | 12345678901234600000 |
| 0x441AC53A7E04BCDAL | 1.2345678901234568E20 | 1.23456789012346E+20 |
| 0xC3E56A95319D63E1L | -1.2345678901234567E19 | -12345678901234600000 |
| 0xC41AC53A7E04BCDAL | -1.2345678901234568E20 | -1.23456789012346E+20 |
| 0x54820FE0BA17F46DL | 1.2345678901234577E99 | 1.2345678901235E+99 |
| 0x54B693D8E89DF188L | 1.2345678901234576E100 | 1.2345678901235E+100 |
| 0x4A611B0EC57E649AL | 2.0E50 | 2E+50 |
| 0x7FEFFFFFFFFFFFFFL | 1.7976931348623157E308 | 1.7976931348623E+308 |
| 0x0010000000000000L | 2.2250738585072014E-308 | 2.2250738585072E-308 |
| 0x000FFFFFFFFFFFFFL | 2.225073858507201E-308 | 0 |
| 0x0000000000000001L | 4.9E-324 | 0 |
| 0x7FF0000000000000L | Infinity | 1.7976931348623E+308 |
| 0xFFF0000000000000L | -Infinity | 1.7976931348623E+308 |
| 0x441AC7A08EAD02F2L | 1.234999999999999E20 | 1.235E+20 |
| 0x40FE26BFFFFFFFF9L | 123499.9999999999 | 123500 |
| 0x3E4A857BFB2F2809L | 1.234999999999999E-8 | 0.00000001235 |
| 0x3BCD291DEF868C89L | 1.234999999999999E-20 | 1.235E-20 |
| 0x444B1AE4D6E2EF4FL | 9.999999999999999E20 | 1E+21 |
| 0x412E847FFFFFFFFFL | 999999.9999999999 | 1000000 |
| 0x3E45798EE2308C39L | 9.999999999999999E-9 | 0.00000001 |
| 0x3C32725DD1D243ABL | 9.999999999999999E-19 | 0.000000000000000001 |
| 0x3BFD83C94FB6D2ABL | 9.999999999999999E-20 | 1E-19 |
| 0xC44B1AE4D6E2EF4FL | -9.999999999999999E20 | -1E+21 |
| 0xC12E847FFFFFFFFFL | -999999.9999999999 | -1000000 |
| 0xBE45798EE2308C39L | -9.999999999999999E-9 | -0.00000001 |
| 0xBC32725DD1D243ABL | -9.999999999999999E-19 | -0.000000000000000001 |
| 0xBBFD83C94FB6D2ABL | -9.999999999999999E-20 | -1E-19 |
| 0xFFFF0420003C0000L | NaN | 3.484840871308E+308 |
| 0x7FF8000000000000L | NaN | 2.6965397022935E+308 |
| 0x7FFF0420003C0000L | NaN | 3.484840871308E+308 |
| 0xFFF8000000000000L | NaN | 2.6965397022935E+308 |
| 0xFFFF0AAAAAAAAAAAL | NaN | 3.4877119413344E+308 |
| 0x7FF80AAAAAAAAAAAL | NaN | 2.7012211948322E+308 |
| 0xFFFFFFFFFFFFFFFFL | NaN | 3.5953862697246E+308 |
| 0x7FFFFFFFFFFFFFFFL | NaN | 3.5953862697246E+308 |
| 0xFFF7FFFFFFFFFFFFL | NaN | 2.6965397022935E+308 |
Note:
Excel has inconsistent rules for the following numeric operations:
- Conversion to string (as handled here)
- Rendering numerical quantities in the cell grid.
- Conversion from text
- General arithmetic
Excel's text to number conversion is not a true inverse of this operation. The
allowable ranges are different. Some numbers that don't correctly convert to text actually
do get handled properly when used in arithmetic evaluations.
|
Method Summary |
static java.lang.String |
toText(double value)
Converts the supplied value to the text representation that Excel would give if
the value were to appear in an unformatted cell, or as a literal number in a formula.
Note - the results from this method differ slightly from those of Double.toString()
In some special cases Excel behaves quite differently. |
| Methods inherited from class java.lang.Object |
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait |
toText
public static java.lang.String toText(double value)
- Converts the supplied value to the text representation that Excel would give if
the value were to appear in an unformatted cell, or as a literal number in a formula.
Note - the results from this method differ slightly from those of Double.toString()
In some special cases Excel behaves quite differently. This function attempts to reproduce
those results.