View | Details | Raw Unified | Return to bug 44450
Collapse All | Expand All

(-)src/documentation/content/xdocs/changes.xml (+1 lines)
Lines 36-41 Link Here
36
36
37
		<!-- Don't forget to update status.xml too! -->
37
		<!-- Don't forget to update status.xml too! -->
38
        <release version="3.1-beta1" date="2008-??-??">
38
        <release version="3.1-beta1" date="2008-??-??">
39
           <action dev="POI-DEVELOPERS" type="add">44450 - Support for Lookup, HLookup and VLookup functions</action>
39
           <action dev="POI-DEVELOPERS" type="fix">44449 - Avoid getting confused when two sheets have shared formulas for the same areas, and when the shared formula is set incorrectly</action>
40
           <action dev="POI-DEVELOPERS" type="fix">44449 - Avoid getting confused when two sheets have shared formulas for the same areas, and when the shared formula is set incorrectly</action>
40
           <action dev="POI-DEVELOPERS" type="fix">44366 - InputStreams passed to POIFSFileSystem are now automatically closed. A warning is generated for people who might've relied on them not being closed before, and a wrapper to restore the old behaviour is supplied</action>
41
           <action dev="POI-DEVELOPERS" type="fix">44366 - InputStreams passed to POIFSFileSystem are now automatically closed. A warning is generated for people who might've relied on them not being closed before, and a wrapper to restore the old behaviour is supplied</action>
41
           <action dev="POI-DEVELOPERS" type="add">44371 - Support for the Offset function</action>
42
           <action dev="POI-DEVELOPERS" type="add">44371 - Support for the Offset function</action>
(-)src/documentation/content/xdocs/status.xml (+1 lines)
Lines 33-38 Link Here
33
	<!-- Don't forget to update changes.xml too! -->
33
	<!-- Don't forget to update changes.xml too! -->
34
    <changes>
34
    <changes>
35
        <release version="3.1-beta1" date="2008-??-??">
35
        <release version="3.1-beta1" date="2008-??-??">
36
           <action dev="POI-DEVELOPERS" type="add">44450 - Support for Lookup, HLookup and VLookup functions</action>
36
           <action dev="POI-DEVELOPERS" type="fix">44449 - Avoid getting confused when two sheets have shared formulas for the same areas, and when the shared formula is set incorrectly</action>
37
           <action dev="POI-DEVELOPERS" type="fix">44449 - Avoid getting confused when two sheets have shared formulas for the same areas, and when the shared formula is set incorrectly</action>
37
           <action dev="POI-DEVELOPERS" type="fix">44366 - InputStreams passed to POIFSFileSystem are now automatically closed. A warning is generated for people who might've relied on them not being closed before, and a wrapper to restore the old behaviour is supplied</action>
38
           <action dev="POI-DEVELOPERS" type="fix">44366 - InputStreams passed to POIFSFileSystem are now automatically closed. A warning is generated for people who might've relied on them not being closed before, and a wrapper to restore the old behaviour is supplied</action>
38
           <action dev="POI-DEVELOPERS" type="add">44371 - Support for the Offset function</action>
39
           <action dev="POI-DEVELOPERS" type="add">44371 - Support for the Offset function</action>
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java (-1 / +1 lines)
Lines 28-34 Link Here
28
    /**
28
    /**
29
     * Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
29
     * Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
30
     */
30
     */
31
    private static final class ErrorCode {
31
    public static final class ErrorCode {
32
        /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
32
        /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
33
        public static final int NULL = 0x00;
33
        public static final int NULL = 0x00;
34
        /** <b>#DIV/0!</b> - Division by zero */
34
        /** <b>#DIV/0!</b> - Division by zero */
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/LookupUtils.java (+530 lines)
Line 0 Link Here
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 org.apache.poi.hssf.record.formula.AreaPtg;
21
import org.apache.poi.hssf.record.formula.eval.Area2DEval;
22
import org.apache.poi.hssf.record.formula.eval.AreaEval;
23
import org.apache.poi.hssf.record.formula.eval.BlankEval;
24
import org.apache.poi.hssf.record.formula.eval.BoolEval;
25
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
26
import org.apache.poi.hssf.record.formula.eval.Eval;
27
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
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.OperandResolver;
31
import org.apache.poi.hssf.record.formula.eval.RefEval;
32
import org.apache.poi.hssf.record.formula.eval.StringEval;
33
import org.apache.poi.hssf.record.formula.eval.ValueEval;
34
35
/**
36
 * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
37
 * 
38
 * @author Josh Micich
39
 */
40
final class LookupUtils {
41
	
42
	/**
43
	 * Represents a single row or column within an <tt>AreaEval</tt>.
44
	 */
45
	public interface ValueVector {
46
		ValueEval getItem(int index);
47
		int getSize();
48
	}
49
	/**
50
	 * Enumeration to support <b>4</b> valued comparison results.<p/>
51
	 * Excel lookup functions have complex behaviour in the case where the lookup array has mixed 
52
	 * types, and/or is unordered.  Contrary to suggestions in some Excel documentation, there
53
	 * does not appear to be a universal ordering across types.  The binary search algorithm used
54
	 * changes behaviour when the evaluated 'mid' value has a different type to the lookup value.<p/>
55
	 * 
56
	 * A simple int might have done the same job, but there is risk in confusion with the well 
57
	 * known <tt>Comparable.compareTo()</tt> and <tt>Comparator.compare()</tt> which both use
58
	 * a ubiquitous 3 value result encoding.
59
	 */
60
	public static final class CompareResult {
61
		private final boolean _isTypeMismatch;
62
		private final boolean _isLessThan;
63
		private final boolean _isEqual;
64
		private final boolean _isGreaterThan;
65
66
		private CompareResult(boolean isTypeMismatch, int simpleCompareResult) {
67
			if(isTypeMismatch) {
68
				_isTypeMismatch = true;
69
				_isLessThan = false;
70
				_isEqual = false;
71
				_isGreaterThan = false;
72
			} else {
73
				_isTypeMismatch = false;
74
				_isLessThan = simpleCompareResult < 0;
75
				_isEqual = simpleCompareResult == 0;
76
				_isGreaterThan = simpleCompareResult > 0;
77
			}
78
		}
79
		public static final CompareResult TYPE_MISMATCH = new CompareResult(true, 0);
80
		public static final CompareResult LESS_THAN = new CompareResult(false, -1);
81
		public static final CompareResult EQUAL = new CompareResult(false, 0);
82
		public static final CompareResult GREATER_THAN = new CompareResult(false, +1);
83
		
84
		public static final CompareResult valueOf(int simpleCompareResult) {
85
			if(simpleCompareResult < 0) {
86
				return LESS_THAN;
87
			}
88
			if(simpleCompareResult > 0) {
89
				return GREATER_THAN;
90
			}
91
			return EQUAL;
92
		}
93
		
94
		public boolean isTypeMismatch() {
95
			return _isTypeMismatch;
96
		}
97
		public boolean isLessThan() {
98
			return _isLessThan;
99
		}
100
		public boolean isEqual() {
101
			return _isEqual;
102
		}
103
		public boolean isGreaterThan() {
104
			return _isGreaterThan;
105
		}
106
		public String toString() {
107
	        StringBuffer sb = new StringBuffer(64);
108
	        sb.append(getClass().getName()).append(" [");
109
	        sb.append(formatAsString());
110
	        sb.append("]");
111
	        return sb.toString();
112
		}
113
114
		private String formatAsString() {
115
			if(_isTypeMismatch) {
116
				return "TYPE_MISMATCH";
117
			}
118
			if(_isLessThan) {
119
				return "LESS_THAN";
120
			}
121
			if(_isEqual) {
122
				return "EQUAL";
123
			}
124
			if(_isGreaterThan) {
125
				return "GREATER_THAN";
126
			}
127
			// toString must be reliable
128
			return "??error??";
129
		}
130
	}
131
	
132
	public interface LookupValueComparer {
133
		/**
134
		 * @return one of 4 instances or <tt>CompareResult</tt>: <tt>LESS_THAN</tt>, <tt>EQUAL</tt>, 
135
		 * <tt>GREATER_THAN</tt> or <tt>TYPE_MISMATCH</tt>
136
		 */
137
		CompareResult compareTo(ValueEval other);
138
	}
139
	
140
	private static abstract class LookupValueComparerBase implements LookupValueComparer {
141
		
142
		private final Class _targetClass;
143
		protected LookupValueComparerBase(ValueEval targetValue) {
144
			if(targetValue == null) {
145
				throw new RuntimeException("targetValue cannot be null");
146
			}
147
			_targetClass = targetValue.getClass();
148
		}
149
		public final CompareResult compareTo(ValueEval other) {
150
			if (other == null) {
151
				throw new RuntimeException("compare to value cannot be null");
152
			}
153
			if (_targetClass != other.getClass()) {
154
				return CompareResult.TYPE_MISMATCH;
155
			}
156
			if (_targetClass == StringEval.class) {
157
				
158
			}
159
			return compareSameType(other);
160
		}
161
		public String toString() {
162
	        StringBuffer sb = new StringBuffer(64);
163
	        sb.append(getClass().getName()).append(" [");
164
	        sb.append(getValueAsString());
165
	        sb.append("]");
166
	        return sb.toString();
167
		}
168
		protected abstract CompareResult compareSameType(ValueEval other);
169
		/** used only for debug purposes */
170
		protected abstract String getValueAsString();
171
	}
172
	
173
	private static final class StringLookupComparer extends LookupValueComparerBase {
174
		private String _value;
175
176
		protected StringLookupComparer(StringEval se) {
177
			super(se);
178
			_value = se.getStringValue();
179
		}
180
		protected CompareResult compareSameType(ValueEval other) {
181
			StringEval se = (StringEval) other;
182
			return CompareResult.valueOf(_value.compareToIgnoreCase(se.getStringValue()));
183
		}
184
		protected String getValueAsString() {
185
			return _value;
186
		}
187
	}
188
	private static final class NumberLookupComparer extends LookupValueComparerBase {
189
		private double _value;
190
191
		protected NumberLookupComparer(NumberEval ne) {
192
			super(ne);
193
			_value = ne.getNumberValue();
194
		}
195
		protected CompareResult compareSameType(ValueEval other) {
196
			NumberEval ne = (NumberEval) other;
197
			return CompareResult.valueOf(Double.compare(_value, ne.getNumberValue()));
198
		}
199
		protected String getValueAsString() {
200
			return String.valueOf(_value);
201
		}
202
	}
203
	private static final class BooleanLookupComparer extends LookupValueComparerBase {
204
		private boolean _value;
205
206
		protected BooleanLookupComparer(BoolEval be) {
207
			super(be);
208
			_value = be.getBooleanValue();
209
		}
210
		protected CompareResult compareSameType(ValueEval other) {
211
			BoolEval be = (BoolEval) other;
212
			boolean otherVal = be.getBooleanValue();
213
			if(_value == otherVal) {
214
				return CompareResult.EQUAL;
215
			}
216
			// TRUE > FALSE
217
			if(_value) {
218
				return CompareResult.GREATER_THAN;
219
			}
220
			return CompareResult.LESS_THAN;
221
		}
222
		protected String getValueAsString() {
223
			return String.valueOf(_value);
224
		}
225
	}
226
	
227
	/**
228
	 * Processes the third argument to VLOOKUP, or HLOOKUP (<b>col_index_num</b> 
229
	 * or <b>row_index_num</b> respectively).<br>
230
	 * Sample behaviour:
231
	 *    <table border="0" cellpadding="1" cellspacing="2" summary="Sample behaviour">
232
	 *      <tr><th>Input&nbsp;&nbsp;&nbsp;Return</th><th>Value&nbsp;&nbsp;</th><th>Thrown Error</th></tr>
233
	 *      <tr><td>5</td><td>4</td><td>&nbsp;</td></tr>
234
	 *      <tr><td>2.9</td><td>2</td><td>&nbsp;</td></tr>
235
	 *      <tr><td>"5"</td><td>4</td><td>&nbsp;</td></tr>
236
	 *      <tr><td>"2.18e1"</td><td>21</td><td>&nbsp;</td></tr>
237
	 *      <tr><td>"-$2"</td><td>-3</td><td>*</td></tr>
238
	 *      <tr><td>FALSE</td><td>-1</td><td>*</td></tr>
239
	 *      <tr><td>TRUE</td><td>0</td><td>&nbsp;</td></tr>
240
	 *      <tr><td>"TRUE"</td><td>&nbsp;</td><td>#REF!</td></tr>
241
	 *      <tr><td>"abc"</td><td>&nbsp;</td><td>#REF!</td></tr>
242
	 *      <tr><td>""</td><td>&nbsp;</td><td>#REF!</td></tr>
243
	 *      <tr><td>&lt;blank&gt;</td><td>&nbsp;</td><td>#VALUE!</td></tr>
244
	 *    </table><br/>
245
	 *    
246
	 *  * Note - out of range errors (both too high and too low) are handled by the caller. 
247
	 * @return column or row index as a zero-based value
248
	 * 
249
	 */
250
	public static int resolveRowOrColIndexArg(ValueEval veRowColIndexArg) throws EvaluationException {
251
		if(veRowColIndexArg == null) {
252
			throw new IllegalArgumentException("argument must not be null");
253
		}
254
		if(veRowColIndexArg instanceof BlankEval) {
255
			throw EvaluationException.invalidValue(); 
256
		}
257
		if(veRowColIndexArg instanceof StringEval) {
258
			StringEval se = (StringEval) veRowColIndexArg;
259
			String strVal = se.getStringValue();
260
			Double dVal = OperandResolver.parseDouble(strVal);
261
			if(dVal == null) {
262
				// String does not resolve to a number. Raise #VALUE! error.
263
				throw EvaluationException.invalidRef(); 
264
				// This includes text booleans "TRUE" and "FALSE".  They are not valid.
265
			}
266
			// else - numeric value parses OK
267
		}
268
		// actual BoolEval values get interpreted as FALSE->0 and TRUE->1
269
		return OperandResolver.coerceValueToInt(veRowColIndexArg) - 1;
270
	}
271
	
272
	
273
	
274
	/**
275
	 * The second argument (table_array) should be an area ref, but can actually be a cell ref, in
276
	 * which case it is interpreted as a 1x1 area ref.  Other scalar values cause #VALUE! error.
277
	 */
278
	public static AreaEval resolveTableArrayArg(Eval eval) throws EvaluationException {
279
		if (eval instanceof AreaEval) {
280
			return (AreaEval) eval;
281
		}
282
		
283
		if(eval instanceof RefEval) {
284
			RefEval refEval = (RefEval) eval;
285
			// Make this cell ref look like a 1x1 area ref.
286
			
287
			// It doesn't matter if eval is a 2D or 3D ref, because that detail is never asked of AreaEval.
288
			// This code only requires the value array item. 
289
			// anything would be ok for rowIx and colIx, but may as well get it right.
290
			short rowIx = refEval.getRow();
291
			short colIx = refEval.getColumn();
292
			AreaPtg ap = new AreaPtg(rowIx, rowIx, colIx, colIx, false, false, false, false);
293
			ValueEval value = refEval.getInnerValueEval();
294
			return new Area2DEval(ap, new ValueEval[] { value, });
295
		}
296
		throw EvaluationException.invalidValue();
297
	}
298
	
299
300
	/**
301
	 * Resolves the last (optional) parameter (<b>range_lookup</b>) to the VLOOKUP and HLOOKUP functions. 
302
	 * @param rangeLookupArg
303
	 * @param srcCellRow
304
	 * @param srcCellCol
305
	 * @return
306
	 * @throws EvaluationException
307
	 */
308
	public static boolean resolveRangeLookupArg(Eval rangeLookupArg, int srcCellRow, short srcCellCol) throws EvaluationException {
309
		if(rangeLookupArg == null) {
310
			// range_lookup arg not provided
311
			return true; // default is TRUE
312
		}
313
		ValueEval valEval = OperandResolver.getSingleValue(rangeLookupArg, srcCellRow, srcCellCol);
314
		if(valEval instanceof BlankEval) {
315
			// Tricky:
316
			// fourth arg supplied but evaluates to blank
317
			// this does not get the default value
318
			return false;
319
		}
320
		if(valEval instanceof BoolEval) {
321
			// Happy day flow 
322
			BoolEval boolEval = (BoolEval) valEval;
323
			return boolEval.getBooleanValue();
324
		}
325
326
		if (valEval instanceof StringEval) {
327
			String stringValue = ((StringEval) valEval).getStringValue();
328
			if(stringValue.length() < 1) {
329
				// More trickiness:
330
				// Empty string is not the same as BlankEval.  It causes #VALUE! error 
331
				throw EvaluationException.invalidValue();
332
			}
333
			// TODO move parseBoolean to OperandResolver
334
			Boolean b = Countif.parseBoolean(stringValue);
335
			if(b != null) {
336
				// string converted to boolean OK
337
				return b.booleanValue();
338
			}
339
			// Even more trickiness:
340
			// Note - even if the StringEval represents a number value (for example "1"), 
341
			// Excel does not resolve it to a boolean.  
342
			throw EvaluationException.invalidValue();
343
			// This is in contrast to the code below,, where NumberEvals values (for 
344
			// example 0.01) *do* resolve to equivalent boolean values.
345
		}
346
		if (valEval instanceof NumericValueEval) {
347
			NumericValueEval nve = (NumericValueEval) valEval;
348
			// zero is FALSE, everything else is TRUE
349
			return 0.0 != nve.getNumberValue();
350
		}
351
		throw new RuntimeException("Unexpected eval type (" + valEval.getClass().getName() + ")");
352
	}
353
	
354
	public static int lookupIndexOfValue(ValueEval lookupValue, ValueVector vector, boolean isRangeLookup) throws EvaluationException {
355
		LookupValueComparer lookupComparer = createLookupComparer(lookupValue);
356
		int result;
357
		if(isRangeLookup) {
358
			result = performBinarySearch(vector, lookupComparer);
359
		} else {
360
			result = lookupIndexOfExactValue(lookupComparer, vector);
361
		}
362
		if(result < 0) {
363
			throw new EvaluationException(ErrorEval.NA);
364
		}
365
		return result;
366
	}
367
	
368
	
369
	/**
370
	 * Finds first (lowest index) exact occurrence of specified value.
371
	 * @param lookupValue the value to be found in column or row vector
372
	 * @param vector the values to be searched. For VLOOKUP this is the first column of the 
373
	 * 	tableArray. For HLOOKUP this is the first row of the tableArray. 
374
	 * @return zero based index into the vector, -1 if value cannot be found
375
	 */
376
	private static int lookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector) {
377
378
		// find first occurrence of lookup value
379
		int size = vector.getSize();
380
		for (int i = 0; i < size; i++) {
381
			if(lookupComparer.compareTo(vector.getItem(i)).isEqual()) {
382
				return i;
383
			}
384
		}
385
		return -1;
386
	}
387
388
	
389
	/**
390
	 * Encapsulates some standard binary search functionality so the unusual Excel behaviour can
391
	 * be clearly distinguished. 
392
	 */
393
	private static final class BinarySearchIndexes {
394
395
		private int _lowIx;
396
		private int _highIx;
397
398
		public BinarySearchIndexes(int highIx) {
399
			_lowIx = -1;
400
			_highIx = highIx;
401
		}
402
403
		/**
404
		 * @return -1 if the search range is empty
405
		 */
406
		public int getMidIx() {
407
			int ixDiff = _highIx - _lowIx;
408
			if(ixDiff < 2) {
409
				return -1;
410
			}
411
			return _lowIx + (ixDiff / 2);
412
		}
413
414
		public int getLowIx() {
415
			return _lowIx;
416
		}
417
		public int getHighIx() {
418
			return _highIx;
419
		}
420
		public void narrowSearch(int midIx, boolean isLessThan) {
421
			if(isLessThan) {
422
				_highIx = midIx;
423
			} else {
424
				_lowIx = midIx;
425
			}
426
		}
427
	}
428
	/**
429
	 * Excel has funny behaviour when the some elements in the search vector are the wrong type.
430
	 * 
431
	 */
432
	private static int performBinarySearch(ValueVector vector, LookupValueComparer lookupComparer) {
433
		// both low and high indexes point to values assumed too low and too high.
434
		BinarySearchIndexes bsi = new BinarySearchIndexes(vector.getSize());
435
436
		while(true) {
437
			int midIx = bsi.getMidIx();
438
			
439
			if(midIx < 0) {
440
				return bsi.getLowIx();
441
			}
442
			CompareResult cr = lookupComparer.compareTo(vector.getItem(midIx));
443
			if(cr.isTypeMismatch()) {
444
				int newMidIx = handleMidValueTypeMismatch(lookupComparer, vector, bsi, midIx);
445
				if(newMidIx < 0) {
446
					continue;
447
				}
448
				midIx = newMidIx;
449
				cr = lookupComparer.compareTo(vector.getItem(midIx));
450
			}
451
			if(cr.isEqual()) {
452
				return findLastIndexInRunOfEqualValues(lookupComparer, vector, midIx, bsi.getHighIx());
453
			}
454
			bsi.narrowSearch(midIx, cr.isLessThan());
455
		}
456
	}
457
	/**
458
	 * Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the 
459
	 * first compatible value.
460
	 * @param midIx 'mid' index (value which has the wrong type)
461
	 * @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid 
462
	 * index.  Zero or greater signifies that an exact match for the lookup value was found
463
	 */
464
	private static int handleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector,
465
			BinarySearchIndexes bsi, int midIx) {
466
		int newMid = midIx;
467
		int highIx = bsi.getHighIx();
468
		
469
		while(true) {
470
			newMid++;
471
			if(newMid == highIx) {
472
				// every element from midIx to highIx was the wrong type
473
				// move highIx down to the low end of the mid values
474
				bsi.narrowSearch(midIx, true);
475
				return -1;
476
			}
477
			CompareResult cr = lookupComparer.compareTo(vector.getItem(newMid));
478
			if(cr.isLessThan() && newMid == highIx-1) {
479
				// move highIx down to the low end of the mid values
480
				bsi.narrowSearch(midIx, true);
481
				return -1;
482
				// but only when "newMid == highIx-1"? slightly weird.
483
				// It would seem more efficient to always do this.
484
			}
485
			if(cr.isTypeMismatch()) {
486
				// keep stepping over values until the right type is found
487
				continue;
488
			}
489
			if(cr.isEqual()) {
490
				return newMid;
491
			}
492
			// Note - if moving highIx down (due to lookup<vector[newMid]),
493
			// this execution path only moves highIx it down as far as newMid, not midIx,
494
			// which would be more efficient.
495
			bsi.narrowSearch(newMid, cr.isLessThan());
496
			return -1;
497
		}
498
	}
499
	/**
500
	 * Once the binary search has found a single match, (V/H)LOOKUP steps one by one over subsequent
501
	 * values to choose the last matching item.
502
	 */
503
	private static int findLastIndexInRunOfEqualValues(LookupValueComparer lookupComparer, ValueVector vector,
504
				int firstFoundIndex, int maxIx) {
505
		for(int i=firstFoundIndex+1; i<maxIx; i++) {
506
			if(!lookupComparer.compareTo(vector.getItem(i)).isEqual()) {
507
				return i-1;
508
			}
509
		}
510
		return maxIx - 1;
511
	}
512
513
	public static LookupValueComparer createLookupComparer(ValueEval lookupValue) throws EvaluationException {
514
		
515
		if (lookupValue instanceof BlankEval) {
516
			// blank eval can never be found in a lookup array 
517
			throw new EvaluationException(ErrorEval.NA);
518
		}
519
		if (lookupValue instanceof StringEval) {
520
			return new StringLookupComparer((StringEval) lookupValue);
521
		}
522
		if (lookupValue instanceof NumberEval) {
523
			return new NumberLookupComparer((NumberEval) lookupValue);
524
		}
525
		if (lookupValue instanceof BoolEval) {
526
			return new BooleanLookupComparer((BoolEval) lookupValue);
527
		}
528
		throw new IllegalArgumentException("Bad lookup value type (" + lookupValue.getClass().getName() + ")");
529
	}
530
}
0
  + *
531
  + *
1
  + native
532
  + native
(-)src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java (+387 lines)
Line 0 Link Here
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
19
package org.apache.poi.hssf.record.formula.functions;
20
21
import java.io.FileInputStream;
22
import java.io.IOException;
23
import java.io.PrintStream;
24
25
import junit.framework.Assert;
26
import junit.framework.AssertionFailedError;
27
import junit.framework.TestCase;
28
29
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
30
import org.apache.poi.hssf.usermodel.HSSFCell;
31
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
32
import org.apache.poi.hssf.usermodel.HSSFRow;
33
import org.apache.poi.hssf.usermodel.HSSFSheet;
34
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
35
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
36
import org.apache.poi.hssf.util.CellReference;
37
38
/**
39
 * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.<p/>
40
 * These tests have been separated from the common function and operator tests because the lookup
41
 * functions have more complex test cases and test data setup.
42
 * 
43
 * Tests for bug fixes and specific/tricky behaviour can be found in the corresponding test class
44
 * (<tt>TestXxxx</tt>) of the target (<tt>Xxxx</tt>) implementor, where execution can be observed
45
 *  more easily.
46
 * 
47
 * @author Josh Micich
48
 */
49
public final class TestLookupFunctionsFromSpreadsheet extends TestCase {
50
	
51
	private static final class Result {
52
		public static final int SOME_EVALUATIONS_FAILED = -1;
53
		public static final int ALL_EVALUATIONS_SUCCEEDED = +1;
54
		public static final int NO_EVALUATIONS_FOUND = 0;
55
	}
56
57
	/** 
58
	 * This class defines constants for navigating around the test data spreadsheet used for these tests.
59
	 */
60
	private static final class SS {
61
		
62
		/** Name of the test spreadsheet (found in the standard test data folder) */
63
		public final static String FILENAME = "LookupFunctionsTestCaseData.xls";
64
		
65
		/** Name of the first sheet in the spreadsheet (contains comments) */
66
		public final static String README_SHEET_NAME = "Read Me";
67
		
68
		
69
	    /** Row (zero-based) in each sheet where the evaluation cases start.   */
70
		public static final int START_TEST_CASES_ROW_INDEX = 4; // Row '5'
71
		/**  Index of the column that contains the function names */
72
	    public static final short COLUMN_INDEX_MARKER = 0; // Column 'A'
73
	    public static final short COLUMN_INDEX_EVALUATION = 1; // Column 'B'
74
	    public static final short COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C'
75
	    public static final short COLUMN_ROW_COMMENT = 3; // Column 'D'
76
	
77
	    /** Used to indicate when there are no more test cases on the current sheet   */
78
		public static final String TEST_CASES_END_MARKER = "<end>";
79
	    /** Used to indicate that the test on the current row should be ignored */
80
		public static final String SKIP_CURRENT_TEST_CASE_MARKER = "<skip>";
81
	
82
	}
83
84
 	// Note - multiple failures are aggregated before ending.  
85
	// If one or more functions fail, a single AssertionFailedError is thrown at the end
86
	private int _sheetFailureCount;
87
	private int _sheetSuccessCount;
88
	private int _evaluationFailureCount;
89
	private int _evaluationSuccessCount;
90
91
92
93
    private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) {
94
        if (expected == null) {
95
			throw new AssertionFailedError(msg + " - Bad setup data expected value is null");
96
		}
97
		if(actual == null) {
98
			throw new AssertionFailedError(msg + " - actual value was null");
99
		}
100
		if(expected.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
101
			confirmErrorResult(msg, expected.getErrorCellValue(), actual);
102
			return;
103
		}
104
		if(actual.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
105
			throw unexpectedError(msg, expected, actual.getErrorValue());
106
		}
107
		if(actual.getCellType() != expected.getCellType()) {
108
			throw wrongTypeError(msg, expected, actual);
109
		}
110
		
111
        
112
		switch (expected.getCellType()) {
113
			case HSSFCell.CELL_TYPE_BOOLEAN:
114
			    assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue());
115
			    break;
116
			case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
117
			    throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg);
118
			case HSSFCell.CELL_TYPE_NUMERIC:
119
			    assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0);
120
			    break;
121
			case HSSFCell.CELL_TYPE_STRING:
122
			    assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString());
123
			    break;
124
		}
125
    }
126
127
128
	private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) {
129
		return new AssertionFailedError(msgPrefix + " Result type mismatch. Evaluated result was "
130
				+ formatValue(actualValue) 
131
				+ " but the expected result was "
132
				+ formatValue(expectedCell)
133
				);
134
	}
135
136
137
	private static AssertionFailedError unexpectedError(String msgPrefix, HSSFCell expected, int actualErrorCode) {
138
		return new AssertionFailedError(msgPrefix + " Error code ("
139
				+ ErrorEval.ErrorCode.getText(actualErrorCode) 
140
				+ ") was evaluated, but the expected result was "
141
				+ formatValue(expected)
142
				);
143
	}
144
145
146
	private static void confirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual) {
147
		if(actual.getCellType() != HSSFCell.CELL_TYPE_ERROR) {
148
			throw new AssertionFailedError(msgPrefix + " Expected cell error (" 
149
					+ ErrorEval.ErrorCode.getText(expectedErrorCode) + ") but actual value was "
150
					+ formatValue(actual));
151
		}
152
		if(expectedErrorCode != actual.getErrorValue()) {
153
			throw new AssertionFailedError(msgPrefix + " Expected cell error code (" 
154
					+ ErrorEval.ErrorCode.getText(expectedErrorCode) 
155
					+ ") but actual error code was ("
156
					+ ErrorEval.ErrorCode.getText(actual.getErrorValue()) 
157
					+ ")");
158
		}
159
	}
160
161
162
	private static String formatValue(HSSFCell expecedCell) {
163
		switch (expecedCell.getCellType()) {
164
			case HSSFCell.CELL_TYPE_BLANK: return "<blank>";
165
			case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(expecedCell.getBooleanCellValue());
166
			case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(expecedCell.getNumericCellValue());
167
			case HSSFCell.CELL_TYPE_STRING: return expecedCell.getRichStringCellValue().getString();
168
		}
169
		throw new RuntimeException("Unexpected cell type of expected value (" + expecedCell.getCellType() + ")");
170
	}
171
	private static String formatValue(CellValue actual) {
172
		switch (actual.getCellType()) {
173
			case HSSFCell.CELL_TYPE_BLANK: return "<blank>";
174
			case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(actual.getBooleanValue());
175
			case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(actual.getNumberValue());
176
			case HSSFCell.CELL_TYPE_STRING: return actual.getRichTextStringValue().getString();
177
		}
178
		throw new RuntimeException("Unexpected cell type of evaluated value (" + actual.getCellType() + ")");
179
	}
180
181
182
	protected void setUp() throws Exception {
183
        _sheetFailureCount = 0;
184
        _sheetSuccessCount = 0;
185
        _evaluationFailureCount = 0;
186
        _evaluationSuccessCount = 0;
187
    }
188
    
189
    public void testFunctionsFromTestSpreadsheet() {
190
    	String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME;
191
        HSSFWorkbook workbook;
192
		try {
193
			FileInputStream fin = new FileInputStream( filePath );
194
			workbook = new HSSFWorkbook( fin );
195
		} catch (IOException e) {
196
			throw new RuntimeException(e);
197
		}
198
   	
199
    	confirmReadMeSheet(workbook);
200
    	int nSheets = workbook.getNumberOfSheets();
201
    	for(int i=1; i< nSheets; i++) {
202
    		int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i));
203
    		switch(sheetResult) {
204
    			case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break; 
205
    			case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break; 
206
    		}
207
    	}
208
        
209
        // confirm results
210
    	String successMsg = "There were " 
211
    			+ _sheetSuccessCount + " successful sheets(s) and "
212
				+ _evaluationSuccessCount + " function(s) without error";
213
 		if(_sheetFailureCount > 0) {
214
			String msg = _sheetFailureCount + " sheets(s) failed with "
215
			+ _evaluationFailureCount + " evaluation(s).  " + successMsg;
216
        	throw new AssertionFailedError(msg);
217
        }
218
 		if(false) { // normally no output for successful tests
219
 			System.out.println(getClass().getName() + ": " + successMsg);
220
 		}
221
	}
222
223
    private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) {
224
		HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
225
		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
226
		int maxRows = sheet.getLastRowNum()+1;
227
        int result = Result.NO_EVALUATIONS_FOUND; // so far
228
		
229
		String currentGroupComment = null;
230
		for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) {
231
            HSSFRow r = sheet.getRow(rowIndex);
232
			String newMarkerValue = getMarkerColumnValue(r);
233
			if(r == null) {
234
				continue;
235
			}
236
			if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
237
				// normal exit point
238
				return result;
239
			}
240
			if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
241
				// currently disabled test case row
242
				continue;
243
			}
244
			if(newMarkerValue != null) {
245
				currentGroupComment = newMarkerValue;
246
			}
247
            HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
248
			if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
249
				continue;
250
			}
251
			evaluator.setCurrentRow(r);
252
			CellValue actualValue = evaluator.evaluate(c);
253
			HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
254
			String rowComment = getRowCommentColumnValue(r);
255
			
256
			String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
257
			try {
258
				confirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
259
				_evaluationSuccessCount ++;
260
				if(result != Result.SOME_EVALUATIONS_FAILED) {
261
					result = Result.ALL_EVALUATIONS_SUCCEEDED;
262
				}
263
			} catch (RuntimeException e) {
264
				_evaluationFailureCount ++;
265
				printShortStackTrace(System.err, e);
266
				result = Result.SOME_EVALUATIONS_FAILED;
267
			} catch (AssertionFailedError e) {
268
				_evaluationFailureCount ++;
269
				printShortStackTrace(System.err, e);
270
				result = Result.SOME_EVALUATIONS_FAILED;
271
			}
272
			
273
		}
274
		throw new RuntimeException("Missing end marker '" + SS.TEST_CASES_END_MARKER 
275
				+ "' on sheet '" + sheetName + "'");
276
		
277
	}
278
279
280
	private static String formatTestCaseDetails(String sheetName, int rowNum, HSSFCell c, String currentGroupComment,
281
			String rowComment) {
282
		
283
		StringBuffer sb = new StringBuffer();
284
		CellReference cr = new CellReference(sheetName, rowNum, c.getCellNum(), false, false);
285
		sb.append(cr.formatAsString());
286
		sb.append(" {=").append(c.getCellFormula()).append("}");
287
		
288
		if(currentGroupComment != null) {
289
			sb.append(" '");
290
			sb.append(currentGroupComment);
291
			if(rowComment != null) {
292
				sb.append(" - ");
293
				sb.append(rowComment);
294
			}
295
			sb.append("' ");
296
		} else {
297
			if(rowComment != null) {
298
				sb.append(" '");
299
				sb.append(rowComment);
300
				sb.append("' ");
301
			}
302
		}
303
		
304
		return sb.toString();
305
	}
306
307
	/**
308
     * Asserts that the 'read me' comment page exists, and has this class' name in one of the 
309
     * cells.  This back-link is to make it easy to find this class if a reader encounters the 
310
     * spreadsheet first.
311
     */
312
    private void confirmReadMeSheet(HSSFWorkbook workbook) {
313
		String firstSheetName = workbook.getSheetName(0);
314
		if(!firstSheetName.equalsIgnoreCase(SS.README_SHEET_NAME)) {
315
			throw new RuntimeException("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'");
316
		}
317
		HSSFSheet sheet = workbook.getSheetAt(0);
318
		String specifiedClassName = sheet.getRow(2).getCell((short)0).getRichStringCellValue().getString();
319
		assertEquals("Test class name in spreadsheet comment", getClass().getName(), specifiedClassName);
320
		
321
	}
322
323
324
    /**
325
     * Useful to keep output concise when expecting many failures to be reported by this test case
326
     */
327
	private static void printShortStackTrace(PrintStream ps, Throwable e) {
328
		StackTraceElement[] stes = e.getStackTrace();
329
		
330
		int startIx = 0;
331
		// skip any top frames inside junit.framework.Assert
332
		while(startIx<stes.length) {
333
			if(!stes[startIx].getClassName().equals(Assert.class.getName())) {
334
				break;
335
			}
336
			startIx++;
337
		}
338
		// skip bottom frames (part of junit framework)
339
		int endIx = startIx+1;
340
		while(endIx < stes.length) {
341
			if(stes[endIx].getClassName().equals(TestCase.class.getName())) {
342
				break;
343
			}
344
			endIx++;
345
		}
346
		if(startIx >= endIx) {
347
			// something went wrong. just print the whole stack trace
348
			e.printStackTrace(ps);
349
		}
350
		endIx -= 4; // skip 4 frames of reflection invocation
351
		ps.println(e.toString());
352
		for(int i=startIx; i<endIx; i++) {
353
			ps.println("\tat " + stes[i].toString());
354
		}
355
		
356
	}
357
358
	private static String getRowCommentColumnValue(HSSFRow r) {
359
		return getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment");
360
	}
361
	
362
	private static String getMarkerColumnValue(HSSFRow r) {
363
		return getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker");
364
	}
365
	
366
	/**
367
	 * @return <code>null</code> if cell is missing, empty or blank
368
     */
369
	private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) {
370
		if(r == null) {
371
			return null;
372
		}
373
		HSSFCell cell = r.getCell((short) colIndex);
374
		if(cell == null) {
375
			return null;
376
		}
377
		if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
378
			return null;
379
		}
380
		if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
381
			return cell.getRichStringCellValue().getString();
382
		}
383
		
384
		throw new RuntimeException("Bad cell type for '" + columnName + "' column: ("
385
				+ cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")");
386
	}
387
}
0
  + *
388
  + *
1
  + native
389
  + native

Return to bug 44450