From 919b35e9e12fde62244d03aea0ab1c9f531d18be Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 15 Feb 2008 12:13:25 +0000 Subject: [PATCH] Fix from Josh from bug #44421 - Update Match function to properly support Area references git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@628035 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../hssf/record/formula/functions/Match.java | 3 +- .../record/formula/functions/TestMatch.java | 55 +++++++++++++++---- 4 files changed, 46 insertions(+), 14 deletions(-) diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index f7e9714e5b..f951a78176 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 44421 - Update Match function to properly support Area references 44417 - Improved handling of references for the need to quote the sheet name for some formulas, but not when fetching a sheet by name 44413 - Fix for circular references in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself 44403 - Fix for Mid function handling its arguments wrong diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 22eca6da81..2202a0fc87 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 44421 - Update Match function to properly support Area references 44417 - Improved handling of references for the need to quote the sheet name for some formulas, but not when fetching a sheet by name 44413 - Fix for circular references in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself 44403 - Fix for Mid function handling its arguments wrong diff --git a/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java b/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java index b2b252c4f0..a3ad49b194 100644 --- a/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java +++ b/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java @@ -26,7 +26,6 @@ import org.apache.poi.hssf.record.formula.eval.NumericValueEval; import org.apache.poi.hssf.record.formula.eval.RefEval; import org.apache.poi.hssf.record.formula.eval.StringEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.util.AreaReference; /** * Implementation for the MATCH() Excel function.

@@ -188,7 +187,7 @@ public final class Match implements Function { private static double evaluateMatchTypeArg(Eval arg, int srcCellRow, short srcCellCol) throws EvalEx { Eval match_type = arg; - if(arg instanceof AreaReference) { + if(arg instanceof AreaEval) { AreaEval ae = (AreaEval) arg; // an area ref can work as a scalar value if it is 1x1 if(ae.isColumn() && ae.isRow()) { diff --git a/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java b/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java index 7ad528d61f..d275e5f333 100755 --- a/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java +++ b/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java @@ -21,6 +21,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.record.formula.AreaPtg; import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.AreaEval; import org.apache.poi.hssf.record.formula.eval.BoolEval; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; @@ -53,6 +54,13 @@ public final class TestMatch extends TestCase { NumericValueEval nve = (NumericValueEval)actualEval; assertEquals(expected, nve.getNumberValue(), 0); } + /** + * Convenience method + * @return new Area2DEval(new AreaPtg(ref), values) + */ + private static AreaEval createAreaEval(String ref, ValueEval[] values) { + return new Area2DEval(new AreaPtg(ref), values); + } public void testSimpleNumber() { @@ -64,9 +72,7 @@ public final class TestMatch extends TestCase { new NumberEval(25), }; - AreaPtg areaPtg = new AreaPtg("A1:A5"); - Area2DEval ae = new Area2DEval(areaPtg, values); - + AreaEval ae = createAreaEval("A1:A5", values); confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE)); confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_EXACT)); @@ -86,9 +92,7 @@ public final class TestMatch extends TestCase { new NumberEval(4), }; - AreaPtg areaPtg = new AreaPtg("A1:A5"); - Area2DEval ae = new Area2DEval(areaPtg, values); - + AreaEval ae = createAreaEval("A1:A5", values); confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_SMALLEST_GTE)); confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_EXACT)); @@ -108,8 +112,7 @@ public final class TestMatch extends TestCase { new StringEval("Ian"), }; - AreaPtg areaPtg = new AreaPtg("A1:A5"); - Area2DEval ae = new Area2DEval(areaPtg, values); + AreaEval ae = createAreaEval("A1:A5", values); // Note String comparisons are case insensitive confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_LARGEST_LTE)); @@ -129,8 +132,7 @@ public final class TestMatch extends TestCase { BoolEval.TRUE, }; - AreaPtg areaPtg = new AreaPtg("A1:A4"); - Area2DEval ae = new Area2DEval(areaPtg, values); + AreaEval ae = createAreaEval("A1:A4", values); // Note String comparisons are case insensitive confirmInt(2, invokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE)); @@ -157,8 +159,7 @@ public final class TestMatch extends TestCase { new StringEval("Ed"), }; - AreaPtg areaPtg = new AreaPtg("A1:A13"); - Area2DEval ae = new Area2DEval(areaPtg, values); + AreaEval ae = createAreaEval("A1:A13", values); assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Aaron"), ae, MATCH_LARGEST_LTE)); @@ -181,4 +182,34 @@ public final class TestMatch extends TestCase { confirmInt(12, invokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE)); } + + /** + * Ensures that the match_type argument can be an AreaEval.
+ * Bugzilla 44421 + */ + public void testMatchArgTypeArea() { + + ValueEval[] values = { + new NumberEval(4), + new NumberEval(5), + new NumberEval(10), + new NumberEval(10), + new NumberEval(25), + }; + + AreaEval ae = createAreaEval("A1:A5", values); + + AreaEval matchAE = createAreaEval("C1:C1", new ValueEval[] { MATCH_LARGEST_LTE, }); + + try { + confirmInt(4, invokeMatch(new NumberEval(10), ae, matchAE)); + } catch (RuntimeException e) { + if(e.getMessage().startsWith("Unexpected match_type type")) { + // identified bug 44421 + fail(e.getMessage()); + } + // some other error ?? + throw e; + } + } } -- 2.39.5