Package org.xlsx4j.org.apache.poi.ss.util

Class NumberToTextConverter

java.lang.Object
org.xlsx4j.org.apache.poi.ss.util.NumberToTextConverter
public final class NumberToTextConverter extends 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.

  • Field Details

  • Constructor Details

    • NumberToTextConverter

      private NumberToTextConverter()

  • Method Details

    • toText

      public static 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.

    • rawDoubleBitsToText

      static String rawDoubleBitsToText(long pRawBits)

    • convertToText

      private static void convertToText(StringBuilder sb, NormalisedDecimal pnd)

    • formatLessThanOne

      private static void formatLessThanOne(StringBuilder sb, String decimalDigits, int decExponent, int countSigDigits)

    • formatGreaterThanOne

      private static void formatGreaterThanOne(StringBuilder sb, String decimalDigits, int decExponent, int countSigDigits)

    • needsScientificNotation

      private static boolean needsScientificNotation(int nDigits)

    • countSignifantDigits

      private static int countSignifantDigits(String sb)

    • appendExp

      private static void appendExp(StringBuilder sb, int val)