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

(-)src/java/org/apache/poi/hssf/record/ArrayRecord.java (-87 / +131 lines)
Lines 1-87 Link Here
1
/* ====================================================================
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
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
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
7
   the License.  You may obtain a copy of the License at
8
8
9
       http://www.apache.org/licenses/LICENSE-2.0
9
       http://www.apache.org/licenses/LICENSE-2.0
10
10
11
   Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
15
   limitations under the License.
16
==================================================================== */
16
==================================================================== */
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.Ptg;
20
import org.apache.poi.hssf.record.formula.AreaPtgBase;
21
import org.apache.poi.ss.formula.Formula;
21
import org.apache.poi.hssf.record.formula.Ptg;
22
import org.apache.poi.util.HexDump;
22
import org.apache.poi.hssf.record.formula.RefPtgBase;
23
import org.apache.poi.util.LittleEndianOutput;
23
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
24
24
import org.apache.poi.ss.formula.Formula;
25
/**
25
import org.apache.poi.util.HexDump;
26
 * ARRAY (0x0221)<p/>
26
import org.apache.poi.util.LittleEndianOutput;
27
 *
27
28
 * Treated in a similar way to SharedFormulaRecord
28
/**
29
 *
29
 * ARRAY (0x0221)<p/>
30
 * @author Josh Micich
30
 *
31
 */
31
 * Treated in a similar way to SharedFormulaRecord
32
public final class ArrayRecord extends SharedValueRecordBase {
32
 *
33
33
 * @author Josh Micich
34
	public final static short sid = 0x0221;
34
 * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support
35
	private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
35
 */
36
	private static final int OPT_CALCULATE_ON_OPEN  = 0x0002;
36
public final class ArrayRecord extends SharedValueRecordBase {
37
37
38
	private int	_options;
38
	public final static short sid = 0x0221;
39
	private int _field3notUsed;
39
	private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
40
	private Formula _formula;
40
	private static final int OPT_CALCULATE_ON_OPEN  = 0x0002;
41
41
42
	public ArrayRecord(RecordInputStream in) {
42
	private int _options;
43
		super(in);
43
	private int _field3notUsed;
44
		_options = in.readUShort();
44
	private Formula _formula;
45
		_field3notUsed = in.readInt();
45
46
		int formulaTokenLen = in.readUShort();
46
	public ArrayRecord(RecordInputStream in) {
47
		int totalFormulaLen = in.available();
47
		super(in);
48
		_formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
48
		_options = in.readUShort();
49
	}
49
		_field3notUsed = in.readInt();
50
50
		int formulaTokenLen = in.readUShort();
51
	public boolean isAlwaysRecalculate() {
51
		int totalFormulaLen = in.available();
52
		return (_options & OPT_ALWAYS_RECALCULATE) != 0;
52
		_formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
53
	}
53
	}
54
	public boolean isCalculateOnOpen() {
54
55
		return (_options & OPT_CALCULATE_ON_OPEN) != 0;
55
	public ArrayRecord(Formula formula, CellRangeAddress8Bit range ) {
56
	}
56
		super(range);
57
57
		_options = OPT_CALCULATE_ON_OPEN;
58
	protected int getExtraDataSize() {
58
		_field3notUsed = 0;
59
		return 2 + 4
59
		_formula = formula;
60
			+ _formula.getEncodedSize();
60
	}
61
	}
61
62
	protected void serializeExtraData(LittleEndianOutput out) {
62
	public boolean isAlwaysRecalculate() {
63
		out.writeShort(_options);
63
		return (_options & OPT_ALWAYS_RECALCULATE) != 0;
64
		out.writeInt(_field3notUsed);
64
	}
65
		_formula.serialize(out);
65
	public boolean isCalculateOnOpen() {
66
	}
66
		return (_options & OPT_CALCULATE_ON_OPEN) != 0;
67
67
	}
68
	public short getSid() {
68
69
		return sid;
69
	protected int getExtraDataSize() {
70
	}
70
		return 2 + 4
71
71
			+ _formula.getEncodedSize();
72
	public String toString() {
72
	}
73
		StringBuffer sb = new StringBuffer();
73
	protected void serializeExtraData(LittleEndianOutput out) {
74
		sb.append(getClass().getName()).append(" [ARRAY]\n");
74
		out.writeShort(_options);
75
		sb.append(" range=").append(getRange().toString()).append("\n");
75
		out.writeInt(_field3notUsed);
76
		sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n");
76
		_formula.serialize(out);
77
		sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n");
77
	}
78
		sb.append(" formula:").append("\n");
78
79
		Ptg[] ptgs = _formula.getTokens();
79
	public short getSid() {
80
		for (int i = 0; i < ptgs.length; i++) {
80
		return sid;
81
			Ptg ptg = ptgs[i];
81
	}
82
			sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n");
82
83
		}
83
	public String toString() {
84
		sb.append("]");
84
		StringBuffer sb = new StringBuffer();
85
		return sb.toString();
85
		sb.append(getClass().getName()).append(" [ARRAY]\n");
86
	}
86
		sb.append(" range=").append(getRange().toString()).append("\n");
87
}
87
		sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n");
88
		sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n");
89
		sb.append(" formula:").append("\n");
90
		Ptg[] ptgs = _formula.getTokens();
91
		for (int i = 0; i < ptgs.length; i++) {
92
			Ptg ptg = ptgs[i];
93
			sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n");
94
		}
95
		sb.append("]");
96
		return sb.toString();
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
	}
131
}
(-)src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java (-3 / +45 lines)
Lines 17-22 Link Here
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 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 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);
186
		}
191
		}
187
		return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
192
		CellReference expRef = _formulaRecord.getFormula().getExpReference();
193
		if (expRef != null) {
194
			ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
195
			return arec.getFormulaTokens();
196
		}
197
		return _formulaRecord.getParsedExpression();
188
	}
198
	}
189
199
190
	/**
200
	/**
Lines 216-219 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
}
(-)src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java (-278 / +322 lines)
Lines 1-278 Link Here
1
/* ====================================================================
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
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
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
7
   the License.  You may obtain a copy of the License at
8
8
9
       http://www.apache.org/licenses/LICENSE-2.0
9
	   http://www.apache.org/licenses/LICENSE-2.0
10
10
11
   Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
15
   limitations under the License.
16
==================================================================== */
16
==================================================================== */
17
17
18
package org.apache.poi.hssf.record.aggregates;
18
package org.apache.poi.hssf.record.aggregates;
19
19
20
import java.util.Arrays;
20
import java.util.ArrayList;
21
import java.util.Comparator;
21
import java.util.Arrays;
22
import java.util.HashMap;
22
import java.util.Comparator;
23
import java.util.Map;
23
import java.util.HashMap;
24
24
import java.util.List;
25
import org.apache.poi.hssf.record.ArrayRecord;
25
import java.util.Map;
26
import org.apache.poi.hssf.record.FormulaRecord;
26
27
import org.apache.poi.hssf.record.SharedFormulaRecord;
27
import org.apache.poi.hssf.record.ArrayRecord;
28
import org.apache.poi.hssf.record.SharedValueRecordBase;
28
import org.apache.poi.hssf.record.FormulaRecord;
29
import org.apache.poi.hssf.record.TableRecord;
29
import org.apache.poi.hssf.record.SharedFormulaRecord;
30
import org.apache.poi.hssf.record.formula.ExpPtg;
30
import org.apache.poi.hssf.record.SharedValueRecordBase;
31
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
31
import org.apache.poi.hssf.record.TableRecord;
32
import org.apache.poi.ss.util.CellReference;
32
import org.apache.poi.hssf.record.formula.ExpPtg;
33
33
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
34
/**
34
import org.apache.poi.ss.util.CellReference;
35
 * Manages various auxiliary records while constructing a
35
36
 * {@link RowRecordsAggregate}:
36
/**
37
 * <ul>
37
 * Manages various auxiliary records while constructing a
38
 * <li>{@link SharedFormulaRecord}s</li>
38
 * {@link RowRecordsAggregate}:
39
 * <li>{@link ArrayRecord}s</li>
39
 * <ul>
40
 * <li>{@link TableRecord}s</li>
40
 * <li>{@link SharedFormulaRecord}s</li>
41
 * </ul>
41
 * <li>{@link ArrayRecord}s</li>
42
 *
42
 * <li>{@link TableRecord}s</li>
43
 * @author Josh Micich
43
 * </ul>
44
 */
44
 *
45
public final class SharedValueManager {
45
 * @author Josh Micich
46
46
 * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - handling of ArrayRecords 
47
	private static final class SharedFormulaGroup {
47
 */
48
		private final SharedFormulaRecord _sfr;
48
public final class SharedValueManager {
49
		private final FormulaRecordAggregate[] _frAggs;
49
50
		private int _numberOfFormulas;
50
	private static final class SharedFormulaGroup {
51
		/**
51
		private final SharedFormulaRecord _sfr;
52
		 * Coordinates of the first cell having a formula that uses this shared formula.
52
		private final FormulaRecordAggregate[] _frAggs;
53
		 * This is often <i>but not always</i> the top left cell in the range covered by
53
		private int _numberOfFormulas;
54
		 * {@link #_sfr}
54
		/**
55
		 */
55
		 * Coordinates of the first cell having a formula that uses this shared formula.
56
		private final CellReference _firstCell;
56
		 * This is often <i>but not always</i> the top left cell in the range covered by
57
57
		 * {@link #_sfr}
58
		public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) {
58
		 */
59
			if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) {
59
		private final CellReference _firstCell;
60
				throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString()
60
61
						+ " is not shared formula range " + sfr.getRange().toString() + ".");
61
		public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) {
62
			}
62
			if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) {
63
			_sfr = sfr;
63
				throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString()
64
			_firstCell = firstCell;
64
						+ " is not shared formula range " + sfr.getRange().toString() + ".");
65
			int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1;
65
			}
66
			int height = sfr.getLastRow() - sfr.getFirstRow() + 1;
66
			_sfr = sfr;
67
			_frAggs = new FormulaRecordAggregate[width * height];
67
			_firstCell = firstCell;
68
			_numberOfFormulas = 0;
68
			int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1;
69
		}
69
			int height = sfr.getLastRow() - sfr.getFirstRow() + 1;
70
70
			_frAggs = new FormulaRecordAggregate[width * height];
71
		public void add(FormulaRecordAggregate agg) {
71
			_numberOfFormulas = 0;
72
			if (_numberOfFormulas == 0) {
72
		}
73
				if (_firstCell.getRow() != agg.getRow() || _firstCell.getCol() != agg.getColumn()) {
73
74
					throw new IllegalStateException("shared formula coding error");
74
		public void add(FormulaRecordAggregate agg) {
75
				}
75
			if (_numberOfFormulas == 0) {
76
			}
76
				if (_firstCell.getRow() != agg.getRow() || _firstCell.getCol() != agg.getColumn()) {
77
			if (_numberOfFormulas >= _frAggs.length) {
77
					throw new IllegalStateException("shared formula coding error");
78
				throw new RuntimeException("Too many formula records for shared formula group");
78
				}
79
			}
79
			}
80
			_frAggs[_numberOfFormulas++] = agg;
80
			if (_numberOfFormulas >= _frAggs.length) {
81
		}
81
				throw new RuntimeException("Too many formula records for shared formula group");
82
82
			}
83
		public void unlinkSharedFormulas() {
83
			_frAggs[_numberOfFormulas++] = agg;
84
			for (int i = 0; i < _numberOfFormulas; i++) {
84
		}
85
				_frAggs[i].unlinkSharedFormula();
85
86
			}
86
		public void unlinkSharedFormulas() {
87
		}
87
			for (int i = 0; i < _numberOfFormulas; i++) {
88
88
				_frAggs[i].unlinkSharedFormula();
89
		public SharedFormulaRecord getSFR() {
89
			}
90
			return _sfr;
90
		}
91
		}
91
92
92
		public SharedFormulaRecord getSFR() {
93
		public final String toString() {
93
			return _sfr;
94
			StringBuffer sb = new StringBuffer(64);
94
		}
95
			sb.append(getClass().getName()).append(" [");
95
96
			sb.append(_sfr.getRange().toString());
96
		public final String toString() {
97
			sb.append("]");
97
			StringBuffer sb = new StringBuffer(64);
98
			return sb.toString();
98
			sb.append(getClass().getName()).append(" [");
99
		}
99
			sb.append(_sfr.getRange().toString());
100
100
			sb.append("]");
101
		/**
101
			return sb.toString();
102
		 * Note - the 'first cell' of a shared formula group is not always the top-left cell
102
		}
103
		 * of the enclosing range.
103
104
		 * @return <code>true</code> if the specified coordinates correspond to the 'first cell'
104
		/**
105
		 * of this shared formula group.
105
		 * Note - the 'first cell' of a shared formula group is not always the top-left cell
106
		 */
106
		 * of the enclosing range.
107
		public boolean isFirstCell(int row, int column) {
107
		 * @return <code>true</code> if the specified coordinates correspond to the 'first cell'
108
			return _firstCell.getRow() == row && _firstCell.getCol() == column;
108
		 * of this shared formula group.
109
		}
109
		 */
110
	}
110
		public boolean isFirstCell(int row, int column) {
111
111
			return _firstCell.getRow() == row && _firstCell.getCol() == column;
112
	public static final SharedValueManager EMPTY = new SharedValueManager(
112
		}
113
			new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]);
113
	}
114
	private final ArrayRecord[] _arrayRecords;
114
115
	private final TableRecord[] _tableRecords;
115
	public static final SharedValueManager EMPTY = new SharedValueManager(
116
	private final Map<SharedFormulaRecord, SharedFormulaGroup> _groupsBySharedFormulaRecord;
116
			new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]);
117
	/** cached for optimization purposes */
117
	private final List<ArrayRecord> _arrayRecords;
118
	private SharedFormulaGroup[] _groups;
118
	private final TableRecord[] _tableRecords;
119
119
	private final Map<SharedFormulaRecord, SharedFormulaGroup> _groupsBySharedFormulaRecord;
120
	private SharedValueManager(SharedFormulaRecord[] sharedFormulaRecords,
120
	/** cached for optimization purposes */
121
			CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) {
121
	private SharedFormulaGroup[] _groups;
122
		int nShF = sharedFormulaRecords.length;
122
123
		if (nShF != firstCells.length) {
123
	private SharedValueManager(SharedFormulaRecord[] sharedFormulaRecords,
124
			throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + ".");
124
			CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) {
125
		}
125
		int nShF = sharedFormulaRecords.length;
126
		_arrayRecords = arrayRecords;
126
		if (nShF != firstCells.length) {
127
		_tableRecords = tableRecords;
127
			throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + ".");
128
		Map<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2);
128
		}
129
		for (int i = 0; i < nShF; i++) {
129
		_arrayRecords = toList(arrayRecords);
130
			SharedFormulaRecord sfr = sharedFormulaRecords[i];
130
		_tableRecords = tableRecords;
131
			m.put(sfr, new SharedFormulaGroup(sfr, firstCells[i]));
131
		Map<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2);
132
		}
132
		for (int i = 0; i < nShF; i++) {
133
		_groupsBySharedFormulaRecord = m;
133
			SharedFormulaRecord sfr = sharedFormulaRecords[i];
134
	}
134
			m.put(sfr, new SharedFormulaGroup(sfr, firstCells[i]));
135
135
		}
136
	/**
136
		_groupsBySharedFormulaRecord = m;
137
	 * @param firstCells
137
	}
138
	 * @param recs list of sheet records (possibly contains records for other parts of the Excel file)
138
139
	 * @param startIx index of first row/cell record for current sheet
139
	private static <Z> List<Z> toList(Z[] zz) {
140
	 * @param endIx one past index of last row/cell record for current sheet.  It is important
140
		List<Z> result = new ArrayList<Z>(zz.length);
141
	 * that this code does not inadvertently collect <tt>SharedFormulaRecord</tt>s from any other
141
		for (int i = 0; i < zz.length; i++) {
142
	 * sheet (which could happen if endIx is chosen poorly).  (see bug 44449)
142
			result.add(zz[i]);
143
	 */
143
		}
144
	public static SharedValueManager create(SharedFormulaRecord[] sharedFormulaRecords,
144
		return result;
145
			CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) {
145
	}
146
		if (sharedFormulaRecords.length + firstCells.length + arrayRecords.length + tableRecords.length < 1) {
146
147
			return EMPTY;
147
	/**
148
		}
148
	 * @param firstCells
149
		return new SharedValueManager(sharedFormulaRecords, firstCells, arrayRecords, tableRecords);
149
	 * @param recs list of sheet records (possibly contains records for other parts of the Excel file)
150
	}
150
	 * @param startIx index of first row/cell record for current sheet
151
151
	 * @param endIx one past index of last row/cell record for current sheet.  It is important
152
152
	 * that this code does not inadvertently collect <tt>SharedFormulaRecord</tt>s from any other
153
	/**
153
	 * sheet (which could happen if endIx is chosen poorly).  (see bug 44449)
154
	 * @param firstCell as extracted from the {@link ExpPtg} from the cell's formula.
154
	 */
155
	 * @return never <code>null</code>
155
	public static SharedValueManager create(SharedFormulaRecord[] sharedFormulaRecords,
156
	 */
156
			CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) {
157
	public SharedFormulaRecord linkSharedFormulaRecord(CellReference firstCell, FormulaRecordAggregate agg) {
157
		if (sharedFormulaRecords.length + firstCells.length + arrayRecords.length + tableRecords.length < 1) {
158
158
			return EMPTY;
159
		SharedFormulaGroup result = findFormulaGroup(getGroups(), firstCell);
159
		}
160
		result.add(agg);
160
		return new SharedValueManager(sharedFormulaRecords, firstCells, arrayRecords, tableRecords);
161
		return result.getSFR();
161
	}
162
	}
162
163
163
164
	private static SharedFormulaGroup findFormulaGroup(SharedFormulaGroup[] groups, CellReference firstCell) {
164
	/**
165
		int row = firstCell.getRow();
165
	 * @param firstCell as extracted from the {@link ExpPtg} from the cell's formula.
166
		int column = firstCell.getCol();
166
	 * @return never <code>null</code>
167
		// Traverse the list of shared formulas and try to find the correct one for us
167
	 */
168
168
	public SharedFormulaRecord linkSharedFormulaRecord(CellReference firstCell, FormulaRecordAggregate agg) {
169
		// perhaps this could be optimised to some kind of binary search
169
170
		for (int i = 0; i < groups.length; i++) {
170
		SharedFormulaGroup result = findFormulaGroup(getGroups(), firstCell);
171
			SharedFormulaGroup svg = groups[i];
171
		result.add(agg);
172
			if (svg.isFirstCell(row, column)) {
172
		return result.getSFR();
173
				return svg;
173
	}
174
			}
174
175
		}
175
	private static SharedFormulaGroup findFormulaGroup(SharedFormulaGroup[] groups, CellReference firstCell) {
176
		// TODO - fix file "15228.xls" so it opens in Excel after rewriting with POI
176
		int row = firstCell.getRow();
177
		throw new RuntimeException("Failed to find a matching shared formula record");
177
		int column = firstCell.getCol();
178
	}
178
		// Traverse the list of shared formulas and try to find the correct one for us
179
179
180
	private SharedFormulaGroup[] getGroups() {
180
		// perhaps this could be optimised to some kind of binary search
181
		if (_groups == null) {
181
		for (int i = 0; i < groups.length; i++) {
182
			SharedFormulaGroup[] groups = new SharedFormulaGroup[_groupsBySharedFormulaRecord.size()];
182
			SharedFormulaGroup svg = groups[i];
183
			_groupsBySharedFormulaRecord.values().toArray(groups);
183
			if (svg.isFirstCell(row, column)) {
184
			Arrays.sort(groups, SVGComparator); // make search behaviour more deterministic
184
				return svg;
185
			_groups = groups;
185
			}
186
		}
186
		}
187
		return _groups;
187
		// TODO - fix file "15228.xls" so it opens in Excel after rewriting with POI
188
	}
188
		throw new RuntimeException("Failed to find a matching shared formula record");
189
189
	}
190
	private static final Comparator<SharedFormulaGroup> SVGComparator = new Comparator<SharedFormulaGroup>() {
190
191
191
	private SharedFormulaGroup[] getGroups() {
192
		public int compare(SharedFormulaGroup a, SharedFormulaGroup b) {
192
		if (_groups == null) {
193
			CellRangeAddress8Bit rangeA = a.getSFR().getRange();
193
			SharedFormulaGroup[] groups = new SharedFormulaGroup[_groupsBySharedFormulaRecord.size()];
194
			CellRangeAddress8Bit rangeB = b.getSFR().getRange();
194
			_groupsBySharedFormulaRecord.values().toArray(groups);
195
195
			Arrays.sort(groups, SVGComparator); // make search behaviour more deterministic
196
			int cmp;
196
			_groups = groups;
197
			cmp = rangeA.getFirstRow() - rangeB.getFirstRow();
197
		}
198
			if (cmp != 0) {
198
		return _groups;
199
				return cmp;
199
	}
200
			}
200
201
			cmp = rangeA.getFirstColumn() - rangeB.getFirstColumn();
201
	private static final Comparator<SharedFormulaGroup> SVGComparator = new Comparator<SharedFormulaGroup>() {
202
			if (cmp != 0) {
202
203
				return cmp;
203
		public int compare(SharedFormulaGroup a, SharedFormulaGroup b) {
204
			}
204
			CellRangeAddress8Bit rangeA = a.getSFR().getRange();
205
			return 0;
205
			CellRangeAddress8Bit rangeB = b.getSFR().getRange();
206
		}
206
207
	};
207
			int cmp;
208
208
			cmp = rangeA.getFirstRow() - rangeB.getFirstRow();
209
	/**
209
			if (cmp != 0) {
210
	 * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the
210
				return cmp;
211
	 * formula record contained in the specified {@link FormulaRecordAggregate} agg.  Note - the
211
			}
212
	 * shared value record always appears after the first formula record in the group.  For arrays
212
			cmp = rangeA.getFirstColumn() - rangeB.getFirstColumn();
213
	 * and tables the first formula is always the in the top left cell.  However, since shared
213
			if (cmp != 0) {
214
	 * formula groups can be sparse and/or overlap, the first formula may not actually be in the
214
				return cmp;
215
	 * top left cell.
215
			}
216
	 *
216
			return 0;
217
	 * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of
217
		}
218
	 * a table or array region. <code>null</code> if the formula cell is not shared/array/table,
218
	};
219
	 * or if the specified formula is not the the first in the group.
219
220
	 */
220
	/**
221
	public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) {
221
	 * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the
222
		CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference();
222
	 * formula record contained in the specified {@link FormulaRecordAggregate} agg.  Note - the
223
		// perhaps this could be optimised by consulting the (somewhat unreliable) isShared flag
223
	 * shared value record always appears after the first formula record in the group.  For arrays
224
		// and/or distinguishing between tExp and tTbl.
224
	 * and tables the first formula is always the in the top left cell.  However, since shared
225
		if (firstCell == null) {
225
	 * formula groups can be sparse and/or overlap, the first formula may not actually be in the
226
			// not a shared/array/table formula
226
	 * top left cell.
227
			return null;
227
	 *
228
		}
228
	 * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of
229
229
	 * a table or array region. <code>null</code> if the formula cell is not shared/array/table,
230
230
	 * or if the specified formula is not the the first in the group.
231
		int row = firstCell.getRow();
231
	 */
232
		int column = firstCell.getCol();
232
	public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) {
233
		if (agg.getRow() != row || agg.getColumn() != column) {
233
		CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference();
234
			// not the first formula cell in the group
234
		// perhaps this could be optimised by consulting the (somewhat unreliable) isShared flag
235
			return null;
235
		// and/or distinguishing between tExp and tTbl.
236
		}
236
		if (firstCell == null) {
237
		SharedFormulaGroup[] groups = getGroups();
237
			// not a shared/array/table formula
238
		for (int i = 0; i < groups.length; i++) {
238
			return null;
239
			// note - logic for finding correct shared formula group is slightly
239
		}
240
			// more complicated since the first cell
240
241
			SharedFormulaGroup sfg = groups[i];
241
242
			if (sfg.isFirstCell(row, column)) {
242
		int row = firstCell.getRow();
243
				return sfg.getSFR();
243
		int column = firstCell.getCol();
244
			}
244
		if (agg.getRow() != row || agg.getColumn() != column) {
245
		}
245
			// not the first formula cell in the group
246
246
			return null;
247
		// Since arrays and tables cannot be sparse (all cells in range participate)
247
		}
248
		// The first cell will be the top left in the range.  So we can match the
248
		SharedFormulaGroup[] groups = getGroups();
249
		// ARRAY/TABLE record directly.
249
		for (int i = 0; i < groups.length; i++) {
250
250
			// note - logic for finding correct shared formula group is slightly
251
		for (int i = 0; i < _tableRecords.length; i++) {
251
			// more complicated since the first cell
252
			TableRecord tr = _tableRecords[i];
252
			SharedFormulaGroup sfg = groups[i];
253
			if (tr.isFirstCell(row, column)) {
253
			if (sfg.isFirstCell(row, column)) {
254
				return tr;
254
				return sfg.getSFR();
255
			}
255
			}
256
		}
256
		}
257
		for (int i = 0; i < _arrayRecords.length; i++) {
257
258
			ArrayRecord ar = _arrayRecords[i];
258
		// Since arrays and tables cannot be sparse (all cells in range participate)
259
			if (ar.isFirstCell(row, column)) {
259
		// The first cell will be the top left in the range.  So we can match the
260
				return ar;
260
		// ARRAY/TABLE record directly.
261
			}
261
262
		}
262
		for (TableRecord tr : _tableRecords) {
263
		return null;
263
			if (tr.isFirstCell(row, column)) {
264
	}
264
				return tr;
265
265
			}
266
	/**
266
		}
267
	 * Converts all {@link FormulaRecord}s handled by <tt>sharedFormulaRecord</tt>
267
		for (ArrayRecord ar : _arrayRecords) {
268
	 * to plain unshared formulas
268
			if (ar.isFirstCell(row, column)) {
269
	 */
269
				return ar;
270
	public void unlink(SharedFormulaRecord sharedFormulaRecord) {
270
			}
271
		SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord);
271
		}
272
		_groups = null; // be sure to reset cached value
272
		return null;
273
		if (svg == null) {
273
	}
274
			throw new IllegalStateException("Failed to find formulas for shared formula");
274
275
		}
275
	/**
276
		svg.unlinkSharedFormulas();
276
	 * Converts all {@link FormulaRecord}s handled by <tt>sharedFormulaRecord</tt>
277
	}
277
	 * to plain unshared formulas
278
}
278
	 */
279
	public void unlink(SharedFormulaRecord sharedFormulaRecord) {
280
		SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord);
281
		if (svg == null) {
282
			throw new IllegalStateException("Failed to find formulas for shared formula");
283
		}
284
		_groups = null; // be sure to reset cached value
285
		svg.unlinkSharedFormulas();
286
	}
287
288
	/**
289
	 * Add specified Array Record.
290
	 */
291
	public void addArrayRecord(ArrayRecord ar) {
292
		// could do a check here to make sure none of the ranges overlap
293
		_arrayRecords.add(ar);
294
	}
295
296
	/**
297
	 * Removes the {@link ArrayRecord} for the cell group containing the specified cell.
298
	 * The caller should clear (set blank) all cells in the returned range.
299
	 * @return the range of the array formula which was just removed. Never <code>null</code>.
300
	 */
301
	public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) {
302
		for (ArrayRecord ar : _arrayRecords) {
303
			if (ar.isInRange(rowIndex, columnIndex)) {
304
				_arrayRecords.remove(ar);
305
				return ar.getRange();
306
			}
307
		}
308
		throw new IllegalArgumentException("Specified cell is not part of an array formula.");
309
	}
310
311
	/**
312
	 * @return the shared ArrayRecord identified by (firstRow, firstColumn). never <code>null</code>.
313
	 */
314
	public ArrayRecord getArrayRecord(int firstRow, int firstColumn) {
315
		for(ArrayRecord ar : _arrayRecords) {
316
			if(ar.isFirstCell(firstRow, firstColumn)) {
317
				return ar;
318
			}
319
		}
320
		return null;
321
	}
322
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (-1 / +8 lines)
Lines 25-34 Link Here
25
25
26
/**
26
/**
27
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
27
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
28
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array suport;
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 Link Here
66
67
67
			return new NumberEval(result);
68
			return new NumberEval(result);
68
		}
69
		}
70
		
71
		public boolean supportArray(int paramIndex){
72
			if ( paramIndex == 1)
73
				return false;
74
			return true;
75
		}
69
	}
76
	}
70
	private static final class ValueCollector extends MultiOperandNumericFunction {
77
	private static final class ValueCollector extends MultiOperandNumericFunction {
71
		private static final ValueCollector instance = new ValueCollector();
78
		private static final ValueCollector instance = new ValueCollector();
(-)src/java/org/apache/poi/hssf/record/formula/functions/ArrayMode.java (+17 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.hssf.record.formula.functions;
5
6
import org.apache.poi.hssf.record.formula.eval.ValueEval;
7
8
/**
9
 * Interface for those functions that behaves differently in array formula
10
 * 
11
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
12
 */
13
public interface ArrayMode {
14
15
    ValueEval evaluateInArrayFormula(ValueEval[] args, int srcRowIndex, int srcColumnIndex);
16
17
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/BooleanFunction.java (-1 / +28 lines)
Lines 24-29 Link Here
24
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
25
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
25
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.ss.formula.ArrayEval;
27
28
28
/**
29
/**
29
 * Here are the general rules concerning Boolean functions:
30
 * Here are the general rules concerning Boolean functions:
Lines 36-42 Link Here
36
 *
37
 *
37
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
38
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
38
 */
39
 */
39
public abstract class BooleanFunction implements Function {
40
public abstract class BooleanFunction implements FunctionWithArraySupport {
40
41
41
	public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) {
42
	public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) {
42
		if (args.length < 1) {
43
		if (args.length < 1) {
Lines 51-56 Link Here
51
		return BoolEval.valueOf(boolResult);
52
		return BoolEval.valueOf(boolResult);
52
	}
53
	}
53
54
55
	public boolean supportArray(int paramIndex){
56
		return true;
57
	}
58
54
	private boolean calculate(ValueEval[] args) throws EvaluationException {
59
	private boolean calculate(ValueEval[] args) throws EvaluationException {
55
60
56
		boolean result = getInitialResultValue();
61
		boolean result = getInitialResultValue();
Lines 77-86 Link Here
77
				}
82
				}
78
				continue;
83
				continue;
79
			}
84
			}
85
86
			if (arg instanceof ArrayEval){
87
				ArrayEval ae = (ArrayEval)arg;
88
				int rows = ae.getRowCount();
89
				int cols = ae.getColCount();
90
				for (int r=0; r<rows; r++){
91
					for (int c=0; c<cols; c++){
92
						ValueEval ve = ae.getArrayElementAsEval(r, c);
93
						Boolean tempVe = OperandResolver.coerceValueToBoolean(ve, true);
94
						if (tempVe != null) {
95
							result = partialEvaluate(result, tempVe.booleanValue());
96
							atleastOneNonBlank = true;
97
						}
98
						
99
					}
100
				}
101
				continue;
102
			}
103
80
			Boolean tempVe;
104
			Boolean tempVe;
81
			if (arg instanceof RefEval) {
105
			if (arg instanceof RefEval) {
82
				ValueEval ve = ((RefEval) arg).getInnerValueEval();
106
				ValueEval ve = ((RefEval) arg).getInnerValueEval();
83
				tempVe = OperandResolver.coerceValueToBoolean(ve, true);
107
				tempVe = OperandResolver.coerceValueToBoolean(ve, true);
108
			} else if (arg instanceof ValueEval) {
109
				ValueEval ve = (ValueEval) arg;
110
				tempVe = OperandResolver.coerceValueToBoolean(ve, false);
84
			} else {
111
			} else {
85
				tempVe = OperandResolver.coerceValueToBoolean(arg, false);
112
				tempVe = OperandResolver.coerceValueToBoolean(arg, false);
86
			}
113
			}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Choose.java (-1 / +6 lines)
Lines 27-33 Link Here
27
/**
27
/**
28
 * @author Josh Micich
28
 * @author Josh Micich
29
 */
29
 */
30
public final class Choose implements Function {
30
public final class Choose implements FunctionWithArraySupport {
31
31
32
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
32
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
33
		if (args.length < 2) {
33
		if (args.length < 2) {
Lines 54-57 Link Here
54
		ValueEval ev = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
54
		ValueEval ev = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
55
		return OperandResolver.coerceValueToInt(ev);
55
		return OperandResolver.coerceValueToInt(ev);
56
	}
56
	}
57
	
58
	public boolean supportArray(int paramIndex) {
59
60
        return false;
61
    }
57
}
62
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Column.java (-1 / +15 lines)
Lines 22-29 Link Here
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
25
import org.apache.poi.ss.formula.ArrayEval;
25
26
26
public final class Column implements Function0Arg, Function1Arg {
27
public final class Column implements Function0Arg, Function1Arg, ArrayMode {
27
28
28
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
29
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
29
        return new NumberEval(srcColumnIndex+1);
30
        return new NumberEval(srcColumnIndex+1);
Lines 51-54 Link Here
51
        }
52
        }
52
        return ErrorEval.VALUE_INVALID;
53
        return ErrorEval.VALUE_INVALID;
53
    }
54
    }
55
56
    public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) {
57
        if ((evals.length == 1) && (evals[0] instanceof AreaEval)) {
58
            AreaEval ae = (AreaEval) evals[0];
59
            ValueEval[][] result = new ValueEval[1][ae.getWidth()];
60
            for (int c = 0; c < ae.getWidth(); c++) {
61
                result[0][c] = new NumberEval(ae.getFirstColumn() + c + 1);
62
            }
63
            return new ArrayEval(result);
64
        }
65
        return evaluate(evals, srcCellRow, srcCellCol);
66
67
    }
54
}
68
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Columns.java (-1 / +9 lines)
Lines 22-38 Link Here
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
25
import org.apache.poi.ss.formula.ArrayEval;
25
26
26
/**
27
/**
27
 * Implementation for Excel COLUMNS function.
28
 * Implementation for Excel COLUMNS function.
28
 *
29
 *
29
 * @author Josh Micich
30
 * @author Josh Micich
30
 */
31
 */
31
public final class Columns extends Fixed1ArgFunction {
32
public final class Columns extends Fixed1ArgFunction implements FunctionWithArraySupport {
32
33
33
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34
35
35
		int result;
36
		int result;
37
		if (arg0 instanceof ArrayEval) {
38
            arg0 = ((ArrayEval) arg0).arrayAsArea();
39
        }
36
		if (arg0 instanceof AreaEval) {
40
		if (arg0 instanceof AreaEval) {
37
			result = ((AreaEval) arg0).getWidth();
41
			result = ((AreaEval) arg0).getWidth();
38
		} else if (arg0 instanceof RefEval) {
42
		} else if (arg0 instanceof RefEval) {
Lines 42-45 Link Here
42
		}
46
		}
43
		return new NumberEval(result);
47
		return new NumberEval(result);
44
	}
48
	}
49
	
50
	public boolean supportArray(int paramIndex) {
51
        return true;
52
    }
45
}
53
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Count.java (-1 / +5 lines)
Lines 34-40 Link Here
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 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
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/CountUtils.java (+5 lines)
Lines 20-30 Link Here
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
21
import org.apache.poi.hssf.record.formula.eval.RefEval;
21
import org.apache.poi.hssf.record.formula.eval.RefEval;
22
import org.apache.poi.hssf.record.formula.eval.ValueEval;
22
import org.apache.poi.hssf.record.formula.eval.ValueEval;
23
import org.apache.poi.ss.formula.ArrayEval;
23
24
24
/**
25
/**
25
 * Common logic for COUNT, COUNTA and COUNTIF
26
 * Common logic for COUNT, COUNTA and COUNTIF
26
 *
27
 *
27
 * @author Josh Micich
28
 * @author Josh Micich
29
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
28
 */
30
 */
29
final class CountUtils {
31
final class CountUtils {
30
32
Lines 72-77 Link Here
72
		if (eval instanceof AreaEval) {
74
		if (eval instanceof AreaEval) {
73
			return CountUtils.countMatchingCellsInArea((AreaEval) eval, criteriaPredicate);
75
			return CountUtils.countMatchingCellsInArea((AreaEval) eval, criteriaPredicate);
74
		}
76
		}
77
		if (eval instanceof ArrayEval){
78
			return CountUtils.countMatchingCellsInArea( ( (ArrayEval)eval).arrayAsArea(), criteriaPredicate);
79
		}
75
		if (eval instanceof RefEval) {
80
		if (eval instanceof RefEval) {
76
			return CountUtils.countMatchingCell((RefEval) eval, criteriaPredicate);
81
			return CountUtils.countMatchingCell((RefEval) eval, criteriaPredicate);
77
		}
82
		}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Counta.java (-1 / +5 lines)
Lines 32-38 Link Here
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 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
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Countif.java (-1 / +7 lines)
Lines 44-50 Link Here
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 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
        return false;  // TODO - counif does not allow first param as array - only as range 
533
    }
528
}
534
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/FunctionWithArraySupport.java (+15 lines)
Line 0 Link Here
1
/**
2
 * Later this interface should be merged with Function interface
3
 */
4
package org.apache.poi.hssf.record.formula.functions;
5
6
/**
7
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
8
 */
9
public interface FunctionWithArraySupport extends Function {
10
11
	/*
12
	 * true if parameter accept array, false otherwise
13
	 */
14
	public boolean supportArray(int paramIndex); 
15
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java (-1 / +7 lines)
Lines 39-45 Link Here
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 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
        if (paramIndex == 1)
83
            return true;
84
        return false;
85
    }
80
}
86
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Index.java (-6 / +65 lines)
Lines 25-30 Link Here
25
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
25
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.hssf.record.formula.eval.ValueEval;
27
import org.apache.poi.hssf.record.formula.eval.ValueEval;
28
import org.apache.poi.ss.formula.ArrayEval;
28
29
29
/**
30
/**
30
 * Implementation for the Excel function INDEX
31
 * Implementation for the Excel function INDEX
Lines 43-72 Link Here
43
 * </p>
44
 * </p>
44
 *
45
 *
45
 * @author Josh Micich
46
 * @author Josh Micich
47
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
46
 */
48
 */
47
public final class Index implements Function2Arg, Function3Arg, Function4Arg {
49
public final class Index implements Function2Arg, Function3Arg, Function4Arg, FunctionWithArraySupport, ArrayMode {
48
50
49
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
51
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
52
		return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, false);
53
	}
54
	
55
	
56
	public ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, boolean supportRowColumn) {
57
58
		if (arg0 instanceof ArrayEval){
59
			supportRowColumn = true;
60
		}
50
		AreaEval reference = convertFirstArg(arg0);
61
		AreaEval reference = convertFirstArg(arg0);
51
62
52
		boolean colArgWasPassed = false;
63
		boolean colArgWasPassed = false;
53
		int columnIx = 0;
64
		int columnIx = 0;
54
		try {
65
		try {
55
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
66
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
56
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex);
67
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn);
57
		} catch (EvaluationException e) {
68
		} catch (EvaluationException e) {
58
			return e.getErrorEval();
69
			return e.getErrorEval();
59
		}
70
		}
60
	}
71
	}
61
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
72
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
62
			ValueEval arg2) {
73
			ValueEval arg2) {
74
		return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, false);
75
	}
76
	public ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
77
			ValueEval arg2, boolean supportRowColumn) {
78
79
		if (arg0 instanceof ArrayEval){
80
			supportRowColumn = true;
81
		}
63
		AreaEval reference = convertFirstArg(arg0);
82
		AreaEval reference = convertFirstArg(arg0);
64
83
65
		boolean colArgWasPassed = true;
84
		boolean colArgWasPassed = true;
66
		try {
85
		try {
67
			int columnIx = resolveIndexArg(arg2, srcRowIndex, srcColumnIndex);
86
			int columnIx = resolveIndexArg(arg2, srcRowIndex, srcColumnIndex);
68
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
87
			int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex);
69
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex);
88
			return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn);
70
		} catch (EvaluationException e) {
89
		} catch (EvaluationException e) {
71
			return e.getErrorEval();
90
			return e.getErrorEval();
72
		}
91
		}
Lines 87-92 Link Here
87
			// convert to area ref for simpler code in getValueFromArea()
106
			// convert to area ref for simpler code in getValueFromArea()
88
			return ((RefEval)firstArg).offset(0, 0, 0, 0);
107
			return ((RefEval)firstArg).offset(0, 0, 0, 0);
89
		}
108
		}
109
		if (firstArg instanceof ArrayEval){
110
			firstArg = ((ArrayEval)firstArg).arrayAsArea();
111
		}
90
		if((firstArg instanceof AreaEval)) {
112
		if((firstArg instanceof AreaEval)) {
91
			return (AreaEval) firstArg;
113
			return (AreaEval) firstArg;
92
		}
114
		}
Lines 98-108 Link Here
98
	}
120
	}
99
121
100
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
122
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
123
		return evaluateX(args, srcRowIndex, srcColumnIndex, false);
124
	}
125
	
126
	public ValueEval evaluateX(ValueEval[] args, int srcRowIndex, int srcColumnIndex, boolean supportRowColumn) {
101
		switch (args.length) {
127
		switch (args.length) {
102
			case 2:
128
			case 2:
103
				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]);
129
				return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], supportRowColumn);
104
			case 3:
130
			case 3:
105
				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]);
131
				return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], supportRowColumn);
106
			case 4:
132
			case 4:
107
				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]);
133
				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]);
108
		}
134
		}
Lines 118-129 Link Here
118
	 *            different when only 2 args are passed.
144
	 *            different when only 2 args are passed.
119
	 */
145
	 */
120
	private static ValueEval getValueFromArea(AreaEval ae, int pRowIx, int pColumnIx,
146
	private static ValueEval getValueFromArea(AreaEval ae, int pRowIx, int pColumnIx,
121
			boolean colArgWasPassed, int srcRowIx, int srcColIx) throws EvaluationException {
147
			boolean colArgWasPassed, int srcRowIx, int srcColIx, boolean supportRowColumn) throws EvaluationException {
122
		boolean rowArgWasEmpty = pRowIx == 0;
148
		boolean rowArgWasEmpty = pRowIx == 0;
123
		boolean colArgWasEmpty = pColumnIx == 0;
149
		boolean colArgWasEmpty = pColumnIx == 0;
124
		int rowIx;
150
		int rowIx;
125
		int columnIx;
151
		int columnIx;
126
152
153
		// implementation of this function isn't support all features of the Excel
154
		// here I'm adding only support for return of entire row or columm
155
		if ( supportRowColumn && ( (rowArgWasEmpty && !ae.isRow() && pColumnIx<=ae.getWidth() && !colArgWasEmpty) ||
156
			 (colArgWasEmpty && !ae.isColumn() && pRowIx<=ae.getHeight() && !rowArgWasEmpty))
157
			){
158
			// return row or column
159
			ValueEval[][] result = null;
160
			if (rowArgWasEmpty ){ // entire column
161
				result = new ValueEval[ae.getHeight()][1];
162
				for( int r=0; r<ae.getHeight(); r++ ){
163
					result[r][0] = ae.getRelativeValue(r, pColumnIx-1);
164
				}
165
			}
166
			else { //entire row
167
				result = new ValueEval[1][ae.getWidth()];
168
				for (int c=0; c<ae.getWidth(); c++){
169
					result[0][c] = ae.getRelativeValue(	pRowIx-1,c);
170
				}
171
			}
172
			return (new ArrayEval(result));
173
		}
174
127
		// when the area ref is a single row or a single column,
175
		// when the area ref is a single row or a single column,
128
		// there are special rules for conversion of rowIx and columnIx
176
		// there are special rules for conversion of rowIx and columnIx
129
		if (ae.isRow()) {
177
		if (ae.isRow()) {
Lines 209-212 Link Here
209
		}
257
		}
210
		return result;
258
		return result;
211
	}
259
	}
260
	
261
	public ValueEval evaluateInArrayFormula(ValueEval[] args, int srcCellRow, int srcCellCol) {
262
		// in array formula index(reference,row,0) and index(reference,0,col) should return entire row/column
263
		return evaluateX(args, srcCellRow, srcCellCol, true);
264
	}
265
	
266
	public boolean supportArray(int paramIndex){
267
		if (paramIndex == 0)
268
			return true;
269
		return false;
270
	}
212
}
271
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java (-1 / +13 lines)
Lines 36-43 Link Here
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 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
            default:
81
                return false;
82
            case 1:
83
            case 2:
84
                return true;
85
        }
86
87
    }
76
}
88
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java (+5 lines)
Lines 28-38 Link Here
28
import org.apache.poi.hssf.record.formula.eval.RefEval;
28
import org.apache.poi.hssf.record.formula.eval.RefEval;
29
import org.apache.poi.hssf.record.formula.eval.StringEval;
29
import org.apache.poi.hssf.record.formula.eval.StringEval;
30
import org.apache.poi.hssf.record.formula.eval.ValueEval;
30
import org.apache.poi.hssf.record.formula.eval.ValueEval;
31
import org.apache.poi.ss.formula.ArrayEval;
31
32
32
/**
33
/**
33
 * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
34
 * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
34
 *
35
 *
35
 * @author Josh Micich
36
 * @author Josh Micich
37
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
36
 */
38
 */
37
final class LookupUtils {
39
final class LookupUtils {
38
40
Lines 373-378 Link Here
373
			// It doesn't matter if eval is a 2D or 3D ref, because that detail is never asked of AreaEval.
375
			// It doesn't matter if eval is a 2D or 3D ref, because that detail is never asked of AreaEval.
374
			return refEval.offset(0, 0, 0, 0);
376
			return refEval.offset(0, 0, 0, 0);
375
		}
377
		}
378
		if (eval instanceof ArrayEval){
379
			return  ((ArrayEval)eval).arrayAsArea();
380
		}
376
		throw EvaluationException.invalidValue();
381
		throw EvaluationException.invalidValue();
377
	}
382
	}
378
383
(-)src/java/org/apache/poi/hssf/record/formula/functions/Match.java (-1 / +11 lines)
Lines 29-34 Link Here
29
import org.apache.poi.hssf.record.formula.functions.LookupUtils.CompareResult;
29
import org.apache.poi.hssf.record.formula.functions.LookupUtils.CompareResult;
30
import org.apache.poi.hssf.record.formula.functions.LookupUtils.LookupValueComparer;
30
import org.apache.poi.hssf.record.formula.functions.LookupUtils.LookupValueComparer;
31
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
31
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
32
import org.apache.poi.ss.formula.ArrayEval;
32
33
33
/**
34
/**
34
 * Implementation for the MATCH() Excel function.<p/>
35
 * Implementation for the MATCH() Excel function.<p/>
Lines 63-69 Link Here
63
 *
64
 *
64
 * @author Josh Micich
65
 * @author Josh Micich
65
 */
66
 */
66
public final class Match extends Var2or3ArgFunction {
67
public final class Match extends Var2or3ArgFunction implements FunctionWithArraySupport {
67
68
68
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
69
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
69
		// default match_type is 1.0
70
		// default match_type is 1.0
Lines 93-98 Link Here
93
		boolean matchExact = match_type == 0;
94
		boolean matchExact = match_type == 0;
94
		// Note - Excel does not strictly require -1 and +1
95
		// Note - Excel does not strictly require -1 and +1
95
		boolean findLargestLessThanOrEqual = match_type > 0;
96
		boolean findLargestLessThanOrEqual = match_type > 0;
97
		if (arg1 instanceof ArrayEval){
98
			arg1 = ((ArrayEval)arg1).arrayAsArea();
99
		}
96
100
97
		try {
101
		try {
98
			ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
102
			ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
Lines 248-251 Link Here
248
		}
252
		}
249
		return false;
253
		return false;
250
	}
254
	}
255
256
	public boolean supportArray(int paramIndex) {
257
        if (paramIndex == 1)
258
            return true;
259
        return false;
260
    }
251
}
261
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (-1 / +9 lines)
Lines 30-41 Link Here
30
import org.apache.poi.hssf.record.formula.eval.RefEval;
30
import org.apache.poi.hssf.record.formula.eval.RefEval;
31
import org.apache.poi.hssf.record.formula.eval.StringEval;
31
import org.apache.poi.hssf.record.formula.eval.StringEval;
32
import org.apache.poi.hssf.record.formula.eval.ValueEval;
32
import org.apache.poi.hssf.record.formula.eval.ValueEval;
33
import org.apache.poi.ss.formula.ArrayEval;
33
34
34
/**
35
/**
35
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
36
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
36
 *
37
 *
37
 */
38
 */
38
public final class Mode implements Function {
39
public final class Mode implements FunctionWithArraySupport {
39
40
40
	/**
41
	/**
41
	 * if v is zero length or contains no duplicates, return value is
42
	 * if v is zero length or contains no duplicates, return value is
Lines 92-97 Link Here
92
	}
93
	}
93
94
94
	private static void collectValues(ValueEval arg, List<Double> temp) throws EvaluationException {
95
	private static void collectValues(ValueEval arg, List<Double> temp) throws EvaluationException {
96
		if (arg instanceof ArrayEval) {
97
			arg = ((ArrayEval) arg).arrayAsArea();
98
		}
95
		if (arg instanceof AreaEval) {
99
		if (arg instanceof AreaEval) {
96
			AreaEval ae = (AreaEval) arg;
100
			AreaEval ae = (AreaEval) arg;
97
			int width = ae.getWidth();
101
			int width = ae.getWidth();
Lines 130-133 Link Here
130
		}
134
		}
131
		throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");
135
		throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");
132
	}
136
	}
137
138
	public boolean supportArray(int paramIndex) {
139
        return true;
140
    }
133
}
141
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (-1 / +19 lines)
Lines 27-40 Link Here
27
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.hssf.record.formula.eval.RefEval;
28
import org.apache.poi.hssf.record.formula.eval.StringEval;
28
import org.apache.poi.hssf.record.formula.eval.StringEval;
29
import org.apache.poi.hssf.record.formula.eval.ValueEval;
29
import org.apache.poi.hssf.record.formula.eval.ValueEval;
30
import org.apache.poi.ss.formula.ArrayEval;
30
31
31
/**
32
/**
32
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
33
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
34
 * @author zshulkins(ZS) array support;
33
 * This is the super class for all excel function evaluator
35
 * This is the super class for all excel function evaluator
34
 * classes that take variable number of operands, and
36
 * classes that take variable number of operands, and
35
 * where the order of operands does not matter
37
 * where the order of operands does not matter
36
 */
38
 */
37
public abstract class MultiOperandNumericFunction implements Function {
39
public abstract class MultiOperandNumericFunction implements FunctionWithArraySupport {
38
40
39
	private final boolean _isReferenceBoolCounted;
41
	private final boolean _isReferenceBoolCounted;
40
	private final boolean _isBlankCounted;
42
	private final boolean _isBlankCounted;
Lines 151-156 Link Here
151
			collectValue(re.getInnerValueEval(), true, temp);
153
			collectValue(re.getInnerValueEval(), true, temp);
152
			return;
154
			return;
153
		}
155
		}
156
		if (operand instanceof ArrayEval){
157
			ArrayEval ae = (ArrayEval)operand;
158
			if (ae.isIllegalForAggregation())
159
				throw new EvaluationException(ErrorEval.NA); // =sum(atan2({1,2},{1,2,3})) produces error
160
			for (ValueEval v: ae.getArrayAsEval()){
161
				if (v instanceof ArrayEval || v instanceof AreaEval)
162
					collectValues(v, temp); // array that contains arrays or areas
163
				else
164
					collectValue(v,false,temp);				
165
			}
166
			return;
167
		}
154
		collectValue(operand, false, temp);
168
		collectValue(operand, false, temp);
155
	}
169
	}
156
	private void collectValue(ValueEval ve, boolean isViaReference, DoubleList temp)  throws EvaluationException {
170
	private void collectValue(ValueEval ve, boolean isViaReference, DoubleList temp)  throws EvaluationException {
Lines 194-197 Link Here
194
		throw new RuntimeException("Invalid ValueEval type passed for conversion: ("
208
		throw new RuntimeException("Invalid ValueEval type passed for conversion: ("
195
				+ ve.getClass() + ")");
209
				+ ve.getClass() + ")");
196
	}
210
	}
211
212
	public boolean supportArray(int paramIndex) {
213
        return true;
214
    }
197
}
215
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Offset.java (-1 / +8 lines)
Lines 41-48 Link Here
41
 * <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/>
41
 * <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/>
42
 *
42
 *
43
 * @author Josh Micich
43
 * @author Josh Micich
44
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
44
 */
45
 */
45
public final class Offset implements Function {
46
public final class Offset implements FunctionWithArraySupport {
46
	// These values are specific to BIFF8
47
	// These values are specific to BIFF8
47
	private static final int LAST_VALID_ROW_INDEX = 0xFFFF;
48
	private static final int LAST_VALID_ROW_INDEX = 0xFFFF;
48
	private static final int LAST_VALID_COLUMN_INDEX = 0xFF;
49
	private static final int LAST_VALID_COLUMN_INDEX = 0xFF;
Lines 262-265 Link Here
262
		}
263
		}
263
		throw new RuntimeException("Unexpected eval type (" + ve.getClass().getName() + ")");
264
		throw new RuntimeException("Unexpected eval type (" + ve.getClass().getName() + ")");
264
	}
265
	}
266
267
	public boolean supportArray(int paramIndex) {
268
        if (paramIndex == 0)
269
            return true;
270
        return false;
271
    }
265
}
272
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Row.java (-1 / +15 lines)
Lines 22-29 Link Here
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
25
import org.apache.poi.ss.formula.ArrayEval;
25
26
26
public final class Row implements Function0Arg, Function1Arg {
27
public final class Row implements Function0Arg, Function1Arg, ArrayMode {
27
28
28
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
29
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
29
        return new NumberEval(srcRowIndex+1);
30
        return new NumberEval(srcRowIndex+1);
Lines 52-55 Link Here
52
        return ErrorEval.VALUE_INVALID;
53
        return ErrorEval.VALUE_INVALID;
53
    }
54
    }
54
55
56
    public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) {
57
        if ((evals.length == 1) && (evals[0] instanceof AreaEval)) {
58
            AreaEval ae = (AreaEval) evals[0];
59
60
            ValueEval[][] result = new ValueEval[ae.getHeight()][1];
61
            for (int r = 0; r < ae.getHeight(); r++) {
62
                result[r][0] = new NumberEval(ae.getFirstRow() + r + 1);
63
            }
64
            return new ArrayEval(result);
65
        }
66
        return evaluate(evals, srcCellRow, srcCellCol);
67
68
    }
55
}
69
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Rows.java (-1 / +9 lines)
Lines 22-38 Link Here
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
22
import org.apache.poi.hssf.record.formula.eval.NumberEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
23
import org.apache.poi.hssf.record.formula.eval.RefEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
24
import org.apache.poi.hssf.record.formula.eval.ValueEval;
25
import org.apache.poi.ss.formula.ArrayEval;
25
26
26
/**
27
/**
27
 * Implementation for Excel ROWS function.
28
 * Implementation for Excel ROWS function.
28
 *
29
 *
29
 * @author Josh Micich
30
 * @author Josh Micich
30
 */
31
 */
31
public final class Rows extends Fixed1ArgFunction {
32
public final class Rows extends Fixed1ArgFunction implements FunctionWithArraySupport {
32
33
33
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
34
35
35
		int result;
36
		int result;
37
		if (arg0 instanceof ArrayEval) {
38
            arg0 = ((ArrayEval) arg0).arrayAsArea();
39
        }
36
		if (arg0 instanceof AreaEval) {
40
		if (arg0 instanceof AreaEval) {
37
			result = ((AreaEval) arg0).getHeight();
41
			result = ((AreaEval) arg0).getHeight();
38
		} else if (arg0 instanceof RefEval) {
42
		} else if (arg0 instanceof RefEval) {
Lines 42-45 Link Here
42
		}
46
		}
43
		return new NumberEval(result);
47
		return new NumberEval(result);
44
	}
48
	}
49
50
	public boolean supportArray(int paramIndex) {
51
        return true;
52
    }
45
}
53
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (-1 / +7 lines)
Lines 37-44 Link Here
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 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
		return true;
132
	}
127
}
133
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java (-1 / +16 lines)
Lines 26-31 Link Here
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.hssf.record.formula.eval.StringEval;
27
import org.apache.poi.hssf.record.formula.eval.StringEval;
28
import org.apache.poi.hssf.record.formula.eval.ValueEval;
28
import org.apache.poi.hssf.record.formula.eval.ValueEval;
29
import org.apache.poi.ss.formula.ArrayEval;
29
30
30
31
31
/**
32
/**
Lines 49-56 Link Here
49
 *  )
50
 *  )
50
 * </p>
51
 * </p>
51
 * @author Josh Micich
52
 * @author Josh Micich
53
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support
52
 */
54
 */
53
public final class Sumproduct implements Function {
55
public final class Sumproduct implements FunctionWithArraySupport {
54
56
55
57
56
	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
58
	public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
Lines 60-65 Link Here
60
		if(maxN < 1) {
62
		if(maxN < 1) {
61
			return ErrorEval.VALUE_INVALID;
63
			return ErrorEval.VALUE_INVALID;
62
		}
64
		}
65
		for (int i=0; i<maxN; i++){
66
			if (args[i] instanceof ArrayEval){
67
				args[i] = ((ArrayEval)args[i]).arrayAsArea();
68
			}
69
		}		
63
		ValueEval firstArg = args[0];
70
		ValueEval firstArg = args[0];
64
		try {
71
		try {
65
			if(firstArg instanceof NumericValueEval) {
72
			if(firstArg instanceof NumericValueEval) {
Lines 115-120 Link Here
115
			eval = ae.getRelativeValue(0, 0);
122
			eval = ae.getRelativeValue(0, 0);
116
		}
123
		}
117
124
125
		if (!(eval instanceof ValueEval)) {
126
			throw new RuntimeException("Unexpected value eval class ("
127
					+ eval.getClass().getName() + ")");
128
		}
118
		return getProductTerm(eval, true);
129
		return getProductTerm(eval, true);
119
	}
130
	}
120
131
Lines 227-230 Link Here
227
		throw new RuntimeException("Unexpected value eval class ("
238
		throw new RuntimeException("Unexpected value eval class ("
228
				+ ve.getClass().getName() + ")");
239
				+ ve.getClass().getName() + ")");
229
	}
240
	}
241
242
	public boolean supportArray(int paramIndex) {
243
		return true;
244
	}
230
}
245
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java (-1 / +8 lines)
Lines 38-45 Link Here
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 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
		if (paramIndex == 1)
85
			return true;
86
		return false;
87
	}
81
}
88
}
(-)src/java/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java (-2 / +34 lines)
Lines 24-34 Link Here
24
import org.apache.poi.hssf.record.formula.eval.RefEval;
24
import org.apache.poi.hssf.record.formula.eval.RefEval;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
26
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
27
import org.apache.poi.ss.formula.ArrayEval;
27
28
28
/**
29
/**
29
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
30
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
30
 */
31
 */
31
public abstract class XYNumericFunction extends Fixed2ArgFunction {
32
public abstract class XYNumericFunction extends Fixed2ArgFunction implements FunctionWithArraySupport {
32
33
33
	private static abstract class ValueArray implements ValueVector {
34
	private static abstract class ValueArray implements ValueVector {
34
		private final int _size;
35
		private final int _size;
Lines 86-91 Link Here
86
		}
87
		}
87
	}
88
	}
88
89
90
	private static final class ArrayEvalValueArray extends ValueArray {
91
		private final ArrayEval _ae;
92
		private final int _width;
93
		
94
		public ArrayEvalValueArray(ArrayEval ae){
95
			super( ae.getRowCount()*ae.getColCount() );
96
			_ae = ae;
97
			_width = ae.getColCount();
98
		}
99
		
100
		protected ValueEval getItemInternal(int index){
101
			int rowIx = index / _width;
102
			int colIx = index % _width;
103
			return _ae.getArrayElementAsEval(rowIx, colIx);
104
		}
105
	}
106
89
	protected static interface Accumulator {
107
	protected static interface Accumulator {
90
		double accumulate(double x, double y);
108
		double accumulate(double x, double y);
91
	}
109
	}
Lines 172-177 Link Here
172
		if (arg instanceof RefEval) {
190
		if (arg instanceof RefEval) {
173
			return new RefValueArray((RefEval) arg);
191
			return new RefValueArray((RefEval) arg);
174
		}
192
		}
175
		return new SingleCellValueArray(arg);
193
		if (arg instanceof ArrayEval){
194
			if ( ((ArrayEval)arg).isIllegalForAggregation())
195
				throw new EvaluationException(ErrorEval.NA);
196
			return new ArrayEvalValueArray((ArrayEval) arg);
197
		}
198
		
199
		
200
		if (arg instanceof ValueEval) {
201
			return new SingleCellValueArray((ValueEval) arg);
202
		}
203
		throw new RuntimeException("Unexpected eval class (" + arg.getClass().getName() + ")");
176
	}
204
	}
205
206
	public boolean supportArray(int paramIndex) {
207
		return true;
208
	}	
177
}
209
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (+28 lines)
Lines 43-48 Link Here
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 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.formula.FormulaType;
55
import org.apache.poi.ss.formula.FormulaType;
54
import org.apache.poi.ss.SpreadsheetVersion;
56
import org.apache.poi.ss.SpreadsheetVersion;
55
import org.apache.poi.util.POILogger;
57
import org.apache.poi.util.POILogger;
Lines 1144-1147 Link Here
1144
        }
1146
        }
1145
        return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
1147
        return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
1146
    }
1148
    }
1149
1150
    void setCellArrayFormula(CellRangeAddress range) {
1151
        int row=_record.getRow();
1152
        short col=_record.getColumn();
1153
        short styleIndex=_record.getXFIndex();
1154
        setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex);
1155
1156
        // Billet for formula in rec
1157
        Ptg[] ptgsForCell = { new ExpPtg(range.getFirstRow(), range.getFirstColumn()) };
1158
        FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
1159
        agg.setParsedExpression(ptgsForCell);
1160
    }
1161
1162
    public CellRangeAddress getArrayFormulaRange() {
1163
        if (_cellType != CELL_TYPE_FORMULA) {
1164
            throw new IllegalArgumentException("Only formula cells can have array ranges");
1165
        }
1166
        return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
1167
    }
1168
1169
    public boolean isPartOfArrayFormulaGroup() {
1170
        if (_cellType != CELL_TYPE_FORMULA) {
1171
            return false;
1172
        }
1173
        return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
1174
    }
1147
}
1175
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (+10 lines)
Lines 19-24 Link Here
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
 * 
Lines 69-72 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 isArrayFormulaContext() {
75
        return _cell.isPartOfArrayFormulaGroup();
76
    }
77
78
    public EvaluationCell getFirstCellInArrayFormula() {
79
        CellRangeAddress range = _cell.getArrayFormulaRange();
80
        return _evalSheet.getCell(range.getFirstRow(), range.getFirstColumn());
81
    }
72
}
82
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (-5 / +99 lines)
Lines 25-37 Link Here
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 Link Here
42
 *
46
 *
43
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
47
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
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 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 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 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
            {
314
                Row row = sheet.getRow(i);
315
                Cell c = row.getCell(j);
316
                if((i-rowStart)<cvs.length && (j-colStart)<cvs[i-rowStart].length)
317
                setCellType(c,cvs[i-rowStart][j-colStart]);
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 = (CellValue[][])ArrayFormulaEvaluatorHelper.transformToRange(cvs, range);
333
        for(int i=rowStart;i<=range.getLastRow();i++ )
334
            for(int j=colStart; j<=range.getLastColumn();j++)
335
            {
336
                Row row = sheet.getRow(i);
337
                if(row == null)
338
                    row = sheet.createRow(i);
339
                Cell c = row.getCell(j);
340
                if(c == null)
341
                    c = row.createCell(j);
342
                CellValue cellValue = answer[i-rowStart][j-colStart];
343
                setCellValue(c,cellValue);
344
            }
345
        return answer;
346
    }
347
284
	/**
348
	/**
285
	 * Loops over all cells in all sheets of the supplied
349
	 * Loops over all cells in all sheets of the supplied
286
	 *  workbook.
350
	 *  workbook.
Lines 315-320 Link Here
315
	 */
379
	 */
316
	private CellValue evaluateFormulaCellValue(Cell cell) {
380
	private CellValue evaluateFormulaCellValue(Cell cell) {
317
		ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
381
		ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
382
		if (eval instanceof ArrayEval) {// support of arrays
383
            if (cell.isPartOfArrayFormulaGroup()) {
384
                eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell);
385
            } else {
386
                eval = ((ArrayEval) eval).getArrayElementAsEval(0, 0);
387
            }
388
        }
318
		if (eval instanceof NumberEval) {
389
		if (eval instanceof NumberEval) {
319
			NumberEval ne = (NumberEval) eval;
390
			NumberEval ne = (NumberEval) eval;
320
			return new CellValue(ne.getNumberValue());
391
			return new CellValue(ne.getNumberValue());
Lines 332-335 Link Here
332
		}
403
		}
333
		throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
404
		throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
334
	}
405
	}
406
407
	/**
408
     * Returns a Array CellValue wrapper around the supplied ArrayEval instance.
409
     */
410
    private CellValue[][] evaluateFormulaCellArrayValues(HSSFCell cell) {
411
        ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell(cell));
412
        if (eval instanceof ArrayEval) {// support of arrays
413
            ArrayEval ae = (ArrayEval) eval;
414
            int rowCount = ae.getRowCount();
415
            int ColCount = ae.getColCount();
416
            CellValue[][] answer = new CellValue[rowCount][ColCount];
417
            for (int i = 0; i < rowCount; i++)
418
                for (int j = 0; j < ColCount; j++) {
419
                    ValueEval val = ae.getArrayElementAsEval(i, j);
420
                    answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val);
421
                }
422
            return answer;
423
        }
424
        // non-array (usually from aggregate function)
425
        CellValue[][] answer = new CellValue[1][1];
426
        answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval);
427
        return answer;
428
    }
335
}
429
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-3 / +50 lines)
Lines 31-36 Link Here
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 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 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 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 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 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
}
(-)src/java/org/apache/poi/ss/formula/ArrayEval.java (+338 lines)
Line 0 Link Here
1
package org.apache.poi.ss.formula;
2
3
import java.util.ArrayList;
4
import java.util.List;
5
6
import org.apache.poi.hssf.record.UnicodeString;
7
import org.apache.poi.hssf.record.constant.ErrorConstant;
8
import org.apache.poi.hssf.record.formula.ArrayPtg;
9
import org.apache.poi.hssf.record.formula.eval.AreaEval;
10
import org.apache.poi.hssf.record.formula.eval.BoolEval;
11
import org.apache.poi.hssf.record.formula.eval.NumberEval;
12
import org.apache.poi.hssf.record.formula.eval.StringEval;
13
import org.apache.poi.hssf.record.formula.eval.ValueEval;
14
15
/** 
16
 *  Class to support evaluated array of values
17
 *  
18
 * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com)
19
 */
20
public class ArrayEval  implements ValueEval {
21
22
	//ArrayPtg thePtg;
23
	Object[][] values = null;
24
	boolean illegalForAggregation = false; // if result is invalid for aggregation. it could be true, if "uncompatible in size" arrays were used 
25
	
26
	
27
	/**
28
	 *  is array unsuitable for future aggregation? 
29
	 * @return
30
	 */
31
	public boolean isIllegalForAggregation(){
32
		return illegalForAggregation;
33
	}
34
	
35
	/**
36
	 *  set feature "unsuitable for future aggregation
37
	 *  it could be true, if "uncompatible in size" arrays were used
38
	 * @param value
39
	 */
40
	public void setIllegalForAggregation(boolean value){
41
		illegalForAggregation = value;
42
	}
43
	
44
	public ArrayEval(ArrayPtg ptg){
45
		if (ptg == null)
46
			throw new IllegalArgumentException("ArrayPtg should not be null");
47
		values = ptg.getTokenArrayValues();
48
	}
49
	
50
    public ArrayEval(Object[][] array){
51
		if (array == null)
52
			throw new IllegalArgumentException("null is not allowed");
53
		values = array;
54
    }
55
    
56
    public ArrayEval(Object[][] array, boolean isIllegalForAggregation){
57
    	this(array);
58
    	illegalForAggregation = isIllegalForAggregation;
59
    }
60
	
61
	public String toString(){
62
		StringBuffer b = new StringBuffer();
63
		b.append("{");
64
	  	for (int r=0;r<values.length;r++) {
65
			if (r > 0) {
66
				b.append(";");
67
			}
68
			for (int c=0;c<values[r].length;r++) {
69
			  	if (c > 0) {
70
					b.append(",");
71
				}
72
		  		Object o = values[r][c];
73
		  		b.append(getConstantText(o));
74
		  	}
75
		  }
76
		b.append("}");
77
		return b.toString();
78
	}
79
80
		
81
	/**
82
	 * get array content
83
	 * @return
84
	 */
85
	public Object[][] getArrayValues(){
86
		return values;
87
	}
88
	
89
	/**
90
	 * get element of array
91
	 * @param row
92
	 * @param col
93
	 * @return
94
	 */
95
	public Object getArrayElement(int row, int col){
96
		return values[row][col];
97
	}
98
	
99
	/**
100
	 * get element of array as Value Eval
101
	 * @param row
102
	 * @param col
103
	 * @return
104
	 */
105
	public ValueEval getArrayElementAsEval(int row, int col){
106
		return constructEval(getArrayElement(row,col));
107
	}
108
	
109
	
110
	/**
111
	 * Convert Object to ValueEval
112
	 * @param o
113
	 * @return
114
	 */
115
	public static ValueEval constructEval(Object o){
116
		if (o == null) {
117
			throw new RuntimeException("Array item cannot be null");
118
		}
119
		if (o instanceof ValueEval)
120
			return (ValueEval)o;
121
		
122
		if (o instanceof String) {
123
			return new StringEval( (String)o );
124
		}
125
		if (o instanceof Double) {
126
			return new NumberEval((Double)o);
127
		}
128
		if (o instanceof Boolean) {
129
			return BoolEval.valueOf((Boolean)o);
130
		}
131
		// I don't know what should we do if error is an array. I throw an exception for now
132
		if (o instanceof ErrorConstant) {
133
			throw new IllegalArgumentException("Error in array" + ((ErrorConstant)o).getText());
134
		}
135
		// if string constants in ArrayPtg are encodes as UnicodeString
136
		if (o instanceof UnicodeString){
137
			return new StringEval( ((UnicodeString)o).getString());
138
		}
139
		
140
		throw new IllegalArgumentException("Unexpected constant class (" + o.getClass());
141
	}
142
	
143
144
	/**
145
	 * get String contains object's value
146
	 * @param o
147
	 * @return
148
	 */
149
	public static String getConstantText(Object o) {
150
151
		if (o == null) {
152
			throw new RuntimeException("Array item cannot be null");
153
		}
154
		if (o instanceof String) {
155
			return "\"" + (String)o + "\"";
156
		}
157
		if (o instanceof Double) {
158
			return ((Double)o).toString();
159
		}
160
		if (o instanceof Boolean) {
161
			return ((Boolean)o).booleanValue() ? "TRUE" : "FALSE";
162
		}
163
		if (o instanceof ErrorConstant) {
164
			return ((ErrorConstant)o).getText();
165
		}
166
		throw new IllegalArgumentException("Unexpected constant class (" + o.getClass().getName() + ")");
167
	}
168
	
169
	/**
170
	 * return Array as ValueEval list
171
	 * @return
172
	 */
173
	public List<ValueEval> getArrayAsEval(){
174
		
175
		List<ValueEval> l = new ArrayList<ValueEval>();
176
		for(int r=0; r< values.length; r++){
177
			for (int c=0; c<values[r].length; c++){
178
				l.add(ArrayEval.constructEval(values[r][c]));
179
			}
180
		}
181
		return l;
182
	}
183
	
184
	/**
185
	 * get row count
186
	 * @return
187
	 */
188
	public int getRowCount(){
189
		return values.length;
190
	}
191
	
192
	/**
193
	 * get column count
194
	 * @return
195
	 */
196
	public int getColCount(){
197
		if (getRowCount() == 0)
198
			return 0;
199
		return values[0].length;
200
	}
201
	
202
	/*
203
	 * offset from the array
204
	 */
205
	/**
206
	 * get subarray
207
	 * @param rowFrom
208
	 * @param rowTo
209
	 * @param colFrom
210
	 * @param colTo
211
	 * @return
212
	 */
213
	public ArrayEval offset(int rowFrom, int rowTo, int colFrom, int colTo){
214
		
215
		if (rowFrom<=0 || rowFrom >= getRowCount() || rowTo<rowFrom || 
216
			colFrom<=0 || colFrom >= getColCount() || colTo<colFrom
217
		)
218
			throw new IllegalArgumentException("rowFrom: " + rowFrom + "  rowTo: " + rowTo + " colFrom: " + colFrom + " colTo: " + colTo );
219
		
220
		int row = Math.min(getRowCount(), rowTo);
221
		int col = Math.min(getColCount(), colTo);
222
		
223
		Object[][] result = new Object[row - rowFrom+1][col - colFrom+1];
224
		for (int r=rowFrom; r<=row; r++){
225
			for (int c=colFrom; c<=col; c++) {
226
				result[r][c] = values[r][c];
227
			}
228
		}
229
		
230
		return new ArrayEval(result);
231
	}
232
	
233
	public enum BooleanContent{ONLY_FALSE,ONLY_TRUE,MIXED};
234
	
235
	/**
236
	 *  Check if content of boolean array ONLY_FALSE, ONLY_TRUE or MIXED
237
	 *  if content is not boolean then return MIXED
238
	 * @return
239
	 */
240
	public BooleanContent checkBooleanContent(){
241
		try{
242
		BoolEval first =  (BoolEval)values[0][0];
243
		for(int i=0;i<values.length;i++)
244
			for(int j=0;j<values[i].length;j++)
245
				if(first.equals((BoolEval)values[i][j]))
246
						return BooleanContent.MIXED;
247
						
248
		if(first.getBooleanValue())
249
			return BooleanContent.ONLY_TRUE;
250
		else
251
			return BooleanContent.ONLY_FALSE;
252
		}
253
		catch (Exception e){
254
			return BooleanContent.MIXED;
255
		}
256
	}
257
	
258
	
259
	/**
260
	 * expose Array as area on sheet (top-left)
261
	 * convenience methods to reuse existing code
262
	 * @return
263
	 */
264
	public AreaEval arrayAsArea(){
265
		
266
		return new AreaEval(){
267
			
268
			public int getFirstRow(){
269
				return 0;
270
			}
271
			
272
			public int getLastRow(){
273
				return getRowCount()-1;
274
			}
275
			
276
			public int getFirstColumn(){
277
				return 0;
278
			}
279
			
280
			public int getLastColumn(){
281
				return getColCount()-1;
282
			}
283
			
284
		    public boolean isRow(){
285
		    	return (getRowCount()==1);
286
		    }
287
		    
288
		    public boolean isColumn(){
289
		    	return (getColCount() == 1);
290
		    }
291
292
		    public ValueEval getValueAt(int row, int col){
293
		    	return getArrayElementAsEval(row, col);
294
		    }
295
296
		    public boolean contains(int row, int col){
297
		    	if ( (row < getRowCount()) && (col < getColCount()) ){
298
		    		return true;
299
		    	}
300
		    	return false;
301
		    }
302
303
		    public boolean containsColumn(short col){
304
		    	if (col < getColCount())
305
		    		return true;
306
		    	return false;
307
		    }
308
		    
309
		    public boolean containsRow(int row){
310
		    	if (row < getRowCount() )
311
		    		return true;
312
		    	return false;
313
		    }
314
315
		    public int getWidth(){
316
		    	return getColCount(); 
317
		    	
318
		    }
319
		    
320
		    public int getHeight(){
321
		    	return getRowCount();
322
		    }
323
324
		    public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex){
325
		    	return getArrayElementAsEval(relativeRowIndex, relativeColumnIndex);
326
		    }
327
328
		    public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx){
329
		    	ArrayEval offset = ArrayEval.this.offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx);
330
		    	return offset.arrayAsArea();
331
		    }
332
		    
333
			
334
		};
335
	}
336
	
337
	
338
}
(-)src/java/org/apache/poi/ss/formula/EvaluationCell.java (+11 lines)
Lines 26-31 Link Here
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 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 isArrayFormulaContext();
51
52
    /**
53
     * @return Main cell in array formula that contains formula.
54
     */
55
    EvaluationCell getFirstCellInArrayFormula();
45
}
56
}
(-)src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (+3 lines)
Lines 72-77 Link Here
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;
(-)src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (-1 / +7 lines)
Lines 44-58 Link Here
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 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
	}
(-)src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (-9 / +37 lines)
Lines 52-65 Link Here
52
import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
52
import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
53
import org.apache.poi.hssf.record.formula.eval.ValueEval;
53
import org.apache.poi.hssf.record.formula.eval.ValueEval;
54
import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
54
import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
55
import org.apache.poi.hssf.record.formula.functions.ArrayMode;
55
import org.apache.poi.hssf.record.formula.functions.Function;
56
import org.apache.poi.hssf.record.formula.functions.Function;
56
import org.apache.poi.hssf.record.formula.functions.Indirect;
57
import org.apache.poi.hssf.record.formula.functions.Indirect;
58
import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
57
59
58
/**
60
/**
59
 * This class creates <tt>OperationEval</tt> instances to help evaluate <tt>OperationPtg</tt>
61
 * This class creates <tt>OperationEval</tt> instances to help evaluate <tt>OperationPtg</tt>
60
 * formula tokens.
62
 * formula tokens.
61
 *
63
 *
62
 * @author Josh Micich
64
 * @author Josh Micich
65
 * @author Petr Udalau(Petr.Udalau at exigenservices.com) - evaluations of array formulas
63
 */
66
 */
64
final class OperationEvaluatorFactory {
67
final class OperationEvaluatorFactory {
65
68
Lines 113-125 Link Here
113
		if(ptg == null) {
116
		if(ptg == null) {
114
			throw new IllegalArgumentException("ptg must not be null");
117
			throw new IllegalArgumentException("ptg must not be null");
115
		}
118
		}
116
		Function result = _instancesByPtgClass.get(ptg);
119
		Function func = _instancesByPtgClass.get(ptg);
117
120
118
		if (result != null) {
121
		if (func == null && ptg instanceof AbstractFunctionPtg) {
119
			return  result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
120
		}
121
122
		if (ptg instanceof AbstractFunctionPtg) {
123
			AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
122
			AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
124
			int functionIndex = fptg.getFunctionIndex();
123
			int functionIndex = fptg.getFunctionIndex();
125
			switch (functionIndex) {
124
			switch (functionIndex) {
Lines 128-136 Link Here
128
				case FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
127
				case FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
129
					return UserDefinedFunction.instance.evaluate(args, ec);
128
					return UserDefinedFunction.instance.evaluate(args, ec);
130
			}
129
			}
131
130
			func = FunctionEval.getBasicFunction(functionIndex);
132
			return FunctionEval.getBasicFunction(functionIndex).evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
133
		}
131
		}
134
		throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")");
132
		if (func != null) {
133
            if (func instanceof ArrayMode && ec.isInArrayFormulaContext()) {
134
                return evaluateInSpecialModeForArrayFormulas((ArrayMode) func, args, ec);
135
            } else {
136
                return invokeOperationInArrayContext(func, args, ec);
137
            }
138
		} else {
139
		    throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")");
140
		}
135
	}
141
	}
142
	
143
    private static ValueEval evaluateInSpecialModeForArrayFormulas(ArrayMode function, ValueEval[] ops,
144
            OperationEvaluationContext ec) {
145
        return function.evaluateInArrayFormula(ops, ec.getRowIndex(), ec.getColumnIndex());
146
    }
147
148
    private static ValueEval invokeOperationInArrayContext(Function func, ValueEval[] ops, OperationEvaluationContext ec) {
149
        boolean isArrayFormula = ec.isInArrayFormulaContext();
150
        ValueEval answer = ArrayFormulaEvaluatorHelper.prepareEmptyResult(func, ops, isArrayFormula);
151
        if (answer instanceof ArrayEval) {
152
            ValueEval[][] values = (ValueEval[][]) ((ArrayEval) answer).getArrayValues();
153
            for (int row = 0; row < values.length; row++)
154
                for (int col = 0; col < values[row].length; col++) {
155
                    ValueEval[] opsloop = ArrayFormulaEvaluatorHelper.prepareArgsForLoop(func, ops, row, col, isArrayFormula);
156
                    ValueEval loopresult = func.evaluate(opsloop, ec.getRowIndex(), ec.getColumnIndex());
157
                    values[row][col] = loopresult;
158
                }
159
            return answer;
160
        } else {
161
            return func.evaluate(ops, ec.getRowIndex(), ec.getColumnIndex());
162
        }
163
    }
136
}
164
}
(-)src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (-52 / +131 lines)
Lines 17-29 Link Here
17
17
18
package org.apache.poi.ss.formula;
18
package org.apache.poi.ss.formula;
19
19
20
import java.util.ArrayList;
20
import java.util.IdentityHashMap;
21
import java.util.IdentityHashMap;
21
import java.util.Map;
22
import java.util.Map;
22
import java.util.Stack;
23
import java.util.Stack;
23
24
25
import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
24
import org.apache.poi.hssf.record.formula.Area3DPtg;
26
import org.apache.poi.hssf.record.formula.Area3DPtg;
25
import org.apache.poi.hssf.record.formula.AreaErrPtg;
27
import org.apache.poi.hssf.record.formula.AreaErrPtg;
26
import org.apache.poi.hssf.record.formula.AreaPtg;
28
import org.apache.poi.hssf.record.formula.AreaPtg;
29
import org.apache.poi.hssf.record.formula.ArrayPtg;
27
import org.apache.poi.hssf.record.formula.AttrPtg;
30
import org.apache.poi.hssf.record.formula.AttrPtg;
28
import org.apache.poi.hssf.record.formula.BoolPtg;
31
import org.apache.poi.hssf.record.formula.BoolPtg;
29
import org.apache.poi.hssf.record.formula.ControlPtg;
32
import org.apache.poi.hssf.record.formula.ControlPtg;
Lines 60-65 Link Here
60
import org.apache.poi.hssf.record.formula.eval.RefEval;
63
import org.apache.poi.hssf.record.formula.eval.RefEval;
61
import org.apache.poi.hssf.record.formula.eval.StringEval;
64
import org.apache.poi.hssf.record.formula.eval.StringEval;
62
import org.apache.poi.hssf.record.formula.eval.ValueEval;
65
import org.apache.poi.hssf.record.formula.eval.ValueEval;
66
import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
63
import org.apache.poi.hssf.record.formula.functions.Choose;
67
import org.apache.poi.hssf.record.formula.functions.Choose;
64
import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
68
import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
65
import org.apache.poi.hssf.record.formula.functions.If;
69
import org.apache.poi.hssf.record.formula.functions.If;
Lines 67-72 Link Here
67
import org.apache.poi.hssf.util.CellReference;
71
import org.apache.poi.hssf.util.CellReference;
68
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
72
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
69
import org.apache.poi.ss.formula.eval.NotImplementedException;
73
import org.apache.poi.ss.formula.eval.NotImplementedException;
74
import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper;
70
import org.apache.poi.ss.usermodel.Cell;
75
import org.apache.poi.ss.usermodel.Cell;
71
76
72
/**
77
/**
Lines 203-209 Link Here
203
208
204
	public ValueEval evaluate(EvaluationCell srcCell) {
209
	public ValueEval evaluate(EvaluationCell srcCell) {
205
		int sheetIndex = getSheetIndex(srcCell.getSheet());
210
		int sheetIndex = getSheetIndex(srcCell.getSheet());
206
		return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));
211
        return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));
207
	}
212
	}
208
213
209
	/**
214
	/**
Lines 241-246 Link Here
241
			return result;
246
			return result;
242
		}
247
		}
243
248
249
        if (srcCell.isArrayFormulaContext()) {
250
            srcCell = srcCell.getFirstCellInArrayFormula();
251
        }
244
		FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell);
252
		FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell);
245
		if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) {
253
		if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) {
246
			tracker.acceptFormulaDependency(cce);
254
			tracker.acceptFormulaDependency(cce);
Lines 251-257 Link Here
251
			if (!tracker.startEvaluate(cce)) {
259
			if (!tracker.startEvaluate(cce)) {
252
				return ErrorEval.CIRCULAR_REF_ERROR;
260
				return ErrorEval.CIRCULAR_REF_ERROR;
253
			}
261
			}
254
			OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
262
			OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex,
263
                    columnIndex, tracker, srcCell.isArrayFormulaContext());
255
264
256
			try {
265
			try {
257
266
Lines 333-338 Link Here
333
	// visibility raised for testing
342
	// visibility raised for testing
334
	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {
343
	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {
335
344
345
		Stack<Boolean> stackSkip = new Stack<Boolean>();
346
		Boolean isSkipActive = new Boolean(true);
336
		Stack<ValueEval> stack = new Stack<ValueEval>();
347
		Stack<ValueEval> stack = new Stack<ValueEval>();
337
		for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
348
		for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
338
349
Lines 347-408 Link Here
347
				}
358
				}
348
				if (attrPtg.isOptimizedChoose()) {
359
				if (attrPtg.isOptimizedChoose()) {
349
					ValueEval arg0 = stack.pop();
360
					ValueEval arg0 = stack.pop();
350
					int[] jumpTable = attrPtg.getJumpTable();
361
					if((arg0 instanceof ArrayEval) && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED)
351
					int dist;
362
					{
352
					int nChoices = jumpTable.length;
363
						stack.push(arg0);
353
					try {
364
						stackSkip.push(isSkipActive);
354
						int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());
365
						// Switch off skip option only for this level
355
						if (switchIndex<1 || switchIndex > nChoices) {
366
						isSkipActive = new Boolean(false);
356
							stack.push(ErrorEval.VALUE_INVALID);
367
						continue;
357
							dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)
358
						} else {
359
							dist = jumpTable[switchIndex-1];
360
						}
361
					} catch (EvaluationException e) {
362
						stack.push(e.getErrorEval());
363
						dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)
364
					}
368
					}
365
					// Encoded dist for tAttrChoose includes size of jump table, but
369
					else{
366
					// countTokensToBeSkipped() does not (it counts whole tokens).
370
						ValueEval cond = arg0;
367
					dist -= nChoices*2+2; // subtract jump table size
371
						if((arg0 instanceof ArrayEval))
368
					i+= countTokensToBeSkipped(ptgs, i, dist);
372
							cond = ((ArrayEval)arg0).getArrayElementAsEval(0,0);
369
					continue;
373
	                    int[] jumpTable = attrPtg.getJumpTable();
374
	                    int dist;
375
	                    int nChoices = jumpTable.length;
376
	                    try {
377
	                            int switchIndex = Choose.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
378
	                        if (switchIndex<1 || switchIndex > nChoices) {
379
	                            stack.push(ErrorEval.VALUE_INVALID);
380
	                            dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)
381
	                        } else {
382
	                            dist = jumpTable[switchIndex-1];
383
	                        }
384
	                    } catch (EvaluationException e) {
385
	                        stack.push(e.getErrorEval());
386
	                        dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE)
387
	                    }
388
	                    // Encoded dist for tAttrChoose includes size of jump table, but
389
	                    // countTokensToBeSkipped() does not (it counts whole tokens).
390
	                    dist -= nChoices*2+2; // subtract jump table size
391
	                    i+= countTokensToBeSkipped(ptgs, i, dist);
392
	                    continue;
393
					}
370
				}
394
				}
371
				if (attrPtg.isOptimizedIf()) {
395
				if (attrPtg.isOptimizedIf()) {
372
					ValueEval arg0 = stack.pop();
396
					ValueEval arg0 = stack.pop();
373
					boolean evaluatedPredicate;
397
					boolean evaluatedPredicate;
374
					try {
398
					if((arg0 instanceof ArrayEval) && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED)
375
						evaluatedPredicate = If.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex());
399
					{
376
					} catch (EvaluationException e) {
400
						stack.push(arg0);
377
						stack.push(e.getErrorEval());
401
						stackSkip.push(isSkipActive);
378
						int dist = attrPtg.getData();
402
						// Switch off skip option only for this level
379
						i+= countTokensToBeSkipped(ptgs, i, dist);
403
						isSkipActive = new Boolean(false);
380
						attrPtg = (AttrPtg) ptgs[i];
381
						dist = attrPtg.getData()+1;
382
						i+= countTokensToBeSkipped(ptgs, i, dist);
383
						continue;
404
						continue;
384
					}
405
					}
385
					if (evaluatedPredicate) {
406
					else{
386
						// nothing to skip - true param folows
407
						ValueEval cond = arg0;
387
					} else {
408
						if((arg0 instanceof ArrayEval))
388
						int dist = attrPtg.getData();
409
							cond = ((ArrayEval)arg0).getArrayElementAsEval(0,0);
389
						i+= countTokensToBeSkipped(ptgs, i, dist);
410
	                    try {
390
						Ptg nextPtg = ptgs[i+1];
411
	                        evaluatedPredicate = If.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex());
391
						if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) {
412
	                    } catch (EvaluationException e) {
392
							// this is an if statement without a false param (as opposed to MissingArgPtg as the false param)
413
	                        stack.push(e.getErrorEval());
393
							i++;
414
	                        int dist = attrPtg.getData();
394
							stack.push(BoolEval.FALSE);
415
	                        i+= countTokensToBeSkipped(ptgs, i, dist);
395
						}
416
	                        attrPtg = (AttrPtg) ptgs[i];
417
	                        dist = attrPtg.getData()+1;
418
	                        i+= countTokensToBeSkipped(ptgs, i, dist);
419
	                        continue;
420
	                    }
421
	                    if (evaluatedPredicate) {
422
	                        // nothing to skip - true param folows
423
	                    } else {
424
	                        int dist = attrPtg.getData();
425
	                        i+= countTokensToBeSkipped(ptgs, i, dist);
426
	                        Ptg nextPtg = ptgs[i+1];
427
	                        if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) {
428
	                            // this is an if statement without a false param (as opposed to MissingArgPtg as the false param)
429
	                            i++;
430
	                            stack.push(BoolEval.FALSE);
431
	                        }
432
	                    }
433
	                    continue;
396
					}
434
					}
397
					continue;
398
				}
435
				}
399
				if (attrPtg.isSkip()) {
436
				if (attrPtg.isSkip()) {
400
					int dist = attrPtg.getData()+1;
437
					if(isSkipActive){
401
					i+= countTokensToBeSkipped(ptgs, i, dist);
438
						int dist = attrPtg.getData()+1;
402
					if (stack.peek() == MissingArgEval.instance) {
439
						i+= countTokensToBeSkipped(ptgs, i, dist);
403
						stack.pop();
440
						if (stack.peek() == MissingArgEval.instance) {
404
						stack.push(BlankEval.instance);
441
							stack.pop();
405
					}
442
							stack.push(BlankEval.instance);
443
						}
444
					}	
406
					continue;
445
					continue;
407
				}
446
				}
408
			}
447
			}
Lines 435-440 Link Here
435
				}
474
				}
436
//				logDebug("invoke " + operation + " (nAgs=" + numops + ")");
475
//				logDebug("invoke " + operation + " (nAgs=" + numops + ")");
437
				opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);
476
				opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec);
477
                if(isSkipActive == false && isSkipSensitive(optg))
478
                	isSkipActive = stackSkip.pop();
438
			} else {
479
			} else {
439
				opResult = getEvalForPtg(ptg, ec);
480
				opResult = getEvalForPtg(ptg, ec);
440
			}
481
			}
Lines 460-465 Link Here
460
	}
501
	}
461
502
462
	/**
503
	/**
504
	 *  Has this function "optimized" form?
505
	 * @param optg
506
	 * @return
507
	 */
508
	static  ArrayList<Integer> skipSensitiveFunction = null;
509
	private boolean isSkipSensitive(OperationPtg optg) {
510
		if(optg instanceof FuncVarPtg){
511
			// Skip sensitive is only "optimized" function - just only "IF" and "choose"
512
			if(skipSensitiveFunction==null){
513
				skipSensitiveFunction = new ArrayList<Integer>();
514
				int index = FunctionMetadataRegistry.lookupIndexByName("IF");
515
				skipSensitiveFunction.add(new Integer(index));
516
				index = FunctionMetadataRegistry.lookupIndexByName("CHOOSE");
517
				skipSensitiveFunction.add(new Integer(index));
518
			}
519
			AbstractFunctionPtg fptg = (AbstractFunctionPtg)optg;
520
			int functionIndex = fptg.getFunctionIndex();
521
			return skipSensitiveFunction.contains(new Integer(functionIndex));
522
		}
523
		return false;
524
	}
525
526
	/**
463
	 * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.
527
	 * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.
464
	 *
528
	 *
465
	 * @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped
529
	 * @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped
Lines 481-486 Link Here
481
		return index-startIndex;
545
		return index-startIndex;
482
	}
546
	}
483
	/**
547
	/**
548
	 * Dereferences a single value from any ArrayEval evaluation result.
549
	 * @param evaluationResult
550
	 * @param cell
551
	 * @return
552
	 */
553
	private static ValueEval dereferenceValue(ArrayEval evaluationResult, EvaluationCell evalCell ) {
554
		Cell cell = (Cell)evalCell.getIdentityKey();
555
		return ArrayFormulaEvaluatorHelper.dereferenceValue(evaluationResult,cell);
556
	}
557
	/**
484
	 * Dereferences a single value from any AreaEval or RefEval evaluation result.
558
	 * 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.
559
	 * 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>,
560
	 * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
Lines 572-578 Link Here
572
			AreaPtg aptg = (AreaPtg) ptg;
646
			AreaPtg aptg = (AreaPtg) ptg;
573
			return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
647
			return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
574
		}
648
		}
575
649
		if (ptg instanceof ArrayPtg){
650
			return new ArrayEval((ArrayPtg)ptg);
651
		}
576
		if (ptg instanceof UnknownPtg) {
652
		if (ptg instanceof UnknownPtg) {
577
			// POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.
653
			// 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)
654
			// This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)
Lines 599-607 Link Here
599
	 */
675
	 */
600
	/* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,
676
	/* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex,
601
			int columnIndex, EvaluationTracker tracker) {
677
			int columnIndex, EvaluationTracker tracker) {
602
678
        EvaluationCell cell = sheet.getCell(rowIndex, columnIndex);
603
		EvaluationCell cell = sheet.getCell(rowIndex, columnIndex);
679
        ValueEval result = evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker);
604
		return evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker);
680
        if (result instanceof ArrayEval && cell.isArrayFormulaContext()) {
681
            result = dereferenceValue((ArrayEval) result, cell);
682
        }
683
        return result;
605
	}
684
	}
606
	public FreeRefFunction findUserDefinedFunction(String functionName) {
685
	public FreeRefFunction findUserDefinedFunction(String functionName) {
607
		return _udfFinder.findFunction(functionName);
686
		return _udfFinder.findFunction(functionName);
(-)src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (+8 lines)
Lines 129-132 Link Here
129
	public int getColumnIndex() {
129
	public int getColumnIndex() {
130
		return _masterCell.getColumnIndex();
130
		return _masterCell.getColumnIndex();
131
	}
131
	}
132
133
    public boolean isArrayFormulaContext() {
134
        return _masterCell.isArrayFormulaContext();
135
    }
136
137
    public EvaluationCell getFirstCellInArrayFormula() {
138
        return _masterCell.getFirstCellInArrayFormula();
139
    }
132
}
140
}
(-)src/java/org/apache/poi/ss/usermodel/ArrayFormulaEvaluatorHelper.java (+254 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.ss.usermodel;
5
6
import org.apache.poi.hssf.record.formula.eval.AreaEval;
7
import org.apache.poi.hssf.record.formula.eval.BoolEval;
8
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
9
import org.apache.poi.hssf.record.formula.eval.NumberEval;
10
import org.apache.poi.hssf.record.formula.eval.StringEval;
11
import org.apache.poi.hssf.record.formula.eval.ValueEval;
12
import org.apache.poi.hssf.record.formula.functions.Function;
13
import org.apache.poi.hssf.record.formula.functions.FunctionWithArraySupport;
14
import org.apache.poi.ss.formula.ArrayEval;
15
import org.apache.poi.ss.util.CellRangeAddress;
16
17
/**
18
 * Helper class to manipulate with array formula
19
 * This class contains methods, common for HSSF and XSSF FormulaEvaluator
20
 * Better solution - to have abstract class for FormulaEvaluator Interface Implementation
21
 *  All method need to be static
22
 *   
23
 * @author Vladimirs Abramovs (Vladimirs.Abramovs at exigenservices.com)
24
 */
25
public class ArrayFormulaEvaluatorHelper {
26
    final static int SCALAR_TYPE = 0;
27
    final static int ARRAY_TYPE = 1;
28
29
	
30
	private ArrayFormulaEvaluatorHelper (){
31
		// has no instance
32
	}
33
	
34
	
35
	/** Transform evaluated Array Values according aimed Range
36
	 *   Depending on dimensions correlation, 
37
	 *   result array may be restricted 
38
	 *    or his single row/column will cloned 
39
	 *    or some cell will set to #N/A 
40
	 * @param cvs
41
	 * @param range
42
	 * @return
43
	 */
44
	public static Object[][] transformToRange(Object[][] cvs,CellRangeAddress range){
45
		
46
		Object[][] answer = null;
47
		if(cvs[0][0] instanceof CellValue)
48
			answer = new CellValue[range.getLastRow()-range.getFirstRow()+1][range.getLastColumn()-range.getFirstColumn()+1];
49
		else if (cvs[0][0] instanceof ValueEval)
50
			answer = new ValueEval[range.getLastRow()-range.getFirstRow()+1][range.getLastColumn()-range.getFirstColumn()+1];
51
		else
52
			throw new RuntimeException("transform2Range does not support type "+cvs[0][0].getClass().getName());
53
		int rowStart = range.getFirstRow();
54
		int colStart = range.getFirstColumn();
55
		for(int i=rowStart;i<=range.getLastRow();i++ )
56
			for(int j=colStart; j<=range.getLastColumn();j++)
57
			{
58
				if((i-rowStart)<cvs.length && (j-colStart)<cvs[i-rowStart].length){
59
					answer[i-rowStart][j-colStart] = cvs[i-rowStart][j-colStart];
60
				}
61
				else
62
				{  
63
					boolean needClone = false;
64
					int cloneRow =  0;
65
					int cloneCol = 0;
66
					if(cvs.length == 1)
67
					{  // Need to clone first colm of  cvs
68
						cloneCol = j-colStart;
69
						needClone = true;
70
						
71
					}
72
					if(cvs[0].length == 1 )
73
					{  // Need to clone first row of  cvs
74
						cloneRow = i-rowStart;
75
						needClone = true;
76
						
77
					}
78
					if(needClone &&  cloneCol <cvs[0].length && cloneRow <cvs.length) 
79
					{
80
						
81
						answer[i-rowStart][j-colStart] = cvs[cloneRow][cloneCol];
82
					}	
83
					else 
84
					{
85
						//  For other cases set cell value to #N/A
86
						// For those cells we changes also their type to Error
87
						if(cvs[0][0] instanceof CellValue){
88
								CellValue cvError = CellValue.getError(org.apache.poi.ss.usermodel.ErrorConstants.ERROR_NA);
89
								answer[i-rowStart][j-colStart] = cvError;
90
						}	
91
						else 
92
							 if (cvs[0][0] instanceof ValueEval)
93
								 answer[i-rowStart][j-colStart] = ErrorEval.NA;
94
					}
95
				}	
96
			}
97
		return answer;
98
	}
99
	/**
100
	 * Convert Eval value to CellValue
101
	 * @param val
102
	 * @return
103
	 */
104
	public static CellValue evalToCellValue(ValueEval val){
105
		if(val instanceof BoolEval)
106
			return CellValue.valueOf(((BoolEval)val).getBooleanValue());
107
		if(val instanceof NumberEval)
108
			return  new CellValue(((NumberEval)val).getNumberValue());
109
		if(val instanceof StringEval)
110
			return  new CellValue(((StringEval)val).getStringValue());
111
		if(val instanceof ErrorEval)
112
			return  new CellValue(((ErrorEval)val).getErrorCode());
113
		return new CellValue(ErrorEval.VALUE_INVALID.getErrorCode());
114
	}
115
	/**
116
	 * Get single value from ArrayEval  for desired cell 
117
	 * @param evaluationResult
118
	 * @param cell
119
	 * @return
120
	 */
121
	public static ValueEval dereferenceValue(ArrayEval evaluationResult, Cell cell) {
122
		CellRangeAddress range = cell.getArrayFormulaRange();
123
		Object[][] rangeVal = ArrayFormulaEvaluatorHelper.transformToRange(evaluationResult.getArrayValues(),range);
124
		int rowInArray = cell.getRowIndex()- range.getFirstRow();
125
		int colInArray = cell.getColumnIndex() - range.getFirstColumn();
126
		return  (ValueEval)rangeVal[rowInArray][colInArray];
127
	}
128
    /**
129
     * Get type of parameter (SCALAR_TYPE or ARRAY_TYPE) which support function
130
     * for given argument
131
     * 
132
     * @param function
133
     * @param argIndex
134
     * @return
135
     */
136
    public static int getParameterType(Function function, int argIndex) {
137
        int answer = SCALAR_TYPE;
138
139
        if (function instanceof FunctionWithArraySupport) {
140
            // ask new interface(ZS) for argument type
141
            if (((FunctionWithArraySupport) function).supportArray(argIndex))
142
                answer = ARRAY_TYPE;
143
        }
144
        return answer;
145
    }
146
147
    /**
148
     * Prepare empty template, which will keep result of evaluation
149
     *   A few arguments of function may be array. 
150
     *   In this case result array will have
151
     *   dimension as  such arguments intersection. 
152
     *   This method calculates result's dimension and prepare empty results
153
     * 
154
     * 
155
     * @param function
156
     * @param ops
157
     * @param arrayFormula
158
     * @return
159
     */
160
    public static ValueEval prepareEmptyResult(Function function, ValueEval[] ops, boolean arrayFormula) {
161
        int rowCount = Integer.MAX_VALUE;
162
        int colCount = Integer.MAX_VALUE;
163
164
        boolean illegalForAggregation = false;
165
166
        for (int i = 0; i < ops.length; i++) {
167
            int argRowCount = Integer.MAX_VALUE;
168
            int argColCount = Integer.MAX_VALUE;
169
            if (getParameterType(function, i) == SCALAR_TYPE) {
170
                if (ops[i] instanceof ArrayEval) {
171
                    argRowCount = ((ArrayEval) ops[i]).getRowCount();
172
                    argColCount = ((ArrayEval) ops[i]).getColCount();
173
                    illegalForAggregation = illegalForAggregation || ((ArrayEval) ops[i]).isIllegalForAggregation();
174
                } else if (ops[i] instanceof AreaEval && arrayFormula) {
175
                    argRowCount = ((AreaEval) ops[i]).getHeight();
176
                    argColCount = ((AreaEval) ops[i]).getWidth();
177
                } else
178
                    continue; // Arguments is not array - just skip it
179
                if (argRowCount != rowCount) {
180
                    if (rowCount != Integer.MAX_VALUE) {
181
                        illegalForAggregation = true;
182
                    }
183
                    rowCount = Math.min(rowCount, argRowCount);
184
                }
185
                if (argColCount != colCount) {
186
                    if (colCount != Integer.MAX_VALUE) {
187
                        illegalForAggregation = true;
188
                    }
189
                    colCount = Math.min(colCount, argColCount);
190
                }
191
            }
192
        }
193
194
        if (colCount == Integer.MAX_VALUE || rowCount == Integer.MAX_VALUE)
195
            return null;
196
197
        ValueEval[][] emptyArray = new ValueEval[rowCount][colCount];
198
        ValueEval answer = new ArrayEval(emptyArray);
199
200
        ((ArrayEval) answer).setIllegalForAggregation(illegalForAggregation);
201
        return answer;
202
    }
203
204
    /**
205
     * Prepare arguments for next iteration to call function
206
     *  Each argument may be scalar, full array or element(according iteration) of array
207
     * 
208
     * @param function
209
     * @param ops
210
     * @param i
211
     * @param j
212
     * @param trackAreas
213
     * @return
214
     */
215
    public static ValueEval[] prepareArgsForLoop(Function function, ValueEval[] ops, int i, int j, boolean trackAreas) {
216
        ValueEval[] answer = new ValueEval[ops.length];
217
        for (int argIn = 0; argIn < ops.length; argIn++) {
218
            if (getParameterType(function, argIn) == SCALAR_TYPE) {
219
                if (ops[argIn] instanceof ArrayEval) {
220
                    answer[argIn] = ((ArrayEval) ops[argIn]).getArrayElementAsEval(i, j);
221
                } else if (ops[argIn] instanceof AreaEval && trackAreas) {
222
                    answer[argIn] = ((AreaEval) ops[argIn]).getRelativeValue(i, j);
223
224
                } else {
225
                    answer[argIn] = ops[argIn];
226
                }
227
            } else { // Array type
228
                answer[argIn] = ops[argIn];
229
230
            }
231
232
        }
233
234
        return answer;
235
    }
236
237
    /**
238
     * check if ops contain arrays and those should be iterated
239
     * 
240
     * @param function
241
     * @param ops
242
     * @return
243
     */
244
    public static boolean checkForArrays(Function function, ValueEval[] ops) {
245
246
        for (int i = 0; i < ops.length; i++) {
247
            if ((ops[i] instanceof ArrayEval) && (getParameterType(function, i) == SCALAR_TYPE))
248
                return true;
249
        }
250
        return false;
251
252
    }
253
254
}
(-)src/java/org/apache/poi/ss/usermodel/Cell.java (+12 lines)
Lines 21-26 Link Here
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 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
    public boolean isPartOfArrayFormulaGroup();
375
}
387
}
(-)src/java/org/apache/poi/ss/usermodel/Sheet.java (+17 lines)
Lines 30-35 Link Here
30
 * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
30
 * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
31
 * contain text, numbers, dates, and formulas. Cells can also be formatted.
31
 * contain text, numbers, dates, and formulas. Cells can also be formatted.
32
 * </p>
32
 * </p>
33
 *
34
 * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas
33
 */
35
 */
34
public interface Sheet extends Iterable<Row> {
36
public interface Sheet extends Iterable<Row> {
35
37
Lines 781-784 Link Here
781
     */
783
     */
782
    boolean isSelected();
784
    boolean isSelected();
783
785
786
787
    /**
788
     * Sets array formula to specified region for result.
789
     *
790
     * @param formula Formula
791
     * @param range Region of array formula for result.
792
     */
793
    void setArrayFormula(String formula, CellRangeAddress range);
794
795
    /**
796
     * Remove a Array Formula from this sheet.  All cells contained in the Array Formula range are removed as well
797
     *
798
     * @param cell   any cell within Array Formula range
799
     */
800
    void removeArrayFormula(Cell cell);
784
}
801
}
(-)src/java/org/apache/poi/ss/util/CellRangeAddress.java (+3 lines)
Lines 84-89 Link Here
84
84
85
    public static CellRangeAddress valueOf(String ref) {
85
    public static CellRangeAddress valueOf(String ref) {
86
        int sep = ref.indexOf(":");
86
        int sep = ref.indexOf(":");
87
        if (sep == -1) {
88
            return valueOf(ref + ":" + ref);
89
        }
87
        CellReference cellFrom = new CellReference(ref.substring(0, sep));
90
        CellReference cellFrom = new CellReference(ref.substring(0, sep));
88
        CellReference cellTo = new CellReference(ref.substring(sep + 1));
91
        CellReference cellTo = new CellReference(ref.substring(sep + 1));
89
        return new CellRangeAddress(cellFrom.getRow(), cellTo.getRow(), cellFrom.getCol(), cellTo.getCol());
92
        return new CellRangeAddress(cellFrom.getRow(), cellTo.getRow(), cellFrom.getCol(), cellTo.getCol());
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (-2 / +30 lines)
Lines 37-42 Link Here
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 336-341 Link Here
336
        if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
337
        if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
337
338
338
        CTCellFormula f = _cell.getF();
339
        CTCellFormula f = _cell.getF();
340
        if (isPartOfArrayFormulaGroup() && f == null) {
341
            XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
342
            return cell.getCellFormula();
343
        }
339
        if(f.getT() == STCellFormulaType.SHARED){
344
        if(f.getT() == STCellFormulaType.SHARED){
340
            return convertSharedFormula((int)f.getSi());
345
            return convertSharedFormula((int)f.getSi());
341
        }
346
        }
Lines 363-368 Link Here
363
    }
368
    }
364
369
365
    public void setCellFormula(String formula) {
370
    public void setCellFormula(String formula) {
371
        setFormula(formula, FormulaType.CELL);
372
    }
373
374
    /* package */ void setCellArrayFormula(String formula, CellRangeAddress range) {
375
        setFormula(formula, FormulaType.ARRAY);
376
        CTCellFormula cellFormula = _cell.getF();
377
        cellFormula.setT(STCellFormulaType.ARRAY);
378
        cellFormula.setRef(range.formatAsString());
379
    }
380
381
    private void setFormula(String formula, int formulaType) {
366
        XSSFWorkbook wb = _row.getSheet().getWorkbook();
382
        XSSFWorkbook wb = _row.getSheet().getWorkbook();
367
        if (formula == null) {
383
        if (formula == null) {
368
            wb.onDeleteFormula(this);
384
            wb.onDeleteFormula(this);
Lines 372-378 Link Here
372
388
373
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
389
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
374
        //validate through the FormulaParser
390
        //validate through the FormulaParser
375
        FormulaParser.parse(formula, fpb, FormulaType.CELL, wb.getSheetIndex(getSheet()));
391
        FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()));
376
392
377
        CTCellFormula f =  CTCellFormula.Factory.newInstance();
393
        CTCellFormula f =  CTCellFormula.Factory.newInstance();
378
        f.setStringValue(formula);
394
        f.setStringValue(formula);
Lines 453-459 Link Here
453
     */
469
     */
454
    public int getCellType() {
470
    public int getCellType() {
455
471
456
        if (_cell.getF() != null) {
472
        if (_cell.getF() != null || getSheet().isCellInArrayFormulaContext(this)) {
457
            return CELL_TYPE_FORMULA;
473
            return CELL_TYPE_FORMULA;
458
        }
474
        }
459
475
Lines 911-914 Link Here
911
        throw new RuntimeException("Unexpected cell type (" + cellType + ")");
927
        throw new RuntimeException("Unexpected cell type (" + cellType + ")");
912
    }
928
    }
913
929
930
    public CellRangeAddress getArrayFormulaRange() {
931
        XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
932
        if (cell == null) {
933
            throw new IllegalStateException("not an array formula cell.");
934
        }
935
        String formulaRef = cell._cell.getF().getRef();
936
        return CellRangeAddress.valueOf(formulaRef);
937
    }
938
939
    public boolean isPartOfArrayFormulaGroup() {
940
        return getSheet().isCellInArrayFormulaContext(this);
941
    }
914
}
942
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (+10 lines)
Lines 19-24 Link Here
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
/**
24
/**
24
 * XSSF wrapper for a cell under evaluation
25
 * XSSF wrapper for a cell under evaluation
Lines 72-75 Link Here
72
	public String getStringCellValue() {
73
	public String getStringCellValue() {
73
		return _cell.getRichStringCellValue().getString();
74
		return _cell.getRichStringCellValue().getString();
74
	}
75
	}
76
77
	public boolean isArrayFormulaContext() {
78
        return _cell.isPartOfArrayFormulaGroup();
79
    }
80
81
    public EvaluationCell getFirstCellInArrayFormula() {
82
        CellRangeAddress range = _cell.getArrayFormulaRange();
83
        return _evalSheet.getCell(range.getFirstRow(), range.getFirstColumn());
84
    }
75
}
85
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (-6 / +99 lines)
Lines 25-37 Link Here
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 Link Here
42
 *
45
 *
43
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
46
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
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 142-150 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-179 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
176
		}
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
            }
198
        }
177
		return result;
199
		return result;
178
	}
200
	}
179
	private static void setCellType(Cell cell, CellValue cv) {
201
	private static void setCellType(Cell cell, CellValue cv) {
Lines 217-222 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
     * Set value in Range
258
     * 
259
     * @param cell
260
     * @param cvs
261
     * @return
262
     */
263
    private CellValue[][] setCellValues(Cell cell, CellValue[][] cvs) {
264
        CellRangeAddress range = cell.getArrayFormulaRange();
265
        int rowStart = range.getFirstRow();
266
        int colStart = range.getFirstColumn();
267
        Sheet sheet = cell.getSheet();
268
        CellValue[][] answer = (CellValue[][]) ArrayFormulaEvaluatorHelper.transformToRange(cvs, range);
269
        for (int i = rowStart; i <= range.getLastRow(); i++)
270
            for (int j = colStart; j <= range.getLastColumn(); j++) {
271
                Row row = sheet.getRow(i);
272
                if (row == null)
273
                    row = sheet.createRow(i);
274
                Cell c = row.getCell(j);
275
                if (c == null)
276
                    c = row.createCell(j);
277
                CellValue cellValue = answer[i - rowStart][j - colStart];
278
                setCellValue(c, cellValue);
279
            }
280
        return answer;
281
    }
282
    
220
	/**
283
	/**
221
	 * Loops over all cells in all sheets of the supplied
284
	 * Loops over all cells in all sheets of the supplied
222
	 *  workbook.
285
	 *  workbook.
Lines 250-255 Link Here
250
	 */
313
	 */
251
	private CellValue evaluateFormulaCellValue(Cell cell) {
314
	private CellValue evaluateFormulaCellValue(Cell cell) {
252
		ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell((XSSFCell) cell));
315
		ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell((XSSFCell) cell));
316
		if (eval instanceof ArrayEval) {// support of arrays
317
            if (cell.isPartOfArrayFormulaGroup()) {
318
                eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell);
319
            } else {
320
                eval = ((ArrayEval) eval).getArrayElementAsEval(0, 0);
321
            }
322
        }
253
		if (eval instanceof NumberEval) {
323
		if (eval instanceof NumberEval) {
254
			NumberEval ne = (NumberEval) eval;
324
			NumberEval ne = (NumberEval) eval;
255
			return new CellValue(ne.getNumberValue());
325
			return new CellValue(ne.getNumberValue());
Lines 267-270 Link Here
267
		}
337
		}
268
		throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
338
		throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
269
	}
339
	}
340
341
	/**
342
     * Returns a Array CellValue wrapper around the supplied ArrayEval instance.
343
     */
344
    private CellValue[][] evaluateFormulaCellArrayValues(XSSFCell cell) {
345
        ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell(cell));
346
        if (eval instanceof ArrayEval) {// support of arrays
347
            ArrayEval ae = (ArrayEval) eval;
348
            int rowCount = ae.getRowCount();
349
            int ColCount = ae.getColCount();
350
            CellValue[][] answer = new CellValue[rowCount][ColCount];
351
            for (int i = 0; i < rowCount; i++)
352
                for (int j = 0; j < ColCount; j++) {
353
                    ValueEval val = ae.getArrayElementAsEval(i, j);
354
                    answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val);
355
                }
356
            return answer;
357
        }
358
        // non-array (usually from aggregate function)
359
        CellValue[][] answer = new CellValue[1][1];
360
        answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval);
361
        return answer;
362
    }
270
}
363
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (+58 lines)
Lines 65-70 Link Here
65
 * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
65
 * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
66
 * contain text, numbers, dates, and formulas. Cells can also be formatted.
66
 * contain text, numbers, dates, and formulas. Cells can also be formatted.
67
 * </p>
67
 * </p>
68
 *
69
 * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas
68
 */
70
 */
69
public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
71
public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
70
    private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
72
    private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
Lines 78-83 Link Here
78
    private ColumnHelper columnHelper;
80
    private ColumnHelper columnHelper;
79
    private CommentsTable sheetComments;
81
    private CommentsTable sheetComments;
80
    private Map<Integer, XSSFCell> sharedFormulas;
82
    private Map<Integer, XSSFCell> sharedFormulas;
83
    private List<CellRangeAddress> arrayFormulas;
81
84
82
    /**
85
    /**
83
     * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
86
     * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
Lines 152-157 Link Here
152
    private void initRows(CTWorksheet worksheet) {
155
    private void initRows(CTWorksheet worksheet) {
153
        rows = new TreeMap<Integer, XSSFRow>();
156
        rows = new TreeMap<Integer, XSSFRow>();
154
        sharedFormulas = new HashMap<Integer, XSSFCell>();
157
        sharedFormulas = new HashMap<Integer, XSSFCell>();
158
        arrayFormulas = new ArrayList<CellRangeAddress>();
155
        for (CTRow row : worksheet.getSheetData().getRowArray()) {
159
        for (CTRow row : worksheet.getSheetData().getRowArray()) {
156
            XSSFRow r = new XSSFRow(row, this);
160
            XSSFRow r = new XSSFRow(row, this);
157
            rows.put(r.getRowNum(), r);
161
            rows.put(r.getRowNum(), r);
Lines 2317-2322 Link Here
2317
        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){
2318
            sharedFormulas.put((int)f.getSi(), cell);
2322
            sharedFormulas.put((int)f.getSi(), cell);
2319
        }
2323
        }
2324
        CTCellFormula formula = ct.getF();
2325
        if (formula != null && formula.getT() == STCellFormulaType.ARRAY && formula.getRef() != null) {
2326
            arrayFormulas.add(CellRangeAddress.valueOf(formula.getRef()));
2327
        }
2320
    }
2328
    }
2321
2329
2322
    @Override
2330
    @Override
Lines 2674-2677 Link Here
2674
    private boolean sheetProtectionEnabled() {
2682
    private boolean sheetProtectionEnabled() {
2675
        return worksheet.getSheetProtection().getSheet();
2683
        return worksheet.getSheetProtection().getSheet();
2676
    }
2684
    }
2685
2686
    /* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) {
2687
        for (CellRangeAddress range : arrayFormulas) {
2688
            if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
2689
                return true;
2690
            }
2691
        }
2692
        return false;
2693
    }
2694
2695
    /* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) {
2696
        for (CellRangeAddress range : arrayFormulas) {
2697
            if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
2698
                return getRow(range.getFirstRow()).getCell(range.getFirstColumn());
2699
            }
2700
        }
2701
        return null;
2702
    }
2703
2704
    public void setArrayFormula(String formula, CellRangeAddress range) {
2705
        XSSFRow row = getRow(range.getFirstRow());
2706
        if (row == null) {
2707
            row = createRow(range.getFirstRow());
2708
        }
2709
        XSSFCell mainArrayFormulaCell = row.getCell(range.getFirstColumn());
2710
        if (mainArrayFormulaCell == null) {
2711
            mainArrayFormulaCell = row.createCell(range.getFirstColumn());
2712
        }
2713
        mainArrayFormulaCell.setCellArrayFormula(formula, range);
2714
        arrayFormulas.add(range);
2715
    }
2716
2717
    public void removeArrayFormula(Cell cell) {
2718
        for (CellRangeAddress range : arrayFormulas) {
2719
            if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
2720
                arrayFormulas.remove(range);
2721
                for (int rowIndex = range.getFirstRow(); rowIndex <= range.getLastRow(); rowIndex++) {
2722
                    XSSFRow row = getRow(rowIndex);
2723
                    for (int columnIndex = range.getFirstColumn(); columnIndex <= range.getLastColumn(); columnIndex++) {
2724
                        XSSFCell arrayFormulaCell = row.getCell(columnIndex);
2725
                        if (arrayFormulaCell != null) {
2726
                            arrayFormulaCell.setCellType(Cell.CELL_TYPE_BLANK);
2727
                        }
2728
                    }
2729
                }
2730
                return;
2731
            }
2732
        }
2733
        throw new RuntimeException("Cell does not belong to Array Formula");
2734
    }
2677
}
2735
}
(-)src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (-1 / +1 lines)
Lines 48-54 Link Here
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

Return to bug 48292