From b4bf387e9e7ebe30b5a09a8d17049f311108574c Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Mon, 1 Dec 2008 23:24:23 +0000 Subject: [PATCH] Fix for bug 46156 - improved POI's number rendering to more closely match Excel's git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@722284 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../poi/hssf/record/formula/NumberPtg.java | 4 +- .../hssf/record/formula/eval/NumberEval.java | 42 +- .../poi/ss/util/NumberToTextConverter.java | 405 ++++++++++++++++++ .../org/apache/poi/util/LittleEndian.java | 7 +- .../org/apache/poi/hssf/HSSFTests.java | 2 + .../poi/hssf/model/TestFormulaParser.java | 36 +- .../record/formula/functions/TestNper.java | 20 +- .../apache/poi/hssf/usermodel/TestBugs.java | 190 ++++---- .../usermodel/TestFormulaEvaluatorBugs.java | 10 +- .../apache/poi/ss/util/AllSSUtilTests.java | 35 ++ .../NumberRenderingSpreadsheetGenerator.java | 251 +++++++++++ .../util/NumberToTextConversionExamples.java | 183 ++++++++ .../ss/util/TestNumberToTextConverter.java | 128 ++++++ 15 files changed, 1151 insertions(+), 164 deletions(-) create mode 100644 src/java/org/apache/poi/ss/util/NumberToTextConverter.java create mode 100644 src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java create mode 100644 src/testcases/org/apache/poi/ss/util/NumberRenderingSpreadsheetGenerator.java create mode 100644 src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java create mode 100644 src/testcases/org/apache/poi/ss/util/TestNumberToTextConverter.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 80a6e8cee5..1e11f2a4bc 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + 46156 - Improved number to text conversion to be closer to that of Excel 46312 - Fixed ValueRecordsAggregate to handle removal of new empty row 46269 - Improved error message when attempting to read BIFF2 file 46206 - Fixed Sheet to tolerate missing DIMENSION records diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 8763adc0a0..d1d819ad44 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 46156 - Improved number to text conversion to be closer to that of Excel 46312 - Fixed ValueRecordsAggregate to handle removal of new empty row 46269 - Improved error message when attempting to read BIFF2 file 46206 - Fixed Sheet to tolerate missing DIMENSION records diff --git a/src/java/org/apache/poi/hssf/record/formula/NumberPtg.java b/src/java/org/apache/poi/hssf/record/formula/NumberPtg.java index 5bd8b08453..244c09fe5f 100644 --- a/src/java/org/apache/poi/hssf/record/formula/NumberPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/NumberPtg.java @@ -17,6 +17,7 @@ package org.apache.poi.hssf.record.formula; +import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.util.LittleEndianInput; import org.apache.poi.util.LittleEndianOutput; @@ -65,7 +66,6 @@ public final class NumberPtg extends ScalarConstantPtg { } public String toFormulaString() { - // TODO - java's rendering of double values is not quite same as excel's - return String.valueOf(field_1_value); + return NumberToTextConverter.toText(field_1_value); } } diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/NumberEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/NumberEval.java index e22957bd4a..33aff2a493 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/NumberEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/NumberEval.java @@ -23,51 +23,45 @@ package org.apache.poi.hssf.record.formula.eval; import org.apache.poi.hssf.record.formula.IntPtg; import org.apache.poi.hssf.record.formula.NumberPtg; import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.ss.util.NumberToTextConverter; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > * */ -public class NumberEval implements NumericValueEval, StringValueEval { +public final class NumberEval implements NumericValueEval, StringValueEval { public static final NumberEval ZERO = new NumberEval(0); - private double value; - private String stringValue; + private final double _value; + private String _stringValue; public NumberEval(Ptg ptg) { - if (ptg instanceof IntPtg) { - this.value = ((IntPtg) ptg).getValue(); + if (ptg == null) { + throw new IllegalArgumentException("ptg must not be null"); } - else if (ptg instanceof NumberPtg) { - this.value = ((NumberPtg) ptg).getValue(); + if (ptg instanceof IntPtg) { + _value = ((IntPtg) ptg).getValue(); + } else if (ptg instanceof NumberPtg) { + _value = ((NumberPtg) ptg).getValue(); + } else { + throw new IllegalArgumentException("bad argument type (" + ptg.getClass().getName() + ")"); } } public NumberEval(double value) { - this.value = value; + _value = value; } public double getNumberValue() { - return value; + return _value; } - public String getStringValue() { // TODO: limit to 15 decimal places - if (stringValue == null) - makeString(); - return stringValue; - } - - protected void makeString() { - if (!Double.isNaN(value)) { - long lvalue = Math.round(value); - if (lvalue == value) { - stringValue = String.valueOf(lvalue); - } - else { - stringValue = String.valueOf(value); - } + public String getStringValue() { + if (_stringValue == null) { + _stringValue = NumberToTextConverter.toText(_value); } + return _stringValue; } public final String toString() { StringBuffer sb = new StringBuffer(64); diff --git a/src/java/org/apache/poi/ss/util/NumberToTextConverter.java b/src/java/org/apache/poi/ss/util/NumberToTextConverter.java new file mode 100644 index 0000000000..1d7a901d25 --- /dev/null +++ b/src/java/org/apache/poi/ss/util/NumberToTextConverter.java @@ -0,0 +1,405 @@ +/* ==================================================================== + 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. + *
    + *
  • 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. + * + * @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 @@ -54,6 +55,7 @@ public final class HSSFTests { suite.addTest(new TestSuite(TestEventRecordFactory.class)); suite.addTest(new TestSuite(TestModelFactory.class)); suite.addTest(AllSSFormulaTests.suite()); + suite.addTest(AllSSUtilTests.suite()); return suite; } } diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index fe248ca8b5..c8a46a8dde 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -283,63 +283,63 @@ public final class TestFormulaParser extends TestCase { cell.setCellFormula("1.3E21/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1.3E21/3", formula); + assertEquals("Exponential formula string", "1.3E+21/3", formula); cell.setCellFormula("-1.3E21/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.3E21/3", formula); + assertEquals("Exponential formula string", "-1.3E+21/3", formula); cell.setCellFormula("1322E21/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1.322E24/3", formula); + assertEquals("Exponential formula string", "1.322E+24/3", formula); cell.setCellFormula("-1322E21/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.322E24/3", formula); + assertEquals("Exponential formula string", "-1.322E+24/3", formula); cell.setCellFormula("1.3E1/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "13.0/3", formula); + assertEquals("Exponential formula string", "13/3", formula); cell.setCellFormula("-1.3E1/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-13.0/3", formula); + assertEquals("Exponential formula string", "-13/3", formula); cell.setCellFormula("1.3E-4/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1.3E-4/3", formula); + assertEquals("Exponential formula string", "0.00013/3", formula); cell.setCellFormula("-1.3E-4/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.3E-4/3", formula); + assertEquals("Exponential formula string", "-0.00013/3", formula); cell.setCellFormula("13E-15/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1.3E-14/3", formula); + assertEquals("Exponential formula string", "0.000000000000013/3", formula); cell.setCellFormula("-13E-15/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.3E-14/3", formula); + assertEquals("Exponential formula string", "-0.000000000000013/3", formula); cell.setCellFormula("1.3E3/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1300.0/3", formula); + assertEquals("Exponential formula string", "1300/3", formula); cell.setCellFormula("-1.3E3/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1300.0/3", formula); + assertEquals("Exponential formula string", "-1300/3", formula); cell.setCellFormula("1300000000000000/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "1.3E15/3", formula); + assertEquals("Exponential formula string", "1300000000000000/3", formula); cell.setCellFormula("-1300000000000000/3"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.3E15/3", formula); + assertEquals("Exponential formula string", "-1300000000000000/3", formula); cell.setCellFormula("-10E-1/3.1E2*4E3/3E4"); formula = cell.getCellFormula(); - assertEquals("Exponential formula string", "-1.0/310.0*4000.0/30000.0", formula); + assertEquals("Exponential formula string", "-1/310*4000/30000", formula); } public void testNumbers() { @@ -370,15 +370,15 @@ public final class TestFormulaParser extends TestCase { cell.setCellFormula("10E1"); formula = cell.getCellFormula(); - assertEquals("100.0", formula); + assertEquals("100", formula); cell.setCellFormula("10E+1"); formula = cell.getCellFormula(); - assertEquals("100.0", formula); + assertEquals("100", formula); cell.setCellFormula("10E-1"); formula = cell.getCellFormula(); - assertEquals("1.0", formula); + assertEquals("1", formula); } public void testRanges() { diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestNper.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestNper.java index a2bb0f6adb..a919c35af4 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestNper.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestNper.java @@ -17,51 +17,49 @@ package org.apache.poi.hssf.record.formula.functions; +import junit.framework.TestCase; + import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.NumberEval; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFErrorConstants; -import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import junit.framework.AssertionFailedError; -import junit.framework.TestCase; - /** + * Tests for {@link FinanceFunction#NPER} * * @author Josh Micich */ public final class TestNper extends TestCase { public void testSimpleEvaluate() { - + Eval[] args = { new NumberEval(0.05), new NumberEval(250), new NumberEval(-1000), }; Eval result = FinanceFunction.NPER.evaluate(args, 0, (short)0); - + assertEquals(NumberEval.class, result.getClass()); assertEquals(4.57353557, ((NumberEval)result).getNumberValue(), 0.00000001); } - + public void testEvaluate_bug_45732() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFCell cell = sheet.createRow(0).createCell(0); - + cell.setCellFormula("NPER(12,4500,100000,100000)"); cell.setCellValue(15.0); - assertEquals("NPER(12,4500,100000.0,100000.0)", cell.getCellFormula()); + assertEquals("NPER(12,4500,100000,100000)", cell.getCellFormula()); assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType()); assertEquals(15.0, cell.getNumericCellValue(), 0.0); - + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); fe.evaluateFormulaCell(cell); assertEquals(HSSFCell.CELL_TYPE_ERROR, cell.getCachedFormulaResultType()); assertEquals(HSSFErrorConstants.ERROR_NUM, cell.getErrorCellValue()); } - } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index 402ee797a9..2c28be6501 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -419,7 +419,7 @@ public final class TestBugs extends TestCase { if (name.isFunctionName()) { continue; } - name.getReference(); + name.getRefersToFormula(); } } @@ -1070,8 +1070,8 @@ public final class TestBugs extends TestCase { HSSFSheet s = wb.createSheet(); s.createRow(0); s.createRow(1); - HSSFCell c1 = s.getRow(0).createCell(0); - HSSFCell c2 = s.getRow(1).createCell(0); + s.getRow(0).createCell(0); + s.getRow(1).createCell(0); assertEquals(4, wb.getNumberOfFonts()); @@ -1162,7 +1162,7 @@ public final class TestBugs extends TestCase { c3.setCellFormula("\"90210\""); // Check the formulas - assertEquals("70164.0", c1.getCellFormula()); + assertEquals("70164", c1.getCellFormula()); assertEquals("\"70164\"", c2.getCellFormula()); // And check the values - blank @@ -1415,67 +1415,67 @@ public final class TestBugs extends TestCase { assertFalse(nwb.isSheetHidden(2)); assertTrue(nwb.isSheetVeryHidden(2)); } - + /** * header / footer text too long */ public void test45777() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet(); - - String s248 = ""; - for(int i=0; i<248; i++) { - s248 += "x"; - } - String s249 = s248 + "1"; - String s250 = s248 + "12"; - String s251 = s248 + "123"; - assertEquals(248, s248.length()); - assertEquals(249, s249.length()); - assertEquals(250, s250.length()); - assertEquals(251, s251.length()); - - - // Try on headers - s.getHeader().setCenter(s248); - assertEquals(254, s.getHeader().getRawHeader().length()); - writeOutAndReadBack(wb); - - s.getHeader().setCenter(s249); - assertEquals(255, s.getHeader().getRawHeader().length()); - writeOutAndReadBack(wb); - - try { - s.getHeader().setCenter(s250); // 256 - fail(); - } catch(IllegalArgumentException e) {} - - try { - s.getHeader().setCenter(s251); // 257 - fail(); - } catch(IllegalArgumentException e) {} - - - // Now try on footers - s.getFooter().setCenter(s248); - assertEquals(254, s.getFooter().getRawFooter().length()); - writeOutAndReadBack(wb); - - s.getFooter().setCenter(s249); - assertEquals(255, s.getFooter().getRawFooter().length()); - writeOutAndReadBack(wb); - - try { - s.getFooter().setCenter(s250); // 256 - fail(); - } catch(IllegalArgumentException e) {} - - try { - s.getFooter().setCenter(s251); // 257 - fail(); - } catch(IllegalArgumentException e) {} - } - + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet s = wb.createSheet(); + + String s248 = ""; + for(int i=0; i<248; i++) { + s248 += "x"; + } + String s249 = s248 + "1"; + String s250 = s248 + "12"; + String s251 = s248 + "123"; + assertEquals(248, s248.length()); + assertEquals(249, s249.length()); + assertEquals(250, s250.length()); + assertEquals(251, s251.length()); + + + // Try on headers + s.getHeader().setCenter(s248); + assertEquals(254, s.getHeader().getRawHeader().length()); + writeOutAndReadBack(wb); + + s.getHeader().setCenter(s249); + assertEquals(255, s.getHeader().getRawHeader().length()); + writeOutAndReadBack(wb); + + try { + s.getHeader().setCenter(s250); // 256 + fail(); + } catch(IllegalArgumentException e) {} + + try { + s.getHeader().setCenter(s251); // 257 + fail(); + } catch(IllegalArgumentException e) {} + + + // Now try on footers + s.getFooter().setCenter(s248); + assertEquals(254, s.getFooter().getRawFooter().length()); + writeOutAndReadBack(wb); + + s.getFooter().setCenter(s249); + assertEquals(255, s.getFooter().getRawFooter().length()); + writeOutAndReadBack(wb); + + try { + s.getFooter().setCenter(s250); // 256 + fail(); + } catch(IllegalArgumentException e) {} + + try { + s.getFooter().setCenter(s251); // 257 + fail(); + } catch(IllegalArgumentException e) {} + } + /** * Charts with long titles */ @@ -1485,43 +1485,43 @@ public final class TestBugs extends TestCase { assertEquals(1, wb.getNumberOfSheets()); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); } - + /** * Cell background colours */ public void test45492() { - HSSFWorkbook wb = openSample("45492.xls"); - HSSFSheet s = wb.getSheetAt(0); - HSSFRow r = s.getRow(0); - HSSFPalette p = wb.getCustomPalette(); - - HSSFCell auto = r.getCell(0); - HSSFCell grey = r.getCell(1); - HSSFCell red = r.getCell(2); - HSSFCell blue = r.getCell(3); - HSSFCell green = r.getCell(4); - - assertEquals(64, auto.getCellStyle().getFillForegroundColor()); - assertEquals(64, auto.getCellStyle().getFillBackgroundColor()); - assertEquals("0:0:0", p.getColor(64).getHexString()); - - assertEquals(22, grey.getCellStyle().getFillForegroundColor()); - assertEquals(64, grey.getCellStyle().getFillBackgroundColor()); - assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString()); - - assertEquals(10, red.getCellStyle().getFillForegroundColor()); - assertEquals(64, red.getCellStyle().getFillBackgroundColor()); - assertEquals("FFFF:0:0", p.getColor(10).getHexString()); - - assertEquals(12, blue.getCellStyle().getFillForegroundColor()); - assertEquals(64, blue.getCellStyle().getFillBackgroundColor()); - assertEquals("0:0:FFFF", p.getColor(12).getHexString()); - - assertEquals(11, green.getCellStyle().getFillForegroundColor()); - assertEquals(64, green.getCellStyle().getFillBackgroundColor()); - assertEquals("0:FFFF:0", p.getColor(11).getHexString()); - } - + HSSFWorkbook wb = openSample("45492.xls"); + HSSFSheet s = wb.getSheetAt(0); + HSSFRow r = s.getRow(0); + HSSFPalette p = wb.getCustomPalette(); + + HSSFCell auto = r.getCell(0); + HSSFCell grey = r.getCell(1); + HSSFCell red = r.getCell(2); + HSSFCell blue = r.getCell(3); + HSSFCell green = r.getCell(4); + + assertEquals(64, auto.getCellStyle().getFillForegroundColor()); + assertEquals(64, auto.getCellStyle().getFillBackgroundColor()); + assertEquals("0:0:0", p.getColor(64).getHexString()); + + assertEquals(22, grey.getCellStyle().getFillForegroundColor()); + assertEquals(64, grey.getCellStyle().getFillBackgroundColor()); + assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString()); + + assertEquals(10, red.getCellStyle().getFillForegroundColor()); + assertEquals(64, red.getCellStyle().getFillBackgroundColor()); + assertEquals("FFFF:0:0", p.getColor(10).getHexString()); + + assertEquals(12, blue.getCellStyle().getFillForegroundColor()); + assertEquals(64, blue.getCellStyle().getFillBackgroundColor()); + assertEquals("0:0:FFFF", p.getColor(12).getHexString()); + + assertEquals(11, green.getCellStyle().getFillForegroundColor()); + assertEquals(64, green.getCellStyle().getFillBackgroundColor()); + assertEquals("0:FFFF:0", p.getColor(11).getHexString()); + } + /** * ContinueRecord after EOF */ @@ -1531,7 +1531,7 @@ public final class TestBugs extends TestCase { assertEquals(7, wb.getNumberOfSheets()); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); } - + /** * Odd POIFS blocks issue: * block[ 44 ] already removed from org.apache.poi.poifs.storage.BlockListImpl.remove diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java index ce57c28594..ba791bf58a 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java @@ -151,7 +151,7 @@ public final class TestFormulaEvaluatorBugs extends TestCase { row = sheet.getRow(8); cell = row.getCell(0); - assertEquals("327680.0/32768", cell.getCellFormula()); + assertEquals("327680/32768", cell.getCellFormula()); assertEquals(10, eva.evaluate(cell).getNumberValue(), 0); row = sheet.getRow(9); @@ -166,15 +166,9 @@ public final class TestFormulaEvaluatorBugs extends TestCase { row = sheet.getRow(11); cell = row.getCell(0); - assertEquals("-1000000.0-3000000.0", cell.getCellFormula()); + assertEquals("-1000000-3000000", cell.getCellFormula()); assertEquals(-4000000, eva.evaluate(cell).getNumberValue(), 0); } -// public static void main(String[] args) { -// new TestFormulaEvaluatorBugs().test44410(); -// new TestFormulaEvaluatorBugs().testSlowEvaluate45376(); -// new HSSFWorkbook(); -// System.out.println("done"); -// } /** * Bug 44410: SUM(C:C) is valid in excel, and means a sum diff --git a/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java b/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java new file mode 100644 index 0000000000..9da4c2ca36 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java @@ -0,0 +1,35 @@ +/* ==================================================================== + 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 junit.framework.Test; +import junit.framework.TestSuite; +/** + * Test suite for org.apache.poi.ss.util + * + * @author Josh Micich + */ +public final class AllSSUtilTests { + public static Test suite() { + TestSuite result = new TestSuite(AllSSUtilTests.class.getName()); + result.addTestSuite(TestCellReference.class); + result.addTestSuite(TestNumberToTextConverter.class); + result.addTestSuite(TestRegion.class); + return result; + } +} diff --git a/src/testcases/org/apache/poi/ss/util/NumberRenderingSpreadsheetGenerator.java b/src/testcases/org/apache/poi/ss/util/NumberRenderingSpreadsheetGenerator.java new file mode 100644 index 0000000000..a03bfb0848 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/util/NumberRenderingSpreadsheetGenerator.java @@ -0,0 +1,251 @@ +/* ==================================================================== + 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.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.DataInputStream; +import java.io.File; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.util.ArrayList; +import java.util.List; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFCellStyle; +import org.apache.poi.hssf.usermodel.HSSFFont; +import org.apache.poi.hssf.usermodel.HSSFRichTextString; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.util.NumberToTextConversionExamples.ExampleConversion; +import org.apache.poi.util.HexDump; +import org.apache.poi.util.HexRead; + +/** + * Creates a spreadsheet that demonstrates Excel's rendering of various IEEE double values. + * + * @author Josh Micich + */ +public class NumberRenderingSpreadsheetGenerator { + + private static final class SheetWriter { + + private final HSSFSheet _sheet; + private int _rowIndex; + private final List _replacementNaNs; + + public SheetWriter(HSSFWorkbook wb) { + HSSFSheet sheet = wb.createSheet("Sheet1"); + + writeHeaderRow(wb, sheet); + _sheet = sheet; + _rowIndex = 1; + _replacementNaNs = new ArrayList(); + } + + public void addTestRow(long rawBits, String expectedExcelRendering) { + writeDataRow(_sheet, _rowIndex++, rawBits, expectedExcelRendering); + if(Double.isNaN(Double.longBitsToDouble(rawBits))) { + _replacementNaNs.add(new Long(rawBits)); + } + } + + public long[] getReplacementNaNs() { + int nRepls = _replacementNaNs.size(); + long[] result = new long[nRepls]; + for (int i = 0; i < nRepls; i++) { + result[i] = _replacementNaNs.get(i).longValue(); + } + return result; + } + + } + /** 0x7ff8000000000000 encoded in little endian order */ + private static final byte[] JAVA_NAN_BYTES = HexRead.readFromString("00 00 00 00 00 00 F8 7F"); + + private static void writeHeaderCell(HSSFRow row, int i, String text, HSSFCellStyle style) { + HSSFCell cell = row.createCell(i); + cell.setCellValue(new HSSFRichTextString(text)); + cell.setCellStyle(style); + } + static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) { + sheet.setColumnWidth(0, 3000); + sheet.setColumnWidth(1, 6000); + sheet.setColumnWidth(2, 6000); + sheet.setColumnWidth(3, 6000); + sheet.setColumnWidth(4, 6000); + sheet.setColumnWidth(5, 1600); + sheet.setColumnWidth(6, 20000); + HSSFRow row = sheet.createRow(0); + HSSFCellStyle style = wb.createCellStyle(); + HSSFFont font = wb.createFont(); + font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + style.setFont(font); + writeHeaderCell(row, 0, "Value", style); + writeHeaderCell(row, 1, "Raw Long Bits", style); + writeHeaderCell(row, 2, "JDK Double Rendering", style); + writeHeaderCell(row, 3, "Actual Rendering", style); + writeHeaderCell(row, 4, "Expected Rendering", style); + writeHeaderCell(row, 5, "Match", style); + writeHeaderCell(row, 6, "Java Metadata", style); + } + static void writeDataRow(HSSFSheet sheet, int rowIx, long rawLongBits, String expectedExcelRendering) { + double d = Double.longBitsToDouble(rawLongBits); + HSSFRow row = sheet.createRow(rowIx); + + int rowNum = rowIx + 1; + String cel0ref = "A" + rowNum; + String rawBitsText = formatLongAsHex(rawLongBits); + String jmExpr = "'ec(" + rawBitsText + ", ''\" & C" + rowNum + " & \"'', ''\" & D" + rowNum + " & \"''),'"; + + // The 'Match' column will contain 'OK' if the metadata (from NumberToTextConversionExamples) + // matches Excel's rendering. + String matchExpr = "if(D" + rowNum + "=E" + rowNum + ", \"OK\", \"ERROR\")"; + + row.createCell(0).setCellValue(d); + row.createCell(1).setCellValue(new HSSFRichTextString(rawBitsText)); + row.createCell(2).setCellValue(new HSSFRichTextString(Double.toString(d))); + row.createCell(3).setCellFormula("\"\" & " + cel0ref); + row.createCell(4).setCellValue(new HSSFRichTextString(expectedExcelRendering)); + row.createCell(5).setCellFormula(matchExpr); + row.createCell(6).setCellFormula(jmExpr.replaceAll("'", "\"")); + + if (false) { + // for observing arithmetic near numeric range boundaries + row.createCell(7).setCellFormula(cel0ref + " * 1.0001"); + row.createCell(8).setCellFormula(cel0ref + " / 1.0001"); + } + } + + private static String formatLongAsHex(long l) { + StringBuilder sb = new StringBuilder(20); + sb.append(HexDump.longToHex(l)).append('L'); + return sb.toString(); + } + + public static void main(String[] args) { + writeJavaDoc(); + + HSSFWorkbook wb = new HSSFWorkbook(); + SheetWriter sw = new SheetWriter(wb); + + ExampleConversion[] exampleValues = NumberToTextConversionExamples.getExampleConversions(); + for (int i = 0; i < exampleValues.length; i++) { + ExampleConversion example = exampleValues[i]; + sw.addTestRow(example.getRawDoubleBits(), example.getExcelRendering()); + } + + ByteArrayOutputStream baos = new ByteArrayOutputStream(); + try { + wb.write(baos); + } catch (IOException e) { + throw new RuntimeException(e); + } + byte[] fileContent = baos.toByteArray(); + replaceNaNs(fileContent, sw.getReplacementNaNs()); + + + File outputFile = new File("ExcelNumberRendering.xls"); + + try { + FileOutputStream os = new FileOutputStream(outputFile); + os.write(fileContent); + os.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + System.out.println("Finished writing '" + outputFile.getAbsolutePath() + "'"); + } + + public static void writeJavaDoc() { + + ExampleConversion[] exampleConversions = NumberToTextConversionExamples.getExampleConversions(); + for (int i = 0; i < exampleConversions.length; i++) { + ExampleConversion ec = exampleConversions[i]; + String line = " * " + + formatLongAsHex(ec.getRawDoubleBits()) + + "" + Double.toString(ec.getDoubleValue()) + + "" + ec.getExcelRendering() + ""; + + System.out.println(line); + } + } + + + + private static void replaceNaNs(byte[] fileContent, long[] replacementNaNs) { + int countFound = 0; + for(int i=0; i> 56); + bb[i+6] = (byte) (val >> 48); + bb[i+5] = (byte) (val >> 40); + bb[i+4] = (byte) (val >> 32); + bb[i+3] = (byte) (val >> 24); + bb[i+2] = (byte) (val >> 16); + bb[i+1] = (byte) (val >> 8); + bb[i+0] = (byte) (val >> 0); + if (false) { + String newVal = interpretLong(bb, i); + System.out.println("changed offset " + i + " from " + oldVal + " to " + newVal); + } + + } + + private static String interpretLong(byte[] fileContent, int offset) { + InputStream is = new ByteArrayInputStream(fileContent, offset, 8); + long l; + try { + l = new DataInputStream(is).readLong(); + } catch (IOException e) { + throw new RuntimeException(e); + } + return "0x" + Long.toHexString(l).toUpperCase(); + } + + private static boolean isNaNBytes(byte[] fileContent, int offset) { + if(offset + JAVA_NAN_BYTES.length > fileContent.length) { + return false; + } + // excel NaN bits: 0xFFFF0420003C0000L + // java NaN bits :0x7ff8000000000000L + return areArraySectionsEqual(fileContent, offset, JAVA_NAN_BYTES); + } + private static boolean areArraySectionsEqual(byte[] bb, int off, byte[] section) { + for (int i=section.length-1; i>=0; i--) { + if (bb[off+i] != section[i]){ + return false; + } + } + return true; + } +} diff --git a/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java b/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java new file mode 100644 index 0000000000..59cc5605be --- /dev/null +++ b/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java @@ -0,0 +1,183 @@ +/* ==================================================================== + 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; +/** + * Contains specific examples of double values and their rendering in Excel. + * + * @author Josh Micich + */ +final class NumberToTextConversionExamples { + + private NumberToTextConversionExamples() { + // no instances of this class + } + + public static final class ExampleConversion { + private final String _javaRendering; + private final String _excelRendering; + private final double _doubleValue; + private final long _rawDoubleBits; + + ExampleConversion(long rawDoubleBits, String javaRendering, String excelRendering) { + double d = Double.longBitsToDouble(rawDoubleBits); + if ("NaN".equals(javaRendering)) { + if (!Double.isNaN(d)) { + throw new IllegalArgumentException("value must be NaN"); + } + } else { + if (Double.isNaN(d)) { + throw new IllegalArgumentException("value must not be NaN"); + } + // just to be dead sure test conversion in java both ways + boolean javaToStringOk = javaRendering.equals(Double.toString(d)); + boolean javaParseOk = Double.parseDouble(javaRendering) == d; + if(!javaToStringOk || !javaParseOk) { + String msgA = "Specified rawDoubleBits " + doubleToHexString(d) + " encodes to double '" + d + "'."; + String msgB = "Specified javaRendering '" + javaRendering+ "' parses as double with rawDoubleBits " + + doubleToHexString(Double.parseDouble(javaRendering)); + System.err.println(msgA); + System.err.println(msgB); + + throw new RuntimeException(msgA + msgB); + } + } + _rawDoubleBits = rawDoubleBits; + _javaRendering = javaRendering; + _excelRendering = excelRendering; + _doubleValue = d; + } + private static String doubleToHexString(double d) { + return "0x" + Long.toHexString(Double.doubleToLongBits(d)).toUpperCase() + "L"; + } + public String getJavaRendering() { + return _javaRendering; + } + public String getExcelRendering() { + return _excelRendering; + } + public double getDoubleValue() { + return _doubleValue; + } + public boolean isNaN() { + return Double.isNaN(_doubleValue); + } + public long getRawDoubleBits() { + return _rawDoubleBits; + } + } + + + private static final ExampleConversion[] examples = { + // basic numbers + ec(0x0000000000000000L, "0.0", "0"), + ec(0x3FF0000000000000L, "1.0", "1"), + ec(0x3FF00068DB8BAC71L, "1.0001", "1.0001"), + ec(0x4087A00000000000L, "756.0", "756"), + ec(0x401E3D70A3D70A3DL, "7.56", "7.56"), + + ec(0x405EDD3C07FB4C99L, "123.45678901234568", "123.456789012346"), + ec(0x4132D687E3DF2180L, "1234567.8901234567", "1234567.89012346"), + + // small numbers + ec(0x3EE9E409302678BAL, "1.2345678901234568E-5", "1.23456789012346E-05"), + ec(0x3F202E85BE180B74L, "1.2345678901234567E-4", "0.000123456789012346"), + ec(0x3F543A272D9E0E51L, "0.0012345678901234567", "0.00123456789012346"), + ec(0x3F8948B0F90591E6L, "0.012345678901234568", "0.0123456789012346"), + + ec(0x3EE9E409301B5A02L, "1.23456789E-5", "0.0000123456789"), + + ec(0x3E6E7D05BDABDE50L, "5.6789012345E-8", "0.000000056789012345"), + ec(0x3E6E7D05BDAD407EL, "5.67890123456E-8", "5.67890123456E-08"), + ec(0x3E6E7D06029F18BEL, "5.678902E-8", "0.00000005678902"), + + ec(0x2BCB5733CB32AE6EL, "9.999999999999123E-98", "9.99999999999912E-98"), + ec(0x2B617F7D4ED8C59EL, "1.0000000000001235E-99", "1.0000000000001E-99"), + ec(0x0036319916D67853L, "1.2345678901234578E-307", "1.2345678901235E-307"), + + ec(0x359DEE7A4AD4B81FL, "2.0E-50", "2E-50"), + + // large numbers + ec(0x41678C29DCD6E9E0L, "1.2345678901234567E7", "12345678.9012346"), + ec(0x42A674E79C5FE523L, "1.2345678901234568E13", "12345678901234.6"), + ec(0x42DC12218377DE6BL, "1.2345678901234567E14", "123456789012346"), + ec(0x43118B54F22AEB03L, "1.2345678901234568E15", "1234567890123460"), + ec(0x43E56A95319D63E1L, "1.2345678901234567E19", "12345678901234600000"), + ec(0x441AC53A7E04BCDAL, "1.2345678901234568E20", "1.23456789012346E+20"), + ec(0xC3E56A95319D63E1L, "-1.2345678901234567E19", "-12345678901234600000"), + ec(0xC41AC53A7E04BCDAL, "-1.2345678901234568E20", "-1.23456789012346E+20"), + + ec(0x54820FE0BA17F46DL, "1.2345678901234577E99", "1.2345678901235E+99"), + ec(0x54B693D8E89DF188L, "1.2345678901234576E100", "1.2345678901235E+100"), + + ec(0x4A611B0EC57E649AL, "2.0E50", "2E+50"), + + // range extremities + ec(0x7FEFFFFFFFFFFFFFL, "1.7976931348623157E308", "1.7976931348623E+308"), + ec(0x0010000000000000L, "2.2250738585072014E-308", "2.2250738585072E-308"), + ec(0x000FFFFFFFFFFFFFL, "2.225073858507201E-308", "0"), + ec(0x0000000000000001L, "4.9E-324", "0"), + + // infinity + ec(0x7FF0000000000000L, "Infinity", "1.7976931348623E+308"), + ec(0xFFF0000000000000L, "-Infinity", "1.7976931348623E+308"), + + // shortening due to rounding + ec(0x441AC7A08EAD02F2L, "1.234999999999999E20", "1.235E+20"), + ec(0x40FE26BFFFFFFFF9L, "123499.9999999999", "123500"), + ec(0x3E4A857BFB2F2809L, "1.234999999999999E-8", "0.00000001235"), + ec(0x3BCD291DEF868C89L, "1.234999999999999E-20", "1.235E-20"), + + // carry up due to rounding + // For clarity these tests choose values that don't round in java, + // but will round in excel. In some cases there is almost no difference + // between excel and java (e.g. 9.9..9E-8) + ec(0x444B1AE4D6E2EF4FL, "9.999999999999999E20", "1E+21"), + ec(0x412E847FFFFFFFFFL, "999999.9999999999", "1000000"), + ec(0x3E45798EE2308C39L, "9.999999999999999E-9", "0.00000001"), + ec(0x3C32725DD1D243ABL, "9.999999999999999E-19", "0.000000000000000001"), + ec(0x3BFD83C94FB6D2ABL, "9.999999999999999E-20", "1E-19"), + + ec(0xC44B1AE4D6E2EF4FL, "-9.999999999999999E20", "-1E+21"), + ec(0xC12E847FFFFFFFFFL, "-999999.9999999999", "-1000000"), + ec(0xBE45798EE2308C39L, "-9.999999999999999E-9", "-0.00000001"), + ec(0xBC32725DD1D243ABL, "-9.999999999999999E-19", "-0.000000000000000001"), + ec(0xBBFD83C94FB6D2ABL, "-9.999999999999999E-20", "-1E-19"), + + + // NaNs + // Currently these test cases are not critical, since other limitations prevent any variety in + // or control of the bit patterns used to encode NaNs in evaluations. + ec(0xFFFF0420003C0000L, "NaN", "3.484840871308E+308"), + ec(0x7FF8000000000000L, "NaN", "2.6965397022935E+308"), + ec(0x7FFF0420003C0000L, "NaN", "3.484840871308E+308"), + ec(0xFFF8000000000000L, "NaN", "2.6965397022935E+308"), + ec(0xFFFF0AAAAAAAAAAAL, "NaN", "3.4877119413344E+308"), + ec(0x7FF80AAAAAAAAAAAL, "NaN", "2.7012211948322E+308"), + ec(0xFFFFFFFFFFFFFFFFL, "NaN", "3.5953862697246E+308"), + ec(0x7FFFFFFFFFFFFFFFL, "NaN", "3.5953862697246E+308"), + ec(0xFFF7FFFFFFFFFFFFL, "NaN", "2.6965397022935E+308"), + }; + + private static ExampleConversion ec(long rawDoubleBits, String javaRendering, String excelRendering) { + return new ExampleConversion(rawDoubleBits, javaRendering, excelRendering); + } + + public static ExampleConversion[] getExampleConversions() { + return examples.clone(); + } +} diff --git a/src/testcases/org/apache/poi/ss/util/TestNumberToTextConverter.java b/src/testcases/org/apache/poi/ss/util/TestNumberToTextConverter.java new file mode 100644 index 0000000000..9fa5aa1247 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/util/TestNumberToTextConverter.java @@ -0,0 +1,128 @@ +/* ==================================================================== + 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 junit.framework.AssertionFailedError; +import junit.framework.ComparisonFailure; +import junit.framework.TestCase; + +import org.apache.poi.hssf.biff2.record.BIFF2RecordFactory.NumberRecord; +import org.apache.poi.hssf.record.FormulaRecord; +import org.apache.poi.hssf.record.constant.ConstantValueParser; +import org.apache.poi.hssf.record.formula.NumberPtg; +import org.apache.poi.ss.util.NumberToTextConverter; +import org.apache.poi.ss.util.NumberToTextConversionExamples.ExampleConversion; +/** + * Tests for {@link NumberToTextConverter} + * + * @author Josh Micich + */ +public final class TestNumberToTextConverter extends TestCase { + + + /** + * Confirms that ExcelNumberToTextConverter.toText(d) produces the right results. + * As part of preparing this test class, the ExampleConversion instances should be set + * up to contain the rendering as produced by Excel. + */ + public void testAll() { + int failureCount = 0; + + ExampleConversion[] examples = NumberToTextConversionExamples.getExampleConversions(); + + for (int i = 0; i < examples.length; i++) { + ExampleConversion example = examples[i]; + try { + if (example.isNaN()) { + confirmNaN(example.getRawDoubleBits(), example.getExcelRendering()); + continue; + } + String actual = NumberToTextConverter.toText(example.getDoubleValue()); + if (!example.getExcelRendering().equals(actual)) { + failureCount++; + String msg = "Error rendering for examples[" + i + "] " + + formatExample(example) + " " + + " bad-result='" + actual + "' " + + new ComparisonFailure(null, example.getExcelRendering(), actual).getMessage(); + System.err.println(msg); + continue; + } + } catch (RuntimeException e) { + failureCount++; + System.err.println("Error in excel rendering for examples[" + i + "] " + + formatExample(example) + "':" + e.getMessage()); + e.printStackTrace(); + } + } + if (failureCount > 0) { + throw new AssertionFailedError(failureCount + + " error(s) in excel number to text conversion (see std-err)"); + } + } + + private static String formatExample(ExampleConversion example) { + String hexLong = Long.toHexString(example.getRawDoubleBits()).toUpperCase(); + String longRep = "0x" + "0000000000000000".substring(hexLong.length()) + hexLong+ "L"; + return "ec(" + longRep + ", \"" + example.getJavaRendering() + "\", \"" + example.getExcelRendering() + "\")"; + } + + /** + * Excel's abnormal rendering of NaNs is both difficult to test and even reproduce in java. In + * general, Excel does not attempt to use raw NaN in the IEEE sense. In {@link FormulaRecord}s, + * Excel uses the NaN bit pattern to flag non-numeric (text, boolean, error) cached results. + * If the formula result actually evaluates to raw NaN, Excel transforms it to #NUM!. + * In other places (e.g. {@link NumberRecord}, {@link NumberPtg}, array items (via {@link + * ConstantValueParser}), there seems to be no special NaN translation scheme. If a NaN bit + * pattern is somehow encoded into any of these places Excel actually attempts to render the + * values as a plain number. That is the unusual functionality that this method is testing.

+ * + * There are multiple encodings (bit patterns) for NaN, and CPUs and applications can convert + * to a preferred NaN encoding (Java prefers 0x7FF8000000000000L). Besides the + * special encoding in {@link FormulaRecord.SpecialCachedValue}, it is not known how/whether + * Excel attempts to encode NaN values. + * + * Observed NaN behaviour on HotSpot/Windows: + * Double.longBitsToDouble() will set one bit 51 (the NaN signaling flag) if it isn't + * already. Double.doubleToLongBits() will return a double with bit pattern + * 0x7FF8000000000000L for any NaN bit pattern supplied.
+ * Differences are likely to be observed with other architectures.

+ * + *

+ * The few test case examples calling this method represent functionality which may not be + * important for POI to support. + */ + private void confirmNaN(long l, String excelRep) { + double d = Double.longBitsToDouble(l); + assertEquals("NaN", Double.toString(d)); + + String strExcel = NumberToTextConverter.rawDoubleBitsToText(l); + + assertEquals(excelRep, strExcel); + } + + public void testSimpleRendering_bug56156() { + double dResult = 0.05+0.01; // values chosen to produce rounding anomaly + String actualText = NumberToTextConverter.toText(dResult); + String jdkText = Double.toString(dResult); + if (jdkText.equals(actualText)) { + // "0.060000000000000005" + throw new AssertionFailedError("Should not use default JDK IEEE double rendering"); + } + assertEquals("0.06", actualText); + } +} -- 2.39.5