ASF Bugzilla – Attachment 24658 Details for
Bug 48292
[PATCH] Support of array formulas
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
Final patch
CompleteArraySupport.txt (text/plain), 139.15 KB, created by
Petr.Udalau
on 2009-12-02 05:54:26 UTC
(
hide
)
Description:
Final patch
Filename:
MIME Type:
Creator:
Petr.Udalau
Created:
2009-12-02 05:54:26 UTC
Size:
139.15 KB
patch
obsolete
>Index: src/java/org/apache/poi/hssf/record/ArrayRecord.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/ArrayRecord.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/ArrayRecord.java (working copy) >@@ -1,87 +1,131 @@ >-/* ==================================================================== >- Licensed to the Apache Software Foundation (ASF) under one or more >- contributor license agreements. See the NOTICE file distributed with >- this work for additional information regarding copyright ownership. >- The ASF licenses this file to You under the Apache License, Version 2.0 >- (the "License"); you may not use this file except in compliance with >- the License. You may obtain a copy of the License at >- >- http://www.apache.org/licenses/LICENSE-2.0 >- >- Unless required by applicable law or agreed to in writing, software >- distributed under the License is distributed on an "AS IS" BASIS, >- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. >- See the License for the specific language governing permissions and >- limitations under the License. >-==================================================================== */ >- >-package org.apache.poi.hssf.record; >- >-import org.apache.poi.hssf.record.formula.Ptg; >-import org.apache.poi.ss.formula.Formula; >-import org.apache.poi.util.HexDump; >-import org.apache.poi.util.LittleEndianOutput; >- >-/** >- * ARRAY (0x0221)<p/> >- * >- * Treated in a similar way to SharedFormulaRecord >- * >- * @author Josh Micich >- */ >-public final class ArrayRecord extends SharedValueRecordBase { >- >- public final static short sid = 0x0221; >- private static final int OPT_ALWAYS_RECALCULATE = 0x0001; >- private static final int OPT_CALCULATE_ON_OPEN = 0x0002; >- >- private int _options; >- private int _field3notUsed; >- private Formula _formula; >- >- public ArrayRecord(RecordInputStream in) { >- super(in); >- _options = in.readUShort(); >- _field3notUsed = in.readInt(); >- int formulaTokenLen = in.readUShort(); >- int totalFormulaLen = in.available(); >- _formula = Formula.read(formulaTokenLen, in, totalFormulaLen); >- } >- >- public boolean isAlwaysRecalculate() { >- return (_options & OPT_ALWAYS_RECALCULATE) != 0; >- } >- public boolean isCalculateOnOpen() { >- return (_options & OPT_CALCULATE_ON_OPEN) != 0; >- } >- >- protected int getExtraDataSize() { >- return 2 + 4 >- + _formula.getEncodedSize(); >- } >- protected void serializeExtraData(LittleEndianOutput out) { >- out.writeShort(_options); >- out.writeInt(_field3notUsed); >- _formula.serialize(out); >- } >- >- public short getSid() { >- return sid; >- } >- >- public String toString() { >- StringBuffer sb = new StringBuffer(); >- sb.append(getClass().getName()).append(" [ARRAY]\n"); >- sb.append(" range=").append(getRange().toString()).append("\n"); >- sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n"); >- sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n"); >- sb.append(" formula:").append("\n"); >- Ptg[] ptgs = _formula.getTokens(); >- for (int i = 0; i < ptgs.length; i++) { >- Ptg ptg = ptgs[i]; >- sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n"); >- } >- sb.append("]"); >- return sb.toString(); >- } >-} >+/* ==================================================================== >+ Licensed to the Apache Software Foundation (ASF) under one or more >+ contributor license agreements. See the NOTICE file distributed with >+ this work for additional information regarding copyright ownership. >+ The ASF licenses this file to You under the Apache License, Version 2.0 >+ (the "License"); you may not use this file except in compliance with >+ the License. You may obtain a copy of the License at >+ >+ http://www.apache.org/licenses/LICENSE-2.0 >+ >+ Unless required by applicable law or agreed to in writing, software >+ distributed under the License is distributed on an "AS IS" BASIS, >+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. >+ See the License for the specific language governing permissions and >+ limitations under the License. >+==================================================================== */ >+ >+package org.apache.poi.hssf.record; >+ >+import org.apache.poi.hssf.record.formula.AreaPtgBase; >+import org.apache.poi.hssf.record.formula.Ptg; >+import org.apache.poi.hssf.record.formula.RefPtgBase; >+import org.apache.poi.hssf.util.CellRangeAddress8Bit; >+import org.apache.poi.ss.formula.Formula; >+import org.apache.poi.util.HexDump; >+import org.apache.poi.util.LittleEndianOutput; >+ >+/** >+ * ARRAY (0x0221)<p/> >+ * >+ * Treated in a similar way to SharedFormulaRecord >+ * >+ * @author Josh Micich >+ * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support >+ */ >+public final class ArrayRecord extends SharedValueRecordBase { >+ >+ public final static short sid = 0x0221; >+ private static final int OPT_ALWAYS_RECALCULATE = 0x0001; >+ private static final int OPT_CALCULATE_ON_OPEN = 0x0002; >+ >+ private int _options; >+ private int _field3notUsed; >+ private Formula _formula; >+ >+ public ArrayRecord(RecordInputStream in) { >+ super(in); >+ _options = in.readUShort(); >+ _field3notUsed = in.readInt(); >+ int formulaTokenLen = in.readUShort(); >+ int totalFormulaLen = in.available(); >+ _formula = Formula.read(formulaTokenLen, in, totalFormulaLen); >+ } >+ >+ public ArrayRecord(Formula formula, CellRangeAddress8Bit range ) { >+ super(range); >+ _options = OPT_CALCULATE_ON_OPEN; >+ _field3notUsed = 0; >+ _formula = formula; >+ } >+ >+ public boolean isAlwaysRecalculate() { >+ return (_options & OPT_ALWAYS_RECALCULATE) != 0; >+ } >+ public boolean isCalculateOnOpen() { >+ return (_options & OPT_CALCULATE_ON_OPEN) != 0; >+ } >+ >+ protected int getExtraDataSize() { >+ return 2 + 4 >+ + _formula.getEncodedSize(); >+ } >+ protected void serializeExtraData(LittleEndianOutput out) { >+ out.writeShort(_options); >+ out.writeInt(_field3notUsed); >+ _formula.serialize(out); >+ } >+ >+ public short getSid() { >+ return sid; >+ } >+ >+ public String toString() { >+ StringBuffer sb = new StringBuffer(); >+ sb.append(getClass().getName()).append(" [ARRAY]\n"); >+ sb.append(" range=").append(getRange().toString()).append("\n"); >+ sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n"); >+ sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n"); >+ sb.append(" formula:").append("\n"); >+ Ptg[] ptgs = _formula.getTokens(); >+ for (int i = 0; i < ptgs.length; i++) { >+ Ptg ptg = ptgs[i]; >+ sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n"); >+ } >+ sb.append("]"); >+ return sb.toString(); >+ } >+ >+ /** >+ * @return the equivalent {@link Ptg} array that the formula would have, >+ * were it not shared. >+ */ >+ public Ptg[] getFormulaTokens() { >+ int formulaRow = this.getFirstRow(); >+ int formulaColumn = this.getLastColumn(); >+ >+ // Use SharedFormulaRecord static method to convert formula >+ >+ Ptg[] ptgs = _formula.getTokens(); >+ >+ // Convert from relative addressing to absolute >+ // because all formulas in array need to be referenced to the same >+ // ref/range >+ for (int i = 0; i < ptgs.length; i++) { >+ Ptg ptg = ptgs[i]; >+ if (ptg instanceof AreaPtgBase) { >+ AreaPtgBase aptg = (AreaPtgBase) ptg; >+ aptg.setFirstRowRelative(false); >+ aptg.setLastRowRelative(false); >+ aptg.setFirstColRelative(false); >+ aptg.setLastColRelative(false); >+ >+ } else if (ptg instanceof RefPtgBase) { >+ RefPtgBase rptg = (RefPtgBase) ptg; >+ rptg.setRowRelative(false); >+ rptg.setColRelative(false); >+ } >+ } >+ return SharedFormulaRecord.convertSharedFormulas(ptgs, formulaRow, formulaColumn); >+ } >+} >Index: src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java (working copy) >@@ -17,6 +17,7 @@ > > package org.apache.poi.hssf.record.aggregates; > >+import org.apache.poi.hssf.record.ArrayRecord; > import org.apache.poi.hssf.record.CellValueRecordInterface; > import org.apache.poi.hssf.record.FormulaRecord; > import org.apache.poi.hssf.record.Record; >@@ -25,13 +26,17 @@ > import org.apache.poi.hssf.record.StringRecord; > import org.apache.poi.hssf.record.formula.ExpPtg; > import org.apache.poi.hssf.record.formula.Ptg; >+import org.apache.poi.hssf.util.CellRangeAddress8Bit; > import org.apache.poi.hssf.util.CellReference; >+import org.apache.poi.ss.formula.Formula; >+import org.apache.poi.ss.util.CellRangeAddress; > > /** > * The formula record aggregate is used to join together the formula record and it's > * (optional) string record and (optional) Shared Formula Record (template reads, excel optimization). > * > * @author Glen Stampoultzis (glens at apache.org) >+ * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support > */ > public final class FormulaRecordAggregate extends RecordAggregate implements CellValueRecordInterface { > >@@ -181,10 +186,15 @@ > } > > public Ptg[] getFormulaTokens() { >- if (_sharedFormulaRecord == null) { >- return _formulaRecord.getParsedExpression(); >+ if (_sharedFormulaRecord != null) { >+ return _sharedFormulaRecord.getFormulaTokens(_formulaRecord); > } >- return _sharedFormulaRecord.getFormulaTokens(_formulaRecord); >+ CellReference expRef = _formulaRecord.getFormula().getExpReference(); >+ if (expRef != null) { >+ ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol()); >+ return arec.getFormulaTokens(); >+ } >+ return _formulaRecord.getParsedExpression(); > } > > /** >@@ -216,4 +226,36 @@ > _sharedValueManager.unlink(_sharedFormulaRecord); > } > } >+ public boolean isPartOfArrayFormula() { >+ if (_sharedFormulaRecord != null) { >+ return false; >+ } >+ return _formulaRecord.getFormula().getExpReference() != null; >+ } >+ >+ public CellRangeAddress getArrayFormulaRange() { >+ if (_sharedFormulaRecord != null) { >+ throw new IllegalStateException("not an array formula cell."); >+ } >+ CellReference expRef = _formulaRecord.getFormula().getExpReference(); >+ if (expRef == null) { >+ throw new IllegalStateException("not an array formula cell."); >+ } >+ ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol()); >+ CellRangeAddress8Bit a = arec.getRange(); >+ return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn()); >+ } >+ public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) { >+ >+ ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn())); >+ _sharedValueManager.addArrayRecord(arr); >+ } >+ /** >+ * Removes an array formula >+ * @return the range of the array formula containing the specified cell. Never <code>null</code> >+ */ >+ public CellRangeAddress removeArrayFormula(int rowIndex, int columnIndex) { >+ CellRangeAddress8Bit a = _sharedValueManager.removeArrayFormula(rowIndex, columnIndex); >+ return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(), a.getLastColumn()); >+ } > } >Index: src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java (working copy) >@@ -1,278 +1,322 @@ >-/* ==================================================================== >- Licensed to the Apache Software Foundation (ASF) under one or more >- contributor license agreements. See the NOTICE file distributed with >- this work for additional information regarding copyright ownership. >- The ASF licenses this file to You under the Apache License, Version 2.0 >- (the "License"); you may not use this file except in compliance with >- the License. You may obtain a copy of the License at >- >- http://www.apache.org/licenses/LICENSE-2.0 >- >- Unless required by applicable law or agreed to in writing, software >- distributed under the License is distributed on an "AS IS" BASIS, >- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. >- See the License for the specific language governing permissions and >- limitations under the License. >-==================================================================== */ >- >-package org.apache.poi.hssf.record.aggregates; >- >-import java.util.Arrays; >-import java.util.Comparator; >-import java.util.HashMap; >-import java.util.Map; >- >-import org.apache.poi.hssf.record.ArrayRecord; >-import org.apache.poi.hssf.record.FormulaRecord; >-import org.apache.poi.hssf.record.SharedFormulaRecord; >-import org.apache.poi.hssf.record.SharedValueRecordBase; >-import org.apache.poi.hssf.record.TableRecord; >-import org.apache.poi.hssf.record.formula.ExpPtg; >-import org.apache.poi.hssf.util.CellRangeAddress8Bit; >-import org.apache.poi.ss.util.CellReference; >- >-/** >- * Manages various auxiliary records while constructing a >- * {@link RowRecordsAggregate}: >- * <ul> >- * <li>{@link SharedFormulaRecord}s</li> >- * <li>{@link ArrayRecord}s</li> >- * <li>{@link TableRecord}s</li> >- * </ul> >- * >- * @author Josh Micich >- */ >-public final class SharedValueManager { >- >- private static final class SharedFormulaGroup { >- private final SharedFormulaRecord _sfr; >- private final FormulaRecordAggregate[] _frAggs; >- private int _numberOfFormulas; >- /** >- * Coordinates of the first cell having a formula that uses this shared formula. >- * This is often <i>but not always</i> the top left cell in the range covered by >- * {@link #_sfr} >- */ >- private final CellReference _firstCell; >- >- public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) { >- if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) { >- throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString() >- + " is not shared formula range " + sfr.getRange().toString() + "."); >- } >- _sfr = sfr; >- _firstCell = firstCell; >- int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1; >- int height = sfr.getLastRow() - sfr.getFirstRow() + 1; >- _frAggs = new FormulaRecordAggregate[width * height]; >- _numberOfFormulas = 0; >- } >- >- public void add(FormulaRecordAggregate agg) { >- if (_numberOfFormulas == 0) { >- if (_firstCell.getRow() != agg.getRow() || _firstCell.getCol() != agg.getColumn()) { >- throw new IllegalStateException("shared formula coding error"); >- } >- } >- if (_numberOfFormulas >= _frAggs.length) { >- throw new RuntimeException("Too many formula records for shared formula group"); >- } >- _frAggs[_numberOfFormulas++] = agg; >- } >- >- public void unlinkSharedFormulas() { >- for (int i = 0; i < _numberOfFormulas; i++) { >- _frAggs[i].unlinkSharedFormula(); >- } >- } >- >- public SharedFormulaRecord getSFR() { >- return _sfr; >- } >- >- public final String toString() { >- StringBuffer sb = new StringBuffer(64); >- sb.append(getClass().getName()).append(" ["); >- sb.append(_sfr.getRange().toString()); >- sb.append("]"); >- return sb.toString(); >- } >- >- /** >- * Note - the 'first cell' of a shared formula group is not always the top-left cell >- * of the enclosing range. >- * @return <code>true</code> if the specified coordinates correspond to the 'first cell' >- * of this shared formula group. >- */ >- public boolean isFirstCell(int row, int column) { >- return _firstCell.getRow() == row && _firstCell.getCol() == column; >- } >- } >- >- public static final SharedValueManager EMPTY = new SharedValueManager( >- new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]); >- private final ArrayRecord[] _arrayRecords; >- private final TableRecord[] _tableRecords; >- private final Map<SharedFormulaRecord, SharedFormulaGroup> _groupsBySharedFormulaRecord; >- /** cached for optimization purposes */ >- private SharedFormulaGroup[] _groups; >- >- private SharedValueManager(SharedFormulaRecord[] sharedFormulaRecords, >- CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) { >- int nShF = sharedFormulaRecords.length; >- if (nShF != firstCells.length) { >- throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + "."); >- } >- _arrayRecords = arrayRecords; >- _tableRecords = tableRecords; >- Map<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2); >- for (int i = 0; i < nShF; i++) { >- SharedFormulaRecord sfr = sharedFormulaRecords[i]; >- m.put(sfr, new SharedFormulaGroup(sfr, firstCells[i])); >- } >- _groupsBySharedFormulaRecord = m; >- } >- >- /** >- * @param firstCells >- * @param recs list of sheet records (possibly contains records for other parts of the Excel file) >- * @param startIx index of first row/cell record for current sheet >- * @param endIx one past index of last row/cell record for current sheet. It is important >- * that this code does not inadvertently collect <tt>SharedFormulaRecord</tt>s from any other >- * sheet (which could happen if endIx is chosen poorly). (see bug 44449) >- */ >- public static SharedValueManager create(SharedFormulaRecord[] sharedFormulaRecords, >- CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) { >- if (sharedFormulaRecords.length + firstCells.length + arrayRecords.length + tableRecords.length < 1) { >- return EMPTY; >- } >- return new SharedValueManager(sharedFormulaRecords, firstCells, arrayRecords, tableRecords); >- } >- >- >- /** >- * @param firstCell as extracted from the {@link ExpPtg} from the cell's formula. >- * @return never <code>null</code> >- */ >- public SharedFormulaRecord linkSharedFormulaRecord(CellReference firstCell, FormulaRecordAggregate agg) { >- >- SharedFormulaGroup result = findFormulaGroup(getGroups(), firstCell); >- result.add(agg); >- return result.getSFR(); >- } >- >- private static SharedFormulaGroup findFormulaGroup(SharedFormulaGroup[] groups, CellReference firstCell) { >- int row = firstCell.getRow(); >- int column = firstCell.getCol(); >- // Traverse the list of shared formulas and try to find the correct one for us >- >- // perhaps this could be optimised to some kind of binary search >- for (int i = 0; i < groups.length; i++) { >- SharedFormulaGroup svg = groups[i]; >- if (svg.isFirstCell(row, column)) { >- return svg; >- } >- } >- // TODO - fix file "15228.xls" so it opens in Excel after rewriting with POI >- throw new RuntimeException("Failed to find a matching shared formula record"); >- } >- >- private SharedFormulaGroup[] getGroups() { >- if (_groups == null) { >- SharedFormulaGroup[] groups = new SharedFormulaGroup[_groupsBySharedFormulaRecord.size()]; >- _groupsBySharedFormulaRecord.values().toArray(groups); >- Arrays.sort(groups, SVGComparator); // make search behaviour more deterministic >- _groups = groups; >- } >- return _groups; >- } >- >- private static final Comparator<SharedFormulaGroup> SVGComparator = new Comparator<SharedFormulaGroup>() { >- >- public int compare(SharedFormulaGroup a, SharedFormulaGroup b) { >- CellRangeAddress8Bit rangeA = a.getSFR().getRange(); >- CellRangeAddress8Bit rangeB = b.getSFR().getRange(); >- >- int cmp; >- cmp = rangeA.getFirstRow() - rangeB.getFirstRow(); >- if (cmp != 0) { >- return cmp; >- } >- cmp = rangeA.getFirstColumn() - rangeB.getFirstColumn(); >- if (cmp != 0) { >- return cmp; >- } >- return 0; >- } >- }; >- >- /** >- * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the >- * formula record contained in the specified {@link FormulaRecordAggregate} agg. Note - the >- * shared value record always appears after the first formula record in the group. For arrays >- * and tables the first formula is always the in the top left cell. However, since shared >- * formula groups can be sparse and/or overlap, the first formula may not actually be in the >- * top left cell. >- * >- * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of >- * a table or array region. <code>null</code> if the formula cell is not shared/array/table, >- * or if the specified formula is not the the first in the group. >- */ >- public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) { >- CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference(); >- // perhaps this could be optimised by consulting the (somewhat unreliable) isShared flag >- // and/or distinguishing between tExp and tTbl. >- if (firstCell == null) { >- // not a shared/array/table formula >- return null; >- } >- >- >- int row = firstCell.getRow(); >- int column = firstCell.getCol(); >- if (agg.getRow() != row || agg.getColumn() != column) { >- // not the first formula cell in the group >- return null; >- } >- SharedFormulaGroup[] groups = getGroups(); >- for (int i = 0; i < groups.length; i++) { >- // note - logic for finding correct shared formula group is slightly >- // more complicated since the first cell >- SharedFormulaGroup sfg = groups[i]; >- if (sfg.isFirstCell(row, column)) { >- return sfg.getSFR(); >- } >- } >- >- // Since arrays and tables cannot be sparse (all cells in range participate) >- // The first cell will be the top left in the range. So we can match the >- // ARRAY/TABLE record directly. >- >- for (int i = 0; i < _tableRecords.length; i++) { >- TableRecord tr = _tableRecords[i]; >- if (tr.isFirstCell(row, column)) { >- return tr; >- } >- } >- for (int i = 0; i < _arrayRecords.length; i++) { >- ArrayRecord ar = _arrayRecords[i]; >- if (ar.isFirstCell(row, column)) { >- return ar; >- } >- } >- return null; >- } >- >- /** >- * Converts all {@link FormulaRecord}s handled by <tt>sharedFormulaRecord</tt> >- * to plain unshared formulas >- */ >- public void unlink(SharedFormulaRecord sharedFormulaRecord) { >- SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord); >- _groups = null; // be sure to reset cached value >- if (svg == null) { >- throw new IllegalStateException("Failed to find formulas for shared formula"); >- } >- svg.unlinkSharedFormulas(); >- } >-} >+/* ==================================================================== >+ Licensed to the Apache Software Foundation (ASF) under one or more >+ contributor license agreements. See the NOTICE file distributed with >+ this work for additional information regarding copyright ownership. >+ The ASF licenses this file to You under the Apache License, Version 2.0 >+ (the "License"); you may not use this file except in compliance with >+ the License. You may obtain a copy of the License at >+ >+ http://www.apache.org/licenses/LICENSE-2.0 >+ >+ Unless required by applicable law or agreed to in writing, software >+ distributed under the License is distributed on an "AS IS" BASIS, >+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. >+ See the License for the specific language governing permissions and >+ limitations under the License. >+==================================================================== */ >+ >+package org.apache.poi.hssf.record.aggregates; >+ >+import java.util.ArrayList; >+import java.util.Arrays; >+import java.util.Comparator; >+import java.util.HashMap; >+import java.util.List; >+import java.util.Map; >+ >+import org.apache.poi.hssf.record.ArrayRecord; >+import org.apache.poi.hssf.record.FormulaRecord; >+import org.apache.poi.hssf.record.SharedFormulaRecord; >+import org.apache.poi.hssf.record.SharedValueRecordBase; >+import org.apache.poi.hssf.record.TableRecord; >+import org.apache.poi.hssf.record.formula.ExpPtg; >+import org.apache.poi.hssf.util.CellRangeAddress8Bit; >+import org.apache.poi.ss.util.CellReference; >+ >+/** >+ * Manages various auxiliary records while constructing a >+ * {@link RowRecordsAggregate}: >+ * <ul> >+ * <li>{@link SharedFormulaRecord}s</li> >+ * <li>{@link ArrayRecord}s</li> >+ * <li>{@link TableRecord}s</li> >+ * </ul> >+ * >+ * @author Josh Micich >+ * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - handling of ArrayRecords >+ */ >+public final class SharedValueManager { >+ >+ private static final class SharedFormulaGroup { >+ private final SharedFormulaRecord _sfr; >+ private final FormulaRecordAggregate[] _frAggs; >+ private int _numberOfFormulas; >+ /** >+ * Coordinates of the first cell having a formula that uses this shared formula. >+ * This is often <i>but not always</i> the top left cell in the range covered by >+ * {@link #_sfr} >+ */ >+ private final CellReference _firstCell; >+ >+ public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) { >+ if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) { >+ throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString() >+ + " is not shared formula range " + sfr.getRange().toString() + "."); >+ } >+ _sfr = sfr; >+ _firstCell = firstCell; >+ int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1; >+ int height = sfr.getLastRow() - sfr.getFirstRow() + 1; >+ _frAggs = new FormulaRecordAggregate[width * height]; >+ _numberOfFormulas = 0; >+ } >+ >+ public void add(FormulaRecordAggregate agg) { >+ if (_numberOfFormulas == 0) { >+ if (_firstCell.getRow() != agg.getRow() || _firstCell.getCol() != agg.getColumn()) { >+ throw new IllegalStateException("shared formula coding error"); >+ } >+ } >+ if (_numberOfFormulas >= _frAggs.length) { >+ throw new RuntimeException("Too many formula records for shared formula group"); >+ } >+ _frAggs[_numberOfFormulas++] = agg; >+ } >+ >+ public void unlinkSharedFormulas() { >+ for (int i = 0; i < _numberOfFormulas; i++) { >+ _frAggs[i].unlinkSharedFormula(); >+ } >+ } >+ >+ public SharedFormulaRecord getSFR() { >+ return _sfr; >+ } >+ >+ public final String toString() { >+ StringBuffer sb = new StringBuffer(64); >+ sb.append(getClass().getName()).append(" ["); >+ sb.append(_sfr.getRange().toString()); >+ sb.append("]"); >+ return sb.toString(); >+ } >+ >+ /** >+ * Note - the 'first cell' of a shared formula group is not always the top-left cell >+ * of the enclosing range. >+ * @return <code>true</code> if the specified coordinates correspond to the 'first cell' >+ * of this shared formula group. >+ */ >+ public boolean isFirstCell(int row, int column) { >+ return _firstCell.getRow() == row && _firstCell.getCol() == column; >+ } >+ } >+ >+ public static final SharedValueManager EMPTY = new SharedValueManager( >+ new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]); >+ private final List<ArrayRecord> _arrayRecords; >+ private final TableRecord[] _tableRecords; >+ private final Map<SharedFormulaRecord, SharedFormulaGroup> _groupsBySharedFormulaRecord; >+ /** cached for optimization purposes */ >+ private SharedFormulaGroup[] _groups; >+ >+ private SharedValueManager(SharedFormulaRecord[] sharedFormulaRecords, >+ CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) { >+ int nShF = sharedFormulaRecords.length; >+ if (nShF != firstCells.length) { >+ throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + "."); >+ } >+ _arrayRecords = toList(arrayRecords); >+ _tableRecords = tableRecords; >+ Map<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2); >+ for (int i = 0; i < nShF; i++) { >+ SharedFormulaRecord sfr = sharedFormulaRecords[i]; >+ m.put(sfr, new SharedFormulaGroup(sfr, firstCells[i])); >+ } >+ _groupsBySharedFormulaRecord = m; >+ } >+ >+ private static <Z> List<Z> toList(Z[] zz) { >+ List<Z> result = new ArrayList<Z>(zz.length); >+ for (int i = 0; i < zz.length; i++) { >+ result.add(zz[i]); >+ } >+ return result; >+ } >+ >+ /** >+ * @param firstCells >+ * @param recs list of sheet records (possibly contains records for other parts of the Excel file) >+ * @param startIx index of first row/cell record for current sheet >+ * @param endIx one past index of last row/cell record for current sheet. It is important >+ * that this code does not inadvertently collect <tt>SharedFormulaRecord</tt>s from any other >+ * sheet (which could happen if endIx is chosen poorly). (see bug 44449) >+ */ >+ public static SharedValueManager create(SharedFormulaRecord[] sharedFormulaRecords, >+ CellReference[] firstCells, ArrayRecord[] arrayRecords, TableRecord[] tableRecords) { >+ if (sharedFormulaRecords.length + firstCells.length + arrayRecords.length + tableRecords.length < 1) { >+ return EMPTY; >+ } >+ return new SharedValueManager(sharedFormulaRecords, firstCells, arrayRecords, tableRecords); >+ } >+ >+ >+ /** >+ * @param firstCell as extracted from the {@link ExpPtg} from the cell's formula. >+ * @return never <code>null</code> >+ */ >+ public SharedFormulaRecord linkSharedFormulaRecord(CellReference firstCell, FormulaRecordAggregate agg) { >+ >+ SharedFormulaGroup result = findFormulaGroup(getGroups(), firstCell); >+ result.add(agg); >+ return result.getSFR(); >+ } >+ >+ private static SharedFormulaGroup findFormulaGroup(SharedFormulaGroup[] groups, CellReference firstCell) { >+ int row = firstCell.getRow(); >+ int column = firstCell.getCol(); >+ // Traverse the list of shared formulas and try to find the correct one for us >+ >+ // perhaps this could be optimised to some kind of binary search >+ for (int i = 0; i < groups.length; i++) { >+ SharedFormulaGroup svg = groups[i]; >+ if (svg.isFirstCell(row, column)) { >+ return svg; >+ } >+ } >+ // TODO - fix file "15228.xls" so it opens in Excel after rewriting with POI >+ throw new RuntimeException("Failed to find a matching shared formula record"); >+ } >+ >+ private SharedFormulaGroup[] getGroups() { >+ if (_groups == null) { >+ SharedFormulaGroup[] groups = new SharedFormulaGroup[_groupsBySharedFormulaRecord.size()]; >+ _groupsBySharedFormulaRecord.values().toArray(groups); >+ Arrays.sort(groups, SVGComparator); // make search behaviour more deterministic >+ _groups = groups; >+ } >+ return _groups; >+ } >+ >+ private static final Comparator<SharedFormulaGroup> SVGComparator = new Comparator<SharedFormulaGroup>() { >+ >+ public int compare(SharedFormulaGroup a, SharedFormulaGroup b) { >+ CellRangeAddress8Bit rangeA = a.getSFR().getRange(); >+ CellRangeAddress8Bit rangeB = b.getSFR().getRange(); >+ >+ int cmp; >+ cmp = rangeA.getFirstRow() - rangeB.getFirstRow(); >+ if (cmp != 0) { >+ return cmp; >+ } >+ cmp = rangeA.getFirstColumn() - rangeB.getFirstColumn(); >+ if (cmp != 0) { >+ return cmp; >+ } >+ return 0; >+ } >+ }; >+ >+ /** >+ * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the >+ * formula record contained in the specified {@link FormulaRecordAggregate} agg. Note - the >+ * shared value record always appears after the first formula record in the group. For arrays >+ * and tables the first formula is always the in the top left cell. However, since shared >+ * formula groups can be sparse and/or overlap, the first formula may not actually be in the >+ * top left cell. >+ * >+ * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of >+ * a table or array region. <code>null</code> if the formula cell is not shared/array/table, >+ * or if the specified formula is not the the first in the group. >+ */ >+ public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) { >+ CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference(); >+ // perhaps this could be optimised by consulting the (somewhat unreliable) isShared flag >+ // and/or distinguishing between tExp and tTbl. >+ if (firstCell == null) { >+ // not a shared/array/table formula >+ return null; >+ } >+ >+ >+ int row = firstCell.getRow(); >+ int column = firstCell.getCol(); >+ if (agg.getRow() != row || agg.getColumn() != column) { >+ // not the first formula cell in the group >+ return null; >+ } >+ SharedFormulaGroup[] groups = getGroups(); >+ for (int i = 0; i < groups.length; i++) { >+ // note - logic for finding correct shared formula group is slightly >+ // more complicated since the first cell >+ SharedFormulaGroup sfg = groups[i]; >+ if (sfg.isFirstCell(row, column)) { >+ return sfg.getSFR(); >+ } >+ } >+ >+ // Since arrays and tables cannot be sparse (all cells in range participate) >+ // The first cell will be the top left in the range. So we can match the >+ // ARRAY/TABLE record directly. >+ >+ for (TableRecord tr : _tableRecords) { >+ if (tr.isFirstCell(row, column)) { >+ return tr; >+ } >+ } >+ for (ArrayRecord ar : _arrayRecords) { >+ if (ar.isFirstCell(row, column)) { >+ return ar; >+ } >+ } >+ return null; >+ } >+ >+ /** >+ * Converts all {@link FormulaRecord}s handled by <tt>sharedFormulaRecord</tt> >+ * to plain unshared formulas >+ */ >+ public void unlink(SharedFormulaRecord sharedFormulaRecord) { >+ SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord); >+ if (svg == null) { >+ throw new IllegalStateException("Failed to find formulas for shared formula"); >+ } >+ _groups = null; // be sure to reset cached value >+ svg.unlinkSharedFormulas(); >+ } >+ >+ /** >+ * Add specified Array Record. >+ */ >+ public void addArrayRecord(ArrayRecord ar) { >+ // could do a check here to make sure none of the ranges overlap >+ _arrayRecords.add(ar); >+ } >+ >+ /** >+ * Removes the {@link ArrayRecord} for the cell group containing the specified cell. >+ * The caller should clear (set blank) all cells in the returned range. >+ * @return the range of the array formula which was just removed. Never <code>null</code>. >+ */ >+ public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) { >+ for (ArrayRecord ar : _arrayRecords) { >+ if (ar.isInRange(rowIndex, columnIndex)) { >+ _arrayRecords.remove(ar); >+ return ar.getRange(); >+ } >+ } >+ throw new IllegalArgumentException("Specified cell is not part of an array formula."); >+ } >+ >+ /** >+ * @return the shared ArrayRecord identified by (firstRow, firstColumn). never <code>null</code>. >+ */ >+ public ArrayRecord getArrayRecord(int firstRow, int firstColumn) { >+ for(ArrayRecord ar : _arrayRecords) { >+ if(ar.isFirstCell(firstRow, firstColumn)) { >+ return ar; >+ } >+ } >+ return null; >+ } >+} >Index: src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (working copy) >@@ -25,10 +25,11 @@ > > /** > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array suport; > */ > public abstract class AggregateFunction extends MultiOperandNumericFunction { > >- private static final class LargeSmall extends Fixed2ArgFunction { >+ private static final class LargeSmall extends Fixed2ArgFunction implements FunctionWithArraySupport { > private final boolean _isLarge; > protected LargeSmall(boolean isLarge) { > _isLarge = isLarge; >@@ -66,6 +67,12 @@ > > return new NumberEval(result); > } >+ >+ public boolean supportArray(int paramIndex){ >+ if ( paramIndex == 1) >+ return false; >+ return true; >+ } > } > private static final class ValueCollector extends MultiOperandNumericFunction { > private static final ValueCollector instance = new ValueCollector(); >Index: src/java/org/apache/poi/hssf/record/formula/functions/ArrayMode.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/ArrayMode.java (revision 0) >+++ src/java/org/apache/poi/hssf/record/formula/functions/ArrayMode.java (revision 0) >@@ -0,0 +1,17 @@ >+/** >+ * >+ */ >+package org.apache.poi.hssf.record.formula.functions; >+ >+import org.apache.poi.hssf.record.formula.eval.ValueEval; >+ >+/** >+ * Interface for those functions that behaves differently in array formula >+ * >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) >+ */ >+public interface ArrayMode { >+ >+ ValueEval evaluateInArrayFormula(ValueEval[] args, int srcRowIndex, int srcColumnIndex); >+ >+} >Index: src/java/org/apache/poi/hssf/record/formula/functions/BooleanFunction.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/BooleanFunction.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/BooleanFunction.java (working copy) >@@ -24,6 +24,7 @@ > import org.apache.poi.hssf.record.formula.eval.EvaluationException; > import org.apache.poi.hssf.record.formula.eval.OperandResolver; > import org.apache.poi.hssf.record.formula.eval.RefEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Here are the general rules concerning Boolean functions: >@@ -36,7 +37,7 @@ > * > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > > */ >-public abstract class BooleanFunction implements Function { >+public abstract class BooleanFunction implements FunctionWithArraySupport { > > public final ValueEval evaluate(ValueEval[] args, int srcRow, int srcCol) { > if (args.length < 1) { >@@ -51,6 +52,10 @@ > return BoolEval.valueOf(boolResult); > } > >+ public boolean supportArray(int paramIndex){ >+ return true; >+ } >+ > private boolean calculate(ValueEval[] args) throws EvaluationException { > > boolean result = getInitialResultValue(); >@@ -77,10 +82,32 @@ > } > continue; > } >+ >+ if (arg instanceof ArrayEval){ >+ ArrayEval ae = (ArrayEval)arg; >+ int rows = ae.getRowCount(); >+ int cols = ae.getColCount(); >+ for (int r=0; r<rows; r++){ >+ for (int c=0; c<cols; c++){ >+ ValueEval ve = ae.getArrayElementAsEval(r, c); >+ Boolean tempVe = OperandResolver.coerceValueToBoolean(ve, true); >+ if (tempVe != null) { >+ result = partialEvaluate(result, tempVe.booleanValue()); >+ atleastOneNonBlank = true; >+ } >+ >+ } >+ } >+ continue; >+ } >+ > Boolean tempVe; > if (arg instanceof RefEval) { > ValueEval ve = ((RefEval) arg).getInnerValueEval(); > tempVe = OperandResolver.coerceValueToBoolean(ve, true); >+ } else if (arg instanceof ValueEval) { >+ ValueEval ve = (ValueEval) arg; >+ tempVe = OperandResolver.coerceValueToBoolean(ve, false); > } else { > tempVe = OperandResolver.coerceValueToBoolean(arg, false); > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Choose.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Choose.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Choose.java (working copy) >@@ -27,7 +27,7 @@ > /** > * @author Josh Micich > */ >-public final class Choose implements Function { >+public final class Choose implements FunctionWithArraySupport { > > public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { > if (args.length < 2) { >@@ -54,4 +54,9 @@ > ValueEval ev = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); > return OperandResolver.coerceValueToInt(ev); > } >+ >+ public boolean supportArray(int paramIndex) { >+ >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Column.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Column.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Column.java (working copy) >@@ -22,8 +22,9 @@ > import org.apache.poi.hssf.record.formula.eval.NumberEval; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > >-public final class Column implements Function0Arg, Function1Arg { >+public final class Column implements Function0Arg, Function1Arg, ArrayMode { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { > return new NumberEval(srcColumnIndex+1); >@@ -51,4 +52,17 @@ > } > return ErrorEval.VALUE_INVALID; > } >+ >+ public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) { >+ if ((evals.length == 1) && (evals[0] instanceof AreaEval)) { >+ AreaEval ae = (AreaEval) evals[0]; >+ ValueEval[][] result = new ValueEval[1][ae.getWidth()]; >+ for (int c = 0; c < ae.getWidth(); c++) { >+ result[0][c] = new NumberEval(ae.getFirstColumn() + c + 1); >+ } >+ return new ArrayEval(result); >+ } >+ return evaluate(evals, srcCellRow, srcCellCol); >+ >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Columns.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Columns.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Columns.java (working copy) >@@ -22,17 +22,21 @@ > import org.apache.poi.hssf.record.formula.eval.NumberEval; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Implementation for Excel COLUMNS function. > * > * @author Josh Micich > */ >-public final class Columns extends Fixed1ArgFunction { >+public final class Columns extends Fixed1ArgFunction implements FunctionWithArraySupport { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { > > int result; >+ if (arg0 instanceof ArrayEval) { >+ arg0 = ((ArrayEval) arg0).arrayAsArea(); >+ } > if (arg0 instanceof AreaEval) { > result = ((AreaEval) arg0).getWidth(); > } else if (arg0 instanceof RefEval) { >@@ -42,4 +46,8 @@ > } > return new NumberEval(result); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Count.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Count.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Count.java (working copy) >@@ -34,7 +34,7 @@ > * TODO: Check this properly matches excel on edge cases > * like formula cells, error cells etc > */ >-public final class Count implements Function { >+public final class Count implements FunctionWithArraySupport { > > public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { > int nArgs = args.length; >@@ -74,4 +74,8 @@ > return false; > } > }; >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/CountUtils.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/CountUtils.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/CountUtils.java (working copy) >@@ -20,11 +20,13 @@ > import org.apache.poi.hssf.record.formula.eval.AreaEval; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Common logic for COUNT, COUNTA and COUNTIF > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ > final class CountUtils { > >@@ -72,6 +74,9 @@ > if (eval instanceof AreaEval) { > return CountUtils.countMatchingCellsInArea((AreaEval) eval, criteriaPredicate); > } >+ if (eval instanceof ArrayEval){ >+ return CountUtils.countMatchingCellsInArea( ( (ArrayEval)eval).arrayAsArea(), criteriaPredicate); >+ } > if (eval instanceof RefEval) { > return CountUtils.countMatchingCell((RefEval) eval, criteriaPredicate); > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Counta.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Counta.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Counta.java (working copy) >@@ -32,7 +32,7 @@ > * > * @author Josh Micich > */ >-public final class Counta implements Function { >+public final class Counta implements FunctionWithArraySupport { > > public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { > int nArgs = args.length; >@@ -69,4 +69,8 @@ > return true; > } > }; >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Countif.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Countif.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Countif.java (working copy) >@@ -44,7 +44,7 @@ > * > * @author Josh Micich > */ >-public final class Countif extends Fixed2ArgFunction { >+public final class Countif extends Fixed2ArgFunction implements FunctionWithArraySupport { > > private static final class CmpOp { > public static final int NONE = 0; >@@ -525,4 +525,10 @@ > } > return null; > } >+ >+ public boolean supportArray(int paramIndex){ >+ if (paramIndex==0) >+ return true; >+ return false; // TODO - counif does not allow first param as array - only as range >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/FunctionWithArraySupport.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/FunctionWithArraySupport.java (revision 0) >+++ src/java/org/apache/poi/hssf/record/formula/functions/FunctionWithArraySupport.java (revision 0) >@@ -0,0 +1,15 @@ >+/** >+ * Later this interface should be merged with Function interface >+ */ >+package org.apache.poi.hssf.record.formula.functions; >+ >+/** >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) >+ */ >+public interface FunctionWithArraySupport extends Function { >+ >+ /* >+ * true if parameter accept array, false otherwise >+ */ >+ public boolean supportArray(int paramIndex); >+} >Index: src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java (working copy) >@@ -39,7 +39,7 @@ > * > * @author Josh Micich > */ >-public final class Hlookup extends Var3or4ArgFunction { >+public final class Hlookup extends Var3or4ArgFunction implements FunctionWithArraySupport { > private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE; > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, >@@ -77,4 +77,10 @@ > } > return LookupUtils.createRowVector(tableArray, rowIndex); > } >+ >+ public boolean supportArray(int paramIndex) { >+ if (paramIndex == 1) >+ return true; >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Index.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Index.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Index.java (working copy) >@@ -25,6 +25,7 @@ > import org.apache.poi.hssf.record.formula.eval.OperandResolver; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Implementation for the Excel function INDEX >@@ -43,30 +44,48 @@ > * </p> > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Index implements Function2Arg, Function3Arg, Function4Arg { >+public final class Index implements Function2Arg, Function3Arg, Function4Arg, FunctionWithArraySupport, ArrayMode { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { >+ return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, false); >+ } >+ >+ >+ public ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, boolean supportRowColumn) { >+ >+ if (arg0 instanceof ArrayEval){ >+ supportRowColumn = true; >+ } > AreaEval reference = convertFirstArg(arg0); > > boolean colArgWasPassed = false; > int columnIx = 0; > try { > int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex); >- return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex); >+ return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn); > } catch (EvaluationException e) { > return e.getErrorEval(); > } > } > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, > ValueEval arg2) { >+ return evaluateX(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, false); >+ } >+ public ValueEval evaluateX(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, >+ ValueEval arg2, boolean supportRowColumn) { >+ >+ if (arg0 instanceof ArrayEval){ >+ supportRowColumn = true; >+ } > AreaEval reference = convertFirstArg(arg0); > > boolean colArgWasPassed = true; > try { > int columnIx = resolveIndexArg(arg2, srcRowIndex, srcColumnIndex); > int rowIx = resolveIndexArg(arg1, srcRowIndex, srcColumnIndex); >- return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex); >+ return getValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex, supportRowColumn); > } catch (EvaluationException e) { > return e.getErrorEval(); > } >@@ -87,6 +106,9 @@ > // convert to area ref for simpler code in getValueFromArea() > return ((RefEval)firstArg).offset(0, 0, 0, 0); > } >+ if (firstArg instanceof ArrayEval){ >+ firstArg = ((ArrayEval)firstArg).arrayAsArea(); >+ } > if((firstArg instanceof AreaEval)) { > return (AreaEval) firstArg; > } >@@ -98,11 +120,15 @@ > } > > public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { >+ return evaluateX(args, srcRowIndex, srcColumnIndex, false); >+ } >+ >+ public ValueEval evaluateX(ValueEval[] args, int srcRowIndex, int srcColumnIndex, boolean supportRowColumn) { > switch (args.length) { > case 2: >- return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]); >+ return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], supportRowColumn); > case 3: >- return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); >+ return evaluateX(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], supportRowColumn); > case 4: > return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3]); > } >@@ -118,12 +144,34 @@ > * different when only 2 args are passed. > */ > private static ValueEval getValueFromArea(AreaEval ae, int pRowIx, int pColumnIx, >- boolean colArgWasPassed, int srcRowIx, int srcColIx) throws EvaluationException { >+ boolean colArgWasPassed, int srcRowIx, int srcColIx, boolean supportRowColumn) throws EvaluationException { > boolean rowArgWasEmpty = pRowIx == 0; > boolean colArgWasEmpty = pColumnIx == 0; > int rowIx; > int columnIx; > >+ // implementation of this function isn't support all features of the Excel >+ // here I'm adding only support for return of entire row or columm >+ if ( supportRowColumn && ( (rowArgWasEmpty && !ae.isRow() && pColumnIx<=ae.getWidth() && !colArgWasEmpty) || >+ (colArgWasEmpty && !ae.isColumn() && pRowIx<=ae.getHeight() && !rowArgWasEmpty)) >+ ){ >+ // return row or column >+ ValueEval[][] result = null; >+ if (rowArgWasEmpty ){ // entire column >+ result = new ValueEval[ae.getHeight()][1]; >+ for( int r=0; r<ae.getHeight(); r++ ){ >+ result[r][0] = ae.getRelativeValue(r, pColumnIx-1); >+ } >+ } >+ else { //entire row >+ result = new ValueEval[1][ae.getWidth()]; >+ for (int c=0; c<ae.getWidth(); c++){ >+ result[0][c] = ae.getRelativeValue( pRowIx-1,c); >+ } >+ } >+ return (new ArrayEval(result)); >+ } >+ > // when the area ref is a single row or a single column, > // there are special rules for conversion of rowIx and columnIx > if (ae.isRow()) { >@@ -209,4 +257,15 @@ > } > return result; > } >+ >+ public ValueEval evaluateInArrayFormula(ValueEval[] args, int srcCellRow, int srcCellCol) { >+ // in array formula index(reference,row,0) and index(reference,0,col) should return entire row/column >+ return evaluateX(args, srcCellRow, srcCellCol, true); >+ } >+ >+ public boolean supportArray(int paramIndex){ >+ if (paramIndex == 0) >+ return true; >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java (working copy) >@@ -36,8 +36,9 @@ > * <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/> > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Lookup extends Var2or3ArgFunction { >+public final class Lookup extends Var2or3ArgFunction implements FunctionWithArraySupport { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { > // complex rules to choose lookupVector and resultVector from the single area ref >@@ -73,4 +74,15 @@ > // extra complexity required to emulate the way LOOKUP can handles these abnormal cases. > throw new RuntimeException("non-vector lookup or result areas not supported yet"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ switch (paramIndex) { >+ default: >+ return false; >+ case 1: >+ case 2: >+ return true; >+ } >+ >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java (working copy) >@@ -28,11 +28,13 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ > final class LookupUtils { > >@@ -373,6 +375,9 @@ > // It doesn't matter if eval is a 2D or 3D ref, because that detail is never asked of AreaEval. > return refEval.offset(0, 0, 0, 0); > } >+ if (eval instanceof ArrayEval){ >+ return ((ArrayEval)eval).arrayAsArea(); >+ } > throw EvaluationException.invalidValue(); > } > >Index: src/java/org/apache/poi/hssf/record/formula/functions/Match.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Match.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Match.java (working copy) >@@ -29,6 +29,7 @@ > import org.apache.poi.hssf.record.formula.functions.LookupUtils.CompareResult; > import org.apache.poi.hssf.record.formula.functions.LookupUtils.LookupValueComparer; > import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Implementation for the MATCH() Excel function.<p/> >@@ -63,7 +64,7 @@ > * > * @author Josh Micich > */ >-public final class Match extends Var2or3ArgFunction { >+public final class Match extends Var2or3ArgFunction implements FunctionWithArraySupport { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { > // default match_type is 1.0 >@@ -93,6 +94,9 @@ > boolean matchExact = match_type == 0; > // Note - Excel does not strictly require -1 and +1 > boolean findLargestLessThanOrEqual = match_type > 0; >+ if (arg1 instanceof ArrayEval){ >+ arg1 = ((ArrayEval)arg1).arrayAsArea(); >+ } > > try { > ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); >@@ -248,4 +252,10 @@ > } > return false; > } >+ >+ public boolean supportArray(int paramIndex) { >+ if (paramIndex == 1) >+ return true; >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Mode.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (working copy) >@@ -30,12 +30,13 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > > * > */ >-public final class Mode implements Function { >+public final class Mode implements FunctionWithArraySupport { > > /** > * if v is zero length or contains no duplicates, return value is >@@ -92,6 +93,9 @@ > } > > private static void collectValues(ValueEval arg, List<Double> temp) throws EvaluationException { >+ if (arg instanceof ArrayEval) { >+ arg = ((ArrayEval) arg).arrayAsArea(); >+ } > if (arg instanceof AreaEval) { > AreaEval ae = (AreaEval) arg; > int width = ae.getWidth(); >@@ -130,4 +134,8 @@ > } > throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (working copy) >@@ -27,14 +27,16 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > >+ * @author zshulkins(ZS) array support; > * This is the super class for all excel function evaluator > * classes that take variable number of operands, and > * where the order of operands does not matter > */ >-public abstract class MultiOperandNumericFunction implements Function { >+public abstract class MultiOperandNumericFunction implements FunctionWithArraySupport { > > private final boolean _isReferenceBoolCounted; > private final boolean _isBlankCounted; >@@ -151,6 +153,18 @@ > collectValue(re.getInnerValueEval(), true, temp); > return; > } >+ if (operand instanceof ArrayEval){ >+ ArrayEval ae = (ArrayEval)operand; >+ if (ae.isIllegalForAggregation()) >+ throw new EvaluationException(ErrorEval.NA); // =sum(atan2({1,2},{1,2,3})) produces error >+ for (ValueEval v: ae.getArrayAsEval()){ >+ if (v instanceof ArrayEval || v instanceof AreaEval) >+ collectValues(v, temp); // array that contains arrays or areas >+ else >+ collectValue(v,false,temp); >+ } >+ return; >+ } > collectValue(operand, false, temp); > } > private void collectValue(ValueEval ve, boolean isViaReference, DoubleList temp) throws EvaluationException { >@@ -194,4 +208,8 @@ > throw new RuntimeException("Invalid ValueEval type passed for conversion: (" > + ve.getClass() + ")"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Offset.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Offset.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Offset.java (working copy) >@@ -41,8 +41,9 @@ > * <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/> > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Offset implements Function { >+public final class Offset implements FunctionWithArraySupport { > // These values are specific to BIFF8 > private static final int LAST_VALID_ROW_INDEX = 0xFFFF; > private static final int LAST_VALID_COLUMN_INDEX = 0xFF; >@@ -262,4 +263,10 @@ > } > throw new RuntimeException("Unexpected eval type (" + ve.getClass().getName() + ")"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ if (paramIndex == 0) >+ return true; >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Row.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Row.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Row.java (working copy) >@@ -22,8 +22,9 @@ > import org.apache.poi.hssf.record.formula.eval.NumberEval; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > >-public final class Row implements Function0Arg, Function1Arg { >+public final class Row implements Function0Arg, Function1Arg, ArrayMode { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) { > return new NumberEval(srcRowIndex+1); >@@ -52,4 +53,17 @@ > return ErrorEval.VALUE_INVALID; > } > >+ public ValueEval evaluateInArrayFormula(ValueEval[] evals, int srcCellRow, int srcCellCol) { >+ if ((evals.length == 1) && (evals[0] instanceof AreaEval)) { >+ AreaEval ae = (AreaEval) evals[0]; >+ >+ ValueEval[][] result = new ValueEval[ae.getHeight()][1]; >+ for (int r = 0; r < ae.getHeight(); r++) { >+ result[r][0] = new NumberEval(ae.getFirstRow() + r + 1); >+ } >+ return new ArrayEval(result); >+ } >+ return evaluate(evals, srcCellRow, srcCellCol); >+ >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Rows.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Rows.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Rows.java (working copy) >@@ -22,17 +22,21 @@ > import org.apache.poi.hssf.record.formula.eval.NumberEval; > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * Implementation for Excel ROWS function. > * > * @author Josh Micich > */ >-public final class Rows extends Fixed1ArgFunction { >+public final class Rows extends Fixed1ArgFunction implements FunctionWithArraySupport { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { > > int result; >+ if (arg0 instanceof ArrayEval) { >+ arg0 = ((ArrayEval) arg0).arrayAsArea(); >+ } > if (arg0 instanceof AreaEval) { > result = ((AreaEval) arg0).getHeight(); > } else if (arg0 instanceof RefEval) { >@@ -42,4 +46,8 @@ > } > return new NumberEval(result); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (working copy) >@@ -37,8 +37,9 @@ > * </table><br/> > * </p> > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Sumif extends Var2or3ArgFunction { >+public final class Sumif extends Var2or3ArgFunction implements FunctionWithArraySupport { > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { > >@@ -124,4 +125,9 @@ > throw new EvaluationException(ErrorEval.VALUE_INVALID); > } > >+ public boolean supportArray(int paramIndex) { >+ if (paramIndex == 1) // TODO - should throw exception if array instead of range >+ return false; >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java (working copy) >@@ -26,6 +26,7 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.ss.formula.ArrayEval; > > > /** >@@ -49,8 +50,9 @@ > * ) > * </p> > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Sumproduct implements Function { >+public final class Sumproduct implements FunctionWithArraySupport { > > > public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { >@@ -60,6 +62,11 @@ > if(maxN < 1) { > return ErrorEval.VALUE_INVALID; > } >+ for (int i=0; i<maxN; i++){ >+ if (args[i] instanceof ArrayEval){ >+ args[i] = ((ArrayEval)args[i]).arrayAsArea(); >+ } >+ } > ValueEval firstArg = args[0]; > try { > if(firstArg instanceof NumericValueEval) { >@@ -115,6 +122,10 @@ > eval = ae.getRelativeValue(0, 0); > } > >+ if (!(eval instanceof ValueEval)) { >+ throw new RuntimeException("Unexpected value eval class (" >+ + eval.getClass().getName() + ")"); >+ } > return getProductTerm(eval, true); > } > >@@ -227,4 +238,8 @@ > throw new RuntimeException("Unexpected value eval class (" > + ve.getClass().getName() + ")"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java (working copy) >@@ -38,8 +38,9 @@ > * the lookup_value. If FALSE, only exact matches will be considered<br/> > * > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - array support > */ >-public final class Vlookup extends Var3or4ArgFunction { >+public final class Vlookup extends Var3or4ArgFunction implements FunctionWithArraySupport { > private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE; > > public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, >@@ -78,4 +79,10 @@ > } > return LookupUtils.createColumnVector(tableArray, colIndex); > } >+ >+ public boolean supportArray(int paramIndex) { >+ if (paramIndex == 1) >+ return true; >+ return false; >+ } > } >Index: src/java/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java (revision 886079) >+++ src/java/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java (working copy) >@@ -24,11 +24,12 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; > import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector; >+import org.apache.poi.ss.formula.ArrayEval; > > /** > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > > */ >-public abstract class XYNumericFunction extends Fixed2ArgFunction { >+public abstract class XYNumericFunction extends Fixed2ArgFunction implements FunctionWithArraySupport { > > private static abstract class ValueArray implements ValueVector { > private final int _size; >@@ -86,6 +87,23 @@ > } > } > >+ private static final class ArrayEvalValueArray extends ValueArray { >+ private final ArrayEval _ae; >+ private final int _width; >+ >+ public ArrayEvalValueArray(ArrayEval ae){ >+ super( ae.getRowCount()*ae.getColCount() ); >+ _ae = ae; >+ _width = ae.getColCount(); >+ } >+ >+ protected ValueEval getItemInternal(int index){ >+ int rowIx = index / _width; >+ int colIx = index % _width; >+ return _ae.getArrayElementAsEval(rowIx, colIx); >+ } >+ } >+ > protected static interface Accumulator { > double accumulate(double x, double y); > } >@@ -172,6 +190,20 @@ > if (arg instanceof RefEval) { > return new RefValueArray((RefEval) arg); > } >- return new SingleCellValueArray(arg); >+ if (arg instanceof ArrayEval){ >+ if ( ((ArrayEval)arg).isIllegalForAggregation()) >+ throw new EvaluationException(ErrorEval.NA); >+ return new ArrayEvalValueArray((ArrayEval) arg); >+ } >+ >+ >+ if (arg instanceof ValueEval) { >+ return new SingleCellValueArray((ValueEval) arg); >+ } >+ throw new RuntimeException("Unexpected eval class (" + arg.getClass().getName() + ")"); > } >+ >+ public boolean supportArray(int paramIndex) { >+ return true; >+ } > } >Index: src/java/org/apache/poi/hssf/usermodel/HSSFCell.java >=================================================================== >--- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (revision 886079) >+++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (working copy) >@@ -43,6 +43,7 @@ > import org.apache.poi.hssf.record.TextObjectRecord; > import org.apache.poi.hssf.record.UnicodeString; > import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; >+import org.apache.poi.hssf.record.formula.ExpPtg; > import org.apache.poi.hssf.record.formula.Ptg; > import org.apache.poi.hssf.record.formula.eval.ErrorEval; > import org.apache.poi.ss.usermodel.Cell; >@@ -50,6 +51,7 @@ > import org.apache.poi.ss.usermodel.Comment; > import org.apache.poi.ss.usermodel.Hyperlink; > import org.apache.poi.ss.usermodel.RichTextString; >+import org.apache.poi.ss.util.CellRangeAddress; > import org.apache.poi.ss.formula.FormulaType; > import org.apache.poi.ss.SpreadsheetVersion; > import org.apache.poi.util.POILogger; >@@ -1144,4 +1146,30 @@ > } > return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType(); > } >+ >+ void setCellArrayFormula(CellRangeAddress range) { >+ int row=_record.getRow(); >+ short col=_record.getColumn(); >+ short styleIndex=_record.getXFIndex(); >+ setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex); >+ >+ // Billet for formula in rec >+ Ptg[] ptgsForCell = { new ExpPtg(range.getFirstRow(), range.getFirstColumn()) }; >+ FormulaRecordAggregate agg = (FormulaRecordAggregate) _record; >+ agg.setParsedExpression(ptgsForCell); >+ } >+ >+ public CellRangeAddress getArrayFormulaRange() { >+ if (_cellType != CELL_TYPE_FORMULA) { >+ throw new IllegalArgumentException("Only formula cells can have array ranges"); >+ } >+ return ((FormulaRecordAggregate)_record).getArrayFormulaRange(); >+ } >+ >+ public boolean isPartOfArrayFormulaGroup() { >+ if (_cellType != CELL_TYPE_FORMULA) { >+ return false; >+ } >+ return ((FormulaRecordAggregate)_record).isPartOfArrayFormula(); >+ } > } >Index: src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java >=================================================================== >--- src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (revision 886079) >+++ src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (working copy) >@@ -19,6 +19,7 @@ > > import org.apache.poi.ss.formula.EvaluationCell; > import org.apache.poi.ss.formula.EvaluationSheet; >+import org.apache.poi.ss.util.CellRangeAddress; > /** > * HSSF wrapper for a cell under evaluation > * >@@ -69,4 +70,13 @@ > public String getStringCellValue() { > return _cell.getRichStringCellValue().getString(); > } >+ >+ public boolean isArrayFormulaContext() { >+ return _cell.isPartOfArrayFormulaGroup(); >+ } >+ >+ public EvaluationCell getFirstCellInArrayFormula() { >+ CellRangeAddress range = _cell.getArrayFormulaRange(); >+ return _evalSheet.getCell(range.getFirstRow(), range.getFirstColumn()); >+ } > } >Index: src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java >=================================================================== >--- src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (revision 886079) >+++ src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (working copy) >@@ -25,13 +25,17 @@ > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; > import org.apache.poi.hssf.record.formula.udf.UDFFinder; >+import org.apache.poi.ss.formula.ArrayEval; > import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment; > import org.apache.poi.ss.formula.IStabilityClassifier; > import org.apache.poi.ss.formula.WorkbookEvaluator; >+import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper; > import org.apache.poi.ss.usermodel.Cell; > import org.apache.poi.ss.usermodel.CellValue; > import org.apache.poi.ss.usermodel.FormulaEvaluator; > import org.apache.poi.ss.usermodel.Row; >+import org.apache.poi.ss.usermodel.Sheet; >+import org.apache.poi.ss.util.CellRangeAddress; > > /** > * Evaluates formula cells.<p/> >@@ -42,6 +46,8 @@ > * > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - Array Formula support >+ * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support > */ > public class HSSFFormulaEvaluator implements FormulaEvaluator { > >@@ -207,9 +213,19 @@ > if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) { > return -1; > } >- CellValue cv = evaluateFormulaCellValue(cell); > // cell remains a formula cell, but the cached value is changed >- setCellValue(cell, cv); >+ CellValue cv; >+ if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context >+ CellValue[][] cvs = evaluateFormulaCellArrayValues((HSSFCell) cell); >+ int rowIndex = cell.getRowIndex() - cell.getArrayFormulaRange().getFirstRow(); >+ int colIndex = cell.getColumnIndex() - cell.getArrayFormulaRange().getFirstColumn(); >+ CellValue[][] values = setCellValues(cell, cvs); >+ cv = values[rowIndex][colIndex]; >+ } else { // Single Formula >+ >+ cv = evaluateFormulaCellValue(cell); >+ setCellValue(cell, cv); >+ } > return cv.getCellType(); > } > >@@ -234,9 +250,15 @@ > } > HSSFCell result = (HSSFCell) cell; > if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { >- CellValue cv = evaluateFormulaCellValue(cell); >- setCellValue(cell, cv); >- setCellType(cell, cv); // cell will no longer be a formula cell >+ if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context >+ CellValue[][] cvs = evaluateFormulaCellArrayValues((HSSFCell) cell); >+ setCellValues(cell, cvs); >+ setCellsTypes(cell, cvs); // cells will no longer be a formula cell >+ } else { // Single Formula >+ CellValue cv = evaluateFormulaCellValue(cell); >+ setCellValue(cell, cv); >+ setCellType(cell, cv); // cell will no longer be a formula cell >+ } > } > return result; > } >@@ -281,6 +303,48 @@ > } > } > >+ private void setCellsTypes(Cell cell, CellValue[][] cvs) { >+ CellRangeAddress range = cell.getArrayFormulaRange(); >+ int rowStart = range.getFirstRow(); >+ int colStart = range.getFirstColumn(); >+ Sheet sheet = cell.getSheet(); >+ for(int i=rowStart;i<=range.getLastRow();i++ ) >+ for(int j=colStart; j<=range.getLastColumn();j++) >+ { >+ Row row = sheet.getRow(i); >+ Cell c = row.getCell(j); >+ if((i-rowStart)<cvs.length && (j-colStart)<cvs[i-rowStart].length) >+ setCellType(c,cvs[i-rowStart][j-colStart]); >+ } >+ } >+ >+ /** >+ * Set value in Range >+ * @param cell >+ * @param cvs >+ * @return >+ */ >+ private CellValue[][] setCellValues(Cell cell, CellValue[][] cvs) { >+ CellRangeAddress range = cell.getArrayFormulaRange(); >+ int rowStart = range.getFirstRow(); >+ int colStart = range.getFirstColumn(); >+ Sheet sheet = cell.getSheet(); >+ CellValue[][] answer = (CellValue[][])ArrayFormulaEvaluatorHelper.transformToRange(cvs, range); >+ for(int i=rowStart;i<=range.getLastRow();i++ ) >+ for(int j=colStart; j<=range.getLastColumn();j++) >+ { >+ Row row = sheet.getRow(i); >+ if(row == null) >+ row = sheet.createRow(i); >+ Cell c = row.getCell(j); >+ if(c == null) >+ c = row.createCell(j); >+ CellValue cellValue = answer[i-rowStart][j-colStart]; >+ setCellValue(c,cellValue); >+ } >+ return answer; >+ } >+ > /** > * Loops over all cells in all sheets of the supplied > * workbook. >@@ -315,6 +379,13 @@ > */ > private CellValue evaluateFormulaCellValue(Cell cell) { > ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell)); >+ if (eval instanceof ArrayEval) {// support of arrays >+ if (cell.isPartOfArrayFormulaGroup()) { >+ eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell); >+ } else { >+ eval = ((ArrayEval) eval).getArrayElementAsEval(0, 0); >+ } >+ } > if (eval instanceof NumberEval) { > NumberEval ne = (NumberEval) eval; > return new CellValue(ne.getNumberValue()); >@@ -332,4 +403,27 @@ > } > throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")"); > } >+ >+ /** >+ * Returns a Array CellValue wrapper around the supplied ArrayEval instance. >+ */ >+ private CellValue[][] evaluateFormulaCellArrayValues(HSSFCell cell) { >+ ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell(cell)); >+ if (eval instanceof ArrayEval) {// support of arrays >+ ArrayEval ae = (ArrayEval) eval; >+ int rowCount = ae.getRowCount(); >+ int ColCount = ae.getColCount(); >+ CellValue[][] answer = new CellValue[rowCount][ColCount]; >+ for (int i = 0; i < rowCount; i++) >+ for (int j = 0; j < ColCount; j++) { >+ ValueEval val = ae.getArrayElementAsEval(i, j); >+ answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val); >+ } >+ return answer; >+ } >+ // non-array (usually from aggregate function) >+ CellValue[][] answer = new CellValue[1][1]; >+ answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval); >+ return answer; >+ } > } >Index: src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >=================================================================== >--- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 886079) >+++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy) >@@ -31,6 +31,7 @@ > import java.util.TreeMap; > > import org.apache.poi.ddf.EscherRecord; >+import org.apache.poi.hssf.model.HSSFFormulaParser; > import org.apache.poi.hssf.model.Sheet; > import org.apache.poi.hssf.model.Workbook; > import org.apache.poi.hssf.record.CellValueRecordInterface; >@@ -44,10 +45,13 @@ > import org.apache.poi.hssf.record.WSBoolRecord; > import org.apache.poi.hssf.record.WindowTwoRecord; > import org.apache.poi.hssf.record.aggregates.DataValidityTable; >+import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; > import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock; > import org.apache.poi.hssf.record.formula.FormulaShifter; >+import org.apache.poi.hssf.record.formula.Ptg; > import org.apache.poi.hssf.util.PaneInformation; > import org.apache.poi.hssf.util.Region; >+import org.apache.poi.ss.formula.FormulaType; > import org.apache.poi.ss.usermodel.Cell; > import org.apache.poi.ss.usermodel.CellStyle; > import org.apache.poi.ss.usermodel.Row; >@@ -64,6 +68,7 @@ > * @author Shawn Laubach (slaubach at apache dot org) (Just a little) > * @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too) > * @author Yegor Kozlov (yegor at apache.org) (Autosizing columns) >+ * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas > */ > public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { > private static final POILogger log = POILogFactory.getLogger(HSSFSheet.class); >@@ -636,12 +641,12 @@ > > /** > * Sets whether the worksheet is displayed from right to left instead of from left to right. >- * >+ * > * @param value true for right to left, false otherwise. > */ > public void setRightToLeft(boolean value) > { >- _sheet.getWindowTwo().setArabic(value); >+ _sheet.getWindowTwo().setArabic(value); > } > > /** >@@ -651,7 +656,7 @@ > */ > public boolean isRightToLeft() > { >- return _sheet.getWindowTwo().getArabic(); >+ return _sheet.getWindowTwo().getArabic(); > } > > /** >@@ -1870,4 +1875,46 @@ > return wb.getSheetName(idx); > } > >+ public void setArrayFormula(String formula, CellRangeAddress range) { >+ // make sure the formula parses OK first >+ Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, _workbook.getSheetIndex(this)); >+ int firstRow = range.getFirstRow(); >+ int firstColumn = range.getFirstColumn(); >+ for (int rowIn = firstRow; rowIn <= range.getLastRow(); rowIn++) { >+ for (int colIn = firstColumn; colIn <= range.getLastColumn(); colIn++) { >+ HSSFRow row = getRow(rowIn); >+ if (row == null) { >+ row = createRow(rowIn); >+ } >+ HSSFCell cell = row.getCell(colIn); >+ if (cell == null) { >+ cell = row.createCell(colIn); >+ } >+ cell.setCellArrayFormula(range); >+ } >+ } >+ HSSFCell firstArrayFormulaCell = getRow(firstRow).getCell(firstColumn); >+ FormulaRecordAggregate agg = (FormulaRecordAggregate) firstArrayFormulaCell.getCellValueRecord(); >+ agg.setArrayFormula(range, ptgs); >+ } >+ >+ >+ public void removeArrayFormula(Cell cell) { >+ CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); >+ if (!(rec instanceof FormulaRecordAggregate)) { >+ throw new IllegalArgumentException("Specified cell is not a formula cell."); >+ } >+ FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; >+ CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); >+ if (range == null) { >+ throw new IllegalArgumentException("Specified cell does not contain an array formula."); >+ } >+ // clear all cells in the range >+ for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) { >+ for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) { >+ Cell rCell = getRow(rowIn).getCell(colIn); >+ rCell.setCellType(Cell.CELL_TYPE_BLANK); >+ } >+ } >+ } > } >Index: src/java/org/apache/poi/ss/formula/ArrayEval.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/ArrayEval.java (revision 0) >+++ src/java/org/apache/poi/ss/formula/ArrayEval.java (revision 0) >@@ -0,0 +1,338 @@ >+package org.apache.poi.ss.formula; >+ >+import java.util.ArrayList; >+import java.util.List; >+ >+import org.apache.poi.hssf.record.UnicodeString; >+import org.apache.poi.hssf.record.constant.ErrorConstant; >+import org.apache.poi.hssf.record.formula.ArrayPtg; >+import org.apache.poi.hssf.record.formula.eval.AreaEval; >+import org.apache.poi.hssf.record.formula.eval.BoolEval; >+import org.apache.poi.hssf.record.formula.eval.NumberEval; >+import org.apache.poi.hssf.record.formula.eval.StringEval; >+import org.apache.poi.hssf.record.formula.eval.ValueEval; >+ >+/** >+ * Class to support evaluated array of values >+ * >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) >+ */ >+public class ArrayEval implements ValueEval { >+ >+ //ArrayPtg thePtg; >+ Object[][] values = null; >+ boolean illegalForAggregation = false; // if result is invalid for aggregation. it could be true, if "uncompatible in size" arrays were used >+ >+ >+ /** >+ * is array unsuitable for future aggregation? >+ * @return >+ */ >+ public boolean isIllegalForAggregation(){ >+ return illegalForAggregation; >+ } >+ >+ /** >+ * set feature "unsuitable for future aggregation >+ * it could be true, if "uncompatible in size" arrays were used >+ * @param value >+ */ >+ public void setIllegalForAggregation(boolean value){ >+ illegalForAggregation = value; >+ } >+ >+ public ArrayEval(ArrayPtg ptg){ >+ if (ptg == null) >+ throw new IllegalArgumentException("ArrayPtg should not be null"); >+ values = ptg.getTokenArrayValues(); >+ } >+ >+ public ArrayEval(Object[][] array){ >+ if (array == null) >+ throw new IllegalArgumentException("null is not allowed"); >+ values = array; >+ } >+ >+ public ArrayEval(Object[][] array, boolean isIllegalForAggregation){ >+ this(array); >+ illegalForAggregation = isIllegalForAggregation; >+ } >+ >+ public String toString(){ >+ StringBuffer b = new StringBuffer(); >+ b.append("{"); >+ for (int r=0;r<values.length;r++) { >+ if (r > 0) { >+ b.append(";"); >+ } >+ for (int c=0;c<values[r].length;r++) { >+ if (c > 0) { >+ b.append(","); >+ } >+ Object o = values[r][c]; >+ b.append(getConstantText(o)); >+ } >+ } >+ b.append("}"); >+ return b.toString(); >+ } >+ >+ >+ /** >+ * get array content >+ * @return >+ */ >+ public Object[][] getArrayValues(){ >+ return values; >+ } >+ >+ /** >+ * get element of array >+ * @param row >+ * @param col >+ * @return >+ */ >+ public Object getArrayElement(int row, int col){ >+ return values[row][col]; >+ } >+ >+ /** >+ * get element of array as Value Eval >+ * @param row >+ * @param col >+ * @return >+ */ >+ public ValueEval getArrayElementAsEval(int row, int col){ >+ return constructEval(getArrayElement(row,col)); >+ } >+ >+ >+ /** >+ * Convert Object to ValueEval >+ * @param o >+ * @return >+ */ >+ public static ValueEval constructEval(Object o){ >+ if (o == null) { >+ throw new RuntimeException("Array item cannot be null"); >+ } >+ if (o instanceof ValueEval) >+ return (ValueEval)o; >+ >+ if (o instanceof String) { >+ return new StringEval( (String)o ); >+ } >+ if (o instanceof Double) { >+ return new NumberEval((Double)o); >+ } >+ if (o instanceof Boolean) { >+ return BoolEval.valueOf((Boolean)o); >+ } >+ // I don't know what should we do if error is an array. I throw an exception for now >+ if (o instanceof ErrorConstant) { >+ throw new IllegalArgumentException("Error in array" + ((ErrorConstant)o).getText()); >+ } >+ // if string constants in ArrayPtg are encodes as UnicodeString >+ if (o instanceof UnicodeString){ >+ return new StringEval( ((UnicodeString)o).getString()); >+ } >+ >+ throw new IllegalArgumentException("Unexpected constant class (" + o.getClass()); >+ } >+ >+ >+ /** >+ * get String contains object's value >+ * @param o >+ * @return >+ */ >+ public static String getConstantText(Object o) { >+ >+ if (o == null) { >+ throw new RuntimeException("Array item cannot be null"); >+ } >+ if (o instanceof String) { >+ return "\"" + (String)o + "\""; >+ } >+ if (o instanceof Double) { >+ return ((Double)o).toString(); >+ } >+ if (o instanceof Boolean) { >+ return ((Boolean)o).booleanValue() ? "TRUE" : "FALSE"; >+ } >+ if (o instanceof ErrorConstant) { >+ return ((ErrorConstant)o).getText(); >+ } >+ throw new IllegalArgumentException("Unexpected constant class (" + o.getClass().getName() + ")"); >+ } >+ >+ /** >+ * return Array as ValueEval list >+ * @return >+ */ >+ public List<ValueEval> getArrayAsEval(){ >+ >+ List<ValueEval> l = new ArrayList<ValueEval>(); >+ for(int r=0; r< values.length; r++){ >+ for (int c=0; c<values[r].length; c++){ >+ l.add(ArrayEval.constructEval(values[r][c])); >+ } >+ } >+ return l; >+ } >+ >+ /** >+ * get row count >+ * @return >+ */ >+ public int getRowCount(){ >+ return values.length; >+ } >+ >+ /** >+ * get column count >+ * @return >+ */ >+ public int getColCount(){ >+ if (getRowCount() == 0) >+ return 0; >+ return values[0].length; >+ } >+ >+ /* >+ * offset from the array >+ */ >+ /** >+ * get subarray >+ * @param rowFrom >+ * @param rowTo >+ * @param colFrom >+ * @param colTo >+ * @return >+ */ >+ public ArrayEval offset(int rowFrom, int rowTo, int colFrom, int colTo){ >+ >+ if (rowFrom<=0 || rowFrom >= getRowCount() || rowTo<rowFrom || >+ colFrom<=0 || colFrom >= getColCount() || colTo<colFrom >+ ) >+ throw new IllegalArgumentException("rowFrom: " + rowFrom + " rowTo: " + rowTo + " colFrom: " + colFrom + " colTo: " + colTo ); >+ >+ int row = Math.min(getRowCount(), rowTo); >+ int col = Math.min(getColCount(), colTo); >+ >+ Object[][] result = new Object[row - rowFrom+1][col - colFrom+1]; >+ for (int r=rowFrom; r<=row; r++){ >+ for (int c=colFrom; c<=col; c++) { >+ result[r][c] = values[r][c]; >+ } >+ } >+ >+ return new ArrayEval(result); >+ } >+ >+ public enum BooleanContent{ONLY_FALSE,ONLY_TRUE,MIXED}; >+ >+ /** >+ * Check if content of boolean array ONLY_FALSE, ONLY_TRUE or MIXED >+ * if content is not boolean then return MIXED >+ * @return >+ */ >+ public BooleanContent checkBooleanContent(){ >+ try{ >+ BoolEval first = (BoolEval)values[0][0]; >+ for(int i=0;i<values.length;i++) >+ for(int j=0;j<values[i].length;j++) >+ if(first.equals((BoolEval)values[i][j])) >+ return BooleanContent.MIXED; >+ >+ if(first.getBooleanValue()) >+ return BooleanContent.ONLY_TRUE; >+ else >+ return BooleanContent.ONLY_FALSE; >+ } >+ catch (Exception e){ >+ return BooleanContent.MIXED; >+ } >+ } >+ >+ >+ /** >+ * expose Array as area on sheet (top-left) >+ * convenience methods to reuse existing code >+ * @return >+ */ >+ public AreaEval arrayAsArea(){ >+ >+ return new AreaEval(){ >+ >+ public int getFirstRow(){ >+ return 0; >+ } >+ >+ public int getLastRow(){ >+ return getRowCount()-1; >+ } >+ >+ public int getFirstColumn(){ >+ return 0; >+ } >+ >+ public int getLastColumn(){ >+ return getColCount()-1; >+ } >+ >+ public boolean isRow(){ >+ return (getRowCount()==1); >+ } >+ >+ public boolean isColumn(){ >+ return (getColCount() == 1); >+ } >+ >+ public ValueEval getValueAt(int row, int col){ >+ return getArrayElementAsEval(row, col); >+ } >+ >+ public boolean contains(int row, int col){ >+ if ( (row < getRowCount()) && (col < getColCount()) ){ >+ return true; >+ } >+ return false; >+ } >+ >+ public boolean containsColumn(short col){ >+ if (col < getColCount()) >+ return true; >+ return false; >+ } >+ >+ public boolean containsRow(int row){ >+ if (row < getRowCount() ) >+ return true; >+ return false; >+ } >+ >+ public int getWidth(){ >+ return getColCount(); >+ >+ } >+ >+ public int getHeight(){ >+ return getRowCount(); >+ } >+ >+ public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex){ >+ return getArrayElementAsEval(relativeRowIndex, relativeColumnIndex); >+ } >+ >+ public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx){ >+ ArrayEval offset = ArrayEval.this.offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); >+ return offset.arrayAsArea(); >+ } >+ >+ >+ }; >+ } >+ >+ >+} >Index: src/java/org/apache/poi/ss/formula/EvaluationCell.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/EvaluationCell.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/EvaluationCell.java (working copy) >@@ -26,6 +26,7 @@ > * For POI internal use only > * > * @author Josh Micich >+ * @author Petr Udalau(Petr.Udalau at exigenservices.com) - array formula support > */ > public interface EvaluationCell { > /** >@@ -42,4 +43,14 @@ > String getStringCellValue(); > boolean getBooleanCellValue(); > int getErrorCellValue(); >+ >+ /** >+ * @return <code>true<code> if cell belongs to to range of Array Formula >+ */ >+ boolean isArrayFormulaContext(); >+ >+ /** >+ * @return Main cell in array formula that contains formula. >+ */ >+ EvaluationCell getFirstCellInArrayFormula(); > } >Index: src/java/org/apache/poi/ss/formula/OperandClassTransformer.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (working copy) >@@ -72,6 +72,9 @@ > case FormulaType.CELL: > rootNodeOperandClass = Ptg.CLASS_VALUE; > break; >+ case FormulaType.ARRAY: >+ rootNodeOperandClass = Ptg.CLASS_ARRAY; >+ break; > case FormulaType.NAMEDRANGE: > case FormulaType.DATAVALIDATION_LIST: > rootNodeOperandClass = Ptg.CLASS_REF; >Index: src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (working copy) >@@ -44,15 +44,17 @@ > private final int _columnIndex; > private final EvaluationTracker _tracker; > private final WorkbookEvaluator _bookEvaluator; >+ private final boolean _inArrayFormulaContext; > > public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, >- int srcColNum, EvaluationTracker tracker) { >+ int srcColNum, EvaluationTracker tracker, boolean inArrayFormulaContext) { > _bookEvaluator = bookEvaluator; > _workbook = workbook; > _sheetIndex = sheetIndex; > _rowIndex = srcRowNum; > _columnIndex = srcColNum; > _tracker = tracker; >+ _inArrayFormulaContext = inArrayFormulaContext; > } > > public EvaluationWorkbook getWorkbook() { >@@ -67,6 +69,10 @@ > return _columnIndex; > } > >+ public boolean isInArrayFormulaContext() { >+ return _inArrayFormulaContext; >+ } >+ > SheetRefEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) { > return createExternSheetRefEvaluator(ptg.getExternSheetIndex()); > } >Index: src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (working copy) >@@ -52,14 +52,17 @@ > import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; > import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; >+import org.apache.poi.hssf.record.formula.functions.ArrayMode; > import org.apache.poi.hssf.record.formula.functions.Function; > import org.apache.poi.hssf.record.formula.functions.Indirect; >+import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper; > > /** > * This class creates <tt>OperationEval</tt> instances to help evaluate <tt>OperationPtg</tt> > * formula tokens. > * > * @author Josh Micich >+ * @author Petr Udalau(Petr.Udalau at exigenservices.com) - evaluations of array formulas > */ > final class OperationEvaluatorFactory { > >@@ -113,13 +116,9 @@ > if(ptg == null) { > throw new IllegalArgumentException("ptg must not be null"); > } >- Function result = _instancesByPtgClass.get(ptg); >+ Function func = _instancesByPtgClass.get(ptg); > >- if (result != null) { >- return result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex()); >- } >- >- if (ptg instanceof AbstractFunctionPtg) { >+ if (func == null && ptg instanceof AbstractFunctionPtg) { > AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg; > int functionIndex = fptg.getFunctionIndex(); > switch (functionIndex) { >@@ -128,9 +127,38 @@ > case FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL: > return UserDefinedFunction.instance.evaluate(args, ec); > } >- >- return FunctionEval.getBasicFunction(functionIndex).evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex()); >+ func = FunctionEval.getBasicFunction(functionIndex); > } >- throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")"); >+ if (func != null) { >+ if (func instanceof ArrayMode && ec.isInArrayFormulaContext()) { >+ return evaluateInSpecialModeForArrayFormulas((ArrayMode) func, args, ec); >+ } else { >+ return invokeOperationInArrayContext(func, args, ec); >+ } >+ } else { >+ throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")"); >+ } > } >+ >+ private static ValueEval evaluateInSpecialModeForArrayFormulas(ArrayMode function, ValueEval[] ops, >+ OperationEvaluationContext ec) { >+ return function.evaluateInArrayFormula(ops, ec.getRowIndex(), ec.getColumnIndex()); >+ } >+ >+ private static ValueEval invokeOperationInArrayContext(Function func, ValueEval[] ops, OperationEvaluationContext ec) { >+ boolean isArrayFormula = ec.isInArrayFormulaContext(); >+ ValueEval answer = ArrayFormulaEvaluatorHelper.prepareEmptyResult(func, ops, isArrayFormula); >+ if (answer instanceof ArrayEval) { >+ ValueEval[][] values = (ValueEval[][]) ((ArrayEval) answer).getArrayValues(); >+ for (int row = 0; row < values.length; row++) >+ for (int col = 0; col < values[row].length; col++) { >+ ValueEval[] opsloop = ArrayFormulaEvaluatorHelper.prepareArgsForLoop(func, ops, row, col, isArrayFormula); >+ ValueEval loopresult = func.evaluate(opsloop, ec.getRowIndex(), ec.getColumnIndex()); >+ values[row][col] = loopresult; >+ } >+ return answer; >+ } else { >+ return func.evaluate(ops, ec.getRowIndex(), ec.getColumnIndex()); >+ } >+ } > } >Index: src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (working copy) >@@ -17,13 +17,16 @@ > > package org.apache.poi.ss.formula; > >+import java.util.ArrayList; > import java.util.IdentityHashMap; > import java.util.Map; > import java.util.Stack; > >+import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; > import org.apache.poi.hssf.record.formula.Area3DPtg; > import org.apache.poi.hssf.record.formula.AreaErrPtg; > import org.apache.poi.hssf.record.formula.AreaPtg; >+import org.apache.poi.hssf.record.formula.ArrayPtg; > import org.apache.poi.hssf.record.formula.AttrPtg; > import org.apache.poi.hssf.record.formula.BoolPtg; > import org.apache.poi.hssf.record.formula.ControlPtg; >@@ -60,6 +63,7 @@ > import org.apache.poi.hssf.record.formula.eval.RefEval; > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; > import org.apache.poi.hssf.record.formula.functions.Choose; > import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; > import org.apache.poi.hssf.record.formula.functions.If; >@@ -67,6 +71,7 @@ > import org.apache.poi.hssf.util.CellReference; > import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; > import org.apache.poi.ss.formula.eval.NotImplementedException; >+import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper; > import org.apache.poi.ss.usermodel.Cell; > > /** >@@ -203,7 +208,7 @@ > > public ValueEval evaluate(EvaluationCell srcCell) { > int sheetIndex = getSheetIndex(srcCell.getSheet()); >- return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache)); >+ return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache)); > } > > /** >@@ -241,6 +246,9 @@ > return result; > } > >+ if (srcCell.isArrayFormulaContext()) { >+ srcCell = srcCell.getFirstCellInArrayFormula(); >+ } > FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell); > if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) { > tracker.acceptFormulaDependency(cce); >@@ -251,7 +259,8 @@ > if (!tracker.startEvaluate(cce)) { > return ErrorEval.CIRCULAR_REF_ERROR; > } >- OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker); >+ OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, >+ columnIndex, tracker, srcCell.isArrayFormulaContext()); > > try { > >@@ -333,6 +342,8 @@ > // visibility raised for testing > /* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) { > >+ Stack<Boolean> stackSkip = new Stack<Boolean>(); >+ Boolean isSkipActive = new Boolean(true); > Stack<ValueEval> stack = new Stack<ValueEval>(); > for (int i = 0, iSize = ptgs.length; i < iSize; i++) { > >@@ -347,62 +358,90 @@ > } > if (attrPtg.isOptimizedChoose()) { > ValueEval arg0 = stack.pop(); >- int[] jumpTable = attrPtg.getJumpTable(); >- int dist; >- int nChoices = jumpTable.length; >- try { >- int switchIndex = Choose.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); >- if (switchIndex<1 || switchIndex > nChoices) { >- stack.push(ErrorEval.VALUE_INVALID); >- dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) >- } else { >- dist = jumpTable[switchIndex-1]; >- } >- } catch (EvaluationException e) { >- stack.push(e.getErrorEval()); >- dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) >+ if((arg0 instanceof ArrayEval) && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) >+ { >+ stack.push(arg0); >+ stackSkip.push(isSkipActive); >+ // Switch off skip option only for this level >+ isSkipActive = new Boolean(false); >+ continue; > } >- // Encoded dist for tAttrChoose includes size of jump table, but >- // countTokensToBeSkipped() does not (it counts whole tokens). >- dist -= nChoices*2+2; // subtract jump table size >- i+= countTokensToBeSkipped(ptgs, i, dist); >- continue; >+ else{ >+ ValueEval cond = arg0; >+ if((arg0 instanceof ArrayEval)) >+ cond = ((ArrayEval)arg0).getArrayElementAsEval(0,0); >+ int[] jumpTable = attrPtg.getJumpTable(); >+ int dist; >+ int nChoices = jumpTable.length; >+ try { >+ int switchIndex = Choose.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex()); >+ if (switchIndex<1 || switchIndex > nChoices) { >+ stack.push(ErrorEval.VALUE_INVALID); >+ dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) >+ } else { >+ dist = jumpTable[switchIndex-1]; >+ } >+ } catch (EvaluationException e) { >+ stack.push(e.getErrorEval()); >+ dist = attrPtg.getChooseFuncOffset() + 4; // +4 for tFuncFar(CHOOSE) >+ } >+ // Encoded dist for tAttrChoose includes size of jump table, but >+ // countTokensToBeSkipped() does not (it counts whole tokens). >+ dist -= nChoices*2+2; // subtract jump table size >+ i+= countTokensToBeSkipped(ptgs, i, dist); >+ continue; >+ } > } > if (attrPtg.isOptimizedIf()) { > ValueEval arg0 = stack.pop(); > boolean evaluatedPredicate; >- try { >- evaluatedPredicate = If.evaluateFirstArg(arg0, ec.getRowIndex(), ec.getColumnIndex()); >- } catch (EvaluationException e) { >- stack.push(e.getErrorEval()); >- int dist = attrPtg.getData(); >- i+= countTokensToBeSkipped(ptgs, i, dist); >- attrPtg = (AttrPtg) ptgs[i]; >- dist = attrPtg.getData()+1; >- i+= countTokensToBeSkipped(ptgs, i, dist); >+ if((arg0 instanceof ArrayEval) && ((ArrayEval)arg0).checkBooleanContent()== ArrayEval.BooleanContent.MIXED) >+ { >+ stack.push(arg0); >+ stackSkip.push(isSkipActive); >+ // Switch off skip option only for this level >+ isSkipActive = new Boolean(false); > continue; > } >- if (evaluatedPredicate) { >- // nothing to skip - true param folows >- } else { >- int dist = attrPtg.getData(); >- i+= countTokensToBeSkipped(ptgs, i, dist); >- Ptg nextPtg = ptgs[i+1]; >- if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) { >- // this is an if statement without a false param (as opposed to MissingArgPtg as the false param) >- i++; >- stack.push(BoolEval.FALSE); >- } >+ else{ >+ ValueEval cond = arg0; >+ if((arg0 instanceof ArrayEval)) >+ cond = ((ArrayEval)arg0).getArrayElementAsEval(0,0); >+ try { >+ evaluatedPredicate = If.evaluateFirstArg(cond, ec.getRowIndex(), ec.getColumnIndex()); >+ } catch (EvaluationException e) { >+ stack.push(e.getErrorEval()); >+ int dist = attrPtg.getData(); >+ i+= countTokensToBeSkipped(ptgs, i, dist); >+ attrPtg = (AttrPtg) ptgs[i]; >+ dist = attrPtg.getData()+1; >+ i+= countTokensToBeSkipped(ptgs, i, dist); >+ continue; >+ } >+ if (evaluatedPredicate) { >+ // nothing to skip - true param folows >+ } else { >+ int dist = attrPtg.getData(); >+ i+= countTokensToBeSkipped(ptgs, i, dist); >+ Ptg nextPtg = ptgs[i+1]; >+ if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) { >+ // this is an if statement without a false param (as opposed to MissingArgPtg as the false param) >+ i++; >+ stack.push(BoolEval.FALSE); >+ } >+ } >+ continue; > } >- continue; > } > if (attrPtg.isSkip()) { >- int dist = attrPtg.getData()+1; >- i+= countTokensToBeSkipped(ptgs, i, dist); >- if (stack.peek() == MissingArgEval.instance) { >- stack.pop(); >- stack.push(BlankEval.instance); >- } >+ if(isSkipActive){ >+ int dist = attrPtg.getData()+1; >+ i+= countTokensToBeSkipped(ptgs, i, dist); >+ if (stack.peek() == MissingArgEval.instance) { >+ stack.pop(); >+ stack.push(BlankEval.instance); >+ } >+ } > continue; > } > } >@@ -435,6 +474,8 @@ > } > // logDebug("invoke " + operation + " (nAgs=" + numops + ")"); > opResult = OperationEvaluatorFactory.evaluate(optg, ops, ec); >+ if(isSkipActive == false && isSkipSensitive(optg)) >+ isSkipActive = stackSkip.pop(); > } else { > opResult = getEvalForPtg(ptg, ec); > } >@@ -460,6 +501,29 @@ > } > > /** >+ * Has this function "optimized" form? >+ * @param optg >+ * @return >+ */ >+ static ArrayList<Integer> skipSensitiveFunction = null; >+ private boolean isSkipSensitive(OperationPtg optg) { >+ if(optg instanceof FuncVarPtg){ >+ // Skip sensitive is only "optimized" function - just only "IF" and "choose" >+ if(skipSensitiveFunction==null){ >+ skipSensitiveFunction = new ArrayList<Integer>(); >+ int index = FunctionMetadataRegistry.lookupIndexByName("IF"); >+ skipSensitiveFunction.add(new Integer(index)); >+ index = FunctionMetadataRegistry.lookupIndexByName("CHOOSE"); >+ skipSensitiveFunction.add(new Integer(index)); >+ } >+ AbstractFunctionPtg fptg = (AbstractFunctionPtg)optg; >+ int functionIndex = fptg.getFunctionIndex(); >+ return skipSensitiveFunction.contains(new Integer(functionIndex)); >+ } >+ return false; >+ } >+ >+ /** > * Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip. > * > * @return the number of tokens (starting from <tt>startIndex+1</tt>) that need to be skipped >@@ -481,6 +545,16 @@ > return index-startIndex; > } > /** >+ * Dereferences a single value from any ArrayEval evaluation result. >+ * @param evaluationResult >+ * @param cell >+ * @return >+ */ >+ private static ValueEval dereferenceValue(ArrayEval evaluationResult, EvaluationCell evalCell ) { >+ Cell cell = (Cell)evalCell.getIdentityKey(); >+ return ArrayFormulaEvaluatorHelper.dereferenceValue(evaluationResult,cell); >+ } >+ /** > * Dereferences a single value from any AreaEval or RefEval evaluation result. > * If the supplied evaluationResult is just a plain value, it is returned as-is. > * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>, >@@ -572,7 +646,9 @@ > AreaPtg aptg = (AreaPtg) ptg; > return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn()); > } >- >+ if (ptg instanceof ArrayPtg){ >+ return new ArrayEval((ArrayPtg)ptg); >+ } > if (ptg instanceof UnknownPtg) { > // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted. > // This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790) >@@ -599,9 +675,12 @@ > */ > /* package */ ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex, > int columnIndex, EvaluationTracker tracker) { >- >- EvaluationCell cell = sheet.getCell(rowIndex, columnIndex); >- return evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker); >+ EvaluationCell cell = sheet.getCell(rowIndex, columnIndex); >+ ValueEval result = evaluateAny(cell, sheetIndex, rowIndex, columnIndex, tracker); >+ if (result instanceof ArrayEval && cell.isArrayFormulaContext()) { >+ result = dereferenceValue((ArrayEval) result, cell); >+ } >+ return result; > } > public FreeRefFunction findUserDefinedFunction(String functionName) { > return _udfFinder.findFunction(functionName); >Index: src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (revision 886079) >+++ src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (working copy) >@@ -129,4 +129,12 @@ > public int getColumnIndex() { > return _masterCell.getColumnIndex(); > } >+ >+ public boolean isArrayFormulaContext() { >+ return _masterCell.isArrayFormulaContext(); >+ } >+ >+ public EvaluationCell getFirstCellInArrayFormula() { >+ return _masterCell.getFirstCellInArrayFormula(); >+ } > } >Index: src/java/org/apache/poi/ss/usermodel/ArrayFormulaEvaluatorHelper.java >=================================================================== >--- src/java/org/apache/poi/ss/usermodel/ArrayFormulaEvaluatorHelper.java (revision 0) >+++ src/java/org/apache/poi/ss/usermodel/ArrayFormulaEvaluatorHelper.java (revision 0) >@@ -0,0 +1,254 @@ >+/** >+ * >+ */ >+package org.apache.poi.ss.usermodel; >+ >+import org.apache.poi.hssf.record.formula.eval.AreaEval; >+import org.apache.poi.hssf.record.formula.eval.BoolEval; >+import org.apache.poi.hssf.record.formula.eval.ErrorEval; >+import org.apache.poi.hssf.record.formula.eval.NumberEval; >+import org.apache.poi.hssf.record.formula.eval.StringEval; >+import org.apache.poi.hssf.record.formula.eval.ValueEval; >+import org.apache.poi.hssf.record.formula.functions.Function; >+import org.apache.poi.hssf.record.formula.functions.FunctionWithArraySupport; >+import org.apache.poi.ss.formula.ArrayEval; >+import org.apache.poi.ss.util.CellRangeAddress; >+ >+/** >+ * Helper class to manipulate with array formula >+ * This class contains methods, common for HSSF and XSSF FormulaEvaluator >+ * Better solution - to have abstract class for FormulaEvaluator Interface Implementation >+ * All method need to be static >+ * >+ * @author Vladimirs Abramovs (Vladimirs.Abramovs at exigenservices.com) >+ */ >+public class ArrayFormulaEvaluatorHelper { >+ final static int SCALAR_TYPE = 0; >+ final static int ARRAY_TYPE = 1; >+ >+ >+ private ArrayFormulaEvaluatorHelper (){ >+ // has no instance >+ } >+ >+ >+ /** Transform evaluated Array Values according aimed Range >+ * Depending on dimensions correlation, >+ * result array may be restricted >+ * or his single row/column will cloned >+ * or some cell will set to #N/A >+ * @param cvs >+ * @param range >+ * @return >+ */ >+ public static Object[][] transformToRange(Object[][] cvs,CellRangeAddress range){ >+ >+ Object[][] answer = null; >+ if(cvs[0][0] instanceof CellValue) >+ answer = new CellValue[range.getLastRow()-range.getFirstRow()+1][range.getLastColumn()-range.getFirstColumn()+1]; >+ else if (cvs[0][0] instanceof ValueEval) >+ answer = new ValueEval[range.getLastRow()-range.getFirstRow()+1][range.getLastColumn()-range.getFirstColumn()+1]; >+ else >+ throw new RuntimeException("transform2Range does not support type "+cvs[0][0].getClass().getName()); >+ int rowStart = range.getFirstRow(); >+ int colStart = range.getFirstColumn(); >+ for(int i=rowStart;i<=range.getLastRow();i++ ) >+ for(int j=colStart; j<=range.getLastColumn();j++) >+ { >+ if((i-rowStart)<cvs.length && (j-colStart)<cvs[i-rowStart].length){ >+ answer[i-rowStart][j-colStart] = cvs[i-rowStart][j-colStart]; >+ } >+ else >+ { >+ boolean needClone = false; >+ int cloneRow = 0; >+ int cloneCol = 0; >+ if(cvs.length == 1) >+ { // Need to clone first colm of cvs >+ cloneCol = j-colStart; >+ needClone = true; >+ >+ } >+ if(cvs[0].length == 1 ) >+ { // Need to clone first row of cvs >+ cloneRow = i-rowStart; >+ needClone = true; >+ >+ } >+ if(needClone && cloneCol <cvs[0].length && cloneRow <cvs.length) >+ { >+ >+ answer[i-rowStart][j-colStart] = cvs[cloneRow][cloneCol]; >+ } >+ else >+ { >+ // For other cases set cell value to #N/A >+ // For those cells we changes also their type to Error >+ if(cvs[0][0] instanceof CellValue){ >+ CellValue cvError = CellValue.getError(org.apache.poi.ss.usermodel.ErrorConstants.ERROR_NA); >+ answer[i-rowStart][j-colStart] = cvError; >+ } >+ else >+ if (cvs[0][0] instanceof ValueEval) >+ answer[i-rowStart][j-colStart] = ErrorEval.NA; >+ } >+ } >+ } >+ return answer; >+ } >+ /** >+ * Convert Eval value to CellValue >+ * @param val >+ * @return >+ */ >+ public static CellValue evalToCellValue(ValueEval val){ >+ if(val instanceof BoolEval) >+ return CellValue.valueOf(((BoolEval)val).getBooleanValue()); >+ if(val instanceof NumberEval) >+ return new CellValue(((NumberEval)val).getNumberValue()); >+ if(val instanceof StringEval) >+ return new CellValue(((StringEval)val).getStringValue()); >+ if(val instanceof ErrorEval) >+ return new CellValue(((ErrorEval)val).getErrorCode()); >+ return new CellValue(ErrorEval.VALUE_INVALID.getErrorCode()); >+ } >+ /** >+ * Get single value from ArrayEval for desired cell >+ * @param evaluationResult >+ * @param cell >+ * @return >+ */ >+ public static ValueEval dereferenceValue(ArrayEval evaluationResult, Cell cell) { >+ CellRangeAddress range = cell.getArrayFormulaRange(); >+ Object[][] rangeVal = ArrayFormulaEvaluatorHelper.transformToRange(evaluationResult.getArrayValues(),range); >+ int rowInArray = cell.getRowIndex()- range.getFirstRow(); >+ int colInArray = cell.getColumnIndex() - range.getFirstColumn(); >+ return (ValueEval)rangeVal[rowInArray][colInArray]; >+ } >+ /** >+ * Get type of parameter (SCALAR_TYPE or ARRAY_TYPE) which support function >+ * for given argument >+ * >+ * @param function >+ * @param argIndex >+ * @return >+ */ >+ public static int getParameterType(Function function, int argIndex) { >+ int answer = SCALAR_TYPE; >+ >+ if (function instanceof FunctionWithArraySupport) { >+ // ask new interface(ZS) for argument type >+ if (((FunctionWithArraySupport) function).supportArray(argIndex)) >+ answer = ARRAY_TYPE; >+ } >+ return answer; >+ } >+ >+ /** >+ * Prepare empty template, which will keep result of evaluation >+ * A few arguments of function may be array. >+ * In this case result array will have >+ * dimension as such arguments intersection. >+ * This method calculates result's dimension and prepare empty results >+ * >+ * >+ * @param function >+ * @param ops >+ * @param arrayFormula >+ * @return >+ */ >+ public static ValueEval prepareEmptyResult(Function function, ValueEval[] ops, boolean arrayFormula) { >+ int rowCount = Integer.MAX_VALUE; >+ int colCount = Integer.MAX_VALUE; >+ >+ boolean illegalForAggregation = false; >+ >+ for (int i = 0; i < ops.length; i++) { >+ int argRowCount = Integer.MAX_VALUE; >+ int argColCount = Integer.MAX_VALUE; >+ if (getParameterType(function, i) == SCALAR_TYPE) { >+ if (ops[i] instanceof ArrayEval) { >+ argRowCount = ((ArrayEval) ops[i]).getRowCount(); >+ argColCount = ((ArrayEval) ops[i]).getColCount(); >+ illegalForAggregation = illegalForAggregation || ((ArrayEval) ops[i]).isIllegalForAggregation(); >+ } else if (ops[i] instanceof AreaEval && arrayFormula) { >+ argRowCount = ((AreaEval) ops[i]).getHeight(); >+ argColCount = ((AreaEval) ops[i]).getWidth(); >+ } else >+ continue; // Arguments is not array - just skip it >+ if (argRowCount != rowCount) { >+ if (rowCount != Integer.MAX_VALUE) { >+ illegalForAggregation = true; >+ } >+ rowCount = Math.min(rowCount, argRowCount); >+ } >+ if (argColCount != colCount) { >+ if (colCount != Integer.MAX_VALUE) { >+ illegalForAggregation = true; >+ } >+ colCount = Math.min(colCount, argColCount); >+ } >+ } >+ } >+ >+ if (colCount == Integer.MAX_VALUE || rowCount == Integer.MAX_VALUE) >+ return null; >+ >+ ValueEval[][] emptyArray = new ValueEval[rowCount][colCount]; >+ ValueEval answer = new ArrayEval(emptyArray); >+ >+ ((ArrayEval) answer).setIllegalForAggregation(illegalForAggregation); >+ return answer; >+ } >+ >+ /** >+ * Prepare arguments for next iteration to call function >+ * Each argument may be scalar, full array or element(according iteration) of array >+ * >+ * @param function >+ * @param ops >+ * @param i >+ * @param j >+ * @param trackAreas >+ * @return >+ */ >+ public static ValueEval[] prepareArgsForLoop(Function function, ValueEval[] ops, int i, int j, boolean trackAreas) { >+ ValueEval[] answer = new ValueEval[ops.length]; >+ for (int argIn = 0; argIn < ops.length; argIn++) { >+ if (getParameterType(function, argIn) == SCALAR_TYPE) { >+ if (ops[argIn] instanceof ArrayEval) { >+ answer[argIn] = ((ArrayEval) ops[argIn]).getArrayElementAsEval(i, j); >+ } else if (ops[argIn] instanceof AreaEval && trackAreas) { >+ answer[argIn] = ((AreaEval) ops[argIn]).getRelativeValue(i, j); >+ >+ } else { >+ answer[argIn] = ops[argIn]; >+ } >+ } else { // Array type >+ answer[argIn] = ops[argIn]; >+ >+ } >+ >+ } >+ >+ return answer; >+ } >+ >+ /** >+ * check if ops contain arrays and those should be iterated >+ * >+ * @param function >+ * @param ops >+ * @return >+ */ >+ public static boolean checkForArrays(Function function, ValueEval[] ops) { >+ >+ for (int i = 0; i < ops.length; i++) { >+ if ((ops[i] instanceof ArrayEval) && (getParameterType(function, i) == SCALAR_TYPE)) >+ return true; >+ } >+ return false; >+ >+ } >+ >+} >Index: src/java/org/apache/poi/ss/usermodel/Cell.java >=================================================================== >--- src/java/org/apache/poi/ss/usermodel/Cell.java (revision 886079) >+++ src/java/org/apache/poi/ss/usermodel/Cell.java (working copy) >@@ -21,6 +21,7 @@ > import java.util.Date; > > import org.apache.poi.ss.formula.FormulaParseException; >+import org.apache.poi.ss.util.CellRangeAddress; > > /** > * High level representation of a cell in a row of a spreadsheet. >@@ -372,4 +373,15 @@ > * @param link hypelrink associated with this cell > */ > void setHyperlink(Hyperlink link); >+ >+ /** >+ * Only valid for array formula cells >+ * @return range of the array formula group that the cell belongs to. >+ */ >+ CellRangeAddress getArrayFormulaRange(); >+ >+ /** >+ * @return <code>true</code> if this cell is part of group of cells having a common array formula. >+ */ >+ public boolean isPartOfArrayFormulaGroup(); > } >Index: src/java/org/apache/poi/ss/usermodel/Sheet.java >=================================================================== >--- src/java/org/apache/poi/ss/usermodel/Sheet.java (revision 886079) >+++ src/java/org/apache/poi/ss/usermodel/Sheet.java (working copy) >@@ -30,6 +30,8 @@ > * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can > * contain text, numbers, dates, and formulas. Cells can also be formatted. > * </p> >+ * >+ * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas > */ > public interface Sheet extends Iterable<Row> { > >@@ -781,4 +783,19 @@ > */ > boolean isSelected(); > >+ >+ /** >+ * Sets array formula to specified region for result. >+ * >+ * @param formula Formula >+ * @param range Region of array formula for result. >+ */ >+ void setArrayFormula(String formula, CellRangeAddress range); >+ >+ /** >+ * Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well >+ * >+ * @param cell any cell within Array Formula range >+ */ >+ void removeArrayFormula(Cell cell); > } >Index: src/java/org/apache/poi/ss/util/CellRangeAddress.java >=================================================================== >--- src/java/org/apache/poi/ss/util/CellRangeAddress.java (revision 886079) >+++ src/java/org/apache/poi/ss/util/CellRangeAddress.java (working copy) >@@ -84,6 +84,9 @@ > > public static CellRangeAddress valueOf(String ref) { > int sep = ref.indexOf(":"); >+ if (sep == -1) { >+ return valueOf(ref + ":" + ref); >+ } > CellReference cellFrom = new CellReference(ref.substring(0, sep)); > CellReference cellTo = new CellReference(ref.substring(sep + 1)); > return new CellRangeAddress(cellFrom.getRow(), cellTo.getRow(), cellFrom.getCol(), cellTo.getCol()); >Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >=================================================================== >--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (revision 886079) >+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (working copy) >@@ -37,6 +37,7 @@ > import org.apache.poi.ss.usermodel.FormulaError; > import org.apache.poi.ss.usermodel.Hyperlink; > import org.apache.poi.ss.usermodel.RichTextString; >+import org.apache.poi.ss.util.CellRangeAddress; > import org.apache.poi.ss.util.CellReference; > import org.apache.poi.xssf.model.SharedStringsTable; > import org.apache.poi.xssf.model.StylesTable; >@@ -336,6 +337,10 @@ > if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false); > > CTCellFormula f = _cell.getF(); >+ if (isPartOfArrayFormulaGroup() && f == null) { >+ XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); >+ return cell.getCellFormula(); >+ } > if(f.getT() == STCellFormulaType.SHARED){ > return convertSharedFormula((int)f.getSi()); > } >@@ -363,6 +368,17 @@ > } > > public void setCellFormula(String formula) { >+ setFormula(formula, FormulaType.CELL); >+ } >+ >+ /* package */ void setCellArrayFormula(String formula, CellRangeAddress range) { >+ setFormula(formula, FormulaType.ARRAY); >+ CTCellFormula cellFormula = _cell.getF(); >+ cellFormula.setT(STCellFormulaType.ARRAY); >+ cellFormula.setRef(range.formatAsString()); >+ } >+ >+ private void setFormula(String formula, int formulaType) { > XSSFWorkbook wb = _row.getSheet().getWorkbook(); > if (formula == null) { > wb.onDeleteFormula(this); >@@ -372,7 +388,7 @@ > > XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); > //validate through the FormulaParser >- FormulaParser.parse(formula, fpb, FormulaType.CELL, wb.getSheetIndex(getSheet())); >+ FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet())); > > CTCellFormula f = CTCellFormula.Factory.newInstance(); > f.setStringValue(formula); >@@ -453,7 +469,7 @@ > */ > public int getCellType() { > >- if (_cell.getF() != null) { >+ if (_cell.getF() != null || getSheet().isCellInArrayFormulaContext(this)) { > return CELL_TYPE_FORMULA; > } > >@@ -911,4 +927,16 @@ > throw new RuntimeException("Unexpected cell type (" + cellType + ")"); > } > >+ public CellRangeAddress getArrayFormulaRange() { >+ XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); >+ if (cell == null) { >+ throw new IllegalStateException("not an array formula cell."); >+ } >+ String formulaRef = cell._cell.getF().getRef(); >+ return CellRangeAddress.valueOf(formulaRef); >+ } >+ >+ public boolean isPartOfArrayFormulaGroup() { >+ return getSheet().isCellInArrayFormulaContext(this); >+ } > } >Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java >=================================================================== >--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (revision 886079) >+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (working copy) >@@ -19,6 +19,7 @@ > > import org.apache.poi.ss.formula.EvaluationCell; > import org.apache.poi.ss.formula.EvaluationSheet; >+import org.apache.poi.ss.util.CellRangeAddress; > > /** > * XSSF wrapper for a cell under evaluation >@@ -72,4 +73,13 @@ > public String getStringCellValue() { > return _cell.getRichStringCellValue().getString(); > } >+ >+ public boolean isArrayFormulaContext() { >+ return _cell.isPartOfArrayFormulaGroup(); >+ } >+ >+ public EvaluationCell getFirstCellInArrayFormula() { >+ CellRangeAddress range = _cell.getArrayFormulaRange(); >+ return _evalSheet.getCell(range.getFirstRow(), range.getFirstColumn()); >+ } > } >Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java >=================================================================== >--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (revision 886079) >+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (working copy) >@@ -25,13 +25,16 @@ > import org.apache.poi.hssf.record.formula.eval.StringEval; > import org.apache.poi.hssf.record.formula.eval.ValueEval; > import org.apache.poi.hssf.record.formula.udf.UDFFinder; >+import org.apache.poi.ss.formula.ArrayEval; > import org.apache.poi.ss.formula.IStabilityClassifier; > import org.apache.poi.ss.formula.WorkbookEvaluator; >+import org.apache.poi.ss.usermodel.ArrayFormulaEvaluatorHelper; > import org.apache.poi.ss.usermodel.Cell; > import org.apache.poi.ss.usermodel.CellValue; > import org.apache.poi.ss.usermodel.FormulaEvaluator; > import org.apache.poi.ss.usermodel.Row; > import org.apache.poi.ss.usermodel.Sheet; >+import org.apache.poi.ss.util.CellRangeAddress; > > /** > * Evaluates formula cells.<p/> >@@ -42,6 +45,8 @@ > * > * @author Amol S. Deshmukh < amolweb at ya hoo dot com > > * @author Josh Micich >+ * @author Zahars Sulkins(Zahars.Sulkins at exigenservices.com) - Array Formula support >+ * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - Array Formula support > */ > public class XSSFFormulaEvaluator implements FormulaEvaluator { > >@@ -142,9 +147,18 @@ > if (cell == null || cell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) { > return -1; > } >- CellValue cv = evaluateFormulaCellValue(cell); > // cell remains a formula cell, but the cached value is changed >- setCellValue(cell, cv); >+ CellValue cv; >+ if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context >+ CellValue[][] cvs = evaluateFormulaCellArrayValues((XSSFCell) cell); >+ CellValue[][] values = setCellValues(cell, cvs); >+ int rowIndex = cell.getRowIndex() - cell.getArrayFormulaRange().getFirstRow(); >+ int colIndex = cell.getColumnIndex() - cell.getArrayFormulaRange().getFirstColumn(); >+ cv = values[rowIndex][colIndex]; >+ } else { // Single Formula >+ cv = evaluateFormulaCellValue(cell); >+ setCellValue(cell, cv); >+ } > return cv.getCellType(); > } > >@@ -170,10 +184,18 @@ > } > XSSFCell result = (XSSFCell) cell; > if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { >- CellValue cv = evaluateFormulaCellValue(cell); >- setCellType(cell, cv); // cell will no longer be a formula cell >- setCellValue(cell, cv); >- } >+ if (cell.isPartOfArrayFormulaGroup()) { // Array Formula Context >+ CellValue[][] cvs = evaluateFormulaCellArrayValues((XSSFCell) cell); >+ setCellsTypes(cell, cvs); // cells will no longer be a formula cell >+ setCellValues(cell, cvs); >+ >+ } else { // Single Formula >+ CellValue cv = evaluateFormulaCellValue(cell); >+ setCellType(cell, cv); // cell will no longer be a formula cell >+ setCellValue(cell, cv); >+ >+ } >+ } > return result; > } > private static void setCellType(Cell cell, CellValue cv) { >@@ -217,6 +239,47 @@ > } > } > >+ private void setCellsTypes(Cell cell, CellValue[][] cvs) { >+ CellRangeAddress range = cell.getArrayFormulaRange(); >+ int rowStart = range.getFirstRow(); >+ int colStart = range.getFirstColumn(); >+ Sheet sheet = cell.getSheet(); >+ for (int i = rowStart; i <= range.getLastRow(); i++) >+ for (int j = colStart; j <= range.getLastColumn(); j++) { >+ Row row = sheet.getRow(i); >+ Cell c = row.getCell(j); >+ if ((i - rowStart) < cvs.length && (j - colStart) < cvs[i - rowStart].length) >+ setCellType(c, cvs[i - rowStart][j - colStart]); >+ } >+ } >+ >+ /** >+ * Set value in Range >+ * >+ * @param cell >+ * @param cvs >+ * @return >+ */ >+ private CellValue[][] setCellValues(Cell cell, CellValue[][] cvs) { >+ CellRangeAddress range = cell.getArrayFormulaRange(); >+ int rowStart = range.getFirstRow(); >+ int colStart = range.getFirstColumn(); >+ Sheet sheet = cell.getSheet(); >+ CellValue[][] answer = (CellValue[][]) ArrayFormulaEvaluatorHelper.transformToRange(cvs, range); >+ for (int i = rowStart; i <= range.getLastRow(); i++) >+ for (int j = colStart; j <= range.getLastColumn(); j++) { >+ Row row = sheet.getRow(i); >+ if (row == null) >+ row = sheet.createRow(i); >+ Cell c = row.getCell(j); >+ if (c == null) >+ c = row.createCell(j); >+ CellValue cellValue = answer[i - rowStart][j - colStart]; >+ setCellValue(c, cellValue); >+ } >+ return answer; >+ } >+ > /** > * Loops over all cells in all sheets of the supplied > * workbook. >@@ -250,6 +313,13 @@ > */ > private CellValue evaluateFormulaCellValue(Cell cell) { > ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell((XSSFCell) cell)); >+ if (eval instanceof ArrayEval) {// support of arrays >+ if (cell.isPartOfArrayFormulaGroup()) { >+ eval = ArrayFormulaEvaluatorHelper.dereferenceValue((ArrayEval) eval, cell); >+ } else { >+ eval = ((ArrayEval) eval).getArrayElementAsEval(0, 0); >+ } >+ } > if (eval instanceof NumberEval) { > NumberEval ne = (NumberEval) eval; > return new CellValue(ne.getNumberValue()); >@@ -267,4 +337,27 @@ > } > throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")"); > } >+ >+ /** >+ * Returns a Array CellValue wrapper around the supplied ArrayEval instance. >+ */ >+ private CellValue[][] evaluateFormulaCellArrayValues(XSSFCell cell) { >+ ValueEval eval = _bookEvaluator.evaluate(new XSSFEvaluationCell(cell)); >+ if (eval instanceof ArrayEval) {// support of arrays >+ ArrayEval ae = (ArrayEval) eval; >+ int rowCount = ae.getRowCount(); >+ int ColCount = ae.getColCount(); >+ CellValue[][] answer = new CellValue[rowCount][ColCount]; >+ for (int i = 0; i < rowCount; i++) >+ for (int j = 0; j < ColCount; j++) { >+ ValueEval val = ae.getArrayElementAsEval(i, j); >+ answer[i][j] = ArrayFormulaEvaluatorHelper.evalToCellValue(val); >+ } >+ return answer; >+ } >+ // non-array (usually from aggregate function) >+ CellValue[][] answer = new CellValue[1][1]; >+ answer[0][0] = ArrayFormulaEvaluatorHelper.evalToCellValue(eval); >+ return answer; >+ } > } >Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >=================================================================== >--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (revision 886079) >+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (working copy) >@@ -65,6 +65,8 @@ > * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can > * contain text, numbers, dates, and formulas. Cells can also be formatted. > * </p> >+ * >+ * @author Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas > */ > public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class); >@@ -78,6 +80,7 @@ > private ColumnHelper columnHelper; > private CommentsTable sheetComments; > private Map<Integer, XSSFCell> sharedFormulas; >+ private List<CellRangeAddress> arrayFormulas; > > /** > * Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch. >@@ -152,6 +155,7 @@ > private void initRows(CTWorksheet worksheet) { > rows = new TreeMap<Integer, XSSFRow>(); > sharedFormulas = new HashMap<Integer, XSSFCell>(); >+ arrayFormulas = new ArrayList<CellRangeAddress>(); > for (CTRow row : worksheet.getSheetData().getRowArray()) { > XSSFRow r = new XSSFRow(row, this); > rows.put(r.getRowNum(), r); >@@ -2317,6 +2321,10 @@ > if(f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null){ > sharedFormulas.put((int)f.getSi(), cell); > } >+ CTCellFormula formula = ct.getF(); >+ if (formula != null && formula.getT() == STCellFormulaType.ARRAY && formula.getRef() != null) { >+ arrayFormulas.add(CellRangeAddress.valueOf(formula.getRef())); >+ } > } > > @Override >@@ -2674,4 +2682,54 @@ > private boolean sheetProtectionEnabled() { > return worksheet.getSheetProtection().getSheet(); > } >+ >+ /* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) { >+ for (CellRangeAddress range : arrayFormulas) { >+ if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { >+ return true; >+ } >+ } >+ return false; >+ } >+ >+ /* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) { >+ for (CellRangeAddress range : arrayFormulas) { >+ if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { >+ return getRow(range.getFirstRow()).getCell(range.getFirstColumn()); >+ } >+ } >+ return null; >+ } >+ >+ public void setArrayFormula(String formula, CellRangeAddress range) { >+ XSSFRow row = getRow(range.getFirstRow()); >+ if (row == null) { >+ row = createRow(range.getFirstRow()); >+ } >+ XSSFCell mainArrayFormulaCell = row.getCell(range.getFirstColumn()); >+ if (mainArrayFormulaCell == null) { >+ mainArrayFormulaCell = row.createCell(range.getFirstColumn()); >+ } >+ mainArrayFormulaCell.setCellArrayFormula(formula, range); >+ arrayFormulas.add(range); >+ } >+ >+ public void removeArrayFormula(Cell cell) { >+ for (CellRangeAddress range : arrayFormulas) { >+ if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { >+ arrayFormulas.remove(range); >+ for (int rowIndex = range.getFirstRow(); rowIndex <= range.getLastRow(); rowIndex++) { >+ XSSFRow row = getRow(rowIndex); >+ for (int columnIndex = range.getFirstColumn(); columnIndex <= range.getLastColumn(); columnIndex++) { >+ XSSFCell arrayFormulaCell = row.getCell(columnIndex); >+ if (arrayFormulaCell != null) { >+ arrayFormulaCell.setCellType(Cell.CELL_TYPE_BLANK); >+ } >+ } >+ } >+ return; >+ } >+ } >+ throw new RuntimeException("Cell does not belong to Array Formula"); >+ } > } >Index: src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java >=================================================================== >--- src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (revision 886079) >+++ src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (working copy) >@@ -48,7 +48,7 @@ > public class TestWorkbookEvaluator extends TestCase { > > private static ValueEval evaluateFormula(Ptg[] ptgs) { >- OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null); >+ OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null, false); > return new WorkbookEvaluator(null, null, null).evaluateFormula(ec, ptgs); > } >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 48292
:
24623
|
24645
|
24658
|
24659
|
24682
|
24683
|
24731
|
24732
|
24746
|
28053
|
28054