src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java

   1 /* ====================================================================
   2    Licensed to the Apache Software Foundation (ASF) under one or more
   3    contributor license agreements.  See the NOTICE file distributed with
   4    this work for additional information regarding copyright ownership.
   5    The ASF licenses this file to You under the Apache License, Version 2.0
   6    (the "License"); you may not use this file except in compliance with
   7    the License.  You may obtain a copy of the License at
   8
   9        http://www.apache.org/licenses/LICENSE-2.0
  10
  11    Unless required by applicable law or agreed to in writing, software
  12    distributed under the License is distributed on an "AS IS" BASIS,
  13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14    See the License for the specific language governing permissions and
  15    limitations under the License.
  16 ==================================================================== */
  17
  18 package org.apache.poi.hssf.record.formula.functions;
  19
  20 import junit.framework.TestCase;
  21
  22 import org.apache.poi.hssf.record.formula.AreaPtg;
  23 import org.apache.poi.hssf.record.formula.eval.Area2DEval;
  24 import org.apache.poi.hssf.record.formula.eval.AreaEval;
  25 import org.apache.poi.hssf.record.formula.eval.BoolEval;
  26 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
  27 import org.apache.poi.hssf.record.formula.eval.Eval;
  28 import org.apache.poi.hssf.record.formula.eval.NumberEval;
  29 import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
  30 import org.apache.poi.hssf.record.formula.eval.StringEval;
  31 import org.apache.poi.hssf.record.formula.eval.ValueEval;
  32
  33 /**
  34  * Test cases for MATCH()
  35  *
  36  * @author Josh Micich
  37  */
  38 public final class TestMatch extends TestCase {
  39         /** less than or equal to */
  40         private static final NumberEval MATCH_LARGEST_LTE = new NumberEval(1);
  41         private static final NumberEval MATCH_EXACT = new NumberEval(0);
  42         /** greater than or equal to */
  43         private static final NumberEval MATCH_SMALLEST_GTE = new NumberEval(-1);
  44
  45
  46         private static Eval invokeMatch(Eval lookup_value, Eval lookup_array, Eval match_type) {
  47                 Eval[] args = { lookup_value, lookup_array, match_type, };
  48                 return new Match().evaluate(args, -1, (short)-1);
  49         }
  50         private static void confirmInt(int expected, Eval actualEval) {
  51                 if(!(actualEval instanceof NumericValueEval)) {
  52                         fail("Expected numeric result");
  53                 }
  54                 NumericValueEval nve = (NumericValueEval)actualEval;
  55                 assertEquals(expected, nve.getNumberValue(), 0);
  56         }
  57         /**
  58          * Convenience method
  59          * @return <code>new Area2DEval(new AreaPtg(ref), values)</code>
  60          */
  61         private static AreaEval createAreaEval(String ref, ValueEval[] values) {
  62                 return new Area2DEval(new AreaPtg(ref), values);
  63         }
  64
  65         public void testSimpleNumber() {
  66
  67                 ValueEval[] values = {
  68                         new NumberEval(4),
  69                         new NumberEval(5),
  70                         new NumberEval(10),
  71                         new NumberEval(10),
  72                         new NumberEval(25),
  73                 };
  74
  75                 AreaEval ae = createAreaEval("A1:A5", values);
  76
  77                 confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE));
  78                 confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_EXACT));
  79                 confirmInt(4, invokeMatch(new NumberEval(10), ae, MATCH_LARGEST_LTE));
  80                 confirmInt(3, invokeMatch(new NumberEval(10), ae, MATCH_EXACT));
  81                 confirmInt(4, invokeMatch(new NumberEval(20), ae, MATCH_LARGEST_LTE));
  82                 assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(20), ae, MATCH_EXACT));
  83         }
  84
  85         public void testReversedNumber() {
  86
  87                 ValueEval[] values = {
  88                         new NumberEval(25),
  89                         new NumberEval(10),
  90                         new NumberEval(10),
  91                         new NumberEval(10),
  92                         new NumberEval(4),
  93                 };
  94
  95                 AreaEval ae = createAreaEval("A1:A5", values);
  96
  97                 confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_SMALLEST_GTE));
  98                 confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_EXACT));
  99                 confirmInt(4, invokeMatch(new NumberEval(9), ae, MATCH_SMALLEST_GTE));
 100                 confirmInt(1, invokeMatch(new NumberEval(20), ae, MATCH_SMALLEST_GTE));
 101                 assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(20), ae, MATCH_EXACT));
 102                 assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(26), ae, MATCH_SMALLEST_GTE));
 103         }
 104
 105         public void testSimpleString() {
 106
 107                 ValueEval[] values = {
 108                         new StringEval("Albert"),
 109                         new StringEval("Charles"),
 110                         new StringEval("Ed"),
 111                         new StringEval("Greg"),
 112                         new StringEval("Ian"),
 113                 };
 114
 115                 AreaEval ae = createAreaEval("A1:A5", values);
 116
 117                 // Note String comparisons are case insensitive
 118                 confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_LARGEST_LTE));
 119                 confirmInt(3, invokeMatch(new StringEval("eD"), ae, MATCH_LARGEST_LTE));
 120                 confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_EXACT));
 121                 confirmInt(3, invokeMatch(new StringEval("ed"), ae, MATCH_EXACT));
 122                 confirmInt(4, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE));
 123                 assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT));
 124         }
 125
 126         public void testSimpleBoolean() {
 127
 128                 ValueEval[] values = {
 129                                 BoolEval.FALSE,
 130                                 BoolEval.FALSE,
 131                                 BoolEval.TRUE,
 132                                 BoolEval.TRUE,
 133                 };
 134
 135                 AreaEval ae = createAreaEval("A1:A4", values);
 136
 137                 // Note String comparisons are case insensitive
 138                 confirmInt(2, invokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE));
 139                 confirmInt(1, invokeMatch(BoolEval.FALSE, ae, MATCH_EXACT));
 140                 confirmInt(4, invokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE));
 141                 confirmInt(3, invokeMatch(BoolEval.TRUE, ae, MATCH_EXACT));
 142         }
 143
 144         public void testHeterogeneous() {
 145
 146                 ValueEval[] values = {
 147                                 new NumberEval(4),
 148                                 BoolEval.FALSE,
 149                                 new NumberEval(5),
 150                                 new StringEval("Albert"),
 151                                 BoolEval.FALSE,
 152                                 BoolEval.TRUE,
 153                                 new NumberEval(10),
 154                                 new StringEval("Charles"),
 155                                 new StringEval("Ed"),
 156                                 new NumberEval(10),
 157                                 new NumberEval(25),
 158                                 BoolEval.TRUE,
 159                                 new StringEval("Ed"),
 160                 };
 161
 162                 AreaEval ae = createAreaEval("A1:A13", values);
 163
 164                 assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Aaron"), ae, MATCH_LARGEST_LTE));
 165
 166                 confirmInt(5, invokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE));
 167                 confirmInt(2, invokeMatch(BoolEval.FALSE, ae, MATCH_EXACT));
 168                 confirmInt(3, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE));
 169                 confirmInt(3, invokeMatch(new NumberEval(5), ae, MATCH_EXACT));
 170
 171                 confirmInt(8, invokeMatch(new StringEval("CHARLES"), ae, MATCH_EXACT));
 172
 173                 confirmInt(4, invokeMatch(new StringEval("Ben"), ae, MATCH_LARGEST_LTE));
 174
 175                 confirmInt(13, invokeMatch(new StringEval("ED"), ae, MATCH_LARGEST_LTE));
 176                 confirmInt(9, invokeMatch(new StringEval("ED"), ae, MATCH_EXACT));
 177
 178                 confirmInt(13, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE));
 179                 assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT));
 180
 181                 confirmInt(11, invokeMatch(new NumberEval(30), ae, MATCH_LARGEST_LTE));
 182                 confirmInt(12, invokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE));
 183         }
 184
 185
 186         /**
 187          * Ensures that the match_type argument can be an <tt>AreaEval</tt>.<br/>
 188          * Bugzilla 44421
 189          */
 190         public void testMatchArgTypeArea() {
 191
 192                 ValueEval[] values = {
 193                         new NumberEval(4),
 194                         new NumberEval(5),
 195                         new NumberEval(10),
 196                         new NumberEval(10),
 197                         new NumberEval(25),
 198                 };
 199
 200                 AreaEval ae = createAreaEval("A1:A5", values);
 201
 202                 AreaEval matchAE = createAreaEval("C1:C1", new ValueEval[] { MATCH_LARGEST_LTE, });
 203
 204                 try {
 205                         confirmInt(4, invokeMatch(new NumberEval(10), ae, matchAE));
 206                 } catch (RuntimeException e) {
 207                         if(e.getMessage().startsWith("Unexpected match_type type")) {
 208                                 // identified bug 44421
 209                                 fail(e.getMessage());
 210                         }
 211                         // some other error ??
 212                         throw e;
 213                 }
 214         }
 215 }