Package org.apache.poi.ss.util

Class 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 bitsJavaExcel
    0x0000000000000000L0.00
    0x3FF0000000000000L1.01
    0x3FF00068DB8BAC71L1.00011.0001
    0x4087A00000000000L756.0756
    0x401E3D70A3D70A3DL7.567.56
    0x405EDD3C07FB4C99L123.45678901234568123.456789012346
    0x4132D687E3DF2180L1234567.89012345671234567.89012346
    0x3EE9E409302678BAL1.2345678901234568E-51.23456789012346E-05
    0x3F202E85BE180B74L1.2345678901234567E-40.000123456789012346
    0x3F543A272D9E0E51L0.00123456789012345670.00123456789012346
    0x3F8948B0F90591E6L0.0123456789012345680.0123456789012346
    0x3EE9E409301B5A02L1.23456789E-50.0000123456789
    0x3E6E7D05BDABDE50L5.6789012345E-80.000000056789012345
    0x3E6E7D05BDAD407EL5.67890123456E-85.67890123456E-08
    0x3E6E7D06029F18BEL5.678902E-80.00000005678902
    0x2BCB5733CB32AE6EL9.999999999999123E-989.99999999999912E-98
    0x2B617F7D4ED8C59EL1.0000000000001235E-991.0000000000001E-99
    0x0036319916D67853L1.2345678901234578E-3071.2345678901235E-307
    0x359DEE7A4AD4B81FL2.0E-502E-50
    0x41678C29DCD6E9E0L1.2345678901234567E712345678.9012346
    0x42A674E79C5FE523L1.2345678901234568E1312345678901234.6
    0x42DC12218377DE6BL1.2345678901234567E14123456789012346
    0x43118B54F22AEB03L1.2345678901234568E151234567890123460
    0x43E56A95319D63E1L1.2345678901234567E1912345678901234600000
    0x441AC53A7E04BCDAL1.2345678901234568E201.23456789012346E+20
    0xC3E56A95319D63E1L-1.2345678901234567E19-12345678901234600000
    0xC41AC53A7E04BCDAL-1.2345678901234568E20-1.23456789012346E+20
    0x54820FE0BA17F46DL1.2345678901234577E991.2345678901235E+99
    0x54B693D8E89DF188L1.2345678901234576E1001.2345678901235E+100
    0x4A611B0EC57E649AL2.0E502E+50
    0x7FEFFFFFFFFFFFFFL1.7976931348623157E3081.7976931348623E+308
    0x0010000000000000L2.2250738585072014E-3082.2250738585072E-308
    0x000FFFFFFFFFFFFFL2.225073858507201E-3080
    0x0000000000000001L4.9E-3240
    0x7FF0000000000000LInfinity1.7976931348623E+308
    0xFFF0000000000000L-Infinity1.7976931348623E+308
    0x441AC7A08EAD02F2L1.234999999999999E201.235E+20
    0x40FE26BFFFFFFFF9L123499.9999999999123500
    0x3E4A857BFB2F2809L1.234999999999999E-80.00000001235
    0x3BCD291DEF868C89L1.234999999999999E-201.235E-20
    0x444B1AE4D6E2EF4FL9.999999999999999E201E+21
    0x412E847FFFFFFFFFL999999.99999999991000000
    0x3E45798EE2308C39L9.999999999999999E-90.00000001
    0x3C32725DD1D243ABL9.999999999999999E-190.000000000000000001
    0x3BFD83C94FB6D2ABL9.999999999999999E-201E-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
    0xFFFF0420003C0000LNaN3.484840871308E+308
    0x7FF8000000000000LNaN2.6965397022935E+308
    0x7FFF0420003C0000LNaN3.484840871308E+308
    0xFFF8000000000000LNaN2.6965397022935E+308
    0xFFFF0AAAAAAAAAAALNaN3.4877119413344E+308
    0x7FF80AAAAAAAAAAALNaN2.7012211948322E+308
    0xFFFFFFFFFFFFFFFFLNaN3.5953862697246E+308
    0x7FFFFFFFFFFFFFFFLNaN3.5953862697246E+308
    0xFFF7FFFFFFFFFFFFLNaN2.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

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method Description
      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

        equals, 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.