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

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationCell.java (+12 lines)
Lines 20-25 package org.apache.poi.hssf.usermodel; Link Here
20
import org.apache.poi.ss.formula.EvaluationCell;
20
import org.apache.poi.ss.formula.EvaluationCell;
21
import org.apache.poi.ss.formula.EvaluationSheet;
21
import org.apache.poi.ss.formula.EvaluationSheet;
22
import org.apache.poi.ss.usermodel.CellType;
22
import org.apache.poi.ss.usermodel.CellType;
23
import org.apache.poi.ss.util.CellRangeAddress;
23
import org.apache.poi.util.Internal;
24
import org.apache.poi.util.Internal;
24
/**
25
/**
25
 * HSSF wrapper for a cell under evaluation
26
 * HSSF wrapper for a cell under evaluation
Lines 94-99 final class HSSFEvaluationCell implements EvaluationCell { Link Here
94
	public String getStringCellValue() {
95
	public String getStringCellValue() {
95
		return _cell.getRichStringCellValue().getString();
96
		return _cell.getRichStringCellValue().getString();
96
	}
97
	}
98
	
99
	@Override
100
	public CellRangeAddress getArrayFormulaRange() {
101
		return _cell.getArrayFormulaRange();
102
	}
103
	
104
	@Override
105
	public boolean isPartOfArrayFormulaGroup() {
106
		return _cell.isPartOfArrayFormulaGroup();
107
	}
108
	
97
	/**
109
	/**
98
	 * Will return {@link CellType} in a future version of POI.
110
	 * Will return {@link CellType} in a future version of POI.
99
	 * For forwards compatibility, do not hard-code cell type literals in your code.
111
	 * For forwards compatibility, do not hard-code cell type literals in your code.
(-)a/src/java/org/apache/poi/ss/formula/CacheAreaEval.java (+131 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula;
19
20
import org.apache.poi.ss.formula.TwoDEval;
21
import org.apache.poi.ss.formula.eval.AreaEval;
22
import org.apache.poi.ss.formula.eval.AreaEvalBase;
23
import org.apache.poi.ss.formula.eval.BlankEval;
24
import org.apache.poi.ss.formula.eval.ValueEval;
25
import org.apache.poi.ss.formula.ptg.AreaI;
26
import org.apache.poi.ss.formula.ptg.AreaI.OffsetArea;
27
import org.apache.poi.ss.util.CellReference;
28
29
/**
30
 * @author Robert Hulbert
31
 * Provides holding structure for temporary values in arrays during the evaluation process.
32
 * As such, Row/Column references do not actually correspond to data in the file.
33
 */
34
35
public final class CacheAreaEval extends AreaEvalBase {
36
    
37
    /* Value Containter */
38
    private final ValueEval[] _values;
39
    
40
    public CacheAreaEval(AreaI ptg, ValueEval[] values) {
41
        super(ptg);
42
        _values = values;
43
    }
44
    
45
    public CacheAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn, ValueEval[] values) {
46
        super(firstRow, firstColumn, lastRow, lastColumn);
47
        _values = values;
48
    }
49
    
50
    public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {
51
        return getRelativeValue(-1, relativeRowIndex, relativeColumnIndex);
52
    }
53
    
54
    public ValueEval getRelativeValue(int sheetIndex, int relativeRowIndex, int relativeColumnIndex) {
55
        int oneDimensionalIndex = relativeRowIndex * getWidth() + relativeColumnIndex;
56
        return _values[oneDimensionalIndex];
57
    }
58
59
    public AreaEval offset(int relFirstRowIx, int relLastRowIx,
60
            int relFirstColIx, int relLastColIx) {
61
        
62
        AreaI area = new OffsetArea(getFirstRow(), getFirstColumn(),
63
                relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx);
64
        
65
        int height = area.getLastRow() - area.getFirstRow() + 1;
66
        int width = area.getLastColumn() - area.getFirstColumn() + 1;
67
        
68
        ValueEval newVals[] = new ValueEval[height * width];
69
        
70
        int startRow = area.getFirstRow() - getFirstRow();
71
        int startCol = area.getFirstColumn() - getFirstColumn();
72
        
73
        for (int j = 0; j < height; j++) {
74
            for (int i = 0; i < width; i++) {
75
                ValueEval temp;
76
                
77
                /* CacheAreaEval is only temporary value representation, does not equal sheet selection
78
                 * so any attempts going beyond the selection results in BlankEval
79
                 */
80
                if (startRow + j > getLastRow() || startCol + i > getLastColumn()) {
81
                    temp = BlankEval.instance;
82
                }
83
                else {
84
                    temp = _values[(startRow + j) * getWidth() + (startCol + i)];
85
                }
86
                newVals[j * width + i] = temp;     
87
            }
88
        }
89
90
        return new CacheAreaEval(area, newVals);
91
    }
92
93
    public TwoDEval getRow(int rowIndex) {
94
        if (rowIndex >= getHeight()) {
95
            throw new IllegalArgumentException("Invalid rowIndex " + rowIndex
96
                    + ".  Allowable range is (0.." + getHeight() + ").");
97
        }
98
        int absRowIndex = getFirstRow() + rowIndex;
99
        ValueEval[] values = new ValueEval[getWidth()];
100
        
101
        for (int i = 0; i < values.length; i++) {
102
            values[i] = getRelativeValue(rowIndex, i);
103
        }
104
        return new CacheAreaEval(absRowIndex, getFirstColumn() , absRowIndex, getLastColumn(), values);
105
    }
106
107
    public TwoDEval getColumn(int columnIndex) {
108
        if (columnIndex >= getWidth()) {
109
            throw new IllegalArgumentException("Invalid columnIndex " + columnIndex
110
                    + ".  Allowable range is (0.." + getWidth() + ").");
111
        }
112
        int absColIndex = getFirstColumn() + columnIndex;
113
        ValueEval[] values = new ValueEval[getHeight()];
114
        
115
        for (int i = 0; i < values.length; i++) {
116
            values[i] = getRelativeValue(i, columnIndex);
117
        }
118
        
119
        return new CacheAreaEval(getFirstRow(), absColIndex, getLastRow(), absColIndex, values);
120
    }
121
    
122
    public String toString() {
123
        CellReference crA = new CellReference(getFirstRow(), getFirstColumn());
124
        CellReference crB = new CellReference(getLastRow(), getLastColumn());
125
        return getClass().getName() + "[" +
126
                crA.formatAsString() +
127
                ':' +
128
                crB.formatAsString() +
129
                "]";
130
    }
131
}
(-)a/src/java/org/apache/poi/ss/formula/EvaluationCell.java (+3 lines)
Lines 18-23 Link Here
18
package org.apache.poi.ss.formula;
18
package org.apache.poi.ss.formula;
19
19
20
import org.apache.poi.ss.usermodel.CellType;
20
import org.apache.poi.ss.usermodel.CellType;
21
import org.apache.poi.ss.util.CellRangeAddress;
21
22
22
/**
23
/**
23
 * Abstracts a cell for the purpose of formula evaluation.  This interface represents both formula
24
 * Abstracts a cell for the purpose of formula evaluation.  This interface represents both formula
Lines 56-61 public interface EvaluationCell { Link Here
56
	String getStringCellValue();
57
	String getStringCellValue();
57
	boolean getBooleanCellValue();
58
	boolean getBooleanCellValue();
58
	int getErrorCellValue();
59
	int getErrorCellValue();
60
	CellRangeAddress getArrayFormulaRange();
61
	boolean isPartOfArrayFormulaGroup();
59
62
60
	/**
63
	/**
61
	 * Will return {@link CellType} in a future version of POI.
64
	 * Will return {@link CellType} in a future version of POI.
(-)a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (+40 lines)
Lines 22-32 import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFo Link Here
22
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName;
22
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName;
23
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
23
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
24
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheetRange;
24
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheetRange;
25
import org.apache.poi.ss.formula.constant.ErrorConstant;
25
import org.apache.poi.ss.formula.eval.AreaEval;
26
import org.apache.poi.ss.formula.eval.AreaEval;
27
import org.apache.poi.ss.formula.eval.BoolEval;
26
import org.apache.poi.ss.formula.eval.ErrorEval;
28
import org.apache.poi.ss.formula.eval.ErrorEval;
27
import org.apache.poi.ss.formula.eval.ExternalNameEval;
29
import org.apache.poi.ss.formula.eval.ExternalNameEval;
28
import org.apache.poi.ss.formula.eval.FunctionNameEval;
30
import org.apache.poi.ss.formula.eval.FunctionNameEval;
31
import org.apache.poi.ss.formula.eval.NumberEval;
29
import org.apache.poi.ss.formula.eval.RefEval;
32
import org.apache.poi.ss.formula.eval.RefEval;
33
import org.apache.poi.ss.formula.eval.StringEval;
30
import org.apache.poi.ss.formula.eval.ValueEval;
34
import org.apache.poi.ss.formula.eval.ValueEval;
31
import org.apache.poi.ss.formula.functions.FreeRefFunction;
35
import org.apache.poi.ss.formula.functions.FreeRefFunction;
32
import org.apache.poi.ss.formula.ptg.Area3DPtg;
36
import org.apache.poi.ss.formula.ptg.Area3DPtg;
Lines 331-336 public final class OperationEvaluationContext { Link Here
331
        return new LazyAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(),
335
        return new LazyAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(),
332
                aptg.getLastRow(), aptg.getLastColumn(), sre);
336
                aptg.getLastRow(), aptg.getLastColumn(), sre);
333
    }
337
    }
338
339
    public ValueEval getAreaValueEval(int firstRowIndex, int firstColumnIndex,
340
            int lastRowIndex, int lastColumnIndex, Object[][] tokens) {
341
        
342
        ValueEval values[] = new ValueEval[tokens.length * tokens[0].length];
343
        
344
        int index = 0;
345
        for (int jdx = 0; jdx < tokens.length; jdx++) {
346
            for (int idx = 0; idx < tokens[0].length; idx++) {
347
                values[index++] = convertObjectEval(tokens[jdx][idx]);
348
            }
349
        }
350
        
351
        return new CacheAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex,
352
                                 lastColumnIndex, values);
353
    }
354
    
355
    private ValueEval convertObjectEval(Object token) {
356
        if (token == null) {
357
            throw new RuntimeException("Array item cannot be null");
358
        }
359
        if (token instanceof String) {
360
            return new StringEval((String)token);
361
        }
362
        if (token instanceof Double) {
363
            return new NumberEval(((Double)token).doubleValue());
364
        }
365
        if (token instanceof Boolean) {
366
            return BoolEval.valueOf(((Boolean)token).booleanValue());
367
        }
368
        if (token instanceof ErrorConstant) {
369
            return ErrorEval.valueOf(((ErrorConstant)token).getErrorCode());
370
        }
371
        throw new IllegalArgumentException("Unexpected constant class (" + token.getClass().getName() + ")");            
372
    }
373
    
334
    
374
    
335
    public ValueEval getNameXEval(NameXPtg nameXPtg) {
375
    public ValueEval getNameXEval(NameXPtg nameXPtg) {
336
        // Is the name actually on our workbook?
376
        // Is the name actually on our workbook?
(-)a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (+7 lines)
Lines 52-57 import org.apache.poi.ss.formula.eval.UnaryMinusEval; Link Here
52
import org.apache.poi.ss.formula.eval.UnaryPlusEval;
52
import org.apache.poi.ss.formula.eval.UnaryPlusEval;
53
import org.apache.poi.ss.formula.eval.ValueEval;
53
import org.apache.poi.ss.formula.eval.ValueEval;
54
import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
54
import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
55
import org.apache.poi.ss.formula.functions.ArrayFunction;
55
import org.apache.poi.ss.formula.functions.Function;
56
import org.apache.poi.ss.formula.functions.Function;
56
import org.apache.poi.ss.formula.functions.Indirect;
57
import org.apache.poi.ss.formula.functions.Indirect;
57
58
Lines 116-121 final class OperationEvaluatorFactory { Link Here
116
		Function result = _instancesByPtgClass.get(ptg);
117
		Function result = _instancesByPtgClass.get(ptg);
117
118
118
		if (result != null) {
119
		if (result != null) {
120
			EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex());
121
		    EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex());
122
		    
123
		    if (evalCell.isPartOfArrayFormulaGroup() && result instanceof ArrayFunction)
124
		        return ((ArrayFunction) result).evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex());
125
		                
119
			return  result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
126
			return  result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
120
		}
127
		}
121
128
(-)a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (-1 / +34 lines)
Lines 28-33 import java.util.TreeSet; Link Here
28
import org.apache.poi.ss.SpreadsheetVersion;
28
import org.apache.poi.ss.SpreadsheetVersion;
29
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
29
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
30
import org.apache.poi.ss.formula.atp.AnalysisToolPak;
30
import org.apache.poi.ss.formula.atp.AnalysisToolPak;
31
import org.apache.poi.ss.formula.eval.AreaEval;
31
import org.apache.poi.ss.formula.eval.BlankEval;
32
import org.apache.poi.ss.formula.eval.BlankEval;
32
import org.apache.poi.ss.formula.eval.BoolEval;
33
import org.apache.poi.ss.formula.eval.BoolEval;
33
import org.apache.poi.ss.formula.eval.ErrorEval;
34
import org.apache.poi.ss.formula.eval.ErrorEval;
Lines 538-544 public final class WorkbookEvaluator { Link Here
538
        if (!stack.isEmpty()) {
539
        if (!stack.isEmpty()) {
539
            throw new IllegalStateException("evaluation stack not empty");
540
            throw new IllegalStateException("evaluation stack not empty");
540
        }
541
        }
541
        ValueEval result = dereferenceResult(value, ec.getRowIndex(), ec.getColumnIndex());
542
        
543
        ValueEval result = dereferenceResult(value, ec);
544
542
        if (dbgEvaluationOutputIndent > 0) {
545
        if (dbgEvaluationOutputIndent > 0) {
543
            EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of "
546
            EVAL_LOG.log(POILogger.INFO, dbgIndentStr + "finshed eval of "
544
                            + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString()
547
                            + new CellReference(ec.getRowIndex(), ec.getColumnIndex()).formatAsString()
Lines 574-579 public final class WorkbookEvaluator { Link Here
574
        }
577
        }
575
        return index-startIndex;
578
        return index-startIndex;
576
    }
579
    }
580
    
581
    /**
582
     * Dereferences a single value from any AreaEval or RefEval evaluation
583
     * result. If the supplied evaluationResult is just a plain value, it is
584
     * returned as-is.
585
     *
586
     * @return a {@link NumberEval}, {@link StringEval}, {@link BoolEval}, or
587
     *         {@link ErrorEval}. Never <code>null</code>. {@link BlankEval} is
588
     *         converted to {@link NumberEval#ZERO}
589
     */
590
    private static ValueEval dereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec) {
591
        ValueEval value;
592
        
593
        EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex());
594
        EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex());
595
 
596
        if (evalCell.isPartOfArrayFormulaGroup() && evaluationResult instanceof AreaEval) {
597
            value = OperandResolver.getElementFromArray((AreaEval) evaluationResult, evalCell);
598
        }
599
        else {
600
            value = dereferenceResult(evaluationResult, ec.getRowIndex(), ec.getColumnIndex());
601
        }
602
        
603
        return value;
604
    }
577
605
578
    /**
606
    /**
579
     * Dereferences a single value from any AreaEval or RefEval evaluation
607
     * Dereferences a single value from any AreaEval or RefEval evaluation
Lines 667-672 public final class WorkbookEvaluator { Link Here
667
           AreaPtg aptg = (AreaPtg) ptg;
695
           AreaPtg aptg = (AreaPtg) ptg;
668
           return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
696
           return ec.getAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(), aptg.getLastRow(), aptg.getLastColumn());
669
        }
697
        }
698
        
699
        if (ptg instanceof ArrayPtg) {
700
           ArrayPtg aptg = (ArrayPtg) ptg;
701
           return ec.getAreaValueEval(0, 0, aptg.getRowCount() - 1, aptg.getColumnCount() - 1, aptg.getTokenArrayValues());
702
        }
670
703
671
        if (ptg instanceof UnknownPtg) {
704
        if (ptg instanceof UnknownPtg) {
672
            // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.
705
            // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted.
(-)a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (+5 lines)
Lines 145-150 public final class FunctionEval { Link Here
145
145
146
        retval[82] = TextFunction.SEARCH;
146
        retval[82] = TextFunction.SEARCH;
147
        // 83: TRANSPOSE
147
        // 83: TRANSPOSE
148
        retval[83] = MatrixFunction.TRANSPOSE;
148
149
149
        // 86: TYPE
150
        // 86: TYPE
150
151
Lines 182-187 public final class FunctionEval { Link Here
182
        retval[FunctionID.INDIRECT] = null; // Indirect.evaluate has different signature
183
        retval[FunctionID.INDIRECT] = null; // Indirect.evaluate has different signature
183
184
184
        retval[162] = TextFunction.CLEAN;
185
        retval[162] = TextFunction.CLEAN;
186
        
187
        retval[163] = MatrixFunction.MDETERM;
188
        retval[164] = MatrixFunction.MINVERSE;
189
        retval[165] = MatrixFunction.MMULT;
185
190
186
        retval[167] = new IPMT();
191
        retval[167] = new IPMT();
187
        retval[168] = new PPMT();
192
        retval[168] = new PPMT();
(-)a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java (+37 lines)
Lines 17-22 Link Here
17
17
18
package org.apache.poi.ss.formula.eval;
18
package org.apache.poi.ss.formula.eval;
19
19
20
import org.apache.poi.ss.formula.EvaluationCell;
21
import org.apache.poi.ss.util.CellRangeAddress;
22
import org.apache.poi.ss.formula.eval.ErrorEval;
20
import java.util.regex.Pattern;
23
import java.util.regex.Pattern;
21
24
22
/**
25
/**
Lines 70-75 public final class OperandResolver { Link Here
70
        }
73
        }
71
        return result;
74
        return result;
72
    }
75
    }
76
    
77
    /**
78
     * Retrieves a single value from an area evaluation utilizing the 2D indices of the cell
79
     * within its own area reference to index the value in the area evaluation.
80
     *
81
     * @param ae area reference after evaluation
82
     * @param cell the source cell of the formula that contains its 2D indices
83
     * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt> or <tt>BlankEval</tt>. or <tt>ErrorEval<tt>
84
     * Never <code>null</code>.
85
     */
86
87
    public static ValueEval getElementFromArray(AreaEval ae, EvaluationCell cell) {
88
        CellRangeAddress range =  cell.getArrayFormulaRange();
89
        int relativeRowIndex = cell.getRowIndex() - range.getFirstRow();
90
        int relativeColIndex = cell.getColumnIndex() - range.getFirstColumn();
91
        //System.out.println("Row: " + relativeRowIndex + " Col: " + relativeColIndex);
92
        
93
        if (ae.isColumn()) {
94
            if (ae.isRow()) {
95
                return ae.getRelativeValue(0, 0);
96
            }
97
            else if(relativeRowIndex < ae.getHeight()) {
98
                return ae.getRelativeValue(relativeRowIndex, 0);
99
            }
100
        }
101
        else if (!ae.isRow() && relativeRowIndex < ae.getHeight() && relativeColIndex < ae.getWidth()) {
102
            return ae.getRelativeValue(relativeRowIndex, relativeColIndex);
103
        }
104
        else if (ae.isRow() && relativeColIndex < ae.getWidth()) {
105
            return ae.getRelativeValue(0, relativeColIndex);
106
        }
107
        
108
        return ErrorEval.NA;
109
    }
73
110
74
    /**
111
    /**
75
     * Implements (some perhaps not well known) Excel functionality to select a single cell from an
112
     * Implements (some perhaps not well known) Excel functionality to select a single cell from an
(-)a/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java (-1 / +35 lines)
Lines 17-34 Link Here
17
17
18
package org.apache.poi.ss.formula.eval;
18
package org.apache.poi.ss.formula.eval;
19
19
20
import org.apache.poi.ss.formula.functions.ArrayFunction;
20
import org.apache.poi.ss.formula.functions.Fixed2ArgFunction;
21
import org.apache.poi.ss.formula.functions.Fixed2ArgFunction;
21
import org.apache.poi.ss.formula.functions.Function;
22
import org.apache.poi.ss.formula.functions.Function;
23
import org.apache.poi.ss.formula.functions.MatrixFunction.MutableValueCollector;
24
import org.apache.poi.ss.formula.functions.MatrixFunction.TwoArrayArg;
22
25
23
/**
26
/**
24
 * @author Josh Micich
27
 * @author Josh Micich
25
 */
28
 */
26
public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction {
29
public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction implements ArrayFunction {
27
30
28
	protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
31
	protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
29
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
32
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
30
		return OperandResolver.coerceValueToDouble(ve);
33
		return OperandResolver.coerceValueToDouble(ve);
31
	}
34
	}
35
	
36
	public ValueEval evaluateArray(ValueEval args[], int srcRowIndex, int srcColumnIndex) {
37
	    if (args.length != 2) {
38
	        return ErrorEval.VALUE_INVALID;
39
	    }
40
	    return new ArrayEval().evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]);
41
	}
42
	
32
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
43
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
33
		double result;
44
		double result;
34
		try {
45
		try {
Lines 52-57 public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction { Link Here
52
63
53
	protected abstract double evaluate(double d0, double d1) throws EvaluationException;
64
	protected abstract double evaluate(double d0, double d1) throws EvaluationException;
54
65
66
	private final class ArrayEval extends TwoArrayArg {
67
	    private final MutableValueCollector instance = new MutableValueCollector(false, true);
68
        
69
        protected double[] collectValues(ValueEval arg) throws EvaluationException {
70
            return instance.collectValues(arg);
71
        }
72
	    
73
	    protected double[][] evaluate(double[][] d1, double[][] d2) throws IllegalArgumentException, EvaluationException {
74
	        int width = (d1[0].length < d2[0].length) ? d1[0].length : d2[0].length;
75
	        int height = (d1.length < d2.length) ? d1.length : d2.length;
76
	        
77
	        double result[][] = new double[height][width];
78
	        
79
	        for (int j = 0; j < height; j++) {
80
	            for (int i = 0; i < width; i++) {
81
	                result[j][i] = TwoOperandNumericOperation.this.evaluate(d1[j][i], d2[j][i]);
82
	            }
83
	        }
84
	        
85
	        return result;
86
	    }
87
	}
88
	
55
	public static final Function AddEval = new TwoOperandNumericOperation() {
89
	public static final Function AddEval = new TwoOperandNumericOperation() {
56
		protected double evaluate(double d0, double d1) {
90
		protected double evaluate(double d0, double d1) {
57
			return d0+d1;
91
			return d0+d1;
(-)a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationCell.java (+11 lines)
Lines 27-32 import org.apache.poi.ss.formula.eval.StringEval; Link Here
27
import org.apache.poi.ss.formula.eval.ValueEval;
27
import org.apache.poi.ss.formula.eval.ValueEval;
28
import org.apache.poi.ss.usermodel.Cell;
28
import org.apache.poi.ss.usermodel.Cell;
29
import org.apache.poi.ss.usermodel.CellType;
29
import org.apache.poi.ss.usermodel.CellType;
30
import org.apache.poi.ss.util.CellRangeAddress;
30
import org.apache.poi.util.Internal;
31
import org.apache.poi.util.Internal;
31
32
32
/**
33
/**
Lines 155-160 final class ForkedEvaluationCell implements EvaluationCell { Link Here
155
	public int getColumnIndex() {
156
	public int getColumnIndex() {
156
		return _masterCell.getColumnIndex();
157
		return _masterCell.getColumnIndex();
157
	}
158
	}
159
	
160
	@Override
161
	public CellRangeAddress getArrayFormulaRange() {
162
		return _masterCell.getArrayFormulaRange();
163
	}
164
	
165
	@Override
166
	public boolean isPartOfArrayFormulaGroup() {
167
		return _masterCell.isPartOfArrayFormulaGroup();
168
	}
158
	/**
169
	/**
159
	 * Will return {@link CellType} in a future version of POI.
170
	 * Will return {@link CellType} in a future version of POI.
160
	 * For forwards compatibility, do not hard-code cell type literals in your code.
171
	 * For forwards compatibility, do not hard-code cell type literals in your code.
(-)a/src/java/org/apache/poi/ss/formula/functions/ArrayFunction.java (+44 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import org.apache.poi.ss.formula.eval.BlankEval;
21
import org.apache.poi.ss.formula.eval.ErrorEval;
22
import org.apache.poi.ss.formula.eval.MissingArgEval;
23
import org.apache.poi.ss.formula.eval.ValueEval;
24
25
/**
26
 * @author Robert Hulbert
27
 * Common Interface for any excel built-in function that has implemented array formula functionality.
28
 */
29
30
public interface ArrayFunction {
31
    
32
    /**
33
     * @param args the evaluated function arguments.  Empty values are represented with
34
     * {@link BlankEval} or {@link MissingArgEval}, never <code>null</code>.
35
     * @param srcRowIndex row index of the cell containing the formula under evaluation
36
     * @param srcColumnIndex column index of the cell containing the formula under evaluation
37
     * @return The evaluated result, possibly an {@link ErrorEval}, never <code>null</code>.
38
     * <b>Note</b> - Excel uses the error code <i>#NUM!</i> instead of IEEE <i>NaN</i>, so when
39
     * numeric functions evaluate to {@link Double#NaN} be sure to translate the result to {@link
40
     * ErrorEval#NUM_ERROR}.
41
     */
42
43
    ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex);
44
}
(-)a/src/java/org/apache/poi/ss/formula/functions/MatrixFunction.java (+339 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import org.apache.poi.ss.formula.CacheAreaEval;
21
import org.apache.poi.ss.formula.eval.AreaEval;
22
import org.apache.poi.ss.formula.eval.ErrorEval;
23
import org.apache.poi.ss.formula.eval.EvaluationException;
24
import org.apache.poi.ss.formula.eval.NumberEval;
25
import org.apache.poi.ss.formula.eval.OperandResolver;
26
import org.apache.poi.ss.formula.eval.ValueEval;
27
import org.apache.commons.math3.exception.DimensionMismatchException;
28
import org.apache.commons.math3.linear.Array2DRowRealMatrix;
29
import org.apache.commons.math3.linear.LUDecomposition;
30
import org.apache.commons.math3.linear.MatrixUtils;
31
32
/**
33
 * @author Robert Hulbert
34
 */
35
public abstract class MatrixFunction implements Function{
36
    
37
    public static final void checkValues(double[] results) throws EvaluationException {
38
        for (int idx = 0; idx < results.length; idx++) {
39
            if (Double.isNaN(results[idx]) || Double.isInfinite(results[idx])) {
40
                throw new EvaluationException(ErrorEval.NUM_ERROR);
41
            }
42
        }
43
    }
44
    
45
    protected final double singleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
46
        ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
47
        return OperandResolver.coerceValueToDouble(ve);
48
    }
49
    
50
    /* converts 1D array to 2D array for calculations */
51
    private static double[][] fillDoubleArray(double[] vector, int rows, int cols) throws EvaluationException {
52
        int i = 0, j = 0;
53
        
54
        if (rows < 1 || cols < 1 || vector.length < 1) {
55
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
56
        }
57
        
58
        double[][] matrix = new double[rows][cols];
59
        
60
        for (int idx = 0; idx < vector.length; idx++) {
61
            if (j < matrix.length) {
62
                if (i == matrix[0].length) {
63
                    i = 0;
64
                    j++;
65
                }
66
                matrix[j][i++] = vector[idx];
67
            } 
68
        }
69
        
70
        return matrix;
71
    }
72
    
73
    /* retrieves 1D array from 2D array after calculations */
74
    private static double[] extractDoubleArray(double[][] matrix) throws EvaluationException {
75
        int idx = 0;
76
        
77
        if (matrix == null || matrix.length < 1 || matrix[0].length < 1) {
78
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
79
        }
80
        
81
        double[] vector = new double[matrix.length * matrix[0].length];
82
        
83
        for (int j = 0; j < matrix.length; j++) {
84
            for (int i = 0; i < matrix[0].length; i++) {
85
                vector[idx++] = matrix[j][i];
86
            }
87
        }
88
        return vector;
89
    }
90
    
91
    public static abstract class OneArrayArg extends Fixed1ArgFunction {
92
        protected OneArrayArg() {
93
            //no fields to initialize
94
        }
95
        
96
        @Override
97
        public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
98
            if (arg0 instanceof AreaEval) {
99
                double result[] = null, resultArray[][];
100
                int width = 1, height = 1;
101
                
102
                try {
103
                    double values[] = collectValues(arg0);
104
                    double array[][] = fillDoubleArray(values,((AreaEval) arg0).getHeight(),((AreaEval) arg0).getWidth());
105
                    resultArray = evaluate(array);
106
                    width = resultArray[0].length;
107
                    height = resultArray.length;
108
                    result = extractDoubleArray(resultArray);
109
                    
110
                    checkValues(result);
111
                }
112
                catch(EvaluationException e){
113
                    return e.getErrorEval();
114
                }
115
                
116
                ValueEval vals[] = new ValueEval[result.length];
117
                
118
                for (int idx = 0; idx < result.length; idx++) {
119
                    vals[idx] = new NumberEval(result[idx]);
120
                }
121
                                
122
                if (result.length == 1) {
123
                    return vals[0];
124
                }
125
                else {
126
                    /* find a better solution */
127
                    return new CacheAreaEval(((AreaEval) arg0).getFirstRow(), ((AreaEval) arg0).getFirstColumn(), 
128
                                            ((AreaEval) arg0).getFirstRow() + height - 1, 
129
                                            ((AreaEval) arg0).getFirstColumn() + width - 1, vals);
130
                }
131
            }
132
            else {
133
                double result[][] = null;
134
                try {
135
                    double value = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
136
                    double temp[][] = {{value}};
137
                    result = evaluate(temp);
138
                    NumericFunction.checkValue(result[0][0]);
139
                }
140
                catch (EvaluationException e) {
141
                    return e.getErrorEval();
142
                }
143
                
144
                return new NumberEval(result[0][0]);
145
            }
146
        }
147
        
148
        protected abstract double[][] evaluate(double[][] d1) throws EvaluationException;
149
        protected abstract double[] collectValues(ValueEval arg) throws EvaluationException;
150
    }
151
    
152
    public static abstract class TwoArrayArg extends Fixed2ArgFunction {
153
        protected TwoArrayArg() {
154
            //no fields to initialize
155
        }
156
        
157
        @Override
158
        public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
159
            double result[];
160
            int width = 1, height = 1;
161
162
            try {
163
                double array0[][], array1[][], resultArray[][];
164
                
165
                if (arg0 instanceof AreaEval) {
166
                    try {
167
                        double values[] = collectValues(arg0);
168
                        array0 = fillDoubleArray(values, ((AreaEval) arg0).getHeight(), ((AreaEval) arg0).getWidth());
169
                    }
170
                    catch(EvaluationException e) {
171
                        return e.getErrorEval();
172
                    }
173
                }
174
                else {
175
                    try {
176
                        double value = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
177
                        array0 = new double[][] {{value}};
178
                    }
179
                    catch (EvaluationException e) {
180
                        return e.getErrorEval();
181
                    }
182
                }
183
                    
184
                if (arg1 instanceof AreaEval) {
185
                   try {
186
                      double values[] = collectValues(arg1);
187
                      array1 = fillDoubleArray(values, ((AreaEval) arg1).getHeight(),((AreaEval) arg1).getWidth());
188
                   }
189
                   catch (EvaluationException e) {
190
                      return e.getErrorEval();
191
                   }
192
                }
193
                else {
194
                    try {
195
                        double value = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
196
                        array1 = new double[][] {{value}};
197
                    }
198
                    catch (EvaluationException e) {
199
                        return e.getErrorEval();
200
                    }
201
                }
202
             
203
                resultArray = evaluate(array0, array1);
204
                width = resultArray[0].length;
205
                height = resultArray.length;
206
                result = extractDoubleArray(resultArray);
207
                checkValues(result);
208
            }
209
            catch (EvaluationException e) {
210
                return e.getErrorEval();
211
            }
212
            catch (IllegalArgumentException e) {
213
                return ErrorEval.VALUE_INVALID;
214
            }
215
                
216
                
217
            ValueEval vals[] = new ValueEval[result.length];
218
            
219
            for (int idx = 0; idx < result.length; idx++) {
220
                vals[idx] = new NumberEval(result[idx]);
221
            }
222
            
223
            if (result.length == 1)
224
                return vals[0];
225
            else {
226
                return new CacheAreaEval(((AreaEval) arg0).getFirstRow(), ((AreaEval) arg0).getFirstColumn(), 
227
                        ((AreaEval) arg0).getFirstRow() + height - 1, 
228
                        ((AreaEval) arg0).getFirstColumn() + width - 1, vals);
229
            }
230
      
231
        }
232
        
233
        protected abstract double[][] evaluate(double[][] d1, double[][] d2) throws EvaluationException;
234
        protected abstract double[] collectValues(ValueEval arg) throws EvaluationException;
235
236
    }
237
    
238
    public static final class MutableValueCollector extends MultiOperandNumericFunction {
239
        public MutableValueCollector(boolean isReferenceBoolCounted, boolean isBlankCounted) {
240
            super(isReferenceBoolCounted, isBlankCounted);
241
        }
242
        public double[] collectValues(ValueEval...operands) throws EvaluationException {
243
            return getNumberArray(operands);
244
        }
245
        protected double evaluate(double[] values) {
246
            throw new IllegalStateException("should not be called");
247
        }
248
    }
249
    
250
    public static final Function MINVERSE = new OneArrayArg() {
251
        private final MutableValueCollector instance = new MutableValueCollector(false, false);
252
        
253
        protected double[] collectValues(ValueEval arg) throws EvaluationException {
254
            double[] values = instance.collectValues(arg);
255
            
256
            /* handle case where MDETERM is operating on an array that that is not completely filled*/
257
            if (arg instanceof AreaEval && values.length == 1)
258
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
259
            
260
            return values;
261
        }
262
        
263
        protected double[][] evaluate(double[][] d1) throws EvaluationException {
264
            if (d1.length != d1[0].length) {
265
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
266
            }
267
            
268
            Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1);
269
            return MatrixUtils.inverse(temp).getData();
270
        }
271
    };
272
    
273
    public static final Function TRANSPOSE = new OneArrayArg() {
274
        private final MutableValueCollector instance = new MutableValueCollector(false, true);
275
        
276
        protected double[] collectValues(ValueEval arg) throws EvaluationException {
277
            return instance.collectValues(arg);
278
        }
279
        
280
        protected double[][] evaluate(double[][] d1) throws EvaluationException {
281
            
282
            Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1);
283
            return temp.transpose().getData();
284
        }
285
    };
286
    
287
    public static final Function MDETERM = new OneArrayArg() {
288
        private final MutableValueCollector instance = new MutableValueCollector(false, false);
289
        
290
        protected double[] collectValues(ValueEval arg) throws EvaluationException {
291
            double[] values = instance.collectValues(arg);
292
            
293
            /* handle case where MDETERM is operating on an array that that is not completely filled*/
294
            if (arg instanceof AreaEval && values.length == 1)
295
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
296
            
297
            return instance.collectValues(arg);
298
        }
299
        
300
        protected double[][] evaluate(double[][] d1) throws EvaluationException {
301
            if (d1.length != d1[0].length) {
302
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
303
            }
304
            
305
            double result[][] = new double[1][1];
306
            Array2DRowRealMatrix temp = new Array2DRowRealMatrix(d1);
307
            result[0][0] = (new LUDecomposition(temp)).getDeterminant();
308
            return result;
309
        }
310
    };
311
    
312
    public static final Function MMULT = new TwoArrayArg() {
313
        private final MutableValueCollector instance = new MutableValueCollector(false, false);
314
        
315
        protected double[] collectValues(ValueEval arg) throws EvaluationException {
316
            double values[] = instance.collectValues(arg);
317
            
318
            /* handle case where MMULT is operating on an array that is not completely filled*/
319
            if (arg instanceof AreaEval && values.length == 1)
320
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
321
            
322
            return values;
323
        }
324
        
325
        protected double[][] evaluate(double[][] d1, double[][] d2) throws EvaluationException{
326
            Array2DRowRealMatrix first = new Array2DRowRealMatrix(d1);
327
            Array2DRowRealMatrix second = new Array2DRowRealMatrix(d2);
328
            
329
            try {
330
                MatrixUtils.checkMultiplicationCompatible(first, second);
331
            }
332
            catch (DimensionMismatchException e) {
333
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
334
            }
335
                
336
            return first.multiply(second).getData();    
337
        }
338
    };
339
}
(-)a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationCell.java (+12 lines)
Lines 20-25 package org.apache.poi.xssf.streaming; Link Here
20
import org.apache.poi.ss.formula.EvaluationCell;
20
import org.apache.poi.ss.formula.EvaluationCell;
21
import org.apache.poi.ss.formula.EvaluationSheet;
21
import org.apache.poi.ss.formula.EvaluationSheet;
22
import org.apache.poi.ss.usermodel.CellType;
22
import org.apache.poi.ss.usermodel.CellType;
23
import org.apache.poi.ss.util.CellRangeAddress;
23
import org.apache.poi.util.Internal;
24
import org.apache.poi.util.Internal;
24
25
25
/**
26
/**
Lines 96-101 final class SXSSFEvaluationCell implements EvaluationCell { Link Here
96
    public String getStringCellValue() {
97
    public String getStringCellValue() {
97
        return _cell.getRichStringCellValue().getString();
98
        return _cell.getRichStringCellValue().getString();
98
    }
99
    }
100
    
101
    @Override
102
	public CellRangeAddress getArrayFormulaRange() {
103
		return _cell.getArrayFormulaRange();
104
	}
105
	
106
	@Override
107
	public boolean isPartOfArrayFormulaGroup() {
108
		return _cell.isPartOfArrayFormulaGroup();
109
	}
110
	
99
    /**
111
    /**
100
     * Will return {@link CellType} in a future version of POI.
112
     * Will return {@link CellType} in a future version of POI.
101
     * For forwards compatibility, do not hard-code cell type literals in your code.
113
     * For forwards compatibility, do not hard-code cell type literals in your code.
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (-3 / +6 lines)
Lines 483-491 public final class XSSFCell implements Cell { Link Here
483
        }
483
        }
484
484
485
        CTCellFormula f = _cell.getF();
485
        CTCellFormula f = _cell.getF();
486
        if (isPartOfArrayFormulaGroup() && f == null) {
486
        if (isPartOfArrayFormulaGroup()) {
487
            XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
487
            /* In an excel generated array formula, the formula property might be set, but the string is empty in slave cells */
488
            return cell.getCellFormula(fpb);
488
            if (f == null || f.getStringValue().isEmpty()) {
489
                XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
490
                return cell.getCellFormula(fpb);
491
            }
489
        }
492
        }
490
        if (f.getT() == STCellFormulaType.SHARED) {
493
        if (f.getT() == STCellFormulaType.SHARED) {
491
            return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb);
494
            return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb);
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationCell.java (+12 lines)
Lines 20-25 package org.apache.poi.xssf.usermodel; Link Here
20
import org.apache.poi.ss.formula.EvaluationCell;
20
import org.apache.poi.ss.formula.EvaluationCell;
21
import org.apache.poi.ss.formula.EvaluationSheet;
21
import org.apache.poi.ss.formula.EvaluationSheet;
22
import org.apache.poi.ss.usermodel.CellType;
22
import org.apache.poi.ss.usermodel.CellType;
23
import org.apache.poi.ss.util.CellRangeAddress;
23
import org.apache.poi.util.Internal;
24
import org.apache.poi.util.Internal;
24
25
25
/**
26
/**
Lines 97-102 final class XSSFEvaluationCell implements EvaluationCell { Link Here
97
	public String getStringCellValue() {
98
	public String getStringCellValue() {
98
		return _cell.getRichStringCellValue().getString();
99
		return _cell.getRichStringCellValue().getString();
99
	}
100
	}
101
	
102
	@Override
103
	public CellRangeAddress getArrayFormulaRange() {
104
		return _cell.getArrayFormulaRange();
105
	}
106
	
107
	@Override
108
	public boolean isPartOfArrayFormulaGroup() {
109
		return _cell.isPartOfArrayFormulaGroup();
110
	}
111
	
100
	/**
112
	/**
101
	 * Will return {@link CellType} in a future version of POI.
113
	 * Will return {@link CellType} in a future version of POI.
102
	 * For forwards compatibility, do not hard-code cell type literals in your code.
114
	 * For forwards compatibility, do not hard-code cell type literals in your code.
(-)a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestMatrixFormulasFromSpreadsheet.java (+226 lines)
Line 0 Link Here
1
package org.apache.poi.xssf.usermodel;
2
3
import static org.junit.Assert.assertEquals;
4
import static org.junit.Assert.assertNotNull;
5
import static org.junit.Assert.fail;
6
7
import java.util.ArrayList;
8
import java.util.Collection;
9
import java.util.List;
10
import java.util.Locale;
11
12
13
import org.apache.poi.ss.formula.eval.ErrorEval;
14
import org.apache.poi.ss.formula.functions.TestMathX;
15
import org.apache.poi.ss.usermodel.Cell;
16
import org.apache.poi.ss.usermodel.CellType;
17
import org.apache.poi.ss.usermodel.CellValue;
18
import org.apache.poi.ss.usermodel.FormulaEvaluator;
19
import org.apache.poi.ss.usermodel.Row;
20
import org.apache.poi.ss.usermodel.Sheet;
21
import org.apache.poi.util.LocaleUtil;
22
import org.apache.poi.xssf.XSSFTestDataSamples;
23
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
24
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
25
import org.junit.AfterClass;
26
import org.junit.Test;
27
import org.junit.runner.RunWith;
28
import org.junit.runners.Parameterized;
29
import org.junit.runners.Parameterized.Parameter;
30
import org.junit.runners.Parameterized.Parameters;
31
32
import junit.framework.AssertionFailedError;
33
34
@RunWith(Parameterized.class)
35
public final class TestMatrixFormulasFromSpreadsheet {
36
    
37
    private static XSSFWorkbook workbook;
38
    private static Sheet sheet;
39
    private static FormulaEvaluator evaluator;
40
    private static Locale userLocale;
41
    
42
    /*
43
     * Unlike TestFormulaFromSpreadsheet which this class is modified from, there is no
44
     * differentiation between operators and functions, if more functionality is implemented with
45
     * array formulas then it might be worth it to separate operators from functions
46
     * 
47
     * Also, output matrices are statically 3x3, if larger matrices wanted to be tested
48
     * then adding matrix size parameter would be useful and parsing would be based off that.
49
     */
50
    
51
    private static interface Navigator {
52
        /**
53
         * Name of the test spreadsheet (found in the standard test data folder)
54
         */
55
        String FILENAME = "MatrixFormulaEvalTestData.xlsx";
56
        /**
57
         * Row (zero-based) in the spreadsheet where operations start
58
         */
59
        int START_OPERATORS_ROW_INDEX = 1;
60
        /**
61
         * Column (zero-based) in the spreadsheet where operations start
62
         */
63
        int START_OPERATORS_COL_INDEX = 0;
64
        /**
65
         * Column (zero-based) in the spreadsheet where evaluations start
66
         */
67
        int START_RESULT_COL_INDEX = 7;
68
        /**
69
         * Column separation in the spreadsheet between evaluations and expected results
70
         */
71
        int COL_OFF_EXPECTED_RESULT = 3;
72
        /**
73
         * Row separation in the spreadsheet between operations
74
         */
75
        int ROW_OFF_NEXT_OP = 4;
76
        /**
77
         * Used to indicate when there are no more operations left
78
         */
79
        String END_OF_TESTS = "<END>";
80
        
81
    }
82
    
83
    /* Parameters for test case */
84
    @Parameter(0)
85
    public String targetFunctionName;
86
    @Parameter(1)
87
    public int formulasRowIdx;
88
    
89
    @AfterClass
90
    public static void closeResource() throws Exception {
91
        LocaleUtil.setUserLocale(userLocale);
92
        workbook.close();
93
    }
94
    
95
    /* generating parameter instances */
96
    @Parameters(name="{0}")
97
    public static Collection<Object[]> data() throws Exception {
98
        // Function "Text" uses custom-formats which are locale specific
99
        // can't set the locale on a per-testrun execution, as some settings have been
100
        // already set, when we would try to change the locale by then
101
        userLocale = LocaleUtil.getUserLocale();
102
        LocaleUtil.setUserLocale(Locale.ROOT);
103
        
104
        workbook = XSSFTestDataSamples.openSampleWorkbook(Navigator.FILENAME);
105
        sheet = workbook.getSheetAt(0);
106
        evaluator = new XSSFFormulaEvaluator(workbook);
107
        
108
        List<Object[]> data = new ArrayList<Object[]>();
109
        
110
        processFunctionGroup(data, Navigator.START_OPERATORS_ROW_INDEX, null);
111
        
112
        return data;
113
    }
114
    
115
    /**
116
     * @param startRowIndex row index in the spreadsheet where the first function/operator is found
117
     * @param testFocusFunctionName name of a single function/operator to test alone.
118
     * Typically pass <code>null</code> to test all functions
119
     */
120
    private static void processFunctionGroup(List<Object[]> data, int startRowIndex, String testFocusFunctionName) {
121
        for (int rowIndex = startRowIndex; true; rowIndex += Navigator.ROW_OFF_NEXT_OP) {
122
            Row r = sheet.getRow(rowIndex);
123
            String targetFunctionName = getTargetFunctionName(r);
124
            assertNotNull("Test spreadsheet cell empty on row ("
125
                    + (rowIndex) + "). Expected function name or '"
126
                    + Navigator.END_OF_TESTS + "'", targetFunctionName);
127
            if(targetFunctionName.equals(Navigator.END_OF_TESTS)) {
128
                // found end of functions list
129
                break;
130
            }
131
            if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) {
132
                data.add(new Object[]{targetFunctionName, rowIndex});
133
            }
134
        }
135
    }
136
    
137
    @Test
138
    public void processFunctionRow() {
139
140
       int endColNum = Navigator.START_RESULT_COL_INDEX + Navigator.COL_OFF_EXPECTED_RESULT;
141
142
       for (int rowNum = formulasRowIdx; rowNum < formulasRowIdx + Navigator.ROW_OFF_NEXT_OP - 1; rowNum++) {
143
           for (int colNum = Navigator.START_RESULT_COL_INDEX; colNum < endColNum; colNum++) {
144
               Row r = sheet.getRow(rowNum);
145
               
146
               /* mainly to escape row failures on MDETERM which only returns a scalar */
147
               if (r == null) {
148
                   continue;
149
               }
150
               
151
               Cell c = sheet.getRow(rowNum).getCell(colNum);
152
               
153
               if (c == null || c.getCellTypeEnum() != CellType.FORMULA) {
154
                   continue;
155
               }
156
    
157
               CellValue actValue = evaluator.evaluate(c);
158
               Cell expValue = sheet.getRow(rowNum).getCell(colNum + Navigator.COL_OFF_EXPECTED_RESULT);
159
    
160
               String msg = String.format(Locale.ROOT, "Function '%s': Formula: %s @ %d:%d"
161
                       , targetFunctionName, c.getCellFormula(), rowNum, colNum);
162
    
163
               assertNotNull(msg + " - Bad setup data expected value is null", expValue);
164
               assertNotNull(msg + " - actual value was null", actValue);
165
    
166
               final CellType cellType = expValue.getCellTypeEnum();
167
               switch (cellType) {
168
                   case BLANK:
169
                       assertEquals(msg, CellType.BLANK, actValue.getCellTypeEnum());
170
                       break;
171
                   case BOOLEAN:
172
                       assertEquals(msg, CellType.BOOLEAN, actValue.getCellTypeEnum());
173
                       assertEquals(msg, expValue.getBooleanCellValue(), actValue.getBooleanValue());
174
                       break;
175
                   case ERROR:
176
                       assertEquals(msg, CellType.ERROR, actValue.getCellTypeEnum());
177
                       assertEquals(msg, ErrorEval.getText(expValue.getErrorCellValue()), ErrorEval.getText(actValue.getErrorValue()));
178
                       break;
179
                   case FORMULA: // will never be used, since we will call method after formula evaluation
180
                       fail("Cannot expect formula as result of formula evaluation: " + msg);
181
                   case NUMERIC:
182
                       assertEquals(msg, CellType.NUMERIC, actValue.getCellTypeEnum());
183
                       TestMathX.assertEquals(msg, expValue.getNumericCellValue(), actValue.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
184
                       break;
185
                   case STRING:
186
                       assertEquals(msg, CellType.STRING, actValue.getCellTypeEnum());
187
                       assertEquals(msg, expValue.getRichStringCellValue().getString(), actValue.getStringValue());
188
                       break;
189
                   default:
190
                       fail("Unexpected cell type: " + cellType);
191
               }
192
           }
193
       }
194
   }
195
    
196
    /**
197
     * @return <code>null</code> if cell is missing, empty or blank
198
     */
199
    private static String getTargetFunctionName(Row r) {
200
        if(r == null) {
201
            System.err.println("Warning - given null row, can't figure out function name");
202
            return null;
203
        }
204
        Cell cell = r.getCell(Navigator.START_OPERATORS_COL_INDEX);
205
        System.err.println(String.valueOf(Navigator.START_OPERATORS_COL_INDEX));
206
        if(cell == null) {
207
            System.err.println("Warning - Row " + r.getRowNum() + " has no cell " + Navigator.START_OPERATORS_COL_INDEX + ", can't figure out function name");
208
            return null;
209
        }
210
        if(cell.getCellTypeEnum() == CellType.BLANK) {
211
            return null;
212
        }
213
        if(cell.getCellTypeEnum() == CellType.STRING) {
214
            return cell.getRichStringCellValue().getString();
215
        }
216
217
        throw new AssertionFailedError("Bad cell type for 'function name' column: ("
218
                + cell.getCellTypeEnum() + ") row (" + (r.getRowNum() +1) + ")");
219
    }
220
    
221
    
222
    
223
    
224
    
225
226
}

Return to bug 61469