/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ package org.apache.poi.ss.util; import java.math.BigDecimal; import java.math.BigInteger; /** * Excel converts numbers to text with different rules to those of java, so * Double.toString(value) won't do. * * 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: * * 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 */ public final class NumberToTextConverter { private static final long expMask = 0x7FF0000000000000L; private static final long FRAC_MASK= 0x000FFFFFFFFFFFFFL; private static final int EXPONENT_SHIFT = 52; private static final int FRAC_BITS_WIDTH = EXPONENT_SHIFT; private static final int EXPONENT_BIAS = 1023; private static final long FRAC_ASSUMED_HIGH_BIT = ( 1L<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. */ public static String toText(double value) { return rawDoubleBitsToText(Double.doubleToLongBits(value)); } /* package */ static String rawDoubleBitsToText(long pRawBits) { long rawBits = pRawBits; boolean isNegative = rawBits < 0; // sign bit is in the same place for long and double if (isNegative) { rawBits &= 0x7FFFFFFFFFFFFFFFL; } int biasedExponent = (int) ((rawBits & expMask) >> EXPONENT_SHIFT); if (biasedExponent == 0) { // value is 'denormalised' which means it is less than 2^-1022 // excel displays all these numbers as zero, even though calculations work OK return "0"; } int exponent = biasedExponent - EXPONENT_BIAS; long fracBits = FRAC_ASSUMED_HIGH_BIT | rawBits & FRAC_MASK; // Start by converting double value to BigDecimal BigDecimal bd; if (biasedExponent == 0x07FF) { // Special number NaN /Infinity if(rawBits == EXCEL_NAN_BITS) { return "3.484840871308E+308"; } // This is where excel really gets it wrong // Special numbers like Infinity and Nan are interpreted according to // the standard rules below. isNegative = false; // except that the sign bit is ignored } bd = convertToBigDecimal(exponent, fracBits); return formatBigInteger(isNegative, bd.unscaledValue(), bd.scale()); } private static BigDecimal convertToBigDecimal(int exponent, long fracBits) { byte[] joob = { (byte) (fracBits >> 48), (byte) (fracBits >> 40), (byte) (fracBits >> 32), (byte) (fracBits >> 24), (byte) (fracBits >> 16), (byte) (fracBits >> 8), (byte) (fracBits >> 0), }; BigInteger bigInt = new BigInteger(joob); int lastSigBitIndex = exponent-FRAC_BITS_WIDTH; if(lastSigBitIndex < 0) { BigInteger shifto = new BigInteger("1").shiftLeft(-lastSigBitIndex); int scale = 1 -(int) (lastSigBitIndex/LOG2_10); BigDecimal bd1 = new BigDecimal(bigInt); BigDecimal bdShifto = new BigDecimal(shifto); return bd1.divide(bdShifto, scale, BigDecimal.ROUND_HALF_UP); } BigInteger sl = bigInt.shiftLeft(lastSigBitIndex); return new BigDecimal(sl); } private static String formatBigInteger(boolean isNegative, BigInteger unscaledValue, int scale) { if (scale < 0) { throw new RuntimeException("negative scale"); } StringBuffer sb = new StringBuffer(unscaledValue.toString()); int numberOfLeadingZeros = -1; int unscaledLength = sb.length(); if (scale > 0 && scale >= unscaledLength) { // less than one numberOfLeadingZeros = scale-unscaledLength; formatLessThanOne(sb, numberOfLeadingZeros+1); } else { int decimalPointIndex = unscaledLength - scale; formatGreaterThanOne(sb, decimalPointIndex); } if(isNegative) { sb.insert(0, '-'); } return sb.toString(); } private static int getNumberOfSignificantFiguresDisplayed(int exponent) { int nLostDigits; // number of significand digits lost due big exponents if(exponent > 99) { // any exponent greater than 99 has 3 digits instead of 2 nLostDigits = 1; } else if (exponent < -98) { // For some weird reason on the negative side // step is occurs from -98 to -99 (not from -99 to -100) nLostDigits = 1; } else { nLostDigits = 0; } return DEFAULT_COUNT_SIGNIFICANT_DIGITS - nLostDigits; } private static boolean needsScientificNotation(int nDigits) { return nDigits > MAX_TEXT_LEN; } private static void formatGreaterThanOne(StringBuffer sb, int nIntegerDigits) { int maxSigFigs = getNumberOfSignificantFiguresDisplayed(nIntegerDigits); int decimalPointIndex = nIntegerDigits; boolean roundCausedCarry = performRound(sb, 0, maxSigFigs); int endIx = Math.min(maxSigFigs, sb.length()-1); int nSigFigures; if(roundCausedCarry) { sb.insert(0, '1'); decimalPointIndex++; nSigFigures = 1; } else { nSigFigures = countSignifantDigits(sb, endIx); } if(needsScientificNotation(decimalPointIndex)) { sb.setLength(nSigFigures); if (nSigFigures > 1) { sb.insert(1, '.'); } sb.append("E+"); appendExp(sb, decimalPointIndex-1); return; } if(isAllZeros(sb, decimalPointIndex, maxSigFigs)) { sb.setLength(decimalPointIndex); return; } // else some sig-digits after the decimal point sb.setLength(nSigFigures); sb.insert(decimalPointIndex, '.'); } /** * @param sb initially contains just the significant digits * @param pAbsExponent to be inserted (after "0.") at the start of the number */ private static void formatLessThanOne(StringBuffer sb, int pAbsExponent) { if (sb.charAt(0) == 0) { throw new IllegalArgumentException("First digit of significand should be non-zero"); } if (pAbsExponent < 1) { throw new IllegalArgumentException("abs(exponent) must be positive"); } int numberOfLeadingZeros = pAbsExponent-1; int absExponent = pAbsExponent; int maxSigFigs = getNumberOfSignificantFiguresDisplayed(-absExponent); boolean roundCausedCarry = performRound(sb, 0, maxSigFigs); int nRemainingSigFigs; if(roundCausedCarry) { absExponent--; numberOfLeadingZeros--; nRemainingSigFigs = 1; sb.setLength(0); sb.append("1"); } else { nRemainingSigFigs = countSignifantDigits(sb, 0 + maxSigFigs); sb.setLength(nRemainingSigFigs); } int normalLength = 2 + numberOfLeadingZeros + nRemainingSigFigs; // 2 == "0.".length() if (needsScientificNotation(normalLength)) { if (sb.length()>1) { sb.insert(1, '.'); } sb.append('E'); sb.append('-'); appendExp(sb, absExponent); } else { sb.insert(0, "0."); for(int i=numberOfLeadingZeros; i>0; i--) { sb.insert(2, '0'); } } } private static int countSignifantDigits(StringBuffer sb, int startIx) { int result=startIx; while(sb.charAt(result) == '0') { result--; if(result < 0) { throw new RuntimeException("No non-zero digits found"); } } return result + 1; } private static void appendExp(StringBuffer sb, int val) { if(val < 10) { sb.append('0'); sb.append((char)('0' + val)); return; } sb.append(val); } private static boolean isAllZeros(StringBuffer sb, int startIx, int endIx) { for(int i=startIx; i<=endIx && itrue if carry (out of the MS digit) occurred */ private static boolean performRound(StringBuffer sb, int firstSigFigIx, int nSigFigs) { int nextDigitIx = firstSigFigIx + nSigFigs; if(nextDigitIx == sb.length()) { return false; // nothing to do - digit to be rounded is at the end of the buffer } if(nextDigitIx > sb.length()) { throw new RuntimeException("Buffer too small to fit all significant digits"); } boolean hadCarryOutOfFirstDigit; if(sb.charAt(nextDigitIx) < '5') { // change to digit hadCarryOutOfFirstDigit = false; } else { hadCarryOutOfFirstDigit = roundAndCarry(sb, nextDigitIx); } // clear out the rest of the digits after the rounded digit // (at least the nearby digits) int endIx = Math.min(nextDigitIx + MAX_EXTRA_ZEROS, sb.length()); for(int i = nextDigitIx; i