Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## 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`

Method Detail

### 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.

Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

Copyright 2017 The Apache Software Foundation or its licensors, as applicable.