From 821b4291b232894017da3b34d36d57036d44ed8a Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Fri, 25 Dec 2009 15:15:55 +0000 Subject: [PATCH] added HSSF usermodel tests for array formulas, added support for array formulas in ss interfaces git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@893870 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/record/ArrayRecord.java | 57 ++++- .../aggregates/FormulaRecordAggregate.java | 53 ++++- .../record/aggregates/SharedValueManager.java | 57 +++-- .../apache/poi/hssf/usermodel/HSSFCell.java | 30 +++ .../apache/poi/hssf/usermodel/HSSFSheet.java | 58 +++++ .../ss/formula/OperandClassTransformer.java | 3 + .../org/apache/poi/ss/usermodel/Cell.java | 15 ++ .../org/apache/poi/ss/usermodel/Sheet.java | 16 ++ .../apache/poi/ss/util/CellRangeAddress.java | 7 +- .../org/apache/poi/ss/util/CellReference.java | 23 ++ .../apache/poi/xssf/usermodel/XSSFCell.java | 4 +- .../xssf/usermodel/TestXSSFArrayFormulas.java | 98 ++++++++ .../poi/xssf/usermodel/TestXSSFSheet.java | 135 ----------- .../poi/hssf/record/TestArrayRecord.java | 68 ++++++ .../TestFormulaRecordAggregate.java | 32 ++- .../hssf/usermodel/TestHSSFArrayFormulas.java | 60 +++++ .../poi/hssf/usermodel/TestHSSFSheet.java | 5 +- .../ss/usermodel/BaseTestArrayFormulas.java | 210 ++++++++++++++++++ 18 files changed, 772 insertions(+), 159 deletions(-) create mode 100644 src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFArrayFormulas.java create mode 100644 src/testcases/org/apache/poi/hssf/record/TestArrayRecord.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestHSSFArrayFormulas.java create mode 100644 src/testcases/org/apache/poi/ss/usermodel/BaseTestArrayFormulas.java diff --git a/src/java/org/apache/poi/hssf/record/ArrayRecord.java b/src/java/org/apache/poi/hssf/record/ArrayRecord.java index 0cf6f44e05..540926c60e 100644 --- a/src/java/org/apache/poi/hssf/record/ArrayRecord.java +++ b/src/java/org/apache/poi/hssf/record/ArrayRecord.java @@ -17,7 +17,10 @@ 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; @@ -28,6 +31,7 @@ import org.apache.poi.util.LittleEndianOutput; * 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 { @@ -35,7 +39,7 @@ public final class ArrayRecord extends SharedValueRecordBase { private static final int OPT_ALWAYS_RECALCULATE = 0x0001; private static final int OPT_CALCULATE_ON_OPEN = 0x0002; - private int _options; + private int _options; private int _field3notUsed; private Formula _formula; @@ -48,6 +52,13 @@ public final class ArrayRecord extends SharedValueRecordBase { _formula = Formula.read(formulaTokenLen, in, totalFormulaLen); } + public ArrayRecord(Formula formula, CellRangeAddress8Bit range ) { + super(range); + _options = 0; //YK: Excel 2007 leaves this field unset + _field3notUsed = 0; + _formula = formula; + } + public boolean isAlwaysRecalculate() { return (_options & OPT_ALWAYS_RECALCULATE) != 0; } @@ -55,7 +66,11 @@ public final class ArrayRecord extends SharedValueRecordBase { return (_options & OPT_CALCULATE_ON_OPEN) != 0; } - protected int getExtraDataSize() { + public void setOptions(int val){ + _options = val; + } + + protected int getExtraDataSize() { return 2 + 4 + _formula.getEncodedSize(); } @@ -84,4 +99,42 @@ public final class ArrayRecord extends SharedValueRecordBase { sb.append("]"); return sb.toString(); } + + /** + * @return the equivalent {@link Ptg} array that the formula would have, + * were it not shared. + */ + public Ptg[] getFormulaTokens() { + return _formula.getTokens(); + /* + YK: I don't understand all t + + 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); + */ + } } diff --git a/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java b/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java index 0f4e976d9a..8d822ee523 100644 --- a/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java +++ b/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java @@ -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.SharedFormulaRecord; 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 final class FormulaRecordAggregate extends RecordAggregate implements Cel } public Ptg[] getFormulaTokens() { - if (_sharedFormulaRecord == null) { - return _formulaRecord.getParsedExpression(); + if (_sharedFormulaRecord != null) { + return _sharedFormulaRecord.getFormulaTokens(_formulaRecord); + } + CellReference expRef = _formulaRecord.getFormula().getExpReference(); + if (expRef != null) { + ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol()); + return arec.getFormulaTokens(); } - return _sharedFormulaRecord.getFormulaTokens(_formulaRecord); + return _formulaRecord.getParsedExpression(); } /** @@ -216,4 +226,41 @@ public final class FormulaRecordAggregate extends RecordAggregate implements Cel _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()); + if (arec == null) { + throw new IllegalStateException("ArrayRecord was not found for the locator " + expRef.formatAsString()); + } + 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 null + */ + public CellRangeAddress removeArrayFormula(int rowIndex, int columnIndex) { + CellRangeAddress8Bit a = _sharedValueManager.removeArrayFormula(rowIndex, columnIndex); + return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(), a.getLastColumn()); + } } diff --git a/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java b/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java index 4327ee63af..ff7d4c0f24 100644 --- a/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java +++ b/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java @@ -17,9 +17,11 @@ 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; @@ -41,6 +43,7 @@ import org.apache.poi.ss.util.CellReference; * * * @author Josh Micich + * @author Vladimirs Abramovs(Vladimirs.Abramovs at exigenservices.com) - handling of ArrayRecords */ public final class SharedValueManager { @@ -112,12 +115,12 @@ public final class SharedValueManager { /** * @return a new empty {@link SharedValueManager}. */ - public static final SharedValueManager createEmpty() { + public static SharedValueManager createEmpty() { // Note - must create distinct instances because they are assumed to be mutable. return new SharedValueManager( new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]); } - private final ArrayRecord[] _arrayRecords; + private final List _arrayRecords; private final TableRecord[] _tableRecords; private final Map _groupsBySharedFormulaRecord; /** cached for optimization purposes */ @@ -129,7 +132,8 @@ public final class SharedValueManager { if (nShF != firstCells.length) { throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + "."); } - _arrayRecords = arrayRecords; + _arrayRecords = new ArrayList(); + _arrayRecords.addAll(Arrays.asList(arrayRecords)); _tableRecords = tableRecords; Map m = new HashMap(nShF * 3 / 2); for (int i = 0; i < nShF; i++) { @@ -139,14 +143,6 @@ public final class SharedValueManager { _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 SharedFormulaRecords 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) { @@ -260,8 +256,7 @@ public final class SharedValueManager { return tr; } } - for (int i = 0; i < _arrayRecords.length; i++) { - ArrayRecord ar = _arrayRecords[i]; + for (ArrayRecord ar : _arrayRecords) { if (ar.isFirstCell(row, column)) { return ar; } @@ -281,4 +276,40 @@ public final class SharedValueManager { } 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 null. + */ + 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 null. + */ + public ArrayRecord getArrayRecord(int firstRow, int firstColumn) { + for(ArrayRecord ar : _arrayRecords) { + if(ar.isFirstCell(firstRow, firstColumn)) { + return ar; + } + } + return null; + } + } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index 51319cd70b..c9462b3e14 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -43,6 +43,7 @@ import org.apache.poi.hssf.record.SubRecord; 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,7 +51,9 @@ import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.Hyperlink; import org.apache.poi.ss.usermodel.RichTextString; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.NumberToTextConverter; +import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.util.POILogger; @@ -1160,4 +1163,31 @@ public class HSSFCell implements Cell { } 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) { + String ref = new CellReference(this).formatAsString(); + throw new IllegalStateException("Cell "+ref+" is not part of an array formula"); + } + return ((FormulaRecordAggregate)_record).getArrayFormulaRange(); + } + + public boolean isPartOfArrayFormulaGroup() { + if (_cellType != CELL_TYPE_FORMULA) { + return false; + } + return ((FormulaRecordAggregate)_record).isPartOfArrayFormula(); + } } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index d16ca8e7d4..e3fc7c20f0 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -31,6 +31,7 @@ import java.util.List; import java.util.TreeMap; import org.apache.poi.ddf.EscherRecord; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.InternalSheet; import org.apache.poi.hssf.model.InternalWorkbook; import org.apache.poi.hssf.record.CellValueRecordInterface; @@ -44,14 +45,18 @@ import org.apache.poi.hssf.record.SCLRecord; 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; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -1870,4 +1875,57 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { return wb.getSheetName(idx); } + public HSSFCell[] setArrayFormula(String formula, CellRangeAddress range) { + HSSFCell[] cells = new HSSFCell[range.getNumberOfCells()]; + int k = 0; + + // make sure the formula parses OK first + int sheetIndex = _workbook.getSheetIndex(this); + Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, sheetIndex); + 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); + cells[k++] = cell; + } + } + HSSFCell mainArrayFormulaCell = getRow(firstRow).getCell(firstColumn); + FormulaRecordAggregate agg = (FormulaRecordAggregate)mainArrayFormulaCell.getCellValueRecord(); + agg.setArrayFormula(range, ptgs); + return cells; + } + + + public HSSFCell[] removeArrayFormula(Cell cell) { + ArrayList lst = new ArrayList(); + CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord(); + if (!(rec instanceof FormulaRecordAggregate)) { + String ref = new CellReference(cell).formatAsString(); + throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula"); + } + FormulaRecordAggregate fra = (FormulaRecordAggregate) rec; + CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex()); + if (range == null) { + String ref = new CellReference(cell).formatAsString(); + throw new IllegalArgumentException("Cell " + ref + " is not part of 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++) { + HSSFCell rCell = getRow(rowIn).getCell(colIn); + rCell.setCellType(Cell.CELL_TYPE_BLANK); + lst.add(rCell); + } + } + return lst.toArray(new HSSFCell[lst.size()]); + } } diff --git a/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java index 9af6411285..4d601fb640 100644 --- a/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java +++ b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java @@ -72,6 +72,9 @@ final class OperandClassTransformer { 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; diff --git a/src/java/org/apache/poi/ss/usermodel/Cell.java b/src/java/org/apache/poi/ss/usermodel/Cell.java index 17af1831d6..5adbde3bab 100644 --- a/src/java/org/apache/poi/ss/usermodel/Cell.java +++ b/src/java/org/apache/poi/ss/usermodel/Cell.java @@ -21,6 +21,7 @@ import java.util.Calendar; 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,18 @@ public interface Cell { * @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 true if this cell is part of group of cells having a common array formula. + */ + boolean isPartOfArrayFormulaGroup(); + } diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java index 1ec70bd3d7..f8f8223da4 100644 --- a/src/java/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java @@ -781,4 +781,20 @@ public interface Sheet extends Iterable { */ boolean isSelected(); + + /** + * Sets array formula to specified region for result. + * + * @param formula Formula + * @param range Region of array formula for result. + */ + Cell[] 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 + */ + Cell[] removeArrayFormula(Cell cell); + } diff --git a/src/java/org/apache/poi/ss/util/CellRangeAddress.java b/src/java/org/apache/poi/ss/util/CellRangeAddress.java index 8c907116ae..65689cb4a0 100644 --- a/src/java/org/apache/poi/ss/util/CellRangeAddress.java +++ b/src/java/org/apache/poi/ss/util/CellRangeAddress.java @@ -77,8 +77,11 @@ public class CellRangeAddress extends CellRangeAddressBase { CellReference cellRefFrom = new CellReference(getFirstRow(), getFirstColumn()); CellReference cellRefTo = new CellReference(getLastRow(), getLastColumn()); sb.append(cellRefFrom.formatAsString()); - sb.append(':'); - sb.append(cellRefTo.formatAsString()); + //for a single-cell reference return A1 instead of A1:A1 + if(!cellRefFrom.equals(cellRefTo)){ + sb.append(':'); + sb.append(cellRefTo.formatAsString()); + } return sb.toString(); } diff --git a/src/java/org/apache/poi/ss/util/CellReference.java b/src/java/org/apache/poi/ss/util/CellReference.java index d01cca3540..e542962cba 100644 --- a/src/java/org/apache/poi/ss/util/CellReference.java +++ b/src/java/org/apache/poi/ss/util/CellReference.java @@ -22,6 +22,7 @@ import java.util.regex.Pattern; import org.apache.poi.hssf.record.formula.SheetNameFormatter; import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.usermodel.Cell; /** * @@ -113,6 +114,10 @@ public class CellReference { public CellReference(int pRow, short pCol) { this(pRow, pCol & 0xFFFF, false, false); } + + public CellReference(Cell cell) { + this(cell.getRowIndex(), cell.getColumnIndex(), false, false); + } public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) { this(null, pRow, pCol, pAbsRow, pAbsCol); @@ -483,4 +488,22 @@ public class CellReference { } sb.append(_rowIndex+1); } + + /** + * Checks whether this cell reference is equal to another object. + *

+ * Two cells references are assumed to be equal if their string representations + * ({@link #formatAsString()} are equal. + *

+ */ + @Override + public boolean equals(Object o){ + if(o == null || !(o instanceof CellReference)) { + return false; + } + + String me = formatAsString(); + String anotherRef = ((CellReference)o).formatAsString(); + return me.equals(anotherRef); + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index e5a193ee6c..6a5bcd4802 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -407,9 +407,9 @@ public final class XSSFCell implements Cell { 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(); + CTCellFormula f = CTCellFormula.Factory.newInstance(); f.setStringValue(formula); _cell.setF(f); if(_cell.isSetV()) _cell.unsetV(); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFArrayFormulas.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFArrayFormulas.java new file mode 100644 index 0000000000..b00665cb6a --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFArrayFormulas.java @@ -0,0 +1,98 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.xssf.usermodel; + +import org.apache.poi.ss.usermodel.BaseTestArrayFormulas; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.xssf.XSSFITestDataProvider; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; + +/** + * Test array formulas in XSSF + * + * @author Yegor Kozlov + */ +public final class TestXSSFArrayFormulas extends BaseTestArrayFormulas { + + @Override + protected XSSFITestDataProvider getTestDataProvider(){ + return XSSFITestDataProvider.getInstance(); + } + + public void testXSSFSetArrayFormula_singleCell() { + XSSFWorkbook workbook = getTestDataProvider().createWorkbook(); + XSSFSheet sheet = workbook.createSheet(); + + // row 3 does not yet exist + assertNull(sheet.getRow(2)); + CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); + XSSFCell[] cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range); + assertEquals(1, cells.length); + + // sheet.setArrayFormula creates rows and cells for the designated range + assertNotNull(sheet.getRow(2)); + XSSFCell cell = sheet.getRow(2).getCell(2); + assertNotNull(cell); + + assertTrue(cell.isPartOfArrayFormulaGroup()); + assertSame(cells[0], sheet.getFirstCellInArrayFormula(cells[0])); + //retrieve the range and check it is the same + assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); + + //check the CTCellFormula bean + CTCellFormula f = cell.getCTCell().getF(); + assertEquals("SUM(C11:C12*D11:D12)", f.getStringValue()); + assertEquals("C3", f.getRef()); + assertEquals(STCellFormulaType.ARRAY, f.getT()); + + } + + public void testXSSFSetArrayFormula_multiCell() { + XSSFCell[] cells; + + XSSFWorkbook workbook = getTestDataProvider().createWorkbook(); + XSSFSheet sheet = workbook.createSheet(); + + CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); + assertEquals("C4:C6", range.formatAsString()); + cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); + assertEquals(3, cells.length); + + // sheet.setArrayFormula creates rows and cells for the designated range + assertEquals("C4", cells[0].getCTCell().getR()); + assertEquals("C5", cells[1].getCTCell().getR()); + assertEquals("C6", cells[2].getCTCell().getR()); + assertSame(cells[0], sheet.getFirstCellInArrayFormula(cells[0])); + + /* + * From the spec: + * For a multi-cell formula, the c elements for all cells except the top-left + * cell in that range shall not have an f element; + */ + + //the first cell has an f element + CTCellFormula f = cells[0].getCTCell().getF(); + assertEquals("SUM(A1:A3*B1:B3)", f.getStringValue()); + assertEquals("C4:C6", f.getRef()); + assertEquals(STCellFormulaType.ARRAY, f.getT()); + //the other two cells don't have an f element + assertNull(cells[1].getCTCell().getF()); + assertNull(cells[2].getCTCell().getF()); + } +} \ No newline at end of file diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java index a5fd853b71..4d6fcc6351 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -914,139 +914,4 @@ public class TestXSSFSheet extends BaseTestSheet { //existing cells are invalidated assertEquals(0, wsh.getSheetData().getRowArray(0).sizeOfCArray()); } - - public void testSetArrayFormula() throws Exception { - XSSFCell[] cells; - - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet = workbook.createSheet(); - XSSFCell cell = sheet.createRow(0).createCell(0); - assertFalse(cell.isPartOfArrayFormulaGroup()); - assertFalse(sheet.isCellInArrayFormulaContext(cell)); - try { - CellRangeAddress range = cell.getArrayFormulaRange(); - fail("expected exception"); - } catch (IllegalStateException e){ - assertEquals("Cell A1 is not part of an array formula", e.getMessage()); - } - - // 1. single-cell formula - - // row 3 does not yet exist - assertNull(sheet.getRow(2)); - CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); - cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range); - assertEquals(1, cells.length); - // sheet.setArrayFormula creates rows and cells for the designated range - assertNotNull(sheet.getRow(2)); - cell = sheet.getRow(2).getCell(2); - assertNotNull(cell); - - assertTrue(cell.isPartOfArrayFormulaGroup()); - assertSame(cells[0], sheet.getFirstCellInArrayFormula(cells[0])); - //retrieve the range and check it is the same - assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); - - // 2. multi-cell formula - //rows 3-5 don't exist yet - assertNull(sheet.getRow(3)); - assertNull(sheet.getRow(4)); - assertNull(sheet.getRow(5)); - - range = new CellRangeAddress(3, 5, 2, 2); - assertEquals("C4:C6", range.formatAsString()); - cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); - assertEquals(3, cells.length); - - // sheet.setArrayFormula creates rows and cells for the designated range - assertEquals("C4", cells[0].getCTCell().getR()); - assertEquals("C5", cells[1].getCTCell().getR()); - assertEquals("C6", cells[2].getCTCell().getR()); - assertSame(cells[0], sheet.getFirstCellInArrayFormula(cells[0])); - - /* - * For a multi-cell formula, the c elements for all cells except the top-left - * cell in that range shall not have an f element; - */ - assertEquals("SUM(A1:A3*B1:B3)", cells[0].getCTCell().getF().getStringValue()); - assertNull(cells[1].getCTCell().getF()); - assertNull(cells[2].getCTCell().getF()); - - for(XSSFCell acell : cells){ - assertTrue(acell.isPartOfArrayFormulaGroup()); - assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType()); - assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); - //retrieve the range and check it is the same - assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); - } - } - - public void testRemoveArrayFormula() throws Exception { - XSSFCell[] cells; - - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet = workbook.createSheet(); - - CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); - assertEquals("C4:C6", range.formatAsString()); - cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); - assertEquals(3, cells.length); - - // remove the formula cells in C4:C6 - XSSFCell[] dcells = sheet.removeArrayFormula(cells[0]); - // removeArrayFormula should return the same cells as setArrayFormula - assertTrue(Arrays.equals(cells, dcells)); - - for(XSSFCell acell : cells){ - assertFalse(acell.isPartOfArrayFormulaGroup()); - assertEquals(Cell.CELL_TYPE_BLANK, acell.getCellType()); - } - - //invocation on a not-array-formula cell throws IllegalStateException - try { - sheet.removeArrayFormula(cells[0]); - fail("expected exception"); - } catch (IllegalArgumentException e){ - assertEquals("Cell C4 is not part of an array formula", e.getMessage()); - } - } - - public void testReadArrayFormula() throws Exception { - XSSFCell[] cells; - - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet1 = workbook.createSheet(); - cells = sheet1.setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6")); - assertEquals(3, cells.length); - - cells = sheet1.setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6")); - assertEquals(3, cells.length); - - XSSFSheet sheet2 = workbook.createSheet(); - cells = sheet2.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4")); - assertEquals(3, cells.length); - - workbook = getTestDataProvider().writeOutAndReadBack(workbook); - sheet1 = workbook.getSheetAt(0); - for(int rownum=3; rownum <= 5; rownum++) { - XSSFCell cell1 = sheet1.getRow(rownum).getCell(2); - assertTrue( sheet1.isCellInArrayFormulaContext(cell1)); - assertTrue( cell1.isPartOfArrayFormulaGroup()); - - XSSFCell cell2 = sheet1.getRow(rownum).getCell(0); - assertTrue( sheet1.isCellInArrayFormulaContext(cell2)); - assertTrue( cell2.isPartOfArrayFormulaGroup()); - } - - sheet2 = workbook.getSheetAt(1); - for(int rownum=1; rownum <= 3; rownum++) { - XSSFCell cell1 = sheet2.getRow(rownum).getCell(3); - assertTrue( sheet2.isCellInArrayFormulaContext(cell1)); - assertTrue( cell1.isPartOfArrayFormulaGroup()); - } - XSSFCell acnhorCell = sheet2.getRow(1).getCell(3); - XSSFCell fmlaCell = sheet2.getRow(2).getCell(3); - assertSame(acnhorCell, sheet2.getFirstCellInArrayFormula(fmlaCell)); - assertSame(acnhorCell, sheet2.getFirstCellInArrayFormula(acnhorCell)); - } } diff --git a/src/testcases/org/apache/poi/hssf/record/TestArrayRecord.java b/src/testcases/org/apache/poi/hssf/record/TestArrayRecord.java new file mode 100644 index 0000000000..ab8bb7c2be --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/TestArrayRecord.java @@ -0,0 +1,68 @@ +/* ==================================================================== + 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 junit.framework.AssertionFailedError; +import junit.framework.ComparisonFailure; +import junit.framework.TestCase; + +import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.util.CellRangeAddress8Bit; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.RefPtg; +import org.apache.poi.hssf.usermodel.*; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.formula.FormulaParser; +import org.apache.poi.ss.formula.FormulaRenderer; +import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.formula.Formula; +import org.apache.poi.util.LittleEndianInput; +import org.apache.poi.util.HexRead; +import org.apache.poi.util.HexDump; + +import java.util.Arrays; + +public final class TestArrayRecord extends TestCase { + + public void testRead() { + String hex = + "21 02 25 00 01 00 01 00 01 01 00 00 00 00 00 00 " + + "17 00 65 00 00 01 00 02 C0 02 C0 65 00 00 01 00 " + + "03 C0 03 C0 04 62 01 07 00"; + byte[] data = HexRead.readFromString(hex); + RecordInputStream in = TestcaseRecordInputStream.create(data); + ArrayRecord r1 = new ArrayRecord(in); + CellRangeAddress8Bit range = r1.getRange(); + assertEquals(1, range.getFirstColumn()); + assertEquals(1, range.getLastColumn()); + assertEquals(1, range.getFirstRow()); + assertEquals(1, range.getLastRow()); + + Ptg[] ptg = r1.getFormulaTokens(); + assertEquals("MAX(C1:C2-D1:D2)", FormulaRenderer.toFormulaString(null, ptg)); + + //construct a new ArrayRecord with the same contents as r1 + Ptg[] fmlaPtg = FormulaParser.parse("MAX(C1:C2-D1:D2)", null, FormulaType.ARRAY, 0); + ArrayRecord r2 = new ArrayRecord(Formula.create(fmlaPtg), new CellRangeAddress8Bit(1, 1, 1, 1)); + byte[] ser = r2.serialize(); + //serialize and check that the data is the same as in r1 + assertEquals(HexDump.toHex(data), HexDump.toHex(ser)); + + + } +} \ No newline at end of file diff --git a/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java b/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java index aedabd0d57..867bfc35f6 100644 --- a/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java +++ b/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java @@ -24,7 +24,13 @@ import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.RecordFormatException; import org.apache.poi.hssf.record.StringRecord; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.ExpPtg; import org.apache.poi.hssf.usermodel.RecordInspector.RecordCollector; +import org.apache.poi.hssf.model.HSSFFormulaParser; +import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.formula.FormulaRenderer; +import org.apache.poi.ss.util.CellRangeAddress; /** * @@ -39,7 +45,8 @@ public final class TestFormulaRecordAggregate extends TestCase { s.setString("abc"); FormulaRecordAggregate fagg = new FormulaRecordAggregate(f, s, SharedValueManager.createEmpty()); assertEquals("abc", fagg.getStringValue()); - } + assertFalse(fagg.isPartOfArrayFormula()); + } /** * Sometimes a {@link StringRecord} appears after a {@link FormulaRecord} even though the @@ -71,4 +78,27 @@ public final class TestFormulaRecordAggregate extends TestCase { assertEquals(1, vraRecs.length); assertEquals(fr, vraRecs[0]); } + + public void testArrayFormulas() { + int rownum = 4; + int colnum = 4; + + FormulaRecord fr = new FormulaRecord(); + fr.setRow(rownum); + fr.setColumn((short)colnum); + + FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.createEmpty()); + Ptg[] ptgsForCell = {new ExpPtg(rownum, colnum)}; + agg.setParsedExpression(ptgsForCell); + + String formula = "SUM(A1:A3*B1:B3)"; + Ptg[] ptgs = HSSFFormulaParser.parse(formula, null, FormulaType.ARRAY, 0); + agg.setArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs); + + assertTrue(agg.isPartOfArrayFormula()); + assertEquals("E5", agg.getArrayFormulaRange().formatAsString()); + Ptg[] ptg = agg.getFormulaTokens(); + String fmlaSer = FormulaRenderer.toFormulaString(null, ptg); + assertEquals(formula, fmlaSer); + } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFArrayFormulas.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFArrayFormulas.java new file mode 100644 index 0000000000..fb86b0a627 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFArrayFormulas.java @@ -0,0 +1,60 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.usermodel; + +import org.apache.poi.ss.usermodel.BaseTestArrayFormulas; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.hssf.HSSFITestDataProvider; +import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; + +/** + * Test array formulas in HSSF + * + * @author Yegor Kozlov + */ +public final class TestHSSFArrayFormulas extends BaseTestArrayFormulas { + + @Override + protected HSSFITestDataProvider getTestDataProvider(){ + return HSSFITestDataProvider.getInstance(); + } + + public void testHSSFSetArrayFormula_singleCell() { + HSSFWorkbook workbook = getTestDataProvider().createWorkbook(); + HSSFSheet sheet = workbook.createSheet(); + + CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); + HSSFCell[] cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range); + assertEquals(1, cells.length); + + // sheet.setArrayFormula creates rows and cells for the designated range + assertNotNull(sheet.getRow(2)); + HSSFCell cell = sheet.getRow(2).getCell(2); + assertNotNull(cell); + + assertTrue(cell.isPartOfArrayFormulaGroup()); + //retrieve the range and check it is the same + assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); + + FormulaRecordAggregate agg = (FormulaRecordAggregate)cell.getCellValueRecord(); + assertEquals(range.formatAsString(), agg.getArrayFormulaRange().formatAsString()); + assertTrue(agg.isPartOfArrayFormula()); + + } + +} \ No newline at end of file diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java index fb9dd15a32..1dc6e1ac18 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java @@ -20,6 +20,7 @@ package org.apache.poi.hssf.usermodel; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; +import java.util.Arrays; import junit.framework.AssertionFailedError; @@ -42,6 +43,9 @@ import org.apache.poi.hssf.record.WindowTwoRecord; import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock; import org.apache.poi.hssf.usermodel.RecordInspector.RecordCollector; import org.apache.poi.ss.usermodel.BaseTestSheet; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.TempFile; @@ -820,5 +824,4 @@ public final class TestHSSFSheet extends BaseTestSheet { s.setRightToLeft(true); assertEquals(true, s.isRightToLeft()); } - } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestArrayFormulas.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestArrayFormulas.java new file mode 100644 index 0000000000..d07e13b1a4 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestArrayFormulas.java @@ -0,0 +1,210 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.usermodel; + +import junit.framework.TestCase; +import org.apache.poi.ss.ITestDataProvider; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.formula.FormulaParseException; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; + +import java.util.Iterator; +import java.util.Arrays; + +/** + * Common superclass for testing usermodel API for array formulas + * + * @author Yegor Kozlov + */ +public abstract class BaseTestArrayFormulas extends TestCase { + + /** + * @return an object that provides test data in HSSF / XSSF specific way + */ + protected abstract ITestDataProvider getTestDataProvider(); + + + /** + * Set single-cell array formula + */ + public void testSetArrayFormula_singleCell() { + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet = workbook.createSheet(); + + // row 3 does not yet exist + assertNull(sheet.getRow(2)); + CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); + Cell[] cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range); + assertEquals(1, cells.length); + // sheet.setArrayFormula creates rows and cells for the designated range + assertNotNull(sheet.getRow(2)); + Cell cell = sheet.getRow(2).getCell(2); + assertNotNull(cell); + + assertTrue(cell.isPartOfArrayFormulaGroup()); + //retrieve the range and check it is the same + assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); + //check the formula + assertEquals("SUM(C11:C12*D11:D12)", cell.getCellFormula()); + } + + /** + * Set multi-cell array formula + */ + public void testSetArrayFormula_multiCell() { + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet = workbook.createSheet(); + + // multi-cell formula + // rows 3-5 don't exist yet + assertNull(sheet.getRow(3)); + assertNull(sheet.getRow(4)); + assertNull(sheet.getRow(5)); + + CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); + assertEquals("C4:C6", range.formatAsString()); + Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); + assertEquals(3, cells.length); + + // sheet.setArrayFormula creates rows and cells for the designated range + assertSame(cells[0], sheet.getRow(3).getCell(2)); + assertSame(cells[1], sheet.getRow(4).getCell(2)); + assertSame(cells[2], sheet.getRow(5).getCell(2)); + + for(Cell acell : cells){ + assertTrue(acell.isPartOfArrayFormulaGroup()); + assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType()); + assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); + //retrieve the range and check it is the same + assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); + } + } + + /** + * Passing an incorrect formula to sheet.setArrayFormula + * should throw FormulaParseException + */ + public void testSetArrayFormula_incorrectFormula() { + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet = workbook.createSheet(); + + try { + sheet.setArrayFormula("incorrect-formula(C11_C12*D11_D12)", + new CellRangeAddress(10, 10, 10, 10)); + fail("expected exception"); + } catch (FormulaParseException e){ + //expected exception + } + } + + /** + * Calls of cell.getArrayFormulaRange and sheet.removeArrayFormula + * on a not-array-formula cell throw IllegalStateException + */ + public void testArrayFormulas_illegalCalls() { + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet = workbook.createSheet(); + + Cell cell = sheet.createRow(0).createCell(0); + assertFalse(cell.isPartOfArrayFormulaGroup()); + try { + CellRangeAddress range = cell.getArrayFormulaRange(); + fail("expected exception"); + } catch (IllegalStateException e){ + assertEquals("Cell A1 is not part of an array formula", e.getMessage()); + } + + try { + sheet.removeArrayFormula(cell); + fail("expected exception"); + } catch (IllegalArgumentException e){ + assertEquals("Cell A1 is not part of an array formula", e.getMessage()); + } + } + + /** + * create and remove array formulas + */ + public void testRemoveArrayFormula() { + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet = workbook.createSheet(); + + CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); + assertEquals("C4:C6", range.formatAsString()); + Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); + assertEquals(3, cells.length); + + // remove the formula cells in C4:C6 + Cell[] dcells = sheet.removeArrayFormula(cells[0]); + // removeArrayFormula should return the same cells as setArrayFormula + assertTrue(Arrays.equals(cells, dcells)); + + for(Cell acell : cells){ + assertFalse(acell.isPartOfArrayFormulaGroup()); + assertEquals(Cell.CELL_TYPE_BLANK, acell.getCellType()); + } + + // cells C4:C6 are not included in array formula, + // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException + for(Cell acell : cells){ + try { + sheet.removeArrayFormula(acell); + fail("expected exception"); + } catch (IllegalArgumentException e){ + String ref = new CellReference(acell).formatAsString(); + assertEquals("Cell "+ref+" is not part of an array formula", e.getMessage()); + } + } + } + + /** + * Test that when reading a workbook from input stream, array formulas are recognized + */ + public void testReadArrayFormula() { + Cell[] cells; + + Workbook workbook = getTestDataProvider().createWorkbook(); + Sheet sheet1 = workbook.createSheet(); + cells = sheet1.setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6")); + assertEquals(3, cells.length); + + cells = sheet1.setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6")); + assertEquals(3, cells.length); + + Sheet sheet2 = workbook.createSheet(); + cells = sheet2.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4")); + assertEquals(3, cells.length); + + workbook = getTestDataProvider().writeOutAndReadBack(workbook); + sheet1 = workbook.getSheetAt(0); + for(int rownum=3; rownum <= 5; rownum++) { + Cell cell1 = sheet1.getRow(rownum).getCell(2); + assertTrue( cell1.isPartOfArrayFormulaGroup()); + + Cell cell2 = sheet1.getRow(rownum).getCell(0); + assertTrue( cell2.isPartOfArrayFormulaGroup()); + } + + sheet2 = workbook.getSheetAt(1); + for(int rownum=1; rownum <= 3; rownum++) { + Cell cell1 = sheet2.getRow(rownum).getCell(3); + assertTrue( cell1.isPartOfArrayFormulaGroup()); + } + } +} \ No newline at end of file -- 2.39.5