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.
- Author:
- Josh Micich
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.
Copyright 2012 The Apache Software Foundation or
its licensors, as applicable.