Attachment #24682 for bug #48292

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

Lines 17-23 Link Here

(-)a/src/java/org/apache/poi/hssf/record/ArrayRecord.java (-1 / +45 lines)
17		17
18	package org.apache.poi.hssf.record;	18	package org.apache.poi.hssf.record;
19		19
		20	import org.apache.poi.hssf.record.formula.AreaPtgBase;
20	import org.apache.poi.hssf.record.formula.Ptg;	21	import org.apache.poi.hssf.record.formula.Ptg;
		22	import org.apache.poi.hssf.record.formula.RefPtgBase;
		23	import org.apache.poi.hssf.util.CellRangeAddress8Bit;
21	import org.apache.poi.ss.formula.Formula;	24	import org.apache.poi.ss.formula.Formula;
22	import org.apache.poi.util.HexDump;	25	import org.apache.poi.util.HexDump;
23	import org.apache.poi.util.LittleEndianOutput;	26	import org.apache.poi.util.LittleEndianOutput;
Lines 28-33 import org.apache.poi.util.LittleEndianOutput; Link Here
28	* Treated in a similar way to SharedFormulaRecord	31	* Treated in a similar way to SharedFormulaRecord
29	*	32	*
30	* @author Josh Micich	33	* @author Josh Micich
		34	* @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support
31	*/	35	*/
32	public final class ArrayRecord extends SharedValueRecordBase {	36	public final class ArrayRecord extends SharedValueRecordBase {
33		37
Lines 35-41 public final class ArrayRecord extends SharedValueRecordBase { Link Here
35	private static final int OPT_ALWAYS_RECALCULATE = 0x0001;	39	private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
36	private static final int OPT_CALCULATE_ON_OPEN = 0x0002;	40	private static final int OPT_CALCULATE_ON_OPEN = 0x0002;
37		41
38	private int _options;	42	private int _options;
39	private int _field3notUsed;	43	private int _field3notUsed;
40	private Formula _formula;	44	private Formula _formula;
41		45
Lines 48-53 public final class ArrayRecord extends SharedValueRecordBase { Link Here
48	_formula = Formula.read(formulaTokenLen, in, totalFormulaLen);	52	_formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
49	}	53	}
50		54
		55	public ArrayRecord(Formula formula, CellRangeAddress8Bit range ) {
		56	super(range);
		57	_options = OPT_CALCULATE_ON_OPEN;
		58	_field3notUsed = 0;
		59	_formula = formula;
		60	}
		61
51	public boolean isAlwaysRecalculate() {	62	public boolean isAlwaysRecalculate() {
52	return (_options & OPT_ALWAYS_RECALCULATE) != 0;	63	return (_options & OPT_ALWAYS_RECALCULATE) != 0;
53	}	64	}
Lines 84-87 public final class ArrayRecord extends SharedValueRecordBase { Link Here
84	sb.append("]");	95	sb.append("]");
85	return sb.toString();	96	return sb.toString();
86	}	97	}
		98
		99	/**
		100	* @return the equivalent {@link Ptg} array that the formula would have,
		101	* were it not shared.
		102	*/
		103	public Ptg[] getFormulaTokens() {
		104	int formulaRow = this.getFirstRow();
		105	int formulaColumn = this.getLastColumn();
		106
		107	// Use SharedFormulaRecord static method to convert formula
		108
		109	Ptg[] ptgs = _formula.getTokens();
		110
		111	// Convert from relative addressing to absolute
		112	// because all formulas in array need to be referenced to the same
		113	// ref/range
		114	for (int i = 0; i < ptgs.length; i++) {
		115	Ptg ptg = ptgs[i];
		116	if (ptg instanceof AreaPtgBase) {
		117	AreaPtgBase aptg = (AreaPtgBase) ptg;
		118	aptg.setFirstRowRelative(false);
		119	aptg.setLastRowRelative(false);
		120	aptg.setFirstColRelative(false);
		121	aptg.setLastColRelative(false);
		122
		123	} else if (ptg instanceof RefPtgBase) {
		124	RefPtgBase rptg = (RefPtgBase) ptg;
		125	rptg.setRowRelative(false);
		126	rptg.setColRelative(false);
		127	}
		128	}
		129	return SharedFormulaRecord.convertSharedFormulas(ptgs, formulaRow, formulaColumn);
		130	}
87	}	131	}

Lines 17-22 Link Here

(-)a/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java (-3 / +45 lines)
17		17
18	package org.apache.poi.hssf.record.aggregates;	18	package org.apache.poi.hssf.record.aggregates;
19		19
		20	import org.apache.poi.hssf.record.ArrayRecord;
20	import org.apache.poi.hssf.record.CellValueRecordInterface;	21	import org.apache.poi.hssf.record.CellValueRecordInterface;
21	import org.apache.poi.hssf.record.FormulaRecord;	22	import org.apache.poi.hssf.record.FormulaRecord;
22	import org.apache.poi.hssf.record.Record;	23	import org.apache.poi.hssf.record.Record;
Lines 25-37 import org.apache.poi.hssf.record.SharedFormulaRecord; Link Here
25	import org.apache.poi.hssf.record.StringRecord;	26	import org.apache.poi.hssf.record.StringRecord;
26	import org.apache.poi.hssf.record.formula.ExpPtg;	27	import org.apache.poi.hssf.record.formula.ExpPtg;
27	import org.apache.poi.hssf.record.formula.Ptg;	28	import org.apache.poi.hssf.record.formula.Ptg;
		29	import org.apache.poi.hssf.util.CellRangeAddress8Bit;
28	import org.apache.poi.hssf.util.CellReference;	30	import org.apache.poi.hssf.util.CellReference;
		31	import org.apache.poi.ss.formula.Formula;
		32	import org.apache.poi.ss.util.CellRangeAddress;
29		33
30	/**	34	/**
31	* The formula record aggregate is used to join together the formula record and it's	35	* The formula record aggregate is used to join together the formula record and it's
32	* (optional) string record and (optional) Shared Formula Record (template reads, excel optimization).	36	* (optional) string record and (optional) Shared Formula Record (template reads, excel optimization).
33	*	37	*
34	* @author Glen Stampoultzis (glens at apache.org)	38	* @author Glen Stampoultzis (glens at apache.org)
		39	* @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support
35	*/	40	*/
36	public final class FormulaRecordAggregate extends RecordAggregate implements CellValueRecordInterface {	41	public final class FormulaRecordAggregate extends RecordAggregate implements CellValueRecordInterface {
37		42
Lines 181-190 public final class FormulaRecordAggregate extends RecordAggregate implements Cel Link Here
181	}	186	}
182		187
183	public Ptg[] getFormulaTokens() {	188	public Ptg[] getFormulaTokens() {
184	if (_sharedFormulaRecord == null) {	189	if (_sharedFormulaRecord != null) {
185	return _formulaRecord.getParsedExpression();	190	return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
		191	}
		192	CellReference expRef = _formulaRecord.getFormula().getExpReference();
		193	if (expRef != null) {
		194	ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
		195	return arec.getFormulaTokens();
186	}	196	}
187	return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);	197	return _formulaRecord.getParsedExpression();
188	}	198	}
189		199
190	/**	200	/**
Lines 216-219 public final class FormulaRecordAggregate extends RecordAggregate implements Cel Link Here
216	_sharedValueManager.unlink(_sharedFormulaRecord);	226	_sharedValueManager.unlink(_sharedFormulaRecord);
217	}	227	}
218	}	228	}
		229	public boolean isPartOfArrayFormula() {
		230	if (_sharedFormulaRecord != null) {
		231	return false;
		232	}
		233	return _formulaRecord.getFormula().getExpReference() != null;
		234	}
		235
		236	public CellRangeAddress getArrayFormulaRange() {
		237	if (_sharedFormulaRecord != null) {
		238	throw new IllegalStateException("not an array formula cell.");
		239	}
		240	CellReference expRef = _formulaRecord.getFormula().getExpReference();
		241	if (expRef == null) {
		242	throw new IllegalStateException("not an array formula cell.");
		243	}
		244	ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
		245	CellRangeAddress8Bit a = arec.getRange();
		246	return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn());
		247	}
		248	public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) {
		249
		250	ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn()));
		251	_sharedValueManager.addArrayRecord(arr);
		252	}
		253	/**
		254	* Removes an array formula
		255	* @return the range of the array formula containing the specified cell. Never <code>null</code>
		256	*/
		257	public CellRangeAddress removeArrayFormula(int rowIndex, int columnIndex) {
		258	CellRangeAddress8Bit a = _sharedValueManager.removeArrayFormula(rowIndex, columnIndex);
		259	return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(), a.getLastColumn());
		260	}
219	}	261	}





package org.apache.poi.hssf.record.aggregates;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.record.ArrayRecord;

 * </ul>
 *
 * @author Josh Micich
 * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - handling of ArrayRecords
 */
public final class SharedValueManager {



	public static final SharedValueManager EMPTY = new SharedValueManager(
			new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]);
	private final List<ArrayRecord> _arrayRecords;
	private final TableRecord[] _tableRecords;
	private final Map<SharedFormulaRecord, SharedFormulaGroup> _groupsBySharedFormulaRecord;
	/** cached for optimization purposes */

		if (nShF != firstCells.length) {
			throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + ".");
		}
		_arrayRecords = toList(arrayRecords);
		_tableRecords = tableRecords;
		Map<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2);
		for (int i = 0; i < nShF; i++) {

		_groupsBySharedFormulaRecord = m;
	}

	private static <Z> List<Z> toList(Z[] zz) {
		List<Z> result = new ArrayList<Z>(zz.length);
		for (int i = 0; i < zz.length; i++) {
			result.add(zz[i]);
		}
		return result;
	}

	/**
	 * @param firstCells
	 * @param recs list of sheet records (possibly contains records for other parts of the Excel file)

		// The first cell will be the top left in the range.  So we can match the
		// ARRAY/TABLE record directly.

		for (TableRecord tr : _tableRecords) {
			TableRecord tr = _tableRecords[i];
			if (tr.isFirstCell(row, column)) {
				return tr;
			}
		}
		for (ArrayRecord ar : _arrayRecords) {
			ArrayRecord ar = _arrayRecords[i];
			if (ar.isFirstCell(row, column)) {
				return ar;
			}

	 */
	public void unlink(SharedFormulaRecord sharedFormulaRecord) {
		SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord);
		_groups = null; // be sure to reset cached value
		if (svg == null) {
			throw new IllegalStateException("Failed to find formulas for shared formula");
		}
		_groups = null; // be sure to reset cached value
		svg.unlinkSharedFormulas();
	}

	/**
	 * Add specified Array Record.
	 */
	public void addArrayRecord(ArrayRecord ar) {
		// could do a check here to make sure none of the ranges overlap
		_arrayRecords.add(ar);
	}

	/**
	 * Removes the {@link ArrayRecord} for the cell group containing the specified cell.
	 * The caller should clear (set blank) all cells in the returned range.
	 * @return the range of the array formula which was just removed. Never <code>null</code>.
	 */
	public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) {
		for (ArrayRecord ar : _arrayRecords) {
			if (ar.isInRange(rowIndex, columnIndex)) {
				_arrayRecords.remove(ar);
				return ar.getRange();
			}
		}
		throw new IllegalArgumentException("Specified cell is not part of an array formula.");
	}

	/**
	 * @return the shared ArrayRecord identified by (firstRow, firstColumn). never <code>null</code>.
	 */
	public ArrayRecord getArrayRecord(int firstRow, int firstColumn) {
		for(ArrayRecord ar : _arrayRecords) {
			if(ar.isFirstCell(firstRow, firstColumn)) {
				return ar;
			}
		}
		return null;
	}
}

Lines 25-34 import org.apache.poi.hssf.record.formula.eval.ValueEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (-1 / +6 lines)
25		25
26	/**	26	/**
27	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	27	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
		28	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support;
28	*/	29	*/
29	public abstract class AggregateFunction extends MultiOperandNumericFunction {	30	public abstract class AggregateFunction extends MultiOperandNumericFunction {
30		31
31	private static final class LargeSmall extends Fixed2ArgFunction {	32	private static final class LargeSmall extends Fixed2ArgFunction implements FunctionWithArraySupport {
32	private final boolean _isLarge;	33	private final boolean _isLarge;
33	protected LargeSmall(boolean isLarge) {	34	protected LargeSmall(boolean isLarge) {
34	_isLarge = isLarge;	35	_isLarge = isLarge;
Lines 66-71 public abstract class AggregateFunction extends MultiOperandNumericFunction { Link Here
66		67
67	return new NumberEval(result);	68	return new NumberEval(result);
68	}	69	}
		70
		71	public boolean supportArray(int paramIndex) {
		72	return paramIndex != 1;
		73	}
69	}	74	}
70	private static final class ValueCollector extends MultiOperandNumericFunction {	75	private static final class ValueCollector extends MultiOperandNumericFunction {
71	private static final ValueCollector instance = new ValueCollector();	76	private static final ValueCollector instance = new ValueCollector();




/* ====================================================================
   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.hssf.record.formula.functions;

import org.apache.poi.hssf.record.formula.eval.ValueEval;

/**
 * Interface for those functions that behaves differently in array formula
 *
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
 */
public interface ArrayMode {

	ValueEval evaluateInArrayFormula(ValueEval[] args, int srcRowIndex, int srcColumnIndex);
}

Lines 36-42 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/BooleanFunction.java (-1 / +5 lines)
36	*	36	*
37	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	37	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
38	*/	38	*/
39	public abstract class BooleanFunction implements Function {	39	public abstract class BooleanFunction implements FunctionWithArraySupport {
40		40
41	public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) {	41	public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) {
42	if (args.length < 1) {	42	if (args.length < 1) {
Lines 51-56 public abstract class BooleanFunction implements Function { Link Here
51	return BoolEval.valueOf(boolResult);	51	return BoolEval.valueOf(boolResult);
52	}	52	}
53		53
		54	public boolean supportArray(int paramIndex) {
		55	return true;
		56	}
		57
54	private boolean calculate(ValueEval[] args) throws EvaluationException {	58	private boolean calculate(ValueEval[] args) throws EvaluationException {
55		59
56	boolean result = getInitialResultValue();	60	boolean result = getInitialResultValue();




import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.ss.formula.ArrayEval;

public final class Column implements Function0Arg, Function1Arg, ArrayMode {

    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
        return new NumberEval(srcColumnIndex+1);

        }
        return ErrorEval.VALUE_INVALID;
    }

    public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) {
        if ((evals.length == 1) && (evals[0] instanceof AreaEval)) {
            AreaEval ae = (AreaEval) evals[0];
            ValueEval[][] result = new ValueEval[1][ae.getWidth()];
            for (int c = 0; c < ae.getWidth(); c++) {
                result[0][c] = new NumberEval(ae.getFirstColumn() + c + 1);
            }
            return new ArrayEval(result);
        }
        return evaluate(evals, srcCellRow, srcCellCol);
    }
}

Lines 28-34 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Columns.java (-1 / +5 lines)
28	*	28	*
29	* @author Josh Micich	29	* @author Josh Micich
30	*/	30	*/
31	public final class Columns extends Fixed1ArgFunction {	31	public final class Columns extends Fixed1ArgFunction implements FunctionWithArraySupport {
32		32
33	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {	33	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34		34
Lines 42-45 public final class Columns extends Fixed1ArgFunction { Link Here
42	}	42	}
43	return new NumberEval(result);	43	return new NumberEval(result);
44	}	44	}
		45
		46	public boolean supportArray(int paramIndex) {
		47	return true;
		48	}
45	}	49	}

Lines 34-40 import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Count.java (-1 / +5 lines)
34	* TODO: Check this properly matches excel on edge cases	34	* TODO: Check this properly matches excel on edge cases
35	* like formula cells, error cells etc	35	* like formula cells, error cells etc
36	*/	36	*/
37	public final class Count implements Function {	37	public final class Count implements FunctionWithArraySupport {
38		38
39	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {	39	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
40	int nArgs = args.length;	40	int nArgs = args.length;
Lines 74-77 public final class Count implements Function { Link Here
74	return false;	74	return false;
75	}	75	}
76	};	76	};
		77
		78	public boolean supportArray(int paramIndex) {
		79	return true;
		80	}
77	}	81	}

Lines 32-38 import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Counta.java (-1 / +5 lines)
32	*	32	*
33	* @author Josh Micich	33	* @author Josh Micich
34	*/	34	*/
35	public final class Counta implements Function {	35	public final class Counta implements FunctionWithArraySupport {
36		36
37	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {	37	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
38	int nArgs = args.length;	38	int nArgs = args.length;
Lines 69-72 public final class Counta implements Function { Link Here
69	return true;	69	return true;
70	}	70	}
71	};	71	};
		72
		73	public boolean supportArray(int paramIndex) {
		74	return true;
		75	}
72	}	76	}

Lines 44-50 import org.apache.poi.ss.usermodel.ErrorConstants; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java (-1 / +8 lines)
44	*	44	*
45	* @author Josh Micich	45	* @author Josh Micich
46	*/	46	*/
47	public final class Countif extends Fixed2ArgFunction {	47	public final class Countif extends Fixed2ArgFunction implements FunctionWithArraySupport {
48		48
49	private static final class CmpOp {	49	private static final class CmpOp {
50	public static final int NONE = 0;	50	public static final int NONE = 0;
Lines 525-528 public final class Countif extends Fixed2ArgFunction { Link Here
525	}	525	}
526	return null;	526	return null;
527	}	527	}
		528
		529	public boolean supportArray(int paramIndex) {
		530	if (paramIndex==0) {
		531	return true;
		532	}
		533	return false; // TODO - countif does not allow first param as array - only as range
		534	}
528	}	535	}




/* ====================================================================
   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.hssf.record.formula.functions;

/**
 * TODO - Later this interface should be merged with Function interface
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
 */
public interface FunctionWithArraySupport extends Function {

	/**
	 * @return <code>true</code> if parameter accept array, <code>false</code> otherwise
	 */
	public boolean supportArray(int paramIndex);
}

Lines 39-45 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java (-1 / +5 lines)
39	*	39	*
40	* @author Josh Micich	40	* @author Josh Micich
41	*/	41	*/
42	public final class Hlookup extends Var3or4ArgFunction {	42	public final class Hlookup extends Var3or4ArgFunction implements FunctionWithArraySupport {
43	private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE;	43	private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE;
44		44
45	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,	45	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
Lines 77-80 public final class Hlookup extends Var3or4ArgFunction { Link Here
77	}	77	}
78	return LookupUtils.createRowVector(tableArray, rowIndex);	78	return LookupUtils.createRowVector(tableArray, rowIndex);
79	}	79	}
		80
		81	public boolean supportArray(int paramIndex) {
		82	return paramIndex == 1;
		83	}
80	}	84	}




import org.apache.poi.hssf.record.formula.eval.OperandResolver;
import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.ss.formula.ArrayEval;
import org.apache.poi.ss.formula.TwoDEval;

/**

 * </p>
 *
 * @author Josh Micich
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
 */
public final class Index implements Function2Arg, Function3Arg, Function4Arg, FunctionWithArraySupport, ArrayMode {

	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
		return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, false);
	}


	private ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, boolean supportRowColumn) {

		if (arg0 instanceof ArrayEval) {
			supportRowColumn = true;
		}
		TwoDEval reference = convertFirstArg(arg0);

		boolean colArgWasPassed = false;
		int columnIx = 0;
		try {
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
			ValueEval arg2) {
		return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, false);
	}
	private ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
			ValueEval arg2, boolean supportRowColumn) {

		if (arg0 instanceof ArrayEval) {
			supportRowColumn = true;
		}
		TwoDEval reference = convertFirstArg(arg0);

		boolean colArgWasPassed = true;
		try {
			int columnIx = resolveIndexArg(arg2, srcRowIndex, srcColumnIndex);
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}

	}

	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
		return evaluateX(args, srcRowIndex, srcColumnIndex, false);
	}

	private ValueEval evaluateX(ValueEval[] args, int srcRowIndex, int srcColumnIndex, boolean supportRowColumn) {
		switch (args.length) {
			case 2:
				return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], supportRowColumn);
			case 3:
				return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], supportRowColumn);
			case 4:
				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]);
		}

	 *            different when only 2 args are passed.
	 */
	private static ValueEval getValueFromArea(TwoDEval ae, int pRowIx, int pColumnIx,
			boolean colArgWasPassed, int srcRowIx, int srcColIx, boolean supportRowColumn) throws EvaluationException {
		boolean rowArgWasEmpty = pRowIx == 0;
		boolean colArgWasEmpty = pColumnIx == 0;
		int rowIx;
		int columnIx;

		// implementation of this function isn't support all features of the Excel
		// here I'm adding only support for return of entire row or columm
		if (supportRowColumn
				&& (rowArgWasEmpty && !ae.isRow() && pColumnIx <= ae.getWidth() && !colArgWasEmpty || colArgWasEmpty
						&& !ae.isColumn() && pRowIx <= ae.getHeight() && !rowArgWasEmpty)) {
			// return row or column
			ValueEval[][] result;
			if (rowArgWasEmpty ) { // entire column
				result = new ValueEval[ae.getHeight()][1];
				for( int r=0; r<ae.getHeight(); r++ ) {
					result[r][0] = ae.getValue(r, pColumnIx-1);
				}
			} else { //entire row
				result = new ValueEval[1][ae.getWidth()];
				for (int c=0; c<ae.getWidth(); c++) {
					result[0][c] = ae.getValue(	pRowIx-1,c);
				}
			}
			return new ArrayEval(result);
		}

		// when the area ref is a single row or a single column,
		// there are special rules for conversion of rowIx and columnIx
		if (ae.isRow()) {

		}
		return result;
	}

	public ValueEval evaluateInArrayFormula(ValueEval[] args, int srcCellRow, int srcCellCol) {
		// in array formula index(reference,row,0) and index(reference,0,col) should return entire row/column
		return evaluateX(args, srcCellRow, srcCellCol, true);
	}

	public boolean supportArray(int paramIndex) {
		return paramIndex == 0;
	}
}




 * <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>
 *
 * @author Josh Micich
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
 */
public final class Lookup extends Var2or3ArgFunction implements FunctionWithArraySupport {

	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
		// complex rules to choose lookupVector and resultVector from the single area ref

		// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
		throw new RuntimeException("non-vector lookup or result areas not supported yet");
	}

	public boolean supportArray(int paramIndex) {
		switch (paramIndex) {
			case 1:
			case 2:
				return true;
		}
		return false;
	}
}

Lines 63-69 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Match.java (-1 / +5 lines)
63	*	63	*
64	* @author Josh Micich	64	* @author Josh Micich
65	*/	65	*/
66	public final class Match extends Var2or3ArgFunction {	66	public final class Match extends Var2or3ArgFunction implements FunctionWithArraySupport {
67		67
68	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {	68	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
69	// default match_type is 1.0	69	// default match_type is 1.0
Lines 248-251 public final class Match extends Var2or3ArgFunction { Link Here
248	}	248	}
249	return false;	249	return false;
250	}	250	}
		251
		252	public boolean supportArray(int paramIndex) {
		253	return paramIndex == 1;
		254	}
251	}	255	}

Lines 35-41 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (-1 / +5 lines)
35	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	35	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
36	*	36	*
37	*/	37	*/
38	public final class Mode implements Function {	38	public final class Mode implements FunctionWithArraySupport {
39		39
40	/**	40	/**
41	* if v is zero length or contains no duplicates, return value is	41	* if v is zero length or contains no duplicates, return value is
Lines 130-133 public final class Mode implements Function { Link Here
130	}	130	}
131	throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");	131	throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");
132	}	132	}
		133
		134	public boolean supportArray(int paramIndex) {
		135	return true;
		136	}
133	}	137	}

Lines 34-40 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (-1 / +5 lines)
34	* classes that take variable number of operands, and	34	* classes that take variable number of operands, and
35	* where the order of operands does not matter	35	* where the order of operands does not matter
36	*/	36	*/
37	public abstract class MultiOperandNumericFunction implements Function {	37	public abstract class MultiOperandNumericFunction implements FunctionWithArraySupport {
38		38
39	private final boolean _isReferenceBoolCounted;	39	private final boolean _isReferenceBoolCounted;
40	private final boolean _isBlankCounted;	40	private final boolean _isBlankCounted;
Lines 194-197 public abstract class MultiOperandNumericFunction implements Function { Link Here
194	throw new RuntimeException("Invalid ValueEval type passed for conversion: ("	194	throw new RuntimeException("Invalid ValueEval type passed for conversion: ("
195	+ ve.getClass() + ")");	195	+ ve.getClass() + ")");
196	}	196	}
		197
		198	public boolean supportArray(int paramIndex) {
		199	return true;
		200	}
197	}	201	}

Lines 38-45 import org.apache.poi.hssf.record.formula.eval.ValueEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Offset.java (-1 / +6 lines)
38	* <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/>	38	* <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/>
39	*	39	*
40	* @author Josh Micich	40	* @author Josh Micich
		41	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
41	*/	42	*/
42	public final class Offset implements Function {	43	public final class Offset implements FunctionWithArraySupport {
43	// These values are specific to BIFF8	44	// These values are specific to BIFF8
44	private static final int LAST_VALID_ROW_INDEX = 0xFFFF;	45	private static final int LAST_VALID_ROW_INDEX = 0xFFFF;
45	private static final int LAST_VALID_COLUMN_INDEX = 0xFF;	46	private static final int LAST_VALID_COLUMN_INDEX = 0xFF;
Lines 224-227 public final class Offset implements Function { Link Here
224	ValueEval ve = OperandResolver.getSingleValue(eval, srcCellRow, srcCellCol);	225	ValueEval ve = OperandResolver.getSingleValue(eval, srcCellRow, srcCellCol);
225	return OperandResolver.coerceValueToInt(ve);	226	return OperandResolver.coerceValueToInt(ve);
226	}	227	}
		228
		229	public boolean supportArray(int paramIndex) {
		230	return paramIndex == 0;
		231	}
227	}	232	}




import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.ss.formula.ArrayEval;

public final class Row implements Function0Arg, Function1Arg, ArrayMode {

    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
        return new NumberEval(srcRowIndex+1);

        return ErrorEval.VALUE_INVALID;
    }

    public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) {
        if ((evals.length == 1) && (evals[0] instanceof AreaEval)) {
            AreaEval ae = (AreaEval) evals[0];

            ValueEval[][] result = new ValueEval[ae.getHeight()][1];
            for (int r = 0; r < ae.getHeight(); r++) {
                result[r][0] = new NumberEval(ae.getFirstRow() + r + 1);
            }
            return new ArrayEval(result);
        }
        return evaluate(evals, srcCellRow, srcCellCol);
    }
}

Lines 28-34 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Rows.java (-1 / +5 lines)
28	*	28	*
29	* @author Josh Micich	29	* @author Josh Micich
30	*/	30	*/
31	public final class Rows extends Fixed1ArgFunction {	31	public final class Rows extends Fixed1ArgFunction implements FunctionWithArraySupport {
32		32
33	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {	33	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34		34
Lines 42-45 public final class Rows extends Fixed1ArgFunction { Link Here
42	}	42	}
43	return new NumberEval(result);	43	return new NumberEval(result);
44	}	44	}
		45
		46	public boolean supportArray(int paramIndex) {
		47	return true;
		48	}
45	}	49	}

Lines 37-44 import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (-1 / +8 lines)
37	* </table><br/>	37	* </table><br/>
38	* </p>	38	* </p>
39	* @author Josh Micich	39	* @author Josh Micich
		40	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
40	*/	41	*/
41	public final class Sumif extends Var2or3ArgFunction {	42	public final class Sumif extends Var2or3ArgFunction implements FunctionWithArraySupport {
42		43
43	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {	44	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
44		45
Lines 124-127 public final class Sumif extends Var2or3ArgFunction { Link Here
124	throw new EvaluationException(ErrorEval.VALUE_INVALID);	125	throw new EvaluationException(ErrorEval.VALUE_INVALID);
125	}	126	}
126		127
		128	public boolean supportArray(int paramIndex) {
		129	if (paramIndex == 1) { // TODO - should throw exception if array instead of range
		130	return false;
		131	}
		132	return true;
		133	}
127	}	134	}




 *  )
 * </p>
 * @author Josh Micich
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
 */
public final class Sumproduct implements FunctionWithArraySupport {


	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {

		int maxN = args.length;

		if (maxN < 1) {
			return ErrorEval.VALUE_INVALID;
		}
		ValueEval firstArg = args[0];
		try {
			if (firstArg instanceof NumericValueEval) {
				return evaluateSingleProduct(args);
			}
			if (firstArg instanceof RefEval) {
				return evaluateSingleProduct(args);
			}
			if (firstArg instanceof TwoDEval) {

		throw new RuntimeException("Unexpected value eval class ("
				+ ve.getClass().getName() + ")");
	}

	public boolean supportArray(int paramIndex) {
		return true;
	}
}

Lines 38-45 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java (-1 / +6 lines)
38	* the lookup_value. If FALSE, only exact matches will be considered<br/>	38	* the lookup_value. If FALSE, only exact matches will be considered<br/>
39	*	39	*
40	* @author Josh Micich	40	* @author Josh Micich
		41	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
41	*/	42	*/
42	public final class Vlookup extends Var3or4ArgFunction {	43	public final class Vlookup extends Var3or4ArgFunction implements FunctionWithArraySupport {
43	private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE;	44	private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE;
44		45
45	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,	46	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
Lines 78-81 public final class Vlookup extends Var3or4ArgFunction { Link Here
78	}	79	}
79	return LookupUtils.createColumnVector(tableArray, colIndex);	80	return LookupUtils.createColumnVector(tableArray, colIndex);
80	}	81	}
		82
		83	public boolean supportArray(int paramIndex) {
		84	return paramIndex == 1;
		85	}
81	}	86	}

Lines 28-34 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java (-1 / +5 lines)
28	/**	28	/**
29	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	29	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
30	*/	30	*/
31	public abstract class XYNumericFunction extends Fixed2ArgFunction {	31	public abstract class XYNumericFunction extends Fixed2ArgFunction implements FunctionWithArraySupport {
32		32
33	private static abstract class ValueArray implements ValueVector {	33	private static abstract class ValueArray implements ValueVector {
34	private final int _size;	34	private final int _size;
Lines 174-177 public abstract class XYNumericFunction extends Fixed2ArgFunction { Link Here
174	}	174	}
175	return new SingleCellValueArray(arg);	175	return new SingleCellValueArray(arg);
176	}	176	}
		177
		178	public boolean supportArray(int paramIndex) {
		179	return true;
		180	}
177	}	181	}




import org.apache.poi.hssf.record.TextObjectRecord;
import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import org.apache.poi.hssf.record.formula.ExpPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.SpreadsheetVersion;

        }
        return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
    }

    void setCellArrayFormula(CellRangeAddress range) {
        int row=_record.getRow();
        short col=_record.getColumn();
        short styleIndex=_record.getXFIndex();
        setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex);

        // Billet for formula in rec
        Ptg[] ptgsForCell = { new ExpPtg(range.getFirstRow(), range.getFirstColumn()) };
        FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
        agg.setParsedExpression(ptgsForCell);
    }

    public CellRangeAddress getArrayFormulaRange() {
        if (_cellType != CELL_TYPE_FORMULA) {
            throw new IllegalArgumentException("Only formula cells can have array ranges");
        }
        return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
    }

    public boolean isPartOfArrayFormulaGroup() {
        if (_cellType != CELL_TYPE_FORMULA) {
            return false;
        }
        return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
    }
}

Lines 19-27 package org.apache.poi.hssf.usermodel; Link Here

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (-1 / +6 lines)
19		19
20	import org.apache.poi.ss.formula.EvaluationCell;	20	import org.apache.poi.ss.formula.EvaluationCell;
21	import org.apache.poi.ss.formula.EvaluationSheet;	21	import org.apache.poi.ss.formula.EvaluationSheet;
		22	import org.apache.poi.ss.util.CellRangeAddress;
22	/**	23	/**
23	* HSSF wrapper for a cell under evaluation	24	* HSSF wrapper for a cell under evaluation
24	*	25	*
25	* @author Josh Micich	26	* @author Josh Micich
26	*/	27	*/
27	final class HSSFEvaluationCell implements EvaluationCell {	28	final class HSSFEvaluationCell implements EvaluationCell {
Lines 69-72 final class HSSFEvaluationCell implements EvaluationCell { Link Here
69	public String getStringCellValue() {	70	public String getStringCellValue() {
70	return _cell.getRichStringCellValue().getString();	71	return _cell.getRichStringCellValue().getString();
71	}	72	}
		73
		74	public boolean isArrayFormula() {
		75	return _cell.isPartOfArrayFormulaGroup();
		76	}
72	}	77	}

Lines 25-37 import org.apache.poi.hssf.record.formula.eval.NumberEval; Link Here

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (-5 / +101 lines)
25	import org.apache.poi.hssf.record.formula.eval.StringEval;	25	import org.apache.poi.hssf.record.formula.eval.StringEval;
26	import org.apache.poi.hssf.record.formula.eval.ValueEval;	26	import org.apache.poi.hssf.record.formula.eval.ValueEval;
27	import org.apache.poi.hssf.record.formula.udf.UDFFinder;	27	import org.apache.poi.hssf.record.formula.udf.UDFFinder;
		28	import org.apache.poi.ss.formula.ArrayEval;
28	import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;	29	import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;
29	import org.apache.poi.ss.formula.IStabilityClassifier;	30	import org.apache.poi.ss.formula.IStabilityClassifier;
30	import org.apache.poi.ss.formula.WorkbookEvaluator;	31	import org.apache.poi.ss.formula.WorkbookEvaluator;
		32	import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
31	import org.apache.poi.ss.usermodel.Cell;	33	import org.apache.poi.ss.usermodel.Cell;
32	import org.apache.poi.ss.usermodel.CellValue;	34	import org.apache.poi.ss.usermodel.CellValue;
33	import org.apache.poi.ss.usermodel.FormulaEvaluator;	35	import org.apache.poi.ss.usermodel.FormulaEvaluator;
34	import org.apache.poi.ss.usermodel.Row;	36	import org.apache.poi.ss.usermodel.Row;
		37	import org.apache.poi.ss.usermodel.Sheet;
		38	import org.apache.poi.ss.util.CellRangeAddress;
35		39
36	/**	40	/**
37	* Evaluates formula cells.<p/>	41	* Evaluates formula cells.<p/>
Lines 42-47 import org.apache.poi.ss.usermodel.Row; Link Here
42	*	46	*
43	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	47	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
44	* @author Josh Micich	48	* @author Josh Micich
		49	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - Array Formula support
		50	* @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support
45	*/	51	*/
46	public class HSSFFormulaEvaluator implements FormulaEvaluator {	52	public class HSSFFormulaEvaluator implements FormulaEvaluator {
47		53
Lines 207-215 public class HSSFFormulaEvaluator implements FormulaEvaluator { Link Here
207	if (cell == null \|\| cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {	213	if (cell == null \|\| cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
208	return -1;	214	return -1;
209	}	215	}
210	CellValue cv = evaluateFormulaCellValue(cell);
211	// cell remains a formula cell, but the cached value is changed	216	// cell remains a formula cell, but the cached value is changed
212	setCellValue(cell, cv);	217	CellValue cv;
		218	if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context
		219	CellValue[][] cvs = evaluateFormulaCellArrayValues((HSSFCell) cell);
		220	int rowIndex = cell.getRowIndex() - cell.getArrayFormulaRange().getFirstRow();
		221	int colIndex = cell.getColumnIndex() - cell.getArrayFormulaRange().getFirstColumn();
		222	CellValue[][] values = setCellValues(cell, cvs);
		223	cv = values[rowIndex][colIndex];
		224	} else { // Single Formula
		225
		226	cv = evaluateFormulaCellValue(cell);
		227	setCellValue(cell, cv);
		228	}
213	return cv.getCellType();	229	return cv.getCellType();
214	}	230	}
215		231
Lines 234-242 public class HSSFFormulaEvaluator implements FormulaEvaluator { Link Here
234	}	250	}
235	HSSFCell result = (HSSFCell) cell;	251	HSSFCell result = (HSSFCell) cell;
236	if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {	252	if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
237	CellValue cv = evaluateFormulaCellValue(cell);	253	if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context
238	setCellValue(cell, cv);	254	CellValue[][] cvs = evaluateFormulaCellArrayValues((HSSFCell) cell);
239	setCellType(cell, cv); // cell will no longer be a formula cell	255	setCellValues(cell, cvs);
		256	setCellsTypes(cell, cvs); // cells will no longer be a formula cell
		257	} else { // Single Formula
		258	CellValue cv = evaluateFormulaCellValue(cell);
		259	setCellValue(cell, cv);
		260	setCellType(cell, cv); // cell will no longer be a formula cell
		261	}
240	}	262	}
241	return result;	263	return result;
242	}	264	}
Lines 281-286 public class HSSFFormulaEvaluator implements FormulaEvaluator { Link Here
281	}	303	}
282	}	304	}
283		305
		306	private void setCellsTypes(Cell cell, CellValue[][] cvs) {
		307	CellRangeAddress range = cell.getArrayFormulaRange();
		308	int rowStart = range.getFirstRow();
		309	int colStart = range.getFirstColumn();
		310	Sheet sheet = cell.getSheet();
		311	for(int i=rowStart;i<=range.getLastRow();i++ )
		312	for(int j=colStart; j<=range.getLastColumn();j++) {
		313	Row row = sheet.getRow(i);
		314	Cell c = row.getCell(j);
		315	if ((i-rowStart)<cvs.length && (j-colStart)<cvs[i-rowStart].length) {
		316	setCellType(c,cvs[i-rowStart][j-colStart]);
		317	}
		318	}
		319	}
		320
		321	/**
		322	* Set value in Range
		323	* @param cell
		324	* @param cvs
		325	* @return
		326	*/
		327	private CellValue[][] setCellValues(Cell cell, CellValue[][] cvs) {
		328	CellRangeAddress range = cell.getArrayFormulaRange();
		329	int rowStart = range.getFirstRow();
		330	int colStart = range.getFirstColumn();
		331	Sheet sheet = cell.getSheet();
		332	CellValue[][] answer = ArrayFormulaEvaluatorHelper.transformToRange(cvs, range);
		333	for(int i=rowStart;i<=range.getLastRow();i++ ) {
		334	for(int j=colStart; j<=range.getLastColumn();j++) {
		335	Row row = sheet.getRow(i);
		336	if (row == null) {
		337	row = sheet.createRow(i);
		338	}
		339	Cell c = row.getCell(j);
		340	if (c == null) {
		341	c = row.createCell(j);
		342	}
		343	CellValue cellValue = answer[i-rowStart][j-colStart];
		344	setCellValue(c,cellValue);
		345	}
		346	}
		347	return answer;
		348	}
		349
284	/**	350	/**
285	* Loops over all cells in all sheets of the supplied	351	* Loops over all cells in all sheets of the supplied
286	* workbook.	352	* workbook.
Lines 315-320 public class HSSFFormulaEvaluator implements FormulaEvaluator { Link Here
315	*/	381	*/
316	private CellValue evaluateFormulaCellValue(Cell cell) {	382	private CellValue evaluateFormulaCellValue(Cell cell) {
317	ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));	383	ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
		384	if (eval instanceof ArrayEval) {// support of arrays
		385	if (cell.isPartOfArrayFormulaGroup()) {
		386	eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell);
		387	} else {
		388	eval = ((ArrayEval) eval).getValue(0, 0);
		389	}
		390	}
318	if (eval instanceof NumberEval) {	391	if (eval instanceof NumberEval) {
319	NumberEval ne = (NumberEval) eval;	392	NumberEval ne = (NumberEval) eval;
320	return new CellValue(ne.getNumberValue());	393	return new CellValue(ne.getNumberValue());
Lines 332-335 public class HSSFFormulaEvaluator implements FormulaEvaluator { Link Here
332	}	405	}
333	throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");	406	throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
334	}	407	}
		408
		409	/**
		410	* Returns a Array CellValue wrapper around the supplied ArrayEval instance.
		411	*/
		412	private CellValue[][] evaluateFormulaCellArrayValues(HSSFCell cell) {
		413	ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell(cell));
		414	if (eval instanceof ArrayEval) {// support of arrays
		415	ArrayEval ae = (ArrayEval) eval;
		416	int rowCount = ae.getHeight();
		417	int ColCount = ae.getWidth();
		418	CellValue[][] answer = new CellValue[rowCount][ColCount];
		419	for (int i = 0; i < rowCount; i++)
		420	for (int j = 0; j < ColCount; j++) {
		421	ValueEval val = ae.getValue(i, j);
		422	answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val);
		423	}
		424	return answer;
		425	}
		426	// non-array (usually from aggregate function)
		427	CellValue[][] answer = new CellValue[1][1];
		428	answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval);
		429	return answer;
		430	}
335	}	431	}




import java.util.TreeMap;

import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.CellValueRecordInterface;

import org.apache.poi.hssf.record.WSBoolRecord;
import org.apache.poi.hssf.record.WindowTwoRecord;
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
import org.apache.poi.hssf.record.formula.FormulaShifter;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

 * @author  Shawn Laubach (slaubach at apache dot org) (Just a little)
 * @author  Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
 * @author  Yegor Kozlov (yegor at apache.org) (Autosizing columns)
 * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas
 */
public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
    private static final POILogger log = POILogFactory.getLogger(HSSFSheet.class);


    /**
     * Sets whether the worksheet is displayed from right to left instead of from left to right.
     *
     * @param value true for right to left, false otherwise.
     */
    public void setRightToLeft(boolean value)
    {
        _sheet.getWindowTwo().setArabic(value);
    }

    /**

     */
    public boolean isRightToLeft()
    {
        return _sheet.getWindowTwo().getArabic();
    }

    /**

        return wb.getSheetName(idx);
    }

    public void setArrayFormula(String formula, CellRangeAddress range) {
        // make sure the formula parses OK first
        Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, _workbook.getSheetIndex(this));
        int firstRow = range.getFirstRow();
        int firstColumn = range.getFirstColumn();
        for (int rowIn = firstRow; rowIn <= range.getLastRow(); rowIn++) {
            for (int colIn = firstColumn; colIn <= range.getLastColumn(); colIn++) {
                HSSFRow row = getRow(rowIn);
                if (row == null) {
                    row = createRow(rowIn);
                }
                HSSFCell cell = row.getCell(colIn);
                if (cell == null) {
                    cell = row.createCell(colIn);
                }
                cell.setCellArrayFormula(range);
            }
        }
        HSSFCell firstArrayFormulaCell = getRow(firstRow).getCell(firstColumn);
        FormulaRecordAggregate agg = (FormulaRecordAggregate) firstArrayFormulaCell.getCellValueRecord();
        agg.setArrayFormula(range, ptgs);
    }


    public void removeArrayFormula(Cell cell) {
        CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
        if (!(rec instanceof FormulaRecordAggregate)) {
            throw new IllegalArgumentException("Specified cell is not a formula cell.");
        }
        FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
        CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
        if (range == null) {
            throw new IllegalArgumentException("Specified cell does not contain an array formula.");
        }
        // clear all cells in the range
        for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
            for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
                Cell rCell = getRow(rowIn).getCell(colIn);
                rCell.setCellType(Cell.CELL_TYPE_BLANK);
            }
        }
    }
}




/* ====================================================================
   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.formula;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.record.constant.ErrorConstant;
import org.apache.poi.hssf.record.formula.ArrayPtg;
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.NumberEval;
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.ss.util.NumberToTextConverter;

/**
 *  Class to support evaluated array of values
 *
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
 */
public final class ArrayEval implements TwoDEval {

	private final ValueEval[][] _values;

	public ArrayEval(ArrayPtg ptg) {
		if (ptg == null) {
			throw new IllegalArgumentException("ArrayPtg should not be null");
		}
		Object[][] tokenValues = ptg.getTokenArrayValues();
		int nRows = tokenValues.length;
		int nCols = tokenValues[0].length;
		ValueEval[][] values = new ValueEval[nRows][nCols];
		for (int r=0; r< nRows; r++) {
			Object[] tokenRow = tokenValues[r];
			ValueEval[] row = values[r];
			for (int c=0; c< nCols; c++) {
				row[c] = constructEval(tokenRow[c]);
			}
		}
		_values = values;
	}

	public ArrayEval(ValueEval[][] values) {
		if (values == null) {
			throw new IllegalArgumentException("null is not allowed");
		}
		int nRows = values.length;
		int nCols = values[0].length;
		for (int r=0; r< nRows; r++) {
			ValueEval[] row = values[r];
			for (int c=0; c< nCols; c++) {
				validateValueEval(row[c]);
			}
		}

		_values = values;
	}

	private void validateValueEval(ValueEval valueEval) {
		if (valueEval instanceof NumberEval) {
			return;
		}
		if (valueEval instanceof StringEval) {
			return;
		}
		if (valueEval instanceof BoolEval) {
			return;
		}
		if (valueEval instanceof ErrorEval) {
			return;
		}

		if (valueEval == null) {
			if (false) {
				// TODO throw new IllegalArgumentException("Array elements cannot be null.");
			}
			return;
		}

		if (valueEval instanceof RefEval) {
			throw new IllegalArgumentException("Array elements cannot be of type RefEval");
		}
		if (valueEval instanceof AreaEval) {
			throw new IllegalArgumentException("Array elements cannot be of type AreaEval");
		}
		throw new IllegalArgumentException("Unexpected eval type ("
				+ valueEval.getClass().getSimpleName() + ").");
	}

	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append(getClass().getName()).append(" [");
		sb.append("{");
		for (int r = 0; r < _values.length; r++) {
			if (r > 0) {
				sb.append(";");
			}
			for (int c = 0; c < _values[r].length; c++) {
				if (c > 0) {
					sb.append(",");
				}
				Object o = _values[r][c];
				sb.append(getConstantText(o));
			}
		}
		sb.append("}]");
		return sb.toString();
	}

	/**
	 * TODO - remove this method
	 */
	public ValueEval[][] getArrayValues() {
		return _values;
	}

	/**
	 * get element of array as Value Eval
	 * @param row
	 * @param col
	 * @return
	 */
	public ValueEval getValue(int row, int col) {
		return _values[row][col];
	}

	/**
	 * Convert Object to ValueEval
	 */
	private static ValueEval constructEval(Object o) {
		if (o == null) {
			throw new RuntimeException("Array item cannot be null");
		}
		if (o instanceof String) {
			return new StringEval( (String)o );
		}
		if (o instanceof Double) {
			return new NumberEval(((Double)o).doubleValue());
		}
		if (o instanceof Boolean) {
			return BoolEval.valueOf(((Boolean)o).booleanValue());
		}
		if (o instanceof ErrorConstant) {
			return ErrorEval.valueOf(((ErrorConstant)o).getErrorCode());
		}
		throw new IllegalArgumentException("Unexpected constant class (" + o.getClass());
	}


	/**
	 * get String contains object's value
	 * @param o
	 * @return
	 */
	private static String getConstantText(Object o) {

		if (o == null) {
			return "Error - null";
// TODO			throw new RuntimeException("Array item cannot be null");
		}
		if (o instanceof StringEval) {
			return "\"" + ((StringEval)o).getStringValue() + "\"";
		}
		if (o instanceof NumberEval) {
			return NumberToTextConverter.toText(((NumberEval)o).getNumberValue());
		}
		if (o instanceof Boolean) {
			return ((Boolean)o).booleanValue() ? "TRUE" : "FALSE";
		}
		if (o instanceof ErrorEval) {
			return ErrorEval.getText(((ErrorEval)o).getErrorCode());
		}
		throw new IllegalArgumentException("Unexpected constant class (" + o.getClass().getName() + ")");
	}

	/**
	 * return Array as ValueEval list
	 * @return
	 */
	public List<ValueEval> getArrayAsEval() {

		List<ValueEval> l = new ArrayList<ValueEval>();
		for(int r=0; r< _values.length; r++) {
			ValueEval[] row = _values[r];
			for (int c=0; c<row.length; c++) {
				l.add(row[c]);
			}
		}
		return l;
	}

	/**
	 * get row count
	 * @return
	 */
	public int getHeight() {
		return _values.length;
	}

	/**
	 * get column count
	 * @return
	 */
	public int getWidth() {
		return _values[0].length;
	}

	public boolean isRow() {
		return _values.length == 1;
	}

	public boolean isColumn() {
		return _values[0].length == 1;
	}
	public enum BooleanContent{ONLY_FALSE,ONLY_TRUE,MIXED}

	/**
	 *  Check if content of boolean array ONLY_FALSE, ONLY_TRUE or MIXED
	 *  if content is not boolean then return MIXED
	 * @return
	 */
	public BooleanContent checkBooleanContent() {
		try {
			BoolEval first = (BoolEval) _values[0][0];
			for (int i = 0; i < _values.length; i++) {
				for (int j = 0; j < _values[i].length; j++) {
					if (first.equals(_values[i][j])) {
						return BooleanContent.MIXED;
					}
				}
			}

			if (first.getBooleanValue()) {
				return BooleanContent.ONLY_TRUE;
			}
			return BooleanContent.ONLY_FALSE;
		} catch (Exception e) {
			return BooleanContent.MIXED;
		}
	}
}

Lines 22-31 import java.util.HashMap; Link Here

(-)a/src/java/org/apache/poi/ss/formula/EvaluationCell.java (-2 / +8 lines)
22	/**	22	/**
23	* Abstracts a cell for the purpose of formula evaluation. This interface represents both formula	23	* Abstracts a cell for the purpose of formula evaluation. This interface represents both formula
24	* and non-formula cells.<br/>	24	* and non-formula cells.<br/>
25	*	25	*
26	* For POI internal use only	26	* For POI internal use only
27	*	27	*
28	* @author Josh Micich	28	* @author Josh Micich
		29	* @author Petr Udalau(Petr.Udalau at exigenservices.com) - array formula support
29	*/	30	*/
30	public interface EvaluationCell {	31	public interface EvaluationCell {
31	/**	32	/**
Lines 42-45 public interface EvaluationCell { Link Here
42	String getStringCellValue();	43	String getStringCellValue();
43	boolean getBooleanCellValue();	44	boolean getBooleanCellValue();
44	int getErrorCellValue();	45	int getErrorCellValue();
		46
		47	/**
		48	* @return <code>true<code> if cell belongs to to range of Array Formula
		49	*/
		50	boolean isArrayFormula();
45	}	51	}

Lines 72-77 final class OperandClassTransformer { Link Here

(-)a/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (+3 lines)
72	case FormulaType.CELL:	72	case FormulaType.CELL:
73	rootNodeOperandClass = Ptg.CLASS_VALUE;	73	rootNodeOperandClass = Ptg.CLASS_VALUE;
74	break;	74	break;
		75	case FormulaType.ARRAY:
		76	rootNodeOperandClass = Ptg.CLASS_ARRAY;
		77	break;
75	case FormulaType.NAMEDRANGE:	78	case FormulaType.NAMEDRANGE:
76	case FormulaType.DATAVALIDATION_LIST:	79	case FormulaType.DATAVALIDATION_LIST:
77	rootNodeOperandClass = Ptg.CLASS_REF;	80	rootNodeOperandClass = Ptg.CLASS_REF;




	private final int _columnIndex;
	private final EvaluationTracker _tracker;
	private final WorkbookEvaluator _bookEvaluator;
	private final boolean _inArrayFormulaContext;

	public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
			int srcColNum, EvaluationTracker tracker, boolean inArrayFormulaContext) {
		_bookEvaluator = bookEvaluator;
		_workbook = workbook;
		_sheetIndex = sheetIndex;
		_rowIndex = srcRowNum;
		_columnIndex = srcColNum;
		_tracker = tracker;
		_inArrayFormulaContext = inArrayFormulaContext;
	}

	public EvaluationWorkbook getWorkbook() {

		return _columnIndex;
	}

	public boolean isInArrayFormulaContext() {
		return _inArrayFormulaContext;
	}

	SheetRefEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) {
		return createExternSheetRefEvaluator(ptg.getExternSheetIndex());
	}




import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
import org.apache.poi.hssf.record.formula.functions.ArrayMode;
import org.apache.poi.hssf.record.formula.functions.Function;
import org.apache.poi.hssf.record.formula.functions.Indirect;
import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;

/**
 * This class creates <tt>OperationEval</tt> instances to help evaluate <tt>OperationPtg</tt>
 * formula tokens.
 *
 * @author Josh Micich
 * @author Petr Udalau(Petr.Udalau at exigenservices.com) - evaluations of array formulas
 */
final class OperationEvaluatorFactory {


	 */
	public static ValueEval evaluate(OperationPtg ptg, ValueEval[] args,
			OperationEvaluationContext ec) {
		if (ptg == null) {
			throw new IllegalArgumentException("ptg must not be null");
		}
		Function func = _instancesByPtgClass.get(ptg);

		if (func == null && ptg instanceof AbstractFunctionPtg) {
			return  result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
		}

		if (ptg instanceof AbstractFunctionPtg) {
			AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
			int functionIndex = fptg.getFunctionIndex();
			switch (functionIndex) {

				case FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
					return UserDefinedFunction.instance.evaluate(args, ec);
			}
			func = FunctionEval.getBasicFunction(functionIndex);
		}
		if (func != null) {
			if (func instanceof ArrayMode && ec.isInArrayFormulaContext()) {
				return evaluateInSpecialModeForArrayFormulas((ArrayMode) func, args, ec);
			} // TODO - else not array ... still invoking array though ?
			return invokeOperationInArrayContext(func, args, ec);
		}
		throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")");
	}

	private static ValueEval evaluateInSpecialModeForArrayFormulas(ArrayMode function, ValueEval[] ops,
			OperationEvaluationContext ec) {
		return function.evaluateInArrayFormula(ops, ec.getRowIndex(), ec.getColumnIndex());
	}

	private static ValueEval invokeOperationInArrayContext(Function func, ValueEval[] ops, OperationEvaluationContext ec) {
		boolean isArrayFormula = ec.isInArrayFormulaContext();
		ArrayEval arrayResult = ArrayFormulaEvaluatorHelper.prepareEmptyResult(func, ops, isArrayFormula);
		int srcRowIndex = ec.getRowIndex();
		int srcColIndex = ec.getColumnIndex();
		if (arrayResult == null) {
			return func.evaluate(ops, srcRowIndex, srcColIndex);
		}
		ValueEval[][] values = arrayResult.getArrayValues();
		for (int row = 0; row < values.length; row++) {
			for (int col = 0; col < values[row].length; col++) {
				ValueEval[] singleInvocationArgs = ArrayFormulaEvaluatorHelper.prepareArgsForLoop(func, ops, row, col);
				ValueEval elemResult = func.evaluate(singleInvocationArgs, srcRowIndex, srcColIndex);
				values[row][col] = WorkbookEvaluator.dereferenceValue(elemResult, srcRowIndex, srcColIndex);
			}
		}
		return arrayResult;
	}
}




import java.util.Map;
import java.util.Stack;

import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
import org.apache.poi.hssf.record.formula.Area3DPtg;
import org.apache.poi.hssf.record.formula.AreaErrPtg;
import org.apache.poi.hssf.record.formula.AreaPtg;
import org.apache.poi.hssf.record.formula.ArrayPtg;
import org.apache.poi.hssf.record.formula.AttrPtg;
import org.apache.poi.hssf.record.formula.BoolPtg;
import org.apache.poi.hssf.record.formula.ControlPtg;

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.record.formula.function.FunctionMetadataRegistry;
import org.apache.poi.hssf.record.formula.functions.Choose;
import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
import org.apache.poi.hssf.record.formula.functions.If;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
import org.apache.poi.ss.formula.eval.NotImplementedException;
import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
import org.apache.poi.ss.usermodel.Cell;

/**


	public ValueEval evaluate(EvaluationCell srcCell) {
		int sheetIndex = getSheetIndex(srcCell.getSheet());
        return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));
	}

	/**

			if (!tracker.startEvaluate(cce)) {
				return ErrorEval.CIRCULAR_REF_ERROR;
			}
			OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex,
                    columnIndex, tracker, srcCell.isArrayFormula());

			try {


				tracker.endEvaluate(cce);
			}
		} else {
			if (evalListener != null) {
				evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue());
			}
			return cce.getValue();

	// visibility raised for testing
	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {

		Stack<Boolean> stackSkip = new Stack<Boolean>();
		Boolean isSkipActive = Boolean.TRUE;
		Stack<ValueEval> stack = new Stack<ValueEval>();
		for (int i = 0, iSize = ptgs.length; i < iSize; i++) {


				}
				if (attrPtg.isOptimizedChoose()) {
					ValueEval arg0 = stack.pop();
					if (arg0 instanceof ArrayEval && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) {
						stack.push(arg0);
						stackSkip.push(isSkipActive);
						// Switch off skip option only for this level
						isSkipActive = Boolean.FALSE;
						continue;
					}
					ValueEval cond = arg0;
					if (arg0 instanceof ArrayEval) {
						cond = ((ArrayEval)arg0).getValue(0,0);
					}
					int[] jumpTable = attrPtg.getJumpTable();
					int dist;
					int nChoices = jumpTable.length;
					try {
						int switchIndex = Choose.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
						if (switchIndex<1 || switchIndex > nChoices) {
							stack.push(ErrorEval.VALUE_INVALID);
							dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)

				if (attrPtg.isOptimizedIf()) {
					ValueEval arg0 = stack.pop();
					boolean evaluatedPredicate;
					if (arg0 instanceof ArrayEval && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) {
						stack.push(arg0);
						stackSkip.push(isSkipActive);
						// Switch off skip option only for this level
						isSkipActive = new Boolean(false);
						continue;
					}
					ValueEval cond = arg0;
					if (arg0 instanceof ArrayEval) {
						cond = ((ArrayEval)arg0).getValue(0,0);
					}
					try {
						evaluatedPredicate = If.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
					} catch (EvaluationException e) {
						stack.push(e.getErrorEval());
						int dist = attrPtg.getData();

					continue;
				}
				if (attrPtg.isSkip()) {
					if (isSkipActive.booleanValue()) {
						int dist = attrPtg.getData()+1;
						i+= countTokensToBeSkipped(ptgs, i, dist);
						if (stack.peek() == MissingArgEval.instance) {
							stack.pop();
							stack.push(BlankEval.instance);
						}
					}
					continue;
				}

				}
//				logDebug("invoke " + operation + " (nAgs=" + numops + ")");
				opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);
				if (!isSkipActive.booleanValue() && isSkipSensitive(optg)) {
					isSkipActive = stackSkip.pop();
				}
			} else {
				opResult = getEvalForPtg(ptg, ec);
			}

	}

	/**
	 *  Has this function "optimized" form?
	 * @return <code>true</code> if the operation can be optimised with tAttr tokens (if, choose, skip)
	 */
	private boolean isSkipSensitive(OperationPtg optg) {
		if (optg instanceof AbstractFunctionPtg){
			// Skip sensitive is only "optimized" function - just only "IF" and "choose"
			AbstractFunctionPtg fptg = (AbstractFunctionPtg)optg;
			switch (fptg.getFunctionIndex()) {
				case FunctionMetadataRegistry.FUNCTION_INDEX_IF:
				case FunctionMetadataRegistry.FUNCTION_INDEX_CHOOSE:
					return true;
			}
		}
		return false;
	}

	/**
	 * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.
	 *
	 * @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped

		return index-startIndex;
	}
	/**
	 * Dereferences a single value from any ArrayEval evaluation result.
	 * @param evaluationResult
	 * @param cell
	 * @return
	 */
	private static ValueEval dereferenceValue(ArrayEval evaluationResult, EvaluationCell evalCell ) {
		Cell cell = (Cell)evalCell.getIdentityKey();
		return ArrayFormulaEvaluatorHelper.dereferenceValue(evaluationResult,cell);
	}
	/**
	 * Dereferences a single value from any AreaEval or RefEval evaluation result.
	 * If the supplied evaluationResult is just a plain value, it is returned as-is.
	 * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
	 *  <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.
	 */
	public static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, int srcColNum) {
		if (evaluationResult instanceof RefEval) {
			RefEval rv = (RefEval) evaluationResult;
			return rv.getInnerValueEval();

		if (evaluationResult instanceof AreaEval) {
			AreaEval ae = (AreaEval) evaluationResult;
			if (ae.isRow()) {
				if (ae.isColumn()) {
					return ae.getRelativeValue(0, 0);
				}
				return ae.getAbsoluteValue(ae.getFirstRow(), srcColNum);

			AreaPtg aptg = (AreaPtg) ptg;
			return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
		}
		if (ptg instanceof ArrayPtg) {
			return new ArrayEval((ArrayPtg)ptg);
		}
		if (ptg instanceof UnknownPtg) {
			// POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.
			// This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)

	/**
	 * Used by the lazy ref evals whenever they need to get the value of a contained cell.
	 */
	ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,
			int columnIndex, EvaluationTracker tracker) {

		EvaluationCell cell = sheet.getCell(rowIndex, columnIndex);
		ValueEval result = evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker);
		if (result instanceof ArrayEval && cell.isArrayFormula()) {
			result = dereferenceValue((ArrayEval) result, cell);
		}
		return result;
	}
	public FreeRefFunction findUserDefinedFunction(String functionName) {
		return _udfFinder.findFunction(functionName);

Lines 129-132 final class ForkedEvaluationCell implements EvaluationCell { Link Here

(-)a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (+4 lines)
129	public int getColumnIndex() {	129	public int getColumnIndex() {
130	return _masterCell.getColumnIndex();	130	return _masterCell.getColumnIndex();
131	}	131	}
		132
		133	public boolean isArrayFormula() {
		134	return _masterCell.isArrayFormula();
		135	}
132	}	136	}




/* ====================================================================
   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.usermodel;

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.NumberEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.record.formula.functions.Function;
import org.apache.poi.hssf.record.formula.functions.FunctionWithArraySupport;
import org.apache.poi.ss.formula.ArrayEval;
import org.apache.poi.ss.formula.TwoDEval;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Helper class to manipulate with array formula
 * This class contains methods, common for HSSF and XSSF FormulaEvaluator
 * Better solution - to have abstract class for FormulaEvaluator Interface Implementation
 *  All method need to be static
 *
 * @author Vladimirs Abramovs (Vladimirs.Abramovs at exigenservices.com)
 */
public class ArrayFormulaEvaluatorHelper {
	private static final CellValue CELL_VALUE_NA = CellValue.getError(ErrorConstants.ERROR_NA);
	final static int SCALAR_TYPE = 0;
	final static int ARRAY_TYPE = 1;
	private static final ValueEval ERROR_EVAL_NA = ErrorEval.NA;


	private ArrayFormulaEvaluatorHelper () {
		// has no instance
	}


	/** Transform evaluated Array Values according aimed Range
	 *   Depending on dimensions correlation,
	 *   result array may be restricted
	 *	or his single row/column will cloned
	 *	or some cell will set to #N/A
	 * @param cvs
	 * @param range
	 * @return
	 */
	public static ValueEval[][] transformToRange(ValueEval[][] cvs, CellRangeAddress range) {
		return (ValueEval[][]) transformToRange(cvs, range, false);
	}
	/** Transform evaluated Array Values according aimed Range
	 *   Depending on dimensions correlation,
	 *   result array may be restricted
	 *	or his single row/column will cloned
	 *	or some cell will set to #N/A
	 * @param cvs
	 * @param range
	 * @return
	 */
	public static CellValue[][] transformToRange(CellValue[][] cvs, CellRangeAddress range) {
		return (CellValue[][]) transformToRange(cvs, range, true);
	}
	/** Transform evaluated Array Values according aimed Range
	 *   Depending on dimensions correlation,
	 *   result array may be restricted
	 *	or his single row/column will cloned
	 *	or some cell will set to #N/A
	 * @param cvs
	 * @param range
	 * @return
	 */
	private static Object[][] transformToRange(Object[][] cvs, CellRangeAddress range,
			boolean isCellValue) {

		int nRows = range.getLastRow() - range.getFirstRow() + 1;
		int nColumns = range.getLastColumn() - range.getFirstColumn() + 1;
		Object[][] result = isCellValue ? new CellValue[nRows][nColumns] : new ValueEval[nRows][nColumns];
		int rowStart = range.getFirstRow();
		int colStart = range.getFirstColumn();
		for (int i = rowStart; i <= range.getLastRow(); i++) {
			for (int j = colStart; j <= range.getLastColumn(); j++) {
				Object value;
				if (i - rowStart < cvs.length && j - colStart < cvs[i - rowStart].length) {
					value = cvs[i - rowStart][j - colStart];
				} else {
					boolean needClone = false;
					int cloneRow = 0;
					int cloneCol = 0;
					if (cvs.length == 1) { // Need to clone first colm of cvs
						cloneCol = j - colStart;
						needClone = true;
					}
					if (cvs[0].length == 1) { // Need to clone first row of cvs
						cloneRow = i - rowStart;
						needClone = true;
					}
					if (needClone && cloneCol < cvs[0].length && cloneRow < cvs.length) {
						value = cvs[cloneRow][cloneCol];
					} else {
						// For other cases set cell value to #N/A
						// For those cells we changes also their type to Error
						value = isCellValue ? CELL_VALUE_NA : ErrorEval.NA;
					}
				}
				result[i - rowStart][j - colStart] = value;
			}
		}
		return result;
	}

	/**
	 * Convert Eval value to CellValue
	 * @param val
	 * @return
	 */
	public static CellValue evalToCellValue(ValueEval val) {
		if (val instanceof BoolEval) {
			return CellValue.valueOf(((BoolEval)val).getBooleanValue());
		}
		if (val instanceof NumberEval) {
			return  new CellValue(((NumberEval)val).getNumberValue());
		}
		if (val instanceof StringEval) {
			return  new CellValue(((StringEval)val).getStringValue());
		}
		if (val instanceof ErrorEval) {
			return  new CellValue(((ErrorEval)val).getErrorCode());
		}
		throw new IllegalStateException("Unexpected value (" + val + ")");
	}
	/**
	 * Get single value from ArrayEval  for desired cell
	 * @param evaluationResult
	 * @param cell
	 * @return
	 */
	public static ValueEval dereferenceValue(ArrayEval evaluationResult, Cell cell) {
		CellRangeAddress range = cell.getArrayFormulaRange();
		ValueEval[][] rangeVal = ArrayFormulaEvaluatorHelper.transformToRange(evaluationResult.getArrayValues(),range);
		int rowInArray = cell.getRowIndex()- range.getFirstRow();
		int colInArray = cell.getColumnIndex() - range.getFirstColumn();
		return rangeVal[rowInArray][colInArray];
	}
	/**
	 * Get type of parameter (SCALAR_TYPE or ARRAY_TYPE) which support function
	 * for given argument
	 *
	 * @param function
	 * @param argIndex
	 * @return
	 */
	public static int getParameterType(Function function, int argIndex) {

		if (function instanceof FunctionWithArraySupport) {
			// ask new interface(ZS) for argument type
			if (((FunctionWithArraySupport) function).supportArray(argIndex)) {
				return ARRAY_TYPE;
			}
		}
		return SCALAR_TYPE;
	}

	/**
	 * Prepare empty template, which will keep result of evaluation
	 *   A few arguments of function may be array.
	 *   In this case result array will have
	 *   dimension as  such arguments intersection.
	 *   This method calculates result's dimension and prepare empty results
	 *
	 *
	 * @param function
	 * @param args
	 * @param arrayFormula
	 * @return <code>null</code> if function returns a scalar result
	 */
	public static ArrayEval prepareEmptyResult(Function function, ValueEval[] args, boolean arrayFormula) {
		boolean foundArrayArg = false;
		int rowCount = Integer.MIN_VALUE;
		int colCount = Integer.MIN_VALUE;

		for (int i = 0; i < args.length; i++) {
			int argRowCount = Integer.MIN_VALUE;
			int argColCount = Integer.MIN_VALUE;
			if (getParameterType(function, i) == SCALAR_TYPE) {
				ValueEval arg = args[i];
				if (arg instanceof ArrayEval) {
					ArrayEval aa = (ArrayEval) arg;
					argRowCount = aa.getHeight();
					argColCount = aa.getWidth();
				} else if (arg instanceof AreaEval && arrayFormula) {
					AreaEval aa = (AreaEval) arg;
					argRowCount = aa.getHeight();
					argColCount = aa.getWidth();
				} else {
					continue; // Arguments is not array - just skip it
				}
				foundArrayArg = true;
				rowCount = Math.max(rowCount, argRowCount);
				colCount = Math.max(colCount, argColCount);
			}
		}

		if (!foundArrayArg) {
			return null;
		}

		ValueEval[][] emptyArray = new ValueEval[rowCount][colCount];
		return new ArrayEval(emptyArray);
	}

	/**
	 * Prepare arguments for next iteration to call function
	 *  Each argument may be scalar, full array or element(according iteration) of array
	 *
	 * @param function
	 * @param args
	 * @param i
	 * @param j
	 * @return
	 */
	public static ValueEval[] prepareArgsForLoop(Function function, ValueEval[] args, int i, int j) {
		ValueEval[] answer = new ValueEval[args.length];
		for (int argIn = 0; argIn < args.length; argIn++) {
			ValueEval arg = args[argIn];

			if (getParameterType(function, argIn) == SCALAR_TYPE) {
				if (arg instanceof TwoDEval) {
					arg = getArrayValue((TwoDEval) arg, i, j);
				}
			}
			answer[argIn] = arg;
		}

		return answer;
	}

	private static ValueEval getArrayValue(TwoDEval tde, int pRowIndex, int pColIndex) {
		int rowIndex;
		if (pRowIndex >= tde.getHeight()) {
			if (!tde.isRow()) {
				return ERROR_EVAL_NA;
			}
			rowIndex = 0;
		} else {
			rowIndex = pRowIndex;
		}
		int colIndex;
		if (pColIndex >= tde.getWidth()) {
			if (!tde.isColumn()) {
				return ERROR_EVAL_NA;
			}
			colIndex = 0;
		} else {
			colIndex = pColIndex;
		}
		return tde.getValue(rowIndex, colIndex);
	}


	/**
	 * check if ops contain arrays and those should be iterated
	 *
	 * @param function
	 * @param ops
	 * @return
	 */
	public static boolean checkForArrays(Function function, ValueEval[] ops) {

		for (int i = 0; i < ops.length; i++) {
			if ((ops[i] instanceof ArrayEval) && (getParameterType(function, i) == SCALAR_TYPE)) {
				return true;
			}
		}
		return false;
	}
}




import java.util.Date;

import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * High level representation of a cell in a row of a spreadsheet.

     * @param link hypelrink associated with this cell
     */
    void setHyperlink(Hyperlink link);

    /**
     * Only valid for array formula cells
     * @return range of the array formula group that the cell belongs to.
     */
    CellRangeAddress getArrayFormulaRange();

    /**
     * @return <code>true</code> if this cell is part of group of cells having a common array formula.
     */
    boolean isPartOfArrayFormulaGroup();
}




     */
    boolean isSelected();


    /**
     * Sets array formula to specified region for result.
     *
     * @param formula Formula
     * @param range Region of array formula for result.
     */
    void setArrayFormula(String formula, CellRangeAddress range);

    /**
     * Remove a Array Formula from this sheet.  All cells contained in the Array Formula range are removed as well
     *
     * @param cell   any cell within Array Formula range
     */
    void removeArrayFormula(Cell cell);
}





/**
 * See OOO documentation: excelfileformat.pdf sec 2.5.14 - 'Cell Range Address'<p/>
 *
 * Note - {@link SelectionRecord} uses the BIFF5 version of this structure
 * @author Dragos Buleandra (dragos.buleandra@trade2b.ro)
 */

		out.writeShort(getFirstColumn());
		out.writeShort(getLastColumn());
	}

	public CellRangeAddress(RecordInputStream in) {
		super(readUShortAndCheck(in), in.readUShort(), in.readUShort(), in.readUShort());
	}

		return numberOfItems * ENCODED_SIZE;
	}

	public String formatAsString() {
		StringBuffer sb = new StringBuffer();
		CellReference cellRefFrom = new CellReference(getFirstRow(), getFirstColumn());
		CellReference cellRefTo = new CellReference(getLastRow(), getLastColumn());
		sb.append(cellRefFrom.formatAsString());
		sb.append(':');
		sb.append(cellRefTo.formatAsString());
		return sb.toString();
	}

	/**
	 * @param ref usually a standard area ref (e.g. "B1:D8").  May be a single cell
	 *        ref (e.g. "B5") in which case the result is a 1 x 1 cell range.
	 */
	public static CellRangeAddress valueOf(String ref) {
		int sep = ref.indexOf(":");
		CellReference a;
		CellReference b;
		if (sep == -1) {
			a = new CellReference(ref);
			b = a;
		} else {
			a = new CellReference(ref.substring(0, sep));
			b = new CellReference(ref.substring(sep + 1));
		}
		return new CellRangeAddress(a.getRow(), b.getRow(), a.getCol(), b.getCol());
	}
}

Lines 37-42 import org.apache.poi.ss.usermodel.DateUtil; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (-3 / +31 lines)
37	import org.apache.poi.ss.usermodel.FormulaError;	37	import org.apache.poi.ss.usermodel.FormulaError;
38	import org.apache.poi.ss.usermodel.Hyperlink;	38	import org.apache.poi.ss.usermodel.Hyperlink;
39	import org.apache.poi.ss.usermodel.RichTextString;	39	import org.apache.poi.ss.usermodel.RichTextString;
		40	import org.apache.poi.ss.util.CellRangeAddress;
40	import org.apache.poi.ss.util.CellReference;	41	import org.apache.poi.ss.util.CellReference;
41	import org.apache.poi.xssf.model.SharedStringsTable;	42	import org.apache.poi.xssf.model.SharedStringsTable;
42	import org.apache.poi.xssf.model.StylesTable;	43	import org.apache.poi.xssf.model.StylesTable;
Lines 344-350 public final class XSSFCell implements Cell { Link Here
344	if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);	345	if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
345		346
346	CTCellFormula f = _cell.getF();	347	CTCellFormula f = _cell.getF();
347	if(f.getT() == STCellFormulaType.SHARED){	348	if (isPartOfArrayFormulaGroup() && f == null) {
		349	XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
		350	return cell.getCellFormula();
		351	}
		352	if (f.getT() == STCellFormulaType.SHARED) {
348	return convertSharedFormula((int)f.getSi());	353	return convertSharedFormula((int)f.getSi());
349	}	354	}
350	return f.getStringValue();	355	return f.getStringValue();
Lines 371-376 public final class XSSFCell implements Cell { Link Here
371	}	376	}
372		377
373	public void setCellFormula(String formula) {	378	public void setCellFormula(String formula) {
		379	setFormula(formula, FormulaType.CELL);
		380	}
		381
		382	/* package */ void setCellArrayFormula(String formula, CellRangeAddress range) {
		383	setFormula(formula, FormulaType.ARRAY);
		384	CTCellFormula cellFormula = _cell.getF();
		385	cellFormula.setT(STCellFormulaType.ARRAY);
		386	cellFormula.setRef(range.formatAsString());
		387	}
		388
		389	private void setFormula(String formula, int formulaType) {
374	XSSFWorkbook wb = _row.getSheet().getWorkbook();	390	XSSFWorkbook wb = _row.getSheet().getWorkbook();
375	if (formula == null) {	391	if (formula == null) {
376	wb.onDeleteFormula(this);	392	wb.onDeleteFormula(this);
Lines 380-386 public final class XSSFCell implements Cell { Link Here
380		396
381	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);	397	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
382	//validate through the FormulaParser	398	//validate through the FormulaParser
383	FormulaParser.parse(formula, fpb, FormulaType.CELL, wb.getSheetIndex(getSheet()));	399	FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()));
384		400
385	CTCellFormula f = CTCellFormula.Factory.newInstance();	401	CTCellFormula f = CTCellFormula.Factory.newInstance();
386	f.setStringValue(formula);	402	f.setStringValue(formula);
Lines 461-467 public final class XSSFCell implements Cell { Link Here
461	*/	477	*/
462	public int getCellType() {	478	public int getCellType() {
463		479
464	if (_cell.getF() != null) {	480	if (_cell.getF() != null \|\| getSheet().isCellInArrayFormulaContext(this)) {
465	return CELL_TYPE_FORMULA;	481	return CELL_TYPE_FORMULA;
466	}	482	}
467		483
Lines 941-944 public final class XSSFCell implements Cell { Link Here
941	}	957	}
942	throw new IllegalStateException("Unexpected formula result type (" + cellType + ")");	958	throw new IllegalStateException("Unexpected formula result type (" + cellType + ")");
943	}	959	}
		960	public CellRangeAddress getArrayFormulaRange() {
		961	XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
		962	if (cell == null) {
		963	throw new IllegalStateException("not an array formula cell.");
		964	}
		965	String formulaRef = cell._cell.getF().getRef();
		966	return CellRangeAddress.valueOf(formulaRef);
		967	}
		968
		969	public boolean isPartOfArrayFormulaGroup() {
		970	return getSheet().isCellInArrayFormulaContext(this);
		971	}
944	}	972	}

Lines 22-28 import org.apache.poi.ss.formula.EvaluationSheet; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (-1 / +5 lines)
22		22
23	/**	23	/**
24	* XSSF wrapper for a cell under evaluation	24	* XSSF wrapper for a cell under evaluation
25	*	25	*
26	* @author Josh Micich	26	* @author Josh Micich
27	*/	27	*/
28	final class XSSFEvaluationCell implements EvaluationCell {	28	final class XSSFEvaluationCell implements EvaluationCell {
Lines 72-75 final class XSSFEvaluationCell implements EvaluationCell { Link Here
72	public String getStringCellValue() {	72	public String getStringCellValue() {
73	return _cell.getRichStringCellValue().getString();	73	return _cell.getRichStringCellValue().getString();
74	}	74	}
		75
		76	public boolean isArrayFormula() {
		77	return _cell.isPartOfArrayFormulaGroup();
		78	}
75	}	79	}

Lines 25-37 import org.apache.poi.hssf.record.formula.eval.NumberEval; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (-9 / +109 lines)
25	import org.apache.poi.hssf.record.formula.eval.StringEval;	25	import org.apache.poi.hssf.record.formula.eval.StringEval;
26	import org.apache.poi.hssf.record.formula.eval.ValueEval;	26	import org.apache.poi.hssf.record.formula.eval.ValueEval;
27	import org.apache.poi.hssf.record.formula.udf.UDFFinder;	27	import org.apache.poi.hssf.record.formula.udf.UDFFinder;
		28	import org.apache.poi.ss.formula.ArrayEval;
28	import org.apache.poi.ss.formula.IStabilityClassifier;	29	import org.apache.poi.ss.formula.IStabilityClassifier;
29	import org.apache.poi.ss.formula.WorkbookEvaluator;	30	import org.apache.poi.ss.formula.WorkbookEvaluator;
		31	import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
30	import org.apache.poi.ss.usermodel.Cell;	32	import org.apache.poi.ss.usermodel.Cell;
31	import org.apache.poi.ss.usermodel.CellValue;	33	import org.apache.poi.ss.usermodel.CellValue;
32	import org.apache.poi.ss.usermodel.FormulaEvaluator;	34	import org.apache.poi.ss.usermodel.FormulaEvaluator;
33	import org.apache.poi.ss.usermodel.Row;	35	import org.apache.poi.ss.usermodel.Row;
34	import org.apache.poi.ss.usermodel.Sheet;	36	import org.apache.poi.ss.usermodel.Sheet;
		37	import org.apache.poi.ss.util.CellRangeAddress;
35		38
36	/**	39	/**
37	* Evaluates formula cells.<p/>	40	* Evaluates formula cells.<p/>
Lines 42-47 import org.apache.poi.ss.usermodel.Sheet; Link Here
42	*	45	*
43	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >	46	* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
44	* @author Josh Micich	47	* @author Josh Micich
		48	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - Array Formula support
		49	* @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support
45	*/	50	*/
46	public class XSSFFormulaEvaluator implements FormulaEvaluator {	51	public class XSSFFormulaEvaluator implements FormulaEvaluator {
47		52
Lines 54-63 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
54	* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>	59	* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
55	* for the (conservative) assumption that any cell may have its definition changed after	60	* for the (conservative) assumption that any cell may have its definition changed after
56	* evaluation begins.	61	* evaluation begins.
57	* @deprecated (Sep 2009) (reduce overloading) use {@link #create(XSSFWorkbook, org.apache.poi.ss.formula.IStabilityClassifier, org.apache.poi.hssf.record.formula.udf.UDFFinder)}	62	* @deprecated (Sep 2009) (reduce overloading) use {@link #create(XSSFWorkbook, org.apache.poi.ss.formula.IStabilityClassifier, org.apache.poi.hssf.record.formula.udf.UDFFinder)}
58	*/	63	*/
59	@Deprecated	64	@Deprecated
60	public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) {	65	public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) {
61	_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, null);	66	_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, null);
62	}	67	}
63	private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {	68	private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {
Lines 142-150 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
142	if (cell == null \|\| cell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {	147	if (cell == null \|\| cell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
143	return -1;	148	return -1;
144	}	149	}
145	CellValue cv = evaluateFormulaCellValue(cell);
146	// cell remains a formula cell, but the cached value is changed	150	// cell remains a formula cell, but the cached value is changed
147	setCellValue(cell, cv);	151	CellValue cv;
		152	if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context
		153	CellValue[][] cvs = evaluateFormulaCellArrayValues((XSSFCell) cell);
		154	CellValue[][] values = setCellValues(cell, cvs);
		155	int rowIndex = cell.getRowIndex() - cell.getArrayFormulaRange().getFirstRow();
		156	int colIndex = cell.getColumnIndex() - cell.getArrayFormulaRange().getFirstColumn();
		157	cv = values[rowIndex][colIndex];
		158	} else { // Single Formula
		159	cv = evaluateFormulaCellValue(cell);
		160	setCellValue(cell, cv);
		161	}
148	return cv.getCellType();	162	return cv.getCellType();
149	}	163	}
150		164
Lines 170-178 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
170	}	184	}
171	XSSFCell result = (XSSFCell) cell;	185	XSSFCell result = (XSSFCell) cell;
172	if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {	186	if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
173	CellValue cv = evaluateFormulaCellValue(cell);	187	if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context
174	setCellType(cell, cv); // cell will no longer be a formula cell	188	CellValue[][] cvs = evaluateFormulaCellArrayValues((XSSFCell) cell);
175	setCellValue(cell, cv);	189	setCellsTypes(cell, cvs); // cells will no longer be a formula cell
		190	setCellValues(cell, cvs);
		191
		192	} else { // Single Formula
		193	CellValue cv = evaluateFormulaCellValue(cell);
		194	setCellType(cell, cv); // cell will no longer be a formula cell
		195	setCellValue(cell, cv);
		196
		197	}
176	}	198	}
177	return result;	199	return result;
178	}	200	}
Lines 217-222 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
217	}	239	}
218	}	240	}
219		241
		242	private void setCellsTypes(Cell cell, CellValue[][] cvs) {
		243	CellRangeAddress range = cell.getArrayFormulaRange();
		244	int rowStart = range.getFirstRow();
		245	int colStart = range.getFirstColumn();
		246	Sheet sheet = cell.getSheet();
		247	for (int i = rowStart; i <= range.getLastRow(); i++) {
		248	for (int j = colStart; j <= range.getLastColumn(); j++) {
		249	Row row = sheet.getRow(i);
		250	Cell c = row.getCell(j);
		251	if ((i - rowStart) < cvs.length && (j - colStart) < cvs[i - rowStart].length) {
		252	setCellType(c, cvs[i - rowStart][j - colStart]);
		253	}
		254	}
		255	}
		256	}
		257
		258	/**
		259	* Set value in Range
		260	*
		261	* @param cell
		262	* @param cvs
		263	* @return
		264	*/
		265	private CellValue[][] setCellValues(Cell cell, CellValue[][] cvs) {
		266	CellRangeAddress range = cell.getArrayFormulaRange();
		267	int rowStart = range.getFirstRow();
		268	int colStart = range.getFirstColumn();
		269	Sheet sheet = cell.getSheet();
		270	CellValue[][] answer = ArrayFormulaEvaluatorHelper.transformToRange(cvs, range);
		271	for (int i = rowStart; i <= range.getLastRow(); i++) {
		272	for (int j = colStart; j <= range.getLastColumn(); j++) {
		273	Row row = sheet.getRow(i);
		274	if (row == null) {
		275	row = sheet.createRow(i);
		276	}
		277	Cell c = row.getCell(j);
		278	if (c == null) {
		279	c = row.createCell(j);
		280	}
		281	CellValue cellValue = answer[i - rowStart][j - colStart];
		282	setCellValue(c, cellValue);
		283	}
		284	}
		285	return answer;
		286	}
		287
220	/**	288	/**
221	* Loops over all cells in all sheets of the supplied	289	* Loops over all cells in all sheets of the supplied
222	* workbook.	290	* workbook.
Lines 238-245 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
238		306
239	for (Iterator cit = r.cellIterator(); cit.hasNext();) {	307	for (Iterator cit = r.cellIterator(); cit.hasNext();) {
240	XSSFCell c = (XSSFCell) cit.next();	308	XSSFCell c = (XSSFCell) cit.next();
241	if (c.getCellType() == XSSFCell.CELL_TYPE_FORMULA)	309	if (c.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
242	evaluator.evaluateFormulaCell(c);	310	evaluator.evaluateFormulaCell(c);
		311	}
243	}	312	}
244	}	313	}
245	}	314	}
Lines 250-255 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
250	*/	319	*/
251	private CellValue evaluateFormulaCellValue(Cell cell) {	320	private CellValue evaluateFormulaCellValue(Cell cell) {
252	ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell((XSSFCell) cell));	321	ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell((XSSFCell) cell));
		322	if (eval instanceof ArrayEval) {// support of arrays
		323	if (cell.isPartOfArrayFormulaGroup()) {
		324	eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell);
		325	} else {
		326	eval = ((ArrayEval) eval).getValue(0, 0);
		327	}
		328	}
253	if (eval instanceof NumberEval) {	329	if (eval instanceof NumberEval) {
254	NumberEval ne = (NumberEval) eval;	330	NumberEval ne = (NumberEval) eval;
255	return new CellValue(ne.getNumberValue());	331	return new CellValue(ne.getNumberValue());
Lines 267-270 public class XSSFFormulaEvaluator implements FormulaEvaluator { Link Here
267	}	343	}
268	throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");	344	throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
269	}	345	}
		346
		347	/**
		348	* Returns a Array CellValue wrapper around the supplied ArrayEval instance.
		349	*/
		350	private CellValue[][] evaluateFormulaCellArrayValues(XSSFCell cell) {
		351	ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell(cell));
		352	if (eval instanceof ArrayEval) {// support of arrays
		353	ArrayEval ae = (ArrayEval) eval;
		354	int rowCount = ae.getHeight();
		355	int colCount = ae.getWidth();
		356	CellValue[][] answer = new CellValue[rowCount][colCount];
		357	for (int i = 0; i < rowCount; i++) {
		358	for (int j = 0; j < colCount; j++) {
		359	ValueEval val = ae.getValue(i, j);
		360	answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val);
		361	}
		362	}
		363	return answer;
		364	}
		365	// non-array (usually from aggregate function)
		366	CellValue[][] answer = new CellValue[1][1];
		367	answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval);
		368	return answer;
		369	}
270	}	370	}

Lines 79-84 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (-1 / +56 lines)
79	private ColumnHelper columnHelper;	79	private ColumnHelper columnHelper;
80	private CommentsTable sheetComments;	80	private CommentsTable sheetComments;
81	private Map<Integer, XSSFCell> sharedFormulas;	81	private Map<Integer, XSSFCell> sharedFormulas;
		82	private List<CellRangeAddress> arrayFormulas;
82		83
83	/**	84	/**
84	* Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.	85	* Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.
Lines 153-158 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
153	private void initRows(CTWorksheet worksheet) {	154	private void initRows(CTWorksheet worksheet) {
154	rows = new TreeMap<Integer, XSSFRow>();	155	rows = new TreeMap<Integer, XSSFRow>();
155	sharedFormulas = new HashMap<Integer, XSSFCell>();	156	sharedFormulas = new HashMap<Integer, XSSFCell>();
		157	arrayFormulas = new ArrayList<CellRangeAddress>();
156	for (CTRow row : worksheet.getSheetData().getRowArray()) {	158	for (CTRow row : worksheet.getSheetData().getRowArray()) {
157	XSSFRow r = new XSSFRow(row, this);	159	XSSFRow r = new XSSFRow(row, this);
158	rows.put(r.getRowNum(), r);	160	rows.put(r.getRowNum(), r);
Lines 2316-2324 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2316	//collect cells holding shared formulas	2318	//collect cells holding shared formulas
2317	CTCell ct = cell.getCTCell();	2319	CTCell ct = cell.getCTCell();
2318	CTCellFormula f = ct.getF();	2320	CTCellFormula f = ct.getF();
2319	if(f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null){	2321	if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) {
2320	sharedFormulas.put((int)f.getSi(), cell);	2322	sharedFormulas.put((int)f.getSi(), cell);
2321	}	2323	}
		2324	if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) {
		2325	arrayFormulas.add(CellRangeAddress.valueOf(f.getRef()));
		2326	}
2322	}	2327	}
2323		2328
2324	@Override	2329	@Override
Lines 2676-2679 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2676	private boolean sheetProtectionEnabled() {	2681	private boolean sheetProtectionEnabled() {
2677	return worksheet.getSheetProtection().getSheet();	2682	return worksheet.getSheetProtection().getSheet();
2678	}	2683	}
		2684
		2685	/* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) {
		2686	for (CellRangeAddress range : arrayFormulas) {
		2687	if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
		2688	return true;
		2689	}
		2690	}
		2691	return false;
		2692	}
		2693
		2694	/* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) {
		2695	for (CellRangeAddress range : arrayFormulas) {
		2696	if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
		2697	return getRow(range.getFirstRow()).getCell(range.getFirstColumn());
		2698	}
		2699	}
		2700	return null;
		2701	}
		2702
		2703	public void setArrayFormula(String formula, CellRangeAddress range) {
		2704	XSSFRow row = getRow(range.getFirstRow());
		2705	if (row == null) {
		2706	row = createRow(range.getFirstRow());
		2707	}
		2708	XSSFCell mainArrayFormulaCell = row.getCell(range.getFirstColumn());
		2709	if (mainArrayFormulaCell == null) {
		2710	mainArrayFormulaCell = row.createCell(range.getFirstColumn());
		2711	}
		2712	mainArrayFormulaCell.setCellArrayFormula(formula, range);
		2713	arrayFormulas.add(range);
		2714	}
		2715
		2716	public void removeArrayFormula(Cell cell) {
		2717	for (CellRangeAddress range : arrayFormulas) {
		2718	if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
		2719	arrayFormulas.remove(range);
		2720	for (int rowIndex = range.getFirstRow(); rowIndex <= range.getLastRow(); rowIndex++) {
		2721	XSSFRow row = getRow(rowIndex);
		2722	for (int columnIndex = range.getFirstColumn(); columnIndex <= range.getLastColumn(); columnIndex++) {
		2723	XSSFCell arrayFormulaCell = row.getCell(columnIndex);
		2724	if (arrayFormulaCell != null) {
		2725	arrayFormulaCell.setCellType(Cell.CELL_TYPE_BLANK);
		2726	}
		2727	}
		2728	}
		2729	return;
		2730	}
		2731	}
		2732	throw new RuntimeException("Cell does not belong to Array Formula");
		2733	}
2679	}	2734	}




/* ====================================================================
   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.xssf.usermodel;

import org.apache.poi.ss.usermodel.BaseTestAddRemoveArrayFormula;
import org.apache.poi.xssf.XSSFITestDataProvider;


public final class TestXSSFAddRemoveArrayFormula extends BaseTestAddRemoveArrayFormula {

	public TestXSSFAddRemoveArrayFormula() {
		super(XSSFITestDataProvider.getInstance(), "ArrayFormula.xlsx");
	}
}




/* ====================================================================
   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.xssf.usermodel;

import org.apache.poi.ss.formula.eval.BaseTestArrayEvaluation;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestXSSFArrayEvaluation extends BaseTestArrayEvaluation {

	private static ArrayFormulaTestHelper th;

	protected ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ConstantArray.xlsx");
		}
		return th;
	}
}




/* ====================================================================
   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.xssf.usermodel;

import org.apache.poi.ss.formula.eval.BaseTestArrayFormulaEvaluation;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestXSSFArrayFormulaEvaluation extends BaseTestArrayFormulaEvaluation {

	private static ArrayFormulaTestHelper th;

	protected ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ArrayFormula.xlsx");
		}
		return th;
	}
}




/* ====================================================================
   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.xssf.usermodel;

import org.apache.poi.ss.formula.eval.BaseTestArrayFormulaFunction;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestXSSFArrayFormulaFunction extends BaseTestArrayFormulaFunction {

	private static ArrayFormulaTestHelper th;

	protected ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ArrayFormulaFunctions.xlsx");
		}
		return th;
	}
}




/* ====================================================================
   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.hssf.record.formula.eval;

import junit.framework.AssertionFailedError;
import junit.framework.TestCase;

import org.apache.poi.ss.formula.ArrayEval;

/**
 * Tests for {@link ArrayEval}
 *
 * @author Josh Micich
 */
public final class TestArrayEval extends TestCase {

	public void testToString() {

		ValueEval x = NumberEval.ZERO;

		ValueEval[][] values = {
				{ x, x }, { x, x },
		};
		ArrayEval ae = new ArrayEval(values);

		try {
			ae.toString();
		} catch (ArrayIndexOutOfBoundsException e) {
			throw new AssertionFailedError("Identified bug in toString");
		}
	}
}




/* ====================================================================
   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.hssf.record.formula.eval;

import junit.framework.TestCase;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.ErrorConstants;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author Josh Micich
 */
public class TestArrayEvaluationExtra extends TestCase {
	private static boolean FIXED = false;

	// simple case already works
	public void testArraysInSimpleNonArrayFormula() {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("Sheet1");

		Row row1 = sheet.createRow(0);
		Cell cellA1 = row1.createCell(0);
		Cell cellB1 = row1.createCell(1);
		Cell cellC1 = row1.createCell(2);

		FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

		String formula = "{3,4}+{0.2,0.1,0.4}";

		cellA1.setCellFormula(formula);
		assertEquals(formula, cellA1.getCellFormula());
		confirmEvaluate(cellA1, fe, 3.2);

		sheet.setArrayFormula(formula, CellRangeAddress.valueOf("A1:C1"));
		confirmEvaluate(cellA1, fe, 3.2);
		confirmEvaluate(cellB1, fe, 4.1);

		CellValue cv;
		fe.clearAllCachedResultValues();
		cv = fe.evaluate(cellC1);
		assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
		assertEquals(cv.getErrorValue(), ErrorConstants.ERROR_NA);
	}


	public void testArraysInNonArrayFormula() {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("Sheet1");
		Row row149 = sheet.createRow(148);
		Row row150 = sheet.createRow(149);
		row149.createCell(0).setCellValue(1.0);
		row149.createCell(1).setCellValue(2.0);
		row150.createCell(0).setCellValue(3.0);
		row150.createCell(1).setCellValue(4.0);

		Row row1 = sheet.createRow(0);
		Cell cellD1 = row1.createCell(3);
		Cell cellE1 = row1.createCell(4);
		Cell cellD2 = sheet.createRow(1).createCell(3);
		CellRangeAddress colRange = CellRangeAddress.valueOf("D1:D2");
		CellRangeAddress rowRange = CellRangeAddress.valueOf("D1:E2");

		FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

		// Note: literal arrays are firstly row arrays, then column arrays
		String rowFormula = "SUM(OFFSET(A149:B150,{1,0},{1,0},{1,1},{1,1}))";
		String colFormula = "SUM(OFFSET(A149:B150,{1;0},{1;0},{1;1},{1;1}))";

		cellD1.setCellFormula(rowFormula);
		assertEquals(rowFormula, cellD1.getCellFormula());
		if (FIXED) {
			confirmEvaluate(cellD1, fe, 4.0);
		}

		sheet.setArrayFormula(rowFormula, colRange);
		if (FIXED) {
			confirmEvaluate(cellD1, fe, 4.0);
		}
		if (FIXED) {
			confirmEvaluate(cellD2, fe, 4.0);
		}
		sheet.removeArrayFormula(cellD1);

		sheet.setArrayFormula(rowFormula, rowRange);
		if (FIXED) {
			confirmEvaluate(cellD1, fe, 4.0);
		}
		if (FIXED) {
			confirmEvaluate(cellE1, fe, 1.0);
		}
		sheet.removeArrayFormula(cellD1);


		sheet.setArrayFormula(colFormula, colRange);
		if (FIXED) {
			confirmEvaluate(cellD1, fe, 4.0);
		}
		if (FIXED) {
			confirmEvaluate(cellD2, fe, 1.0);
		}
		sheet.removeArrayFormula(cellD1);
	}

	public void testSumStringElement() {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("Sheet1");

		Row row1 = sheet.createRow(0);
		Cell cellA1 = row1.createCell(0);

		FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

		String formula = "SUM({3,\"4\"})";

		cellA1.setCellFormula(formula);
		assertEquals(formula, cellA1.getCellFormula());
		confirmEvaluate(cellA1, fe, 3.0);
	}

	private void confirmEvaluate(Cell cell, FormulaEvaluator fe, double expectedResult) {
		CellValue cv;
		fe.clearAllCachedResultValues();
		cv = fe.evaluate(cell);
		assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
		assertEquals(expectedResult, cv.getNumberValue(), 0.0);
	}
}




/* ====================================================================
   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.hssf.record.formula.eval;

import org.apache.poi.ss.formula.eval.BaseTestArrayEvaluation;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestHSSFArrayEvaluation extends BaseTestArrayEvaluation{

	private static ArrayFormulaTestHelper th;

	protected ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ConstantArray.xls");
		}
		return th;
	}
}




/* ====================================================================
   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.hssf.record.formula.eval;

import org.apache.poi.ss.formula.eval.BaseTestArrayFormulaEvaluation;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestHSSFArrayFormulaEvaluation extends BaseTestArrayFormulaEvaluation{

	private static ArrayFormulaTestHelper th;

	protected ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ArrayFormula.xls");
		}
		return th;
	}
}




/* ====================================================================
   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.hssf.record.formula.eval;

import org.apache.poi.ss.formula.eval.BaseTestArrayFormulaFunction;
import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

public final class TestHSSFArrayFormulaFunction extends BaseTestArrayFormulaFunction {

	private static ArrayFormulaTestHelper th;

	public ArrayFormulaTestHelper th() {
		if (th == null) {
			th = new ArrayFormulaTestHelper("ArrayFormulaFunctions.xls");
		}
		return th;
	}
}





/**
 * Collects all tests for the <tt>org.apache.poi.hssf.usermodel</tt> package.
 *
 * @author Josh Micich
 */
public class AllUserModelTests {

	public static Test suite() {
		TestSuite result = new TestSuite(AllUserModelTests.class.getName());

		result.addTestSuite(TestBugs.class);
		result.addTestSuite(TestCellStyle.class);
		result.addTestSuite(TestCloneSheet.class);

		result.addTestSuite(TestFormulas.class);
		result.addTestSuite(TestFormulaEvaluatorBugs.class);
		result.addTestSuite(TestFormulaEvaluatorDocs.class);
		result.addTestSuite(TestHSSFAddRemoveArrayFormula.class);
		result.addTestSuite(TestHSSFCell.class);
		result.addTestSuite(TestHSSFClientAnchor.class);
		result.addTestSuite(TestHSSFComment.class);

		}
		result.addTestSuite(TestUnicodeWorkbook.class);
		result.addTestSuite(TestUppercaseWorkbook.class);
		result.addTestSuite(TestWorkbook.class);

		return result;
	}
}




/* ====================================================================
   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.hssf.usermodel;

import org.apache.poi.hssf.HSSFITestDataProvider;
import org.apache.poi.ss.usermodel.BaseTestAddRemoveArrayFormula;

public final class TestHSSFAddRemoveArrayFormula extends BaseTestAddRemoveArrayFormula {

	public TestHSSFAddRemoveArrayFormula() {
		super(HSSFITestDataProvider.getInstance(), "ArrayFormula.xls");
	}
}

Lines 48-54 import org.apache.poi.ss.usermodel.CellValue; Link Here

(-)a/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (-2 / +2 lines)
48	public class TestWorkbookEvaluator extends TestCase {	48	public class TestWorkbookEvaluator extends TestCase {
49		49
50	private static ValueEval evaluateFormula(Ptg[] ptgs) {	50	private static ValueEval evaluateFormula(Ptg[] ptgs) {
51	OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);	51	OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null, false);
52	return new WorkbookEvaluator(null, null, null).evaluateFormula(ec, ptgs);	52	return new WorkbookEvaluator(null, null, null).evaluateFormula(ec, ptgs);
53	}	53	}
54		54
Lines 193-199 public class TestWorkbookEvaluator extends TestCase { Link Here
193	assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());	193	assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
194	// adding blank to "abc" gives "abc"	194	// adding blank to "abc" gives "abc"
195	assertEquals("abc", cv.getStringValue());	195	assertEquals("abc", cv.getStringValue());
196		196
197	// check CHOOSE()	197	// check CHOOSE()
198	cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");	198	cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
199	fe.notifySetFormula(cell);	199	fe.notifySetFormula(cell);




/* ====================================================================
   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.formula.eval;

import junit.framework.TestCase;

import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;
import org.apache.poi.ss.usermodel.FormulaError;

public abstract class BaseTestArrayEvaluation extends TestCase {

	protected abstract ArrayFormulaTestHelper th();

	public final void testSingleArg() {
		confirmNumeric(5);
	}

	public final void testSingleArgOnArray() {
		confirmNumeric(6);
	}

	public final void testTwoArgFirstArray() {
		confirmNumeric(9);
	}

	public final void testTwoArgTwoArrays() {
		confirmNumeric(10);
	}

	public final void testTwoArgFirstArrayResult() {
		confirmNumeric(11);
	}

	public final void testAggregateOnArray() {
		confirmNumeric(13);
	}

	public final void testAggregateOnTwoArrays() {
		confirmNumeric(14);
	}

	public final void testAggregateWithSpecialLast() {
		confirmNumeric(15);
	}

	public final void testAggregateWithSpecialLastAsArray() {
		confirmNumeric(16);
	}

	public final void testAggregateOnArrayFromSingle() {
		confirmNumeric(19);
	}

	public final void testAggregateOnTwoDimArrayFromSingle() {
		confirmNumeric(20);
	}

	public final void testAggregateOnArrayFromTwoArg() {
		confirmNumeric(21);
	}

	public final void testAggregateOnNonProperArray() {
		confirmError(22, FormulaError.NA);
	}

	public final void testAggregateOnTwoArg() {
		confirmNumeric(23);
	}
	public final void testAggregateOnTwoArgWithLastSpecial() {
		confirmNumeric(24);
	}

	public final void testAggregateOnNonProperArrayViaSingle() {
		confirmError(25,  FormulaError.NA);
	}

	public final void testSimpleNumericOneArg() {
		confirmNumeric(27);
	}

	public final void testSimpleNumericTwoArg() {
		confirmNumeric(28);
	}

	public final void testSimpleAgregate() {

		confirmNumeric(29);
	}

	public final void testSimpleAgregateWithLast() {
		confirmNumeric(30);
	}

	public final void testSimpleTextOnNumeric() {
		confirmNumeric(43);
	}

	public final void testSingleArgTextReturnNum() {
		confirmNumeric(44);
	}

	public final void testSingleArgOnArrayFromNum() {
		confirmNumeric(45);
	}
		public void NumericAggregateOnArrayFromTextSingleArg() {

		confirmNumeric(46);
	}
	public final void testNumericAggregateSigleArgtTextSingleArgTextArray() {

		confirmNumeric(47);
	}

	public final void testSimpleMultiArgText() {
		confirmString(48);
	}

	public final void testSimpleMultiArgTextOneArray() {
		confirmString(49);
	}

	public final void testSimpleMultiArgTextTwoArraysSizeMatch() {
		confirmString(50);
	}

	public final void testSimpleMultiArgTextTwoArraysSizeNotMatch() {
		confirmString(51);
	}

	public final void testNumericAggregateOnTextArraySizeMatch() {
		confirmNumeric(52);
	}

	public final void testAggregateOnTextArraySizenotMatch() {
		confirmError(53,  FormulaError.NA);
	}

	public final void testSimpleXY() {
		confirmNumeric(60);
	}

	public final void testXYonArray() {
		confirmNumeric(61);
	}

	public final void testXYonArrayFromFunction() {
		confirmNumeric(62);
	}

	public final void testXYonWrongArray() {
		confirmError(63, FormulaError.NA);
	}

	public final void testAndTrue() {
		confirmBoolean(73);
	}

	public final void testAndFalse() {
		confirmBoolean(74);
	}

	public final void testOrTrue() {
		confirmBoolean(75);
	}

	public final void testOrFalse() {
		confirmBoolean(76);
	}

	public final void testChoose() {
		confirmNumeric(82);
	}

	public final void testChooseAggregate() {
		confirmNumeric(83);
	}

	public final void testVlookupArrayAsTable() {
		confirmNumeric(88);
	}

	public final void testVlookupAllArrays() {
		confirmNumeric(89);
	}

	public final void testHlookupArrayAsTable() {
		confirmNumeric(95);
	}

	public final void testHlookupAllArrays() {
		confirmNumeric(96);
	}

	public final void testLookupArray() {
		confirmNumeric(101);
	}

	public final void testLookupAllArrays() {
		confirmNumeric(102);
	}

	public final void testLookupAggregateArrays() {
		confirmNumeric(103);
	}

	public final void testCountArray() {
		confirmNumeric(108);
	}

	public final void testCountaArray() {
		confirmNumeric(112);
	}

	public final void testCountifArray() {
		confirmNumeric(117);
	}

	public final void testCountifArrayAggregate() {
		confirmNumeric(118);
	}

	public final void testSumifArray() {
		confirmNumeric(124);
	}

	public final void testSumifArrayAggregate() {
		confirmNumeric(125);
	}

	public final void testSumproductArray() {
		confirmNumeric(130);
	}

	public final void testColumnsArray() {
		confirmNumeric(136);
	}

	public final void testMatchArray() {
		confirmNumeric(140);
	}

	public final void testMatchArrayAggregate() {
		confirmNumeric(141);
	}

	public final void testModeArray() {
		confirmNumeric(145);
	}

	public final void testOffsetArray() {
		confirmNumeric(149);
	}

	public final void testRowsArray() {
		confirmNumeric(154);
	}

	public final void testIndexArray() {
		confirmNumeric(160);
	}

	public final void testIndexArrayEntireColumn() {
		confirmNumeric(161);
	}

	public final void testIndexArrayEntireColumnAggregate() {
		confirmNumeric(162);
	}
	private void confirmError(int rowNum, FormulaError err) {
		String formulaCell = "C" + rowNum;
		ArrayFormulaTestHelper th = th();
		FormulaError fe = th.calculateNumericFormulaWithError(formulaCell);
		assertEquals(formulaCell, fe.getCode(), err.getCode());
	}
	private void confirmNumeric(int rowNum) {
		String formulaCell = "C" + rowNum;
		String expResCell = "D" + rowNum;
		ArrayFormulaTestHelper th = th();
		double actResult = th.calculateNumericFormula(formulaCell);
		assertEquals(expResCell + "-" + formulaCell, th.getNumericValue(expResCell), actResult, 0.0);
	}
	private void confirmString(int rowNum) {
		String formulaCell = "C" + rowNum;
		String expResCell = "D" + rowNum;
		ArrayFormulaTestHelper th = th();
		String actResult = th.calculateStringFormula(formulaCell);
		assertEquals(expResCell + "-" + formulaCell, th.getStringValue(expResCell), actResult);
	}
	private void confirmBoolean(int rowNum) {
		String formulaCell = "C" + rowNum;
		String expResCell = "D" + rowNum;
		ArrayFormulaTestHelper th = th();
		boolean actResult = th.calculateBooleanFormula(formulaCell);
		assertEquals(expResCell + "-" + formulaCell, th.getBooleanValue(expResCell), actResult);
	}
}




/* ====================================================================
   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.formula.eval;

import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

import junit.framework.TestCase;

public abstract class BaseTestArrayFormulaEvaluation extends TestCase {

	protected abstract ArrayFormulaTestHelper th();
	private ArrayFormulaTestHelper _th;

	protected void setUp() {
		_th = th();
	}

	public final void testNumericSquareArrayFormula() {
		// Clean cell's values before calculation
		_th.setNumericValue("C4", 0);
		_th.setNumericValue("D4", 0);
		_th.setNumericValue("C5", 0);
		_th.setNumericValue("D5", 0);

		assertEquals("C4-F4",_th.getNumericValue("F4"), _th.calculateNumericFormula("C4"), 0);
		assertEquals("D4-G4",_th.getNumericValue("G4"), _th.calculateNumericFormula("D4"), 0);
		assertEquals("C5-F5",_th.getNumericValue("F5"), _th.calculateNumericFormula("C5"), 0);
		assertEquals("D5-G5",_th.getNumericValue("G5"), _th.calculateNumericFormula("D5"), 0);
	}

	public final void testNumericArrayFormulaWORange() {

		// Clean cell's values before calculation
		_th.setNumericValue("C7", 0);

		assertEquals("C7-F7",_th.getNumericValue("F7"), _th.calculateNumericFormula("C7"), 0);
	}

	public final void testNumericArrayFormulaFullRow() {

		// Clean cell's values before calculation
		_th.setNumericValue("A9", 0);
		_th.setNumericValue("B9", 0);
		_th.setNumericValue("C9", 0);
		assertEquals("A9-F9",_th.getNumericValue("F9"), _th.calculateNumericFormula("A9"), 0);
		assertEquals("B9-G9",_th.getNumericValue("G9"), _th.calculateNumericFormula("B9"), 0);
		assertEquals("C9-H9",_th.getNumericValue("H9"), _th.calculateNumericFormula("C9"), 0);
	}

	public final void testNumericArrayFormulaSmallRow() {

		// Clean cell's values before calculation
		_th.setNumericValue("A11", 0);
		_th.setNumericValue("B11", 0);
		assertEquals("A11-F11",_th.getNumericValue("F11"), _th.calculateNumericFormula("A11"), 0);
		assertEquals("B11-G11",_th.getNumericValue("G11"), _th.calculateNumericFormula("B11"), 0);
	}

	public final void testNumericArrayFormulaBigRow() {

		// Clean cell's values before calculation
		_th.setNumericValue("A13", 0);
		_th.setNumericValue("B13", 0);
		_th.setNumericValue("C13", 0);

		assertEquals("A13-F13",_th.getNumericValue("F13"), _th.calculateNumericFormula("A13"), 0);
		assertEquals("B13-G13",_th.getNumericValue("G13"), _th.calculateNumericFormula("B13"), 0);
		assertEquals("C13-H13",_th.getNumericValue("H13"), _th.calculateNumericFormula("C13"), 0);
		assertEquals("D3-I13",_th.getErrorValue("I13"), _th.calculateNumericFormulaWithError("D13").getString());
	}

	public final void testNumericArrayFormulaFewerRows() {

		// Clean cell's values before calculation
		_th.setNumericValue("A16", 0);
		_th.setNumericValue("B16", 0);
		_th.setNumericValue("A17", 0);
		_th.setNumericValue("B17", 0);

		assertEquals("A16-F16",_th.getNumericValue("F16"), _th.calculateNumericFormula("A16"), 0);
		assertEquals("B16-G16",_th.getNumericValue("G16"), _th.calculateNumericFormula("B16"), 0);
		assertEquals("A17-F17",_th.getNumericValue("F17"), _th.calculateNumericFormula("A17"), 0);
		assertEquals("B17-G17",_th.getNumericValue("G17"), _th.calculateNumericFormula("B17"), 0);
	}

	public final void testNumericArrayFormulaDataExceed() {
		// Clean cell's values before calculation
		_th.setNumericValue("A19", 0);
		_th.setNumericValue("B19", 0);
		_th.setNumericValue("A20", 0);
		_th.setNumericValue("B20", 0);

		assertEquals("A19-F19",_th.getNumericValue("F19"), _th.calculateNumericFormula("A19"), 0);
		assertEquals("B19-G19",_th.getNumericValue("G19"), _th.calculateNumericFormula("B19"), 0);
		assertEquals("A20-F20",_th.getNumericValue("F20"), _th.calculateNumericFormula("A20"), 0);
		assertEquals("B20-G20",_th.getNumericValue("G20"), _th.calculateNumericFormula("B20"), 0);
	}

	public final void testNumericArrayFormulaCol4Row() {

		// Clean cell's values before calculation
		_th.setNumericValue("A22", 0);
		_th.setNumericValue("A23", 0);
		_th.setNumericValue("A24", 0);
		_th.setNumericValue("A25", 0);

		assertEquals("A22-F22",_th.getNumericValue("F22"), _th.calculateNumericFormula("A22"), 0);
		assertEquals("A23-F23",_th.getNumericValue("F23"), _th.calculateNumericFormula("A23"), 0);
		assertEquals("A24-F24",_th.getNumericValue("F24"), _th.calculateNumericFormula("A24"), 0);
		assertEquals("A25-F25",_th.getNumericValue("F25"), _th.calculateNumericFormula("A25"), 0);
	}

	public final void testNumericArrayFormulaRow4Col() {

		// Clean cell's values before calculation
		_th.setNumericValue("A27", 0);
		_th.setNumericValue("B27", 0);
		_th.setNumericValue("C27", 0);
		_th.setNumericValue("D27", 0);

		assertEquals("A27-F27",_th.getNumericValue("F27"), _th.calculateNumericFormula("A27"), 0);
		assertEquals("B27-G27",_th.getNumericValue("G27"), _th.calculateNumericFormula("B27"), 0);
		assertEquals("C27-H27",_th.getNumericValue("H27"), _th.calculateNumericFormula("C27"), 0);
		assertEquals("D27-I27",_th.getNumericValue("I27"), _th.calculateNumericFormula("D27"), 0);
	}

	public final void testNumericArrayFormulaDataRow4Col() {

		// Clean cell's values before calculation
		_th.setNumericValue("A27", 0);
		_th.setNumericValue("B27", 0);
		_th.setNumericValue("C27", 0);
		_th.setNumericValue("D27", 0);

		assertEquals("A27-F27",_th.getNumericValue("F27"), _th.calculateNumericFormula("A27"), 0);
		assertEquals("B27-G27",_th.getNumericValue("G27"), _th.calculateNumericFormula("B27"), 0);
		assertEquals("C27-H27",_th.getNumericValue("H27"), _th.calculateNumericFormula("C27"), 0);
		assertEquals("D27-I27",_th.getNumericValue("I27"), _th.calculateNumericFormula("D27"), 0);
	}

	public final void testNumericArrayFormulaDataShortage() {

		// Clean cell's values before calculation
		_th.setNumericValue("B30", 0);
		_th.setNumericValue("C30", 0);
		_th.setNumericValue("D30", 0);
		_th.setNumericValue("B31", 0);
		_th.setNumericValue("C31", 0);
		_th.setNumericValue("D31", 0);
		_th.setNumericValue("B32", 0);
		_th.setNumericValue("C32", 0);
		_th.setNumericValue("D32", 0);

		assertEquals("C30-G30",_th.getNumericValue("G30"), _th.calculateNumericFormula("C30"), 0);
		assertEquals("B30-F30",_th.getNumericValue("F30"), _th.calculateNumericFormula("B30"), 0);
		if (false) { // TODO - fix this
			assertEquals("C30-G30", _th.getNumericValue("G30"), _th.getNumericValue("C30"), 0);
		}
		assertEquals("B31-G30",_th.getNumericValue("F31"), _th.calculateNumericFormula("B31"), 0);
		assertEquals("C31-G31",_th.getNumericValue("G31"), _th.calculateNumericFormula("C31"), 0);
		assertEquals("D30-H30",_th.getErrorValue("H30"), _th.calculateNumericFormulaWithError("D30").getString());
		assertEquals("D31-H31",_th.getErrorValue("H31"), _th.calculateNumericFormulaWithError("D31").getString());

		assertEquals("B32-F32",_th.getErrorValue("F32"), _th.calculateNumericFormulaWithError("B32").getString());
		assertEquals("C32-G32",_th.getErrorValue("G32"), _th.calculateNumericFormulaWithError("C32").getString());
		assertEquals("D32-H32",_th.getErrorValue("H32"), _th.calculateNumericFormulaWithError("D32").getString());
	}

	public final void testNumericArrayFormulaRefArguments() {

		// Clean cell's values before calculation
		_th.setNumericValue("A37", 0);
		_th.setNumericValue("B37", 0);
		_th.setNumericValue("C37", 0);

		assertEquals("A37-F37",_th.getNumericValue("F37"), _th.calculateNumericFormula("A37"), 0);
		assertEquals("B37-G37",_th.getNumericValue("G37"), _th.calculateNumericFormula("B37"), 0);
		assertEquals("C37-H37",_th.getNumericValue("H37"), _th.calculateNumericFormula("C37"), 0);
	}

	public final void testNumericArrayFormulasRefArguments() {

		// Clean cell's values before calculation
		_th.setNumericValue("A40", 0);
		_th.setNumericValue("B40", 0);
		_th.setNumericValue("C40", 0);

		assertEquals("A40-F40",_th.getNumericValue("F40"), _th.calculateNumericFormula("A40"), 0);
		assertEquals("B40-G40",_th.getNumericValue("G40"), _th.calculateNumericFormula("B40"), 0);
		assertEquals("C40-H40",_th.getNumericValue("H40"), _th.calculateNumericFormula("C40"), 0);
	}

	public final void testNumericOperation4Range() {

		// Clean cell's values before calculation
		_th.setNumericValue("C43", 0);
		_th.setNumericValue("C44", 0);
		_th.setNumericValue("C45", 0);
		_th.setNumericValue("C46", 0);

		assertEquals("C43-F43",_th.getNumericValue("F43"), _th.calculateNumericFormula("C43"), 0);
		assertEquals("C44-F44",_th.getNumericValue("F44"), _th.calculateNumericFormula("C44"), 0);
		assertEquals("C45-F45",_th.getNumericValue("F45"), _th.calculateNumericFormula("C45"), 0);
		assertEquals("C46-F46",_th.getNumericValue("F46"), _th.calculateNumericFormula("C46"), 0);
	}

	public final void testNumericOperation4DiffRanges() {

		// Clean cell's values before calculation
		_th.setNumericValue("C48", 0);
		_th.setNumericValue("C49", 0);
		_th.setNumericValue("C50", 0);
		_th.setNumericValue("C51", 0);

		assertEquals("C48-F48",_th.getNumericValue("F48"), _th.calculateNumericFormula("C48"), 0);
		assertEquals("C49-F49",_th.getNumericValue("F49"), _th.calculateNumericFormula("C49"), 0);
		assertEquals("C50-F50",_th.getNumericValue("F50"), _th.calculateNumericFormula("C50"), 0);
		assertEquals("C51-F51",_th.getNumericValue("F51"), _th.calculateNumericFormula("C51"), 0);
		if (false) { // TODO - fix these
			assertEquals("C50-F50", _th.getErrorValue("F50"), _th.getErrorValue("C50"));
			assertEquals("C51-F51",_th.getErrorValue("F51"), _th.getErrorValue("C51"));
		}
	}

	public final void testNumericArrayChangeRefArguments() {

		// Clean cell's values before calculation
		_th.setNumericValue("A40", 0);
		_th.setNumericValue("B40", 0);
		_th.setNumericValue("C40", 0);

		assertEquals("A40-F40", _th.getNumericValue("F40"), _th.calculateNumericFormula("A40"), 0);
		assertEquals("B40-G40", _th.getNumericValue("G40"), _th.calculateNumericFormula("B40"), 0);
		assertEquals("C40-H40", _th.getNumericValue("H40"), _th.calculateNumericFormula("C40"), 0);

		_th.setNumericValue("A41", 0.4);
		_th.setNumericValue("B41", 0.5);
		_th.setNumericValue("C41", 0.6);

		assertEquals("B40-G40", Math.cos(Math.sin(0.5)), _th.calculateNumericFormula("B40"), 0);
		assertEquals("A40-F40", Math.cos(Math.sin(0.4)), _th.calculateNumericFormula("A40"), 0);
		assertEquals("C40-H40", Math.cos(Math.sin(0.6)), _th.calculateNumericFormula("C40"), 0);
	}

	public final void testNumericArrayDifTypeArguments() {

		// Clean cell's values before calculation
		_th.setNumericValue("A54", 0);
		_th.setNumericValue("B54", 0);
		_th.setNumericValue("C54", 0);
		_th.setNumericValue("A55", 0);
		_th.setNumericValue("B55", 0);
		_th.setNumericValue("C55", 0);

		assertEquals("A54-F54",_th.getNumericValue("F54"), _th.calculateNumericFormula("A54"), 0);
		assertEquals("B54-G54",_th.getNumericValue("G54"), _th.calculateNumericFormula("B54"), 0);
		assertEquals("C54-H54",_th.getNumericValue("H54"), _th.calculateNumericFormula("C54"), 0);
		assertEquals("A55-F55",_th.getNumericValue("F55"), _th.calculateNumericFormula("A55"), 0);
		assertEquals("B55-G55",_th.getNumericValue("G55"), _th.calculateNumericFormula("B55"), 0);
		assertEquals("C55-H55",_th.getNumericValue("H55"), _th.calculateNumericFormula("C55"), 0);
	}
}




/* ====================================================================
   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.formula.eval;

import org.apache.poi.ss.usermodel.ArrayFormulaTestHelper;

import junit.framework.TestCase;

public abstract class BaseTestArrayFormulaFunction extends TestCase {

	private ArrayFormulaTestHelper _th;
	protected abstract ArrayFormulaTestHelper th();

	protected void setUp() {
		_th = th();
	}

	/**
	 *  check if non-calculated array cells does not contain value
	 */
	public final void testNonCalculated1() {
		_th.confirmNotNumeric("C4");
	}

	public final void testHorizontalArray1() {
		assertEquals("C4-I4",_th.getNumericValue("I4"), _th.calculateNumericFormula("C4"), 0);
	}

	public final void testNonCalculated2() {
		_th.confirmNotNumeric("D4");
	}

	public final void testHorizontalArray2() {
		assertEquals("D4-J4",_th.getNumericValue("J4"), _th.calculateNumericFormula("D4"), 0);
		assertEquals("E4-K4",_th.getNumericValue("K4"), _th.calculateNumericFormula("E4"), 0);
	}

	public final void testHorizontalArray3() {
		assertTrue("C4:E4 - I4:K4",_th.calculateAndCompareNumericArray('C', 4, 'E', 4, 'I', 4, 0));
	}

	public final void testTwoDimArray() {
		assertTrue("C6:E7 - I6:K7",_th.calculateAndCompareNumericArray('C', 6, 'E', 7, 'I', 6, 0));
	}

	public final void testVertArray() {
		assertTrue("C9:C11 - I9:I11",_th.calculateAndCompareNumericArray('C', 9, 'C', 11, 'I', 9, 0));
	}

	public final void testPowerArray() {
		assertTrue("C16:D16 - I16:K16",_th.calculateAndCompareNumericArray('C', 16, 'D', 16, 'I', 16, 0));
	}

	public final void testPowerArrayChanged() {
		_th.setNumericValue("A15", 2);
		assertEquals("changed array", 4, _th.calculateNumericFormula("C16"),0);
	}

	public final void testIndexArray() {
		assertTrue("C23:C25 - I23:I25",_th.calculateAndCompareNumericArray('C', 23, 'C', 25, 'I', 23, 0));
	}

	public final void testMatrixMultiply() {
		assertTrue("C33:D34 - I33:K34",_th.calculateAndCompareNumericArray('C', 33, 'D', 34, 'I', 33, 0));
	}

	public final void testSumOnMultiply() {
		assertEquals("C41",_th.getNumericValue("I41"), _th.calculateNumericFormula("C41"), 0);
	}

	public final void testSumOnRangeArray() {
		assertEquals("C48",_th.getNumericValue("I48"), _th.calculateNumericFormula("C48"), 0);
	}

	public final void testSumOnTwoArraya() {
		assertEquals("C50",_th.getNumericValue("I50"), _th.calculateNumericFormula("C50"), 0);
	}

	public final void testArrayMultiply() {
		assertTrue("C55:F57 - I55:L57",_th.calculateAndCompareNumericArray('C', 55, 'F', 57, 'I', 55, 0));
	}


	public final void testSumOnLen() {
		assertEquals("C63",_th.getNumericValue("I63"), _th.calculateNumericFormula("C63"), 0);
	}

	public final void testSmallRangeArray() {
		assertTrue("C69:D69",_th.calculateAndCompareNumericArray('C', 69, 'D', 69, 'I', 69, 0));
	}

	public final void testAverageOnSmall() {
		assertEquals("C72",_th.getNumericValue("I72"), _th.calculateNumericFormula("C72"), 0);
	}

	public final void testSumOnIf() {
		assertEquals("C75",_th.getNumericValue("I75"), _th.calculateNumericFormula("C75"), 0);
	}

	public final void testSumOnComplexIf() {
		assertEquals("C78",_th.getNumericValue("I78"), _th.calculateNumericFormula("C78"), 0);
	}

	public final void testSumOnComplexIf2() {
		assertEquals("C84",_th.getNumericValue("I84"), _th.calculateNumericFormula("C84"), 0);
	}

	public final void testColumn() {
		assertTrue("C87:F87",_th.calculateAndCompareNumericArray('C', 87, 'F', 87, 'I', 87, 0));
	}

	public final void testRow() {
		assertTrue("C90:C94",_th.calculateAndCompareNumericArray('C', 90, 'C', 94, 'I', 90, 0));
	}

	public final void testColumnNonArray() {
		assertEquals("C88",_th.getNumericValue("I88"), _th.calculateNumericFormula("C88"), 0);
	}

	public final void testColumnArray() {
		assertEquals("C89",_th.getNumericValue("I89"), _th.calculateNumericFormula("C89"), 0);
	}

	public final void testReferenceToArray() {
		assertEquals("C99", _th.getNumericValue("i99"), _th.calculateNumericFormula("C99"),0);
	}

	public final void testReferenceArrayToArray() {
		assertEquals("B102", _th.getNumericValue("i102"), _th.calculateNumericFormula("b102"),0.001);
		assertEquals("B103", _th.getNumericValue("i103"), _th.calculateNumericFormula("b103"),0.001);
		assertEquals("C102", _th.getNumericValue("j102"), _th.calculateNumericFormula("c102"),0.001);
		assertEquals("C103", _th.getNumericValue("j103"), _th.calculateNumericFormula("C103"),0.001);
	}
}




/* ====================================================================
   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.usermodel;

import junit.framework.AssertionFailedError;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ErrorConstants;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.XSSFTestDataSamples;

public final class ArrayFormulaTestHelper {
	private static final Log log = LogFactory.getLog(ArrayFormulaTestHelper.class);

	private final Workbook _wb;

	public ArrayFormulaTestHelper(String sampleFileName) {

		boolean isXssf = sampleFileName.endsWith(".xlsx");
		if (isXssf) {
			_wb = XSSFTestDataSamples.openSampleWorkbook(sampleFileName);
		} else {
			_wb = HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
		}
	}

	public Cell getCell(String cellRef) {

		log.debug("Access to Cell:" + cellRef);
		Sheet sheet = _wb.getSheetAt(0);
		CellReference cellReference = new CellReference(cellRef);
		Row row = sheet.getRow(cellReference.getRow());
		if (row==null) {
			throw new IllegalArgumentException("Illegal access to cell:" + cellRef);
		}
		Cell cell = row.getCell(cellReference.getCol());
		if (cell==null) {
			throw new IllegalArgumentException("Illegal access to cell:" + cellRef);
		}
		return cell;
	}


	public FormulaError calculateNumericFormulaWithError(String cellRef) {
		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
			log.error("Not formula in cell: " + cell.toString());
			throw new IllegalArgumentException("Not formula" + cell.toString());
		}

		log.debug("Formula: " + cell.getCellFormula());
		FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
		int type = evaluator.evaluateFormulaCell(cell);
		if (type != Cell.CELL_TYPE_ERROR) {
			log.error("not error result");
			throw new IllegalArgumentException("Not error type: " + type);
		}

		byte result = cell.getErrorCellValue();
		log.debug("Error value:" + result);
		return FormulaError.forInt(result);
	}

	public String calculateStringFormula(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
			log.error("Not formula in cell: " + cell.toString());
			throw new IllegalArgumentException("Not formula" + cell.toString());
		}

		log.debug("Formula: " + cell.getCellFormula());
		FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
		int type = evaluator.evaluateFormulaCell(cell);
		if (type != Cell.CELL_TYPE_STRING) {
			log.error("not string result: " + type);
			throw new IllegalArgumentException("Not string type" + type);
		}

		String result = cell.getStringCellValue();
		log.debug("Calculated:" + result);
		return result;
	}

	public double calculateNumericFormula(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
			log.error("Not formula in cell: " + cell.toString());
			throw new IllegalArgumentException("Not formula" + cell.toString());
		}

		log.debug("Formula: " + cell.getCellFormula());
		FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
		int type = evaluator.evaluateFormulaCell(cell);
		if (type != Cell.CELL_TYPE_NUMERIC) {
			log.error("not numeric result: " + type);
			throw new IllegalArgumentException("Not numeric type" + type);
		}

		double result = cell.getNumericCellValue();
		log.debug("Calculated:" + result);
		return result;
	}

	public boolean calculateBooleanFormula(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
			log.error("Not formula in cell: " + cell.toString());
			throw new IllegalArgumentException("Not formula" + cell.toString());
		}

		log.debug("Formula: " + cell.getCellFormula());
		FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
		int type = evaluator.evaluateFormulaCell(cell);
		if (type != Cell.CELL_TYPE_BOOLEAN) {
			log.error("not boolean result: " + type);
			throw new IllegalArgumentException("Not boolean type" + type);
		}

		boolean result = cell.getBooleanCellValue();
		log.debug("Calculated:" + result);
		return result;
	}

	public void confirmNotNumeric(String cellRef) {
		Cell cell = getCell(cellRef);
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			throw new AssertionFailedError("Cell type was unexpectedly numeric:" + cell.toString());
		}
	}

	public void setNumericValue(String cellRef, double value) {
		getCell(cellRef).setCellValue(value);
	}

	public double getNumericValue(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
			log.error("Not numeric in cell:" + cell.toString());
			throw new IllegalArgumentException("Not numeric:" + cell.toString());
		}
		double result = cell.getNumericCellValue();
		log.debug("value: " + result );
		return result;
	}

	public String getStringValue(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
			log.error("Not string in cell:" + cell.toString());
			throw new IllegalArgumentException("Not string:" + cell.toString());
		}
		String result = cell.getStringCellValue();
		log.debug("value: " + result );
		return result;
	}

	public boolean getBooleanValue(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_BOOLEAN) {
			log.error("Not boolean in cell:" + cell.toString());
			throw new IllegalArgumentException("Not boolean:" + cell.toString());
		}
		boolean result = cell.getBooleanCellValue();
		log.debug("value: " + result );
		return result;
	}

	public boolean calculateAndCompareNumericArray(char calculateFromColumn, int calculateFromRow, char calculateToColumn,
			int calculateToRow, char compareFromColumn, int compareFromRow, double precision) {

		if (calculateFromColumn> calculateToColumn) {
			throw new IllegalArgumentException("columns: " + calculateFromColumn + "   " + calculateToColumn);
		}

		if (calculateFromRow > calculateToRow) {
			throw new IllegalArgumentException("rows: " + calculateFromRow + "   " + calculateToRow);
		}

		for(int c=0; calculateFromColumn + c <= calculateToColumn; c++ ) {
			for(int r=0; calculateFromRow +r <= calculateToRow; r++) {
				char ch[] = new char[1];
				ch[0] = (char)(calculateFromColumn+c);
				String calculateRef = (new String(ch)) + (calculateFromRow+r);
				double calcResult = calculateNumericFormula(calculateRef);

				ch[0]=(char)(compareFromColumn+c);
				String compareRef = (new String(ch)) + (compareFromRow + r);
				double compareResult = getNumericValue(compareRef);

				if (Math.abs(compareResult - calcResult) > precision) {
					// comparison failed
					log.debug("Array comparison failed. " + "Calculated cell:" + calculateRef + " Compared cell: " + compareRef +
					"   calculated result: " + calcResult + "  compared result: " + compareResult);
					return false;
				}
			}
		}
		return true;
	}

	public void setArrayFormula(String cellRef, String formula, String range) {
		Sheet sheet = getCell(cellRef).getSheet();
		sheet.setArrayFormula(formula, CellRangeAddress.valueOf(range));
	}

	public void removeArrayFormula(String cellRef) {
		Cell cell = getCell(cellRef);
		Sheet sheet = cell.getSheet();
		sheet.removeArrayFormula(cell);
	}

	public String getErrorValue(String cellRef) {

		Cell cell = getCell(cellRef);
		if (cell.getCellType() != Cell.CELL_TYPE_ERROR) {
			log.error("Not numeric in cell:" + cell.toString());
			throw new IllegalArgumentException("Not error:" + cell.toString());
		}
		byte result = cell.getErrorCellValue();
		log.debug("value: " + result );
		return ErrorConstants.getText(result);
	}

	public int getCellType(String cellRef) {
		Cell cell = getCell(cellRef);
		return cell.getCellType();
	}
}




/* ====================================================================
   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.usermodel;

import junit.framework.TestCase;

import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Array Formula Set/Remove test class
 *
 * Workbook contains formulas and tests
 */
public abstract class BaseTestAddRemoveArrayFormula extends TestCase {

	protected final ITestDataProvider _testDataProvider;
	private final String _sampleSpreadsheetName;

	private ArrayFormulaTestHelper _th;

	protected BaseTestAddRemoveArrayFormula(ITestDataProvider testDataProvider, String sampleSpreadsheetName) {
		_testDataProvider = testDataProvider;
		_sampleSpreadsheetName = sampleSpreadsheetName;
	}

	protected void setUp() {
		_th = new ArrayFormulaTestHelper(_sampleSpreadsheetName);
	}


	public final void testNewNumericArrayFormula() {

		_th.setArrayFormula("A35", "SIN({0.1,0.2,0.3})", "A35:B35");

		assertEquals("A35-F35", _th.getNumericValue("F35"), _th.calculateNumericFormula("A35"), 0);
		assertEquals("B35-G35", _th.getNumericValue("G35"), _th.calculateNumericFormula("B35"), 0);
	}

	public final void testNewNumericArrayFormulaOut() {

		// create empty workbook
		Workbook workbook = _testDataProvider.createWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		Row rowd = sheet.createRow((short) (0));
		Cell cd = rowd.createCell((short) 0);
		CellRangeAddress range = new CellRangeAddress(0, 1, 0, 1);
		sheet.setArrayFormula("SQRT({1,4;9,16})", range);

		// Calculate formula
		FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
		eval.evaluateFormulaCell(cd);

		// Set tested values (copy from 5 rows above)
		for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
			for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
				Cell cell = sheet.getRow(rowIn).getCell(colIn);
				int lowerRowIx = rowIn + 5;
				double value = cell.getNumericCellValue();
				Row row = sheet.getRow(lowerRowIx);
				if (row == null) {
					row = sheet.createRow(lowerRowIx);
				}
				row.createCell(colIn).setCellValue(value);
			}
		}

		// serialize/deserialize file
		workbook = _testDataProvider.writeOutAndReadBack(workbook);
		sheet = workbook.getSheetAt(0);
		// Set 0 values before calculation
		for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
			for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
				sheet.getRow(rowIn).getCell(colIn).setCellValue(0.0);
			}
		}
		// Calculate formula (we use cell from firstRow and firstColumn)
		eval = workbook.getCreationHelper().createFormulaEvaluator();
		eval.evaluateFormulaCell(sheet.getRow(range.getFirstRow()).getCell(range.getFirstColumn()));
		// Check calculated values
		for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
			for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
				Cell cell = sheet.getRow(rowIn).getCell(colIn);
				double value = cell.getNumericCellValue();
				cell = sheet.getRow(rowIn + 5).getCell(colIn);
				assertEquals("ArrayFormula:" + rowIn + "," + colIn, cell.getNumericCellValue(),
						value, 0);
			}
		}
	}

	public final void testRemoveArrayFormula() {

		_th.removeArrayFormula("A40");

		assertEquals("A40-F40", Cell.CELL_TYPE_BLANK, _th.getCellType("A40"), 0);
		assertEquals("B40-G40", Cell.CELL_TYPE_BLANK, _th.getCellType("B40"), 0);
		assertEquals("C40-H40", Cell.CELL_TYPE_BLANK, _th.getCellType("C40"), 0);
	}
}

Return to bug 48292

Lines 781-784 public interface Sheet extends Iterable<Row> { Link Here

(-)a/src/java/org/apache/poi/ss/usermodel/Sheet.java (+15 lines)
781	*/	781	*/
782	boolean isSelected();	782	boolean isSelected();
783		783
		784
		785	/**
		786	* Sets array formula to specified region for result.
		787	*
		788	* @param formula Formula
		789	* @param range Region of array formula for result.
		790	*/
		791	void setArrayFormula(String formula, CellRangeAddress range);
		792
		793	/**
		794	* Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well
		795	*
		796	* @param cell any cell within Array Formula range
		797	*/
		798	void removeArrayFormula(Cell cell);
784	}	799	}

Line 0 Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/ArrayMode.java (+30 lines)
	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.eval.ValueEval;
	21
	22	/**
	23	* Interface for those functions that behaves differently in array formula
	24	*
	25	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
	26	*/
	27	public interface ArrayMode {
	28
	29	ValueEval evaluateInArrayFormula(ValueEval[] args, int srcRowIndex, int srcColumnIndex);
	30	}

Lines 36-43 import org.apache.poi.ss.formula.TwoDEval; Link Here

(-)a/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java (-1 / +11 lines)
36	* <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>	36	* <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>
37	*	37	*
38	* @author Josh Micich	38	* @author Josh Micich
		39	* @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
39	*/	40	*/
40	public final class Lookup extends Var2or3ArgFunction {	41	public final class Lookup extends Var2or3ArgFunction implements FunctionWithArraySupport {
41		42
42	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {	43	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
43	// complex rules to choose lookupVector and resultVector from the single area ref	44	// complex rules to choose lookupVector and resultVector from the single area ref
Lines 73-76 public final class Lookup extends Var2or3ArgFunction { Link Here
73	// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.	74	// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
74	throw new RuntimeException("non-vector lookup or result areas not supported yet");	75	throw new RuntimeException("non-vector lookup or result areas not supported yet");
75	}	76	}
		77
		78	public boolean supportArray(int paramIndex) {
		79	switch (paramIndex) {
		80	case 1:
		81	case 2:
		82	return true;
		83	}
		84	return false;
		85	}
76	}	86	}

Lines 43-48 import org.apache.poi.hssf.record.SubRecord; Link Here

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (+28 lines)
43	import org.apache.poi.hssf.record.TextObjectRecord;	43	import org.apache.poi.hssf.record.TextObjectRecord;
44	import org.apache.poi.hssf.record.UnicodeString;	44	import org.apache.poi.hssf.record.UnicodeString;
45	import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;	45	import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
		46	import org.apache.poi.hssf.record.formula.ExpPtg;
46	import org.apache.poi.hssf.record.formula.Ptg;	47	import org.apache.poi.hssf.record.formula.Ptg;
47	import org.apache.poi.hssf.record.formula.eval.ErrorEval;	48	import org.apache.poi.hssf.record.formula.eval.ErrorEval;
48	import org.apache.poi.ss.usermodel.Cell;	49	import org.apache.poi.ss.usermodel.Cell;
Lines 50-55 import org.apache.poi.ss.usermodel.CellStyle; Link Here
50	import org.apache.poi.ss.usermodel.Comment;	51	import org.apache.poi.ss.usermodel.Comment;
51	import org.apache.poi.ss.usermodel.Hyperlink;	52	import org.apache.poi.ss.usermodel.Hyperlink;
52	import org.apache.poi.ss.usermodel.RichTextString;	53	import org.apache.poi.ss.usermodel.RichTextString;
		54	import org.apache.poi.ss.util.CellRangeAddress;
53	import org.apache.poi.ss.util.NumberToTextConverter;	55	import org.apache.poi.ss.util.NumberToTextConverter;
54	import org.apache.poi.ss.formula.FormulaType;	56	import org.apache.poi.ss.formula.FormulaType;
55	import org.apache.poi.ss.SpreadsheetVersion;	57	import org.apache.poi.ss.SpreadsheetVersion;
Lines 1160-1163 public class HSSFCell implements Cell { Link Here
1160	}	1162	}
1161	return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();	1163	return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
1162	}	1164	}
		1165
		1166	void setCellArrayFormula(CellRangeAddress range) {
		1167	int row=_record.getRow();
		1168	short col=_record.getColumn();
		1169	short styleIndex=_record.getXFIndex();
		1170	setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex);
		1171
		1172	// Billet for formula in rec
		1173	Ptg[] ptgsForCell = { new ExpPtg(range.getFirstRow(), range.getFirstColumn()) };
		1174	FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
		1175	agg.setParsedExpression(ptgsForCell);
		1176	}
		1177
		1178	public CellRangeAddress getArrayFormulaRange() {
		1179	if (_cellType != CELL_TYPE_FORMULA) {
		1180	throw new IllegalArgumentException("Only formula cells can have array ranges");
		1181	}
		1182	return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
		1183	}
		1184
		1185	public boolean isPartOfArrayFormulaGroup() {
		1186	if (_cellType != CELL_TYPE_FORMULA) {
		1187	return false;
		1188	}
		1189	return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
		1190	}
1163	}	1191	}

Lines 31-36 import java.util.List; Link Here

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-3 / +50 lines)
31	import java.util.TreeMap;	31	import java.util.TreeMap;
32		32
33	import org.apache.poi.ddf.EscherRecord;	33	import org.apache.poi.ddf.EscherRecord;
		34	import org.apache.poi.hssf.model.HSSFFormulaParser;
34	import org.apache.poi.hssf.model.Sheet;	35	import org.apache.poi.hssf.model.Sheet;
35	import org.apache.poi.hssf.model.Workbook;	36	import org.apache.poi.hssf.model.Workbook;
36	import org.apache.poi.hssf.record.CellValueRecordInterface;	37	import org.apache.poi.hssf.record.CellValueRecordInterface;
Lines 44-53 import org.apache.poi.hssf.record.SCLRecord; Link Here
44	import org.apache.poi.hssf.record.WSBoolRecord;	45	import org.apache.poi.hssf.record.WSBoolRecord;
45	import org.apache.poi.hssf.record.WindowTwoRecord;	46	import org.apache.poi.hssf.record.WindowTwoRecord;
46	import org.apache.poi.hssf.record.aggregates.DataValidityTable;	47	import org.apache.poi.hssf.record.aggregates.DataValidityTable;
		48	import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
47	import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;	49	import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
48	import org.apache.poi.hssf.record.formula.FormulaShifter;	50	import org.apache.poi.hssf.record.formula.FormulaShifter;
		51	import org.apache.poi.hssf.record.formula.Ptg;
49	import org.apache.poi.hssf.util.PaneInformation;	52	import org.apache.poi.hssf.util.PaneInformation;
50	import org.apache.poi.hssf.util.Region;	53	import org.apache.poi.hssf.util.Region;
		54	import org.apache.poi.ss.formula.FormulaType;
51	import org.apache.poi.ss.usermodel.Cell;	55	import org.apache.poi.ss.usermodel.Cell;
52	import org.apache.poi.ss.usermodel.CellStyle;	56	import org.apache.poi.ss.usermodel.CellStyle;
53	import org.apache.poi.ss.usermodel.Row;	57	import org.apache.poi.ss.usermodel.Row;
Lines 64-69 import org.apache.poi.util.POILogger; Link Here
64	* @author Shawn Laubach (slaubach at apache dot org) (Just a little)	68	* @author Shawn Laubach (slaubach at apache dot org) (Just a little)
65	* @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)	69	* @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
66	* @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)	70	* @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)
		71	* @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas
67	*/	72	*/
68	public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {	73	public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
69	private static final POILogger log = POILogFactory.getLogger(HSSFSheet.class);	74	private static final POILogger log = POILogFactory.getLogger(HSSFSheet.class);
Lines 636-647 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
636		641
637	/**	642	/**
638	* Sets whether the worksheet is displayed from right to left instead of from left to right.	643	* Sets whether the worksheet is displayed from right to left instead of from left to right.
639	*	644	*
640	* @param value true for right to left, false otherwise.	645	* @param value true for right to left, false otherwise.
641	*/	646	*/
642	public void setRightToLeft(boolean value)	647	public void setRightToLeft(boolean value)
643	{	648	{
644	_sheet.getWindowTwo().setArabic(value);	649	_sheet.getWindowTwo().setArabic(value);
645	}	650	}
646		651
647	/**	652	/**
Lines 651-657 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
651	*/	656	*/
652	public boolean isRightToLeft()	657	public boolean isRightToLeft()
653	{	658	{
654	return _sheet.getWindowTwo().getArabic();	659	return _sheet.getWindowTwo().getArabic();
655	}	660	}
656		661
657	/**	662	/**
Lines 1870-1873 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
1870	return wb.getSheetName(idx);	1875	return wb.getSheetName(idx);
1871	}	1876	}
1872		1877
		1878	public void setArrayFormula(String formula, CellRangeAddress range) {
		1879	// make sure the formula parses OK first
		1880	Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, _workbook.getSheetIndex(this));
		1881	int firstRow = range.getFirstRow();
		1882	int firstColumn = range.getFirstColumn();
		1883	for (int rowIn = firstRow; rowIn <= range.getLastRow(); rowIn++) {
		1884	for (int colIn = firstColumn; colIn <= range.getLastColumn(); colIn++) {
		1885	HSSFRow row = getRow(rowIn);
		1886	if (row == null) {
		1887	row = createRow(rowIn);
		1888	}
		1889	HSSFCell cell = row.getCell(colIn);
		1890	if (cell == null) {
		1891	cell = row.createCell(colIn);
		1892	}
		1893	cell.setCellArrayFormula(range);
		1894	}
		1895	}
		1896	HSSFCell firstArrayFormulaCell = getRow(firstRow).getCell(firstColumn);
		1897	FormulaRecordAggregate agg = (FormulaRecordAggregate) firstArrayFormulaCell.getCellValueRecord();
		1898	agg.setArrayFormula(range, ptgs);
		1899	}
		1900
		1901
		1902	public void removeArrayFormula(Cell cell) {
		1903	CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
		1904	if (!(rec instanceof FormulaRecordAggregate)) {
		1905	throw new IllegalArgumentException("Specified cell is not a formula cell.");
		1906	}
		1907	FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
		1908	CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
		1909	if (range == null) {
		1910	throw new IllegalArgumentException("Specified cell does not contain an array formula.");
		1911	}
		1912	// clear all cells in the range
		1913	for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
		1914	for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
		1915	Cell rCell = getRow(rowIn).getCell(colIn);
		1916	rCell.setCellType(Cell.CELL_TYPE_BLANK);
		1917	}
		1918	}
		1919	}
1873	}	1920	}

Lines 44-58 public final class OperationEvaluationContext { Link Here

(-)a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (-1 / +7 lines)
44	private final int _columnIndex;	44	private final int _columnIndex;
45	private final EvaluationTracker _tracker;	45	private final EvaluationTracker _tracker;
46	private final WorkbookEvaluator _bookEvaluator;	46	private final WorkbookEvaluator _bookEvaluator;
		47	private final boolean _inArrayFormulaContext;
47		48
48	public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,	49	public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
49	int srcColNum, EvaluationTracker tracker) {	50	int srcColNum, EvaluationTracker tracker, boolean inArrayFormulaContext) {
50	_bookEvaluator = bookEvaluator;	51	_bookEvaluator = bookEvaluator;
51	_workbook = workbook;	52	_workbook = workbook;
52	_sheetIndex = sheetIndex;	53	_sheetIndex = sheetIndex;
53	_rowIndex = srcRowNum;	54	_rowIndex = srcRowNum;
54	_columnIndex = srcColNum;	55	_columnIndex = srcColNum;
55	_tracker = tracker;	56	_tracker = tracker;
		57	_inArrayFormulaContext = inArrayFormulaContext;
56	}	58	}
57		59
58	public EvaluationWorkbook getWorkbook() {	60	public EvaluationWorkbook getWorkbook() {
Lines 67-72 public final class OperationEvaluationContext { Link Here
67	return _columnIndex;	69	return _columnIndex;
68	}	70	}
69		71
		72	public boolean isInArrayFormulaContext() {
		73	return _inArrayFormulaContext;
		74	}
		75
70	SheetRefEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) {	76	SheetRefEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) {
71	return createExternSheetRefEvaluator(ptg.getExternSheetIndex());	77	return createExternSheetRefEvaluator(ptg.getExternSheetIndex());
72	}	78	}

Lines 21-29 import java.util.IdentityHashMap; Link Here

(-)a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (-15 / +82 lines)
21	import java.util.Map;	21	import java.util.Map;
22	import java.util.Stack;	22	import java.util.Stack;
23		23
		24	import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
24	import org.apache.poi.hssf.record.formula.Area3DPtg;	25	import org.apache.poi.hssf.record.formula.Area3DPtg;
25	import org.apache.poi.hssf.record.formula.AreaErrPtg;	26	import org.apache.poi.hssf.record.formula.AreaErrPtg;
26	import org.apache.poi.hssf.record.formula.AreaPtg;	27	import org.apache.poi.hssf.record.formula.AreaPtg;
		28	import org.apache.poi.hssf.record.formula.ArrayPtg;
27	import org.apache.poi.hssf.record.formula.AttrPtg;	29	import org.apache.poi.hssf.record.formula.AttrPtg;
28	import org.apache.poi.hssf.record.formula.BoolPtg;	30	import org.apache.poi.hssf.record.formula.BoolPtg;
29	import org.apache.poi.hssf.record.formula.ControlPtg;	31	import org.apache.poi.hssf.record.formula.ControlPtg;
Lines 60-65 import org.apache.poi.hssf.record.formula.eval.NumberEval; Link Here
60	import org.apache.poi.hssf.record.formula.eval.RefEval;	62	import org.apache.poi.hssf.record.formula.eval.RefEval;
61	import org.apache.poi.hssf.record.formula.eval.StringEval;	63	import org.apache.poi.hssf.record.formula.eval.StringEval;
62	import org.apache.poi.hssf.record.formula.eval.ValueEval;	64	import org.apache.poi.hssf.record.formula.eval.ValueEval;
		65	import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
63	import org.apache.poi.hssf.record.formula.functions.Choose;	66	import org.apache.poi.hssf.record.formula.functions.Choose;
64	import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;	67	import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
65	import org.apache.poi.hssf.record.formula.functions.If;	68	import org.apache.poi.hssf.record.formula.functions.If;
Lines 67-72 import org.apache.poi.hssf.record.formula.udf.UDFFinder; Link Here
67	import org.apache.poi.hssf.util.CellReference;	70	import org.apache.poi.hssf.util.CellReference;
68	import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;	71	import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
69	import org.apache.poi.ss.formula.eval.NotImplementedException;	72	import org.apache.poi.ss.formula.eval.NotImplementedException;
		73	import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
70	import org.apache.poi.ss.usermodel.Cell;	74	import org.apache.poi.ss.usermodel.Cell;
71		75
72	/**	76	/**
Lines 203-209 public final class WorkbookEvaluator { Link Here
203		207
204	public ValueEval evaluate(EvaluationCell srcCell) {	208	public ValueEval evaluate(EvaluationCell srcCell) {
205	int sheetIndex = getSheetIndex(srcCell.getSheet());	209	int sheetIndex = getSheetIndex(srcCell.getSheet());
206	return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));	210	return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));
207	}	211	}
208		212
209	/**	213	/**
Lines 251-257 public final class WorkbookEvaluator { Link Here
251	if (!tracker.startEvaluate(cce)) {	255	if (!tracker.startEvaluate(cce)) {
252	return ErrorEval.CIRCULAR_REF_ERROR;	256	return ErrorEval.CIRCULAR_REF_ERROR;
253	}	257	}
254	OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);	258	OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex,
		259	columnIndex, tracker, srcCell.isArrayFormula());
255		260
256	try {	261	try {
257		262
Lines 271-277 public final class WorkbookEvaluator { Link Here
271	tracker.endEvaluate(cce);	276	tracker.endEvaluate(cce);
272	}	277	}
273	} else {	278	} else {
274	if(evalListener != null) {	279	if (evalListener != null) {
275	evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue());	280	evalListener.onCacheHit(sheetIndex, rowIndex, columnIndex, cce.getValue());
276	}	281	}
277	return cce.getValue();	282	return cce.getValue();
Lines 333-338 public final class WorkbookEvaluator { Link Here
333	// visibility raised for testing	338	// visibility raised for testing
334	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {	339	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {
335		340
		341	Stack<Boolean> stackSkip = new Stack<Boolean>();
		342	Boolean isSkipActive = Boolean.TRUE;
336	Stack<ValueEval> stack = new Stack<ValueEval>();	343	Stack<ValueEval> stack = new Stack<ValueEval>();
337	for (int i = 0, iSize = ptgs.length; i < iSize; i++) {	344	for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
338		345
Lines 347-357 public final class WorkbookEvaluator { Link Here
347	}	354	}
348	if (attrPtg.isOptimizedChoose()) {	355	if (attrPtg.isOptimizedChoose()) {
349	ValueEval arg0 = stack.pop();	356	ValueEval arg0 = stack.pop();
		357	if (arg0 instanceof ArrayEval && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) {
		358	stack.push(arg0);
		359	stackSkip.push(isSkipActive);
		360	// Switch off skip option only for this level
		361	isSkipActive = Boolean.FALSE;
		362	continue;
		363	}
		364	ValueEval cond = arg0;
		365	if (arg0 instanceof ArrayEval) {
		366	cond = ((ArrayEval)arg0).getValue(0,0);
		367	}
350	int[] jumpTable = attrPtg.getJumpTable();	368	int[] jumpTable = attrPtg.getJumpTable();
351	int dist;	369	int dist;
352	int nChoices = jumpTable.length;	370	int nChoices = jumpTable.length;
353	try {	371	try {
354	int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());	372	int switchIndex = Choose.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
355	if (switchIndex<1 \|\| switchIndex > nChoices) {	373	if (switchIndex<1 \|\| switchIndex > nChoices) {
356	stack.push(ErrorEval.VALUE_INVALID);	374	stack.push(ErrorEval.VALUE_INVALID);
357	dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)	375	dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)
Lines 371-378 public final class WorkbookEvaluator { Link Here
371	if (attrPtg.isOptimizedIf()) {	389	if (attrPtg.isOptimizedIf()) {
372	ValueEval arg0 = stack.pop();	390	ValueEval arg0 = stack.pop();
373	boolean evaluatedPredicate;	391	boolean evaluatedPredicate;
		392	if (arg0 instanceof ArrayEval && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) {
		393	stack.push(arg0);
		394	stackSkip.push(isSkipActive);
		395	// Switch off skip option only for this level
		396	isSkipActive = new Boolean(false);
		397	continue;
		398	}
		399	ValueEval cond = arg0;
		400	if (arg0 instanceof ArrayEval) {
		401	cond = ((ArrayEval)arg0).getValue(0,0);
		402	}
374	try {	403	try {
375	evaluatedPredicate = If.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());	404	evaluatedPredicate = If.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
376	} catch (EvaluationException e) {	405	} catch (EvaluationException e) {
377	stack.push(e.getErrorEval());	406	stack.push(e.getErrorEval());
378	int dist = attrPtg.getData();	407	int dist = attrPtg.getData();
Lines 397-407 public final class WorkbookEvaluator { Link Here
397	continue;	426	continue;
398	}	427	}
399	if (attrPtg.isSkip()) {	428	if (attrPtg.isSkip()) {
400	int dist = attrPtg.getData()+1;	429	if (isSkipActive.booleanValue()) {
401	i+= countTokensToBeSkipped(ptgs, i, dist);	430	int dist = attrPtg.getData()+1;
402	if (stack.peek() == MissingArgEval.instance) {	431	i+= countTokensToBeSkipped(ptgs, i, dist);
403	stack.pop();	432	if (stack.peek() == MissingArgEval.instance) {
404	stack.push(BlankEval.instance);	433	stack.pop();
		434	stack.push(BlankEval.instance);
		435	}
405	}	436	}
406	continue;	437	continue;
407	}	438	}
Lines 435-440 public final class WorkbookEvaluator { Link Here
435	}	466	}
436	// logDebug("invoke " + operation + " (nAgs=" + numops + ")");	467	// logDebug("invoke " + operation + " (nAgs=" + numops + ")");
437	opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);	468	opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);
		469	if (!isSkipActive.booleanValue() && isSkipSensitive(optg)) {
		470	isSkipActive = stackSkip.pop();
		471	}
438	} else {	472	} else {
439	opResult = getEvalForPtg(ptg, ec);	473	opResult = getEvalForPtg(ptg, ec);
440	}	474	}
Lines 460-465 public final class WorkbookEvaluator { Link Here
460	}	494	}
461		495
462	/**	496	/**
		497	* Has this function "optimized" form?
		498	* @return <code>true</code> if the operation can be optimised with tAttr tokens (if, choose, skip)
		499	*/
		500	private boolean isSkipSensitive(OperationPtg optg) {
		501	if (optg instanceof AbstractFunctionPtg){
		502	// Skip sensitive is only "optimized" function - just only "IF" and "choose"
		503	AbstractFunctionPtg fptg = (AbstractFunctionPtg)optg;
		504	switch (fptg.getFunctionIndex()) {
		505	case FunctionMetadataRegistry.FUNCTION_INDEX_IF:
		506	case FunctionMetadataRegistry.FUNCTION_INDEX_CHOOSE:
		507	return true;
		508	}
		509	}
		510	return false;
		511	}
		512
		513	/**
463	* Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.	514	* Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.
464	*	515	*
465	* @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped	516	* @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped
Lines 481-492 public final class WorkbookEvaluator { Link Here
481	return index-startIndex;	532	return index-startIndex;
482	}	533	}
483	/**	534	/**
		535	* Dereferences a single value from any ArrayEval evaluation result.
		536	* @param evaluationResult
		537	* @param cell
		538	* @return
		539	*/
		540	private static ValueEval dereferenceValue(ArrayEval evaluationResult, EvaluationCell evalCell ) {
		541	Cell cell = (Cell)evalCell.getIdentityKey();
		542	return ArrayFormulaEvaluatorHelper.dereferenceValue(evaluationResult,cell);
		543	}
		544	/**
484	* Dereferences a single value from any AreaEval or RefEval evaluation result.	545	* Dereferences a single value from any AreaEval or RefEval evaluation result.
485	* If the supplied evaluationResult is just a plain value, it is returned as-is.	546	* If the supplied evaluationResult is just a plain value, it is returned as-is.
486	* @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,	547	* @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
487	* <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.	548	* <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.
488	*/	549	*/
489	private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, int srcColNum) {	550	public static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, int srcColNum) {
490	if (evaluationResult instanceof RefEval) {	551	if (evaluationResult instanceof RefEval) {
491	RefEval rv = (RefEval) evaluationResult;	552	RefEval rv = (RefEval) evaluationResult;
492	return rv.getInnerValueEval();	553	return rv.getInnerValueEval();
Lines 494-500 public final class WorkbookEvaluator { Link Here
494	if (evaluationResult instanceof AreaEval) {	555	if (evaluationResult instanceof AreaEval) {
495	AreaEval ae = (AreaEval) evaluationResult;	556	AreaEval ae = (AreaEval) evaluationResult;
496	if (ae.isRow()) {	557	if (ae.isRow()) {
497	if(ae.isColumn()) {	558	if (ae.isColumn()) {
498	return ae.getRelativeValue(0, 0);	559	return ae.getRelativeValue(0, 0);
499	}	560	}
500	return ae.getAbsoluteValue(ae.getFirstRow(), srcColNum);	561	return ae.getAbsoluteValue(ae.getFirstRow(), srcColNum);
Lines 572-578 public final class WorkbookEvaluator { Link Here
572	AreaPtg aptg = (AreaPtg) ptg;	633	AreaPtg aptg = (AreaPtg) ptg;
573	return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());	634	return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
574	}	635	}
575		636	if (ptg instanceof ArrayPtg) {
		637	return new ArrayEval((ArrayPtg)ptg);
		638	}
576	if (ptg instanceof UnknownPtg) {	639	if (ptg instanceof UnknownPtg) {
577	// POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.	640	// POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.
578	// This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)	641	// This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)
Lines 597-607 public final class WorkbookEvaluator { Link Here
597	/**	660	/**
598	* Used by the lazy ref evals whenever they need to get the value of a contained cell.	661	* Used by the lazy ref evals whenever they need to get the value of a contained cell.
599	*/	662	*/
600	/* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,	663	ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,
601	int columnIndex, EvaluationTracker tracker) {	664	int columnIndex, EvaluationTracker tracker) {
602		665
603	EvaluationCell cell = sheet.getCell(rowIndex, columnIndex);	666	EvaluationCell cell = sheet.getCell(rowIndex, columnIndex);
604	return evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker);	667	ValueEval result = evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker);
		668	if (result instanceof ArrayEval && cell.isArrayFormula()) {
		669	result = dereferenceValue((ArrayEval) result, cell);
		670	}
		671	return result;
605	}	672	}
606	public FreeRefFunction findUserDefinedFunction(String functionName) {	673	public FreeRefFunction findUserDefinedFunction(String functionName) {
607	return _udfFinder.findFunction(functionName);	674	return _udfFinder.findFunction(functionName);

Lines 21-26 import java.util.Calendar; Link Here

(-)a/src/java/org/apache/poi/ss/usermodel/Cell.java (+12 lines)
21	import java.util.Date;	21	import java.util.Date;
22		22
23	import org.apache.poi.ss.formula.FormulaParseException;	23	import org.apache.poi.ss.formula.FormulaParseException;
		24	import org.apache.poi.ss.util.CellRangeAddress;
24		25
25	/**	26	/**
26	* High level representation of a cell in a row of a spreadsheet.	27	* High level representation of a cell in a row of a spreadsheet.
Lines 372-375 public interface Cell { Link Here
372	* @param link hypelrink associated with this cell	373	* @param link hypelrink associated with this cell
373	*/	374	*/
374	void setHyperlink(Hyperlink link);	375	void setHyperlink(Hyperlink link);
		376
		377	/**
		378	* Only valid for array formula cells
		379	* @return range of the array formula group that the cell belongs to.
		380	*/
		381	CellRangeAddress getArrayFormulaRange();
		382
		383	/**
		384	* @return <code>true</code> if this cell is part of group of cells having a common array formula.
		385	*/
		386	boolean isPartOfArrayFormulaGroup();
375	}	387	}

Lines 24-30 import org.apache.poi.util.LittleEndianOutput; Link Here

(-)a/src/java/org/apache/poi/ss/util/CellRangeAddress.java (-17 / +28 lines)
24		24
25	/**	25	/**
26	* See OOO documentation: excelfileformat.pdf sec 2.5.14 - 'Cell Range Address'<p/>	26	* See OOO documentation: excelfileformat.pdf sec 2.5.14 - 'Cell Range Address'<p/>
27	*	27	*
28	* Note - {@link SelectionRecord} uses the BIFF5 version of this structure	28	* Note - {@link SelectionRecord} uses the BIFF5 version of this structure
29	* @author Dragos Buleandra (dragos.buleandra@trade2b.ro)	29	* @author Dragos Buleandra (dragos.buleandra@trade2b.ro)
30	*/	30	*/
Lines 51-57 public class CellRangeAddress extends CellRangeAddressBase { Link Here
51	out.writeShort(getFirstColumn());	51	out.writeShort(getFirstColumn());
52	out.writeShort(getLastColumn());	52	out.writeShort(getLastColumn());
53	}	53	}
54		54
55	public CellRangeAddress(RecordInputStream in) {	55	public CellRangeAddress(RecordInputStream in) {
56	super(readUShortAndCheck(in), in.readUShort(), in.readUShort(), in.readUShort());	56	super(readUShortAndCheck(in), in.readUShort(), in.readUShort(), in.readUShort());
57	}	57	}
Lines 72-91 public class CellRangeAddress extends CellRangeAddressBase { Link Here
72	return numberOfItems * ENCODED_SIZE;	72	return numberOfItems * ENCODED_SIZE;
73	}	73	}
74		74
75	public String formatAsString() {	75	public String formatAsString() {
76	StringBuffer sb = new StringBuffer();	76	StringBuffer sb = new StringBuffer();
77	CellReference cellRefFrom = new CellReference(getFirstRow(), getFirstColumn());	77	CellReference cellRefFrom = new CellReference(getFirstRow(), getFirstColumn());
78	CellReference cellRefTo = new CellReference(getLastRow(), getLastColumn());	78	CellReference cellRefTo = new CellReference(getLastRow(), getLastColumn());
79	sb.append(cellRefFrom.formatAsString());	79	sb.append(cellRefFrom.formatAsString());
80	sb.append(':');	80	sb.append(':');
81	sb.append(cellRefTo.formatAsString());	81	sb.append(cellRefTo.formatAsString());
82	return sb.toString();	82	return sb.toString();
83	}	83	}
84		84
85	public static CellRangeAddress valueOf(String ref) {	85	/**
86	int sep = ref.indexOf(":");	86	* @param ref usually a standard area ref (e.g. "B1:D8"). May be a single cell
87	CellReference cellFrom = new CellReference(ref.substring(0, sep));	87	* ref (e.g. "B5") in which case the result is a 1 x 1 cell range.
88	CellReference cellTo = new CellReference(ref.substring(sep + 1));	88	*/
89	return new CellRangeAddress(cellFrom.getRow(), cellTo.getRow(), cellFrom.getCol(), cellTo.getCol());	89	public static CellRangeAddress valueOf(String ref) {
90	}	90	int sep = ref.indexOf(":");
		91	CellReference a;
		92	CellReference b;
		93	if (sep == -1) {
		94	a = new CellReference(ref);
		95	b = a;
		96	} else {
		97	a = new CellReference(ref.substring(0, sep));
		98	b = new CellReference(ref.substring(sep + 1));
		99	}
		100	return new CellRangeAddress(a.getRow(), b.getRow(), a.getCol(), b.getCol());
		101	}
91	}	102	}

Lines 28-34 import org.apache.poi.xssf.usermodel.helpers.TestHeaderFooterHelper; Link Here

(-)a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java (-2 / +6 lines)
28		28
29	/**	29	/**
30	* Collects all tests for <tt>org.apache.poi.xssf.usermodel</tt> and sub-packages.	30	* Collects all tests for <tt>org.apache.poi.xssf.usermodel</tt> and sub-packages.
31	*	31	*
32	* @author Josh Micich	32	* @author Josh Micich
33	*/	33	*/
34	public final class AllXSSFUsermodelTests {	34	public final class AllXSSFUsermodelTests {
Lines 37-42 public final class AllXSSFUsermodelTests { Link Here
37	TestSuite result = new TestSuite(AllXSSFUsermodelTests.class.getName());	37	TestSuite result = new TestSuite(AllXSSFUsermodelTests.class.getName());
38	result.addTestSuite(TestFormulaEvaluatorOnXSSF.class);	38	result.addTestSuite(TestFormulaEvaluatorOnXSSF.class);
39	result.addTestSuite(TestSheetHiding.class);	39	result.addTestSuite(TestSheetHiding.class);
		40	result.addTestSuite(TestXSSFAddRemoveArrayFormula.class);
		41	result.addTestSuite(TestXSSFArrayEvaluation.class);
		42	result.addTestSuite(TestXSSFArrayFormulaEvaluation.class);
		43	result.addTestSuite(TestXSSFArrayFormulaFunction.class);
40	result.addTestSuite(TestXSSFBugs.class);	44	result.addTestSuite(TestXSSFBugs.class);
41	result.addTestSuite(TestXSSFDataFormat.class);	45	result.addTestSuite(TestXSSFDataFormat.class);
42	result.addTestSuite(TestXSSFCellStyle.class);	46	result.addTestSuite(TestXSSFCellStyle.class);
Lines 59-65 public final class AllXSSFUsermodelTests { Link Here
59	result.addTestSuite(TestXSSFBorder.class);	63	result.addTestSuite(TestXSSFBorder.class);
60	result.addTestSuite(TestXSSFCellFill.class);	64	result.addTestSuite(TestXSSFCellFill.class);
61	result.addTestSuite(TestXSSFSheetComments.class);	65	result.addTestSuite(TestXSSFSheetComments.class);
62		66
63	result.addTestSuite(TestColumnHelper.class);	67	result.addTestSuite(TestColumnHelper.class);
64	result.addTestSuite(TestHeaderFooterHelper.class);	68	result.addTestSuite(TestHeaderFooterHelper.class);
65		69

Lines 30-41 public class AllFormulaEvalTests { Link Here

(-)a/src/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java (+4 lines)
30	public static Test suite() {	30	public static Test suite() {
31	TestSuite result = new TestSuite(AllFormulaEvalTests.class.getName());	31	TestSuite result = new TestSuite(AllFormulaEvalTests.class.getName());
32	result.addTestSuite(TestAreaEval.class);	32	result.addTestSuite(TestAreaEval.class);
		33	result.addTestSuite(TestArrayEval.class);
33	result.addTestSuite(TestCircularReferences.class);	34	result.addTestSuite(TestCircularReferences.class);
34	result.addTestSuite(TestDivideEval.class);	35	result.addTestSuite(TestDivideEval.class);
35	result.addTestSuite(TestEqualEval.class);	36	result.addTestSuite(TestEqualEval.class);
36	result.addTestSuite(TestExternalFunction.class);	37	result.addTestSuite(TestExternalFunction.class);
37	result.addTestSuite(TestFormulaBugs.class);	38	result.addTestSuite(TestFormulaBugs.class);
38	result.addTestSuite(TestFormulasFromSpreadsheet.class);	39	result.addTestSuite(TestFormulasFromSpreadsheet.class);
		40	result.addTestSuite(TestHSSFArrayEvaluation.class);
		41	result.addTestSuite(TestHSSFArrayFormulaEvaluation.class);
		42	result.addTestSuite(TestHSSFArrayFormulaFunction.class);
39	result.addTestSuite(TestMinusZeroResult.class);	43	result.addTestSuite(TestMinusZeroResult.class);
40	result.addTestSuite(TestMissingArgEval.class);	44	result.addTestSuite(TestMissingArgEval.class);
41	result.addTestSuite(TestPercentEval.class);	45	result.addTestSuite(TestPercentEval.class);

Lines 22-35 import junit.framework.TestSuite; Link Here

(-)a/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java (-5 / +6 lines)
22		22
23	/**	23	/**
24	* Collects all tests for the <tt>org.apache.poi.hssf.usermodel</tt> package.	24	* Collects all tests for the <tt>org.apache.poi.hssf.usermodel</tt> package.
25	*	25	*
26	* @author Josh Micich	26	* @author Josh Micich
27	*/	27	*/
28	public class AllUserModelTests {	28	public class AllUserModelTests {
29		29
30	public static Test suite() {	30	public static Test suite() {
31	TestSuite result = new TestSuite(AllUserModelTests.class.getName());	31	TestSuite result = new TestSuite(AllUserModelTests.class.getName());
32		32
33	result.addTestSuite(TestBugs.class);	33	result.addTestSuite(TestBugs.class);
34	result.addTestSuite(TestCellStyle.class);	34	result.addTestSuite(TestCellStyle.class);
35	result.addTestSuite(TestCloneSheet.class);	35	result.addTestSuite(TestCloneSheet.class);
Lines 40-45 public class AllUserModelTests { Link Here
40	result.addTestSuite(TestFormulas.class);	40	result.addTestSuite(TestFormulas.class);
41	result.addTestSuite(TestFormulaEvaluatorBugs.class);	41	result.addTestSuite(TestFormulaEvaluatorBugs.class);
42	result.addTestSuite(TestFormulaEvaluatorDocs.class);	42	result.addTestSuite(TestFormulaEvaluatorDocs.class);
		43	result.addTestSuite(TestHSSFAddRemoveArrayFormula.class);
43	result.addTestSuite(TestHSSFCell.class);	44	result.addTestSuite(TestHSSFCell.class);
44	result.addTestSuite(TestHSSFClientAnchor.class);	45	result.addTestSuite(TestHSSFClientAnchor.class);
45	result.addTestSuite(TestHSSFComment.class);	46	result.addTestSuite(TestHSSFComment.class);
Lines 71-78 public class AllUserModelTests { Link Here
71	}	72	}
72	result.addTestSuite(TestUnicodeWorkbook.class);	73	result.addTestSuite(TestUnicodeWorkbook.class);
73	result.addTestSuite(TestUppercaseWorkbook.class);	74	result.addTestSuite(TestUppercaseWorkbook.class);
74	result.addTestSuite(TestWorkbook.class);	75	result.addTestSuite(TestWorkbook.class);
75		76
76	return result;	77	return result;
77	}	78	}
78	}	79	}

Line 0 Link Here

(-)a/src/testcases/org/apache/poi/ss/usermodel/ArrayFormulaTestHelper.java (+258 lines)
		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.ss.usermodel;
		19
		20	import junit.framework.AssertionFailedError;
		21
		22	import org.apache.commons.logging.Log;
		23	import org.apache.commons.logging.LogFactory;
		24	import org.apache.poi.hssf.HSSFTestDataSamples;
		25	import org.apache.poi.ss.usermodel.Cell;
		26	import org.apache.poi.ss.usermodel.ErrorConstants;
		27	import org.apache.poi.ss.usermodel.FormulaError;
		28	import org.apache.poi.ss.usermodel.FormulaEvaluator;
		29	import org.apache.poi.ss.usermodel.Row;
		30	import org.apache.poi.ss.usermodel.Sheet;
		31	import org.apache.poi.ss.usermodel.Workbook;
		32	import org.apache.poi.ss.util.CellRangeAddress;
		33	import org.apache.poi.ss.util.CellReference;
		34	import org.apache.poi.xssf.XSSFTestDataSamples;
		35
		36	public final class ArrayFormulaTestHelper {
		37	private static final Log log = LogFactory.getLog(ArrayFormulaTestHelper.class);
		38
		39	private final Workbook _wb;
		40
		41	public ArrayFormulaTestHelper(String sampleFileName) {
		42
		43	boolean isXssf = sampleFileName.endsWith(".xlsx");
		44	if (isXssf) {
		45	_wb = XSSFTestDataSamples.openSampleWorkbook(sampleFileName);
		46	} else {
		47	_wb = HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
		48	}
		49	}
		50
		51	public Cell getCell(String cellRef) {
		52
		53	log.debug("Access to Cell:" + cellRef);
		54	Sheet sheet = _wb.getSheetAt(0);
		55	CellReference cellReference = new CellReference(cellRef);
		56	Row row = sheet.getRow(cellReference.getRow());
		57	if (row==null) {
		58	throw new IllegalArgumentException("Illegal access to cell:" + cellRef);
		59	}
		60	Cell cell = row.getCell(cellReference.getCol());
		61	if (cell==null) {
		62	throw new IllegalArgumentException("Illegal access to cell:" + cellRef);
		63	}
		64	return cell;
65	}
66
67
68	public FormulaError calculateNumericFormulaWithError(String cellRef) {
69	Cell cell = getCell(cellRef);
70	if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
71	log.error("Not formula in cell: " + cell.toString());
72	throw new IllegalArgumentException("Not formula" + cell.toString());
73	}
74
75	log.debug("Formula: " + cell.getCellFormula());
76	FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
77	int type = evaluator.evaluateFormulaCell(cell);
78	if (type != Cell.CELL_TYPE_ERROR) {
79	log.error("not error result");
80	throw new IllegalArgumentException("Not error type: " + type);
81	}
82
83	byte result = cell.getErrorCellValue();
84	log.debug("Error value:" + result);
85	return FormulaError.forInt(result);
86	}
87
88	public String calculateStringFormula(String cellRef) {
89
90	Cell cell = getCell(cellRef);
91	if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
92	log.error("Not formula in cell: " + cell.toString());
93	throw new IllegalArgumentException("Not formula" + cell.toString());
94	}
95
96	log.debug("Formula: " + cell.getCellFormula());
97	FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
98	int type = evaluator.evaluateFormulaCell(cell);
99	if (type != Cell.CELL_TYPE_STRING) {
100	log.error("not string result: " + type);
101	throw new IllegalArgumentException("Not string type" + type);
102	}
103
104	String result = cell.getStringCellValue();
105	log.debug("Calculated:" + result);
106	return result;
107	}
108
109	public double calculateNumericFormula(String cellRef) {
110
111	Cell cell = getCell(cellRef);
112	if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
113	log.error("Not formula in cell: " + cell.toString());
114	throw new IllegalArgumentException("Not formula" + cell.toString());
115	}
116
117	log.debug("Formula: " + cell.getCellFormula());
118	FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
119	int type = evaluator.evaluateFormulaCell(cell);
120	if (type != Cell.CELL_TYPE_NUMERIC) {
121	log.error("not numeric result: " + type);
122	throw new IllegalArgumentException("Not numeric type" + type);
123	}
124
125	double result = cell.getNumericCellValue();
126	log.debug("Calculated:" + result);
127	return result;
128	}
129
130	public boolean calculateBooleanFormula(String cellRef) {
131
132	Cell cell = getCell(cellRef);
133	if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
134	log.error("Not formula in cell: " + cell.toString());
135	throw new IllegalArgumentException("Not formula" + cell.toString());
136	}
137
138	log.debug("Formula: " + cell.getCellFormula());
139	FormulaEvaluator evaluator = _wb.getCreationHelper().createFormulaEvaluator();
140	int type = evaluator.evaluateFormulaCell(cell);
141	if (type != Cell.CELL_TYPE_BOOLEAN) {
142	log.error("not boolean result: " + type);
143	throw new IllegalArgumentException("Not boolean type" + type);
144	}
145
146	boolean result = cell.getBooleanCellValue();
147	log.debug("Calculated:" + result);
148	return result;
149	}
150
151	public void confirmNotNumeric(String cellRef) {
152	Cell cell = getCell(cellRef);
153	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
154	throw new AssertionFailedError("Cell type was unexpectedly numeric:" + cell.toString());
155	}
156	}
157
158	public void setNumericValue(String cellRef, double value) {
159	getCell(cellRef).setCellValue(value);
160	}
161
162	public double getNumericValue(String cellRef) {
163
164	Cell cell = getCell(cellRef);
165	if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
166	log.error("Not numeric in cell:" + cell.toString());
167	throw new IllegalArgumentException("Not numeric:" + cell.toString());
168	}
169	double result = cell.getNumericCellValue();
170	log.debug("value: " + result );
171	return result;
172	}
173
174	public String getStringValue(String cellRef) {
175
176	Cell cell = getCell(cellRef);
177	if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
178	log.error("Not string in cell:" + cell.toString());
179	throw new IllegalArgumentException("Not string:" + cell.toString());
180	}
181	String result = cell.getStringCellValue();
182	log.debug("value: " + result );
183	return result;
184	}
185
186	public boolean getBooleanValue(String cellRef) {
187
188	Cell cell = getCell(cellRef);
189	if (cell.getCellType() != Cell.CELL_TYPE_BOOLEAN) {
190	log.error("Not boolean in cell:" + cell.toString());
191	throw new IllegalArgumentException("Not boolean:" + cell.toString());
192	}
193	boolean result = cell.getBooleanCellValue();
194	log.debug("value: " + result );
195	return result;
196	}
197
198	public boolean calculateAndCompareNumericArray(char calculateFromColumn, int calculateFromRow, char calculateToColumn,
199	int calculateToRow, char compareFromColumn, int compareFromRow, double precision) {
200
201	if (calculateFromColumn> calculateToColumn) {
202	throw new IllegalArgumentException("columns: " + calculateFromColumn + " " + calculateToColumn);
203	}
204
205	if (calculateFromRow > calculateToRow) {
206	throw new IllegalArgumentException("rows: " + calculateFromRow + " " + calculateToRow);
207	}
208
209	for(int c=0; calculateFromColumn + c <= calculateToColumn; c++ ) {
210	for(int r=0; calculateFromRow +r <= calculateToRow; r++) {
211	char ch[] = new char[1];
212	ch[0] = (char)(calculateFromColumn+c);
213	String calculateRef = (new String(ch)) + (calculateFromRow+r);
214	double calcResult = calculateNumericFormula(calculateRef);
215
216	ch[0]=(char)(compareFromColumn+c);
217	String compareRef = (new String(ch)) + (compareFromRow + r);
218	double compareResult = getNumericValue(compareRef);
219
220	if (Math.abs(compareResult - calcResult) > precision) {
221	// comparison failed
222	log.debug("Array comparison failed. " + "Calculated cell:" + calculateRef + " Compared cell: " + compareRef +
223	" calculated result: " + calcResult + " compared result: " + compareResult);
224	return false;
225	}
226	}
227	}
228	return true;
229	}
230
231	public void setArrayFormula(String cellRef, String formula, String range) {
232	Sheet sheet = getCell(cellRef).getSheet();
233	sheet.setArrayFormula(formula, CellRangeAddress.valueOf(range));
234	}
235
236	public void removeArrayFormula(String cellRef) {
237	Cell cell = getCell(cellRef);
238	Sheet sheet = cell.getSheet();
239	sheet.removeArrayFormula(cell);
240	}
241
242	public String getErrorValue(String cellRef) {
243
244	Cell cell = getCell(cellRef);
245	if (cell.getCellType() != Cell.CELL_TYPE_ERROR) {
246	log.error("Not numeric in cell:" + cell.toString());
247	throw new IllegalArgumentException("Not error:" + cell.toString());
248	}
249	byte result = cell.getErrorCellValue();
250	log.debug("value: " + result );
251	return ErrorConstants.getText(result);
252	}
253
254	public int getCellType(String cellRef) {
255	Cell cell = getCell(cellRef);
256	return cell.getCellType();
257	}
258	}