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

(-)C:\josh\client\poi\svn\trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (-75 / +1 lines)
Lines 24-94 Link Here
24
24
25
import org.apache.poi.hssf.model.FormulaParser;
25
import org.apache.poi.hssf.model.FormulaParser;
26
import org.apache.poi.hssf.model.Workbook;
26
import org.apache.poi.hssf.model.Workbook;
27
import org.apache.poi.hssf.record.formula.AddPtg;
28
import org.apache.poi.hssf.record.formula.Area3DPtg;
27
import org.apache.poi.hssf.record.formula.Area3DPtg;
29
import org.apache.poi.hssf.record.formula.AreaPtg;
28
import org.apache.poi.hssf.record.formula.AreaPtg;
30
import org.apache.poi.hssf.record.formula.AttrPtg;
29
import org.apache.poi.hssf.record.formula.AttrPtg;
31
import org.apache.poi.hssf.record.formula.BoolPtg;
30
import org.apache.poi.hssf.record.formula.BoolPtg;
32
import org.apache.poi.hssf.record.formula.ConcatPtg;
33
import org.apache.poi.hssf.record.formula.ControlPtg;
31
import org.apache.poi.hssf.record.formula.ControlPtg;
34
import org.apache.poi.hssf.record.formula.DividePtg;
35
import org.apache.poi.hssf.record.formula.EqualPtg;
36
import org.apache.poi.hssf.record.formula.FuncPtg;
37
import org.apache.poi.hssf.record.formula.FuncVarPtg;
38
import org.apache.poi.hssf.record.formula.GreaterEqualPtg;
39
import org.apache.poi.hssf.record.formula.GreaterThanPtg;
40
import org.apache.poi.hssf.record.formula.IntPtg;
32
import org.apache.poi.hssf.record.formula.IntPtg;
41
import org.apache.poi.hssf.record.formula.LessEqualPtg;
42
import org.apache.poi.hssf.record.formula.LessThanPtg;
43
import org.apache.poi.hssf.record.formula.MemErrPtg;
33
import org.apache.poi.hssf.record.formula.MemErrPtg;
44
import org.apache.poi.hssf.record.formula.MissingArgPtg;
34
import org.apache.poi.hssf.record.formula.MissingArgPtg;
45
import org.apache.poi.hssf.record.formula.MultiplyPtg;
46
import org.apache.poi.hssf.record.formula.NamePtg;
35
import org.apache.poi.hssf.record.formula.NamePtg;
47
import org.apache.poi.hssf.record.formula.NameXPtg;
36
import org.apache.poi.hssf.record.formula.NameXPtg;
48
import org.apache.poi.hssf.record.formula.NotEqualPtg;
49
import org.apache.poi.hssf.record.formula.NumberPtg;
37
import org.apache.poi.hssf.record.formula.NumberPtg;
50
import org.apache.poi.hssf.record.formula.OperationPtg;
38
import org.apache.poi.hssf.record.formula.OperationPtg;
51
import org.apache.poi.hssf.record.formula.ParenthesisPtg;
39
import org.apache.poi.hssf.record.formula.ParenthesisPtg;
52
import org.apache.poi.hssf.record.formula.PowerPtg;
53
import org.apache.poi.hssf.record.formula.Ptg;
40
import org.apache.poi.hssf.record.formula.Ptg;
54
import org.apache.poi.hssf.record.formula.Ref3DPtg;
41
import org.apache.poi.hssf.record.formula.Ref3DPtg;
55
import org.apache.poi.hssf.record.formula.ReferencePtg;
42
import org.apache.poi.hssf.record.formula.ReferencePtg;
56
import org.apache.poi.hssf.record.formula.StringPtg;
43
import org.apache.poi.hssf.record.formula.StringPtg;
57
import org.apache.poi.hssf.record.formula.SubtractPtg;
58
import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
59
import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
60
import org.apache.poi.hssf.record.formula.UnionPtg;
44
import org.apache.poi.hssf.record.formula.UnionPtg;
61
import org.apache.poi.hssf.record.formula.UnknownPtg;
45
import org.apache.poi.hssf.record.formula.UnknownPtg;
62
import org.apache.poi.hssf.record.formula.eval.AddEval;
63
import org.apache.poi.hssf.record.formula.eval.Area2DEval;
46
import org.apache.poi.hssf.record.formula.eval.Area2DEval;
64
import org.apache.poi.hssf.record.formula.eval.Area3DEval;
47
import org.apache.poi.hssf.record.formula.eval.Area3DEval;
65
import org.apache.poi.hssf.record.formula.eval.AreaEval;
48
import org.apache.poi.hssf.record.formula.eval.AreaEval;
66
import org.apache.poi.hssf.record.formula.eval.BlankEval;
49
import org.apache.poi.hssf.record.formula.eval.BlankEval;
67
import org.apache.poi.hssf.record.formula.eval.BoolEval;
50
import org.apache.poi.hssf.record.formula.eval.BoolEval;
68
import org.apache.poi.hssf.record.formula.eval.ConcatEval;
69
import org.apache.poi.hssf.record.formula.eval.DivideEval;
70
import org.apache.poi.hssf.record.formula.eval.EqualEval;
71
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
51
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
72
import org.apache.poi.hssf.record.formula.eval.Eval;
52
import org.apache.poi.hssf.record.formula.eval.Eval;
73
import org.apache.poi.hssf.record.formula.eval.FuncVarEval;
74
import org.apache.poi.hssf.record.formula.eval.FunctionEval;
53
import org.apache.poi.hssf.record.formula.eval.FunctionEval;
75
import org.apache.poi.hssf.record.formula.eval.GreaterEqualEval;
76
import org.apache.poi.hssf.record.formula.eval.GreaterThanEval;
77
import org.apache.poi.hssf.record.formula.eval.LessEqualEval;
78
import org.apache.poi.hssf.record.formula.eval.LessThanEval;
79
import org.apache.poi.hssf.record.formula.eval.MultiplyEval;
80
import org.apache.poi.hssf.record.formula.eval.NameEval;
54
import org.apache.poi.hssf.record.formula.eval.NameEval;
81
import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
82
import org.apache.poi.hssf.record.formula.eval.NumberEval;
55
import org.apache.poi.hssf.record.formula.eval.NumberEval;
83
import org.apache.poi.hssf.record.formula.eval.OperationEval;
56
import org.apache.poi.hssf.record.formula.eval.OperationEval;
84
import org.apache.poi.hssf.record.formula.eval.PowerEval;
85
import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
57
import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
86
import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
58
import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
87
import org.apache.poi.hssf.record.formula.eval.RefEval;
59
import org.apache.poi.hssf.record.formula.eval.RefEval;
88
import org.apache.poi.hssf.record.formula.eval.StringEval;
60
import org.apache.poi.hssf.record.formula.eval.StringEval;
89
import org.apache.poi.hssf.record.formula.eval.SubtractEval;
90
import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval;
91
import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
92
import org.apache.poi.hssf.record.formula.eval.ValueEval;
61
import org.apache.poi.hssf.record.formula.eval.ValueEval;
93
62
94
/**
63
/**
Lines 98-105 Link Here
98
public class HSSFFormulaEvaluator {
67
public class HSSFFormulaEvaluator {
99
                
68
                
100
    // params to lookup the right constructor using reflection
69
    // params to lookup the right constructor using reflection
101
    private static final Class[] OPERATION_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
102
103
    private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
70
    private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
104
71
105
    private static final Class[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval[].class };
72
    private static final Class[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval[].class };
Lines 111-118 Link Here
111
    // Maps for mapping *Eval to *Ptg
78
    // Maps for mapping *Eval to *Ptg
112
    private static final Map VALUE_EVALS_MAP = new HashMap();
79
    private static final Map VALUE_EVALS_MAP = new HashMap();
113
80
114
    private static final Map OPERATION_EVALS_MAP = new HashMap();
115
116
    /*
81
    /*
117
     * Following is the mapping between the Ptg tokens returned 
82
     * Following is the mapping between the Ptg tokens returned 
118
     * by the FormulaParser and the *Eval classes that are used 
83
     * by the FormulaParser and the *Eval classes that are used 
Lines 124-149 Link Here
124
        VALUE_EVALS_MAP.put(NumberPtg.class, NumberEval.class);
89
        VALUE_EVALS_MAP.put(NumberPtg.class, NumberEval.class);
125
        VALUE_EVALS_MAP.put(StringPtg.class, StringEval.class);
90
        VALUE_EVALS_MAP.put(StringPtg.class, StringEval.class);
126
91
127
        OPERATION_EVALS_MAP.put(AddPtg.class, AddEval.class);
128
        OPERATION_EVALS_MAP.put(ConcatPtg.class, ConcatEval.class);
129
        OPERATION_EVALS_MAP.put(DividePtg.class, DivideEval.class);
130
        OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
131
        //OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO: check
132
        // this
133
        OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); // TODO:
134
                                                                   // check this
135
        OPERATION_EVALS_MAP.put(FuncVarPtg.class, FuncVarEval.class);
136
        OPERATION_EVALS_MAP.put(GreaterEqualPtg.class, GreaterEqualEval.class);
137
        OPERATION_EVALS_MAP.put(GreaterThanPtg.class, GreaterThanEval.class);
138
        OPERATION_EVALS_MAP.put(LessEqualPtg.class, LessEqualEval.class);
139
        OPERATION_EVALS_MAP.put(LessThanPtg.class, LessThanEval.class);
140
        OPERATION_EVALS_MAP.put(MultiplyPtg.class, MultiplyEval.class);
141
        OPERATION_EVALS_MAP.put(NotEqualPtg.class, NotEqualEval.class);
142
        OPERATION_EVALS_MAP.put(PowerPtg.class, PowerEval.class);
143
        OPERATION_EVALS_MAP.put(SubtractPtg.class, SubtractEval.class);
144
        OPERATION_EVALS_MAP.put(UnaryMinusPtg.class, UnaryMinusEval.class);
145
        OPERATION_EVALS_MAP.put(UnaryPlusPtg.class, UnaryPlusEval.class);
146
147
    }
92
    }
148
93
149
    
94
    
Lines 402-408 Link Here
402
                if (optg instanceof AttrPtg) { continue; }
347
                if (optg instanceof AttrPtg) { continue; }
403
                if (optg instanceof UnionPtg) { continue; }
348
                if (optg instanceof UnionPtg) { continue; }
404
349
405
                OperationEval operation = (OperationEval) getOperationEvalForPtg(optg);
350
                OperationEval operation = OperationEvaluatorFactory.create(optg);
406
351
407
                int numops = operation.getNumberOfOperands();
352
                int numops = operation.getNumberOfOperands();
408
                Eval[] ops = new Eval[numops];
353
                Eval[] ops = new Eval[numops];
Lines 558-582 Link Here
558
    }
503
    }
559
504
560
    /**
505
    /**
561
     * returns the OperationEval concrete impl instance corresponding
562
     * to the suplied operationPtg
563
     * @param ptg
564
     */
565
    protected static Eval getOperationEvalForPtg(OperationPtg ptg) {
566
        Eval retval = null;
567
568
        Class clazz = (Class) OPERATION_EVALS_MAP.get(ptg.getClass());
569
        try {
570
            Constructor constructor = clazz.getConstructor(OPERATION_CONSTRUCTOR_CLASS_ARRAY);
571
            retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
572
        }
573
        catch (Exception e) {
574
            throw new RuntimeException("Fatal Error: ", e);
575
        }
576
        return retval;
577
    }
578
579
    /**
580
     * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
506
     * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
581
     * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
507
     * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
582
     * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
508
     * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
(-)C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (-131 / +130 lines)
Lines 15-21 Link Here
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
17
18
19
package org.apache.poi.hssf.record.formula.eval;
18
package org.apache.poi.hssf.record.formula.eval;
20
19
21
import java.io.FileInputStream;
20
import java.io.FileInputStream;
Lines 59-94 Link Here
59
		 * Name of the test spreadsheet (found in the standard test data folder)
58
		 * Name of the test spreadsheet (found in the standard test data folder)
60
		 */
59
		 */
61
		public final static String FILENAME = "FormulaEvalTestData.xls";
60
		public final static String FILENAME = "FormulaEvalTestData.xls";
62
	    /**
61
		/**
63
	     * Row (zero-based) in the test spreadsheet where the operator examples start.
62
		 * Row (zero-based) in the test spreadsheet where the operator examples start.
64
	     */
63
		 */
65
		public static final int START_OPERATORS_ROW_INDEX = 22; // Row '23'
64
		public static final int START_OPERATORS_ROW_INDEX = 22; // Row '23'
66
	    /**
65
		/**
67
	     * Row (zero-based) in the test spreadsheet where the function examples start.
66
		 * Row (zero-based) in the test spreadsheet where the function examples start.
68
	     */
67
		 */
69
		public static final int START_FUNCTIONS_ROW_INDEX = 83; // Row '84' 
68
		public static final int START_FUNCTIONS_ROW_INDEX = 87; // Row '88' 
70
		/** 
69
		/** 
71
		 * Index of the column that contains the function names
70
		 * Index of the column that contains the function names
72
		 */
71
		 */
73
	    public static final short COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B'
72
		public static final short COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B'
74
	
73
	
75
	    /**
74
		/**
76
	     * Used to indicate when there are no more functions left
75
		 * Used to indicate when there are no more functions left
77
	     */
76
		 */
78
		public static final String FUNCTION_NAMES_END_SENTINEL = "<END-OF-FUNCTIONS>";
77
		public static final String FUNCTION_NAMES_END_SENTINEL = "<END-OF-FUNCTIONS>";
79
	
78
	
80
		/**
79
		/**
81
		 * Index of the column where the test values start (for each function)
80
		 * Index of the column where the test values start (for each function)
82
		 */
81
		 */
83
	    public static final short COLUMN_INDEX_FIRST_TEST_VALUE = 3; // Column 'D'
82
		public static final short COLUMN_INDEX_FIRST_TEST_VALUE = 3; // Column 'D'
84
	    
83
		
85
	    /**
84
		/**
86
	     * Each function takes 4 rows in the test spreadsheet 
85
		 * Each function takes 4 rows in the test spreadsheet 
87
	     */
86
		 */
88
		public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4;
87
		public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4;
89
	}
88
	}
90
89
91
    private HSSFWorkbook workbook;
90
	private HSSFWorkbook workbook;
92
	private HSSFSheet sheet;
91
	private HSSFSheet sheet;
93
	// Note - multiple failures are aggregated before ending.  
92
	// Note - multiple failures are aggregated before ending.  
94
	// If one or more functions fail, a single AssertionFailedError is thrown at the end
93
	// If one or more functions fail, a single AssertionFailedError is thrown at the end
Lines 97-234 Link Here
97
	private int _evaluationFailureCount;
96
	private int _evaluationFailureCount;
98
	private int _evaluationSuccessCount;
97
	private int _evaluationSuccessCount;
99
98
100
    private static final HSSFCell getExpectedValueCell(HSSFRow row, short columnIndex) {
99
	private static final HSSFCell getExpectedValueCell(HSSFRow row, short columnIndex) {
101
    	if (row == null) {
100
		if (row == null) {
102
    		return null;
101
			return null;
103
    	}
102
		}
104
    	return row.getCell(columnIndex);
103
		return row.getCell(columnIndex);
105
    }
104
	}
106
105
107
106
108
    private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) {
107
	private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) {
109
        if (expected == null) {
108
		if (expected == null) {
110
			throw new AssertionFailedError(msg + " - Bad setup data expected value is null");
109
			throw new AssertionFailedError(msg + " - Bad setup data expected value is null");
111
		}
110
		}
112
		if(actual == null) {
111
		if(actual == null) {
113
			throw new AssertionFailedError(msg + " - actual value was null");
112
			throw new AssertionFailedError(msg + " - actual value was null");
114
		}
113
		}
115
        
114
		
116
		if (expected.getCellType() == HSSFCell.CELL_TYPE_STRING) {
115
		if (expected.getCellType() == HSSFCell.CELL_TYPE_STRING) {
117
		    String value = expected.getRichStringCellValue().getString();
116
			String value = expected.getRichStringCellValue().getString();
118
		    if (value.startsWith("#")) {
117
			if (value.startsWith("#")) {
119
		    	// TODO - this code never called
118
				// TODO - this code never called
120
		        expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
119
				expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
121
		        // expected.setCellErrorValue(...?);
120
				// expected.setCellErrorValue(...?);
122
		    }
121
			}
123
		}
122
		}
124
		
123
		
125
		switch (expected.getCellType()) {
124
		switch (expected.getCellType()) {
126
			case HSSFCell.CELL_TYPE_BLANK:
125
			case HSSFCell.CELL_TYPE_BLANK:
127
			    assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType());
126
				assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType());
128
			    break;
127
				break;
129
			case HSSFCell.CELL_TYPE_BOOLEAN:
128
			case HSSFCell.CELL_TYPE_BOOLEAN:
130
			    assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN, actual.getCellType());
129
				assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN, actual.getCellType());
131
			    assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue());
130
				assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue());
132
			    break;
131
				break;
133
			case HSSFCell.CELL_TYPE_ERROR:
132
			case HSSFCell.CELL_TYPE_ERROR:
134
			    assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType());
133
				assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType());
135
			    if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values
134
				if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values
136
			    	assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue());
135
					assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue());
137
			    }
136
				}
138
			    break;
137
				break;
139
			case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
138
			case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
140
			    throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg);
139
				throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg);
141
			case HSSFCell.CELL_TYPE_NUMERIC:
140
			case HSSFCell.CELL_TYPE_NUMERIC:
142
			    assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType());
141
				assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType());
143
			    TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
142
				TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
144
//	            double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
143
//				double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
145
//	            double pctExpected = Math.abs(0.00001*expected.getNumericCellValue());
144
//				double pctExpected = Math.abs(0.00001*expected.getNumericCellValue());
146
//	            assertTrue(msg, delta <= pctExpected);
145
//				assertTrue(msg, delta <= pctExpected);
147
			    break;
146
				break;
148
			case HSSFCell.CELL_TYPE_STRING:
147
			case HSSFCell.CELL_TYPE_STRING:
149
			    assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType());
148
				assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType());
150
			    assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString());
149
				assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString());
151
			    break;
150
				break;
152
		}
151
		}
153
    }
152
	}
154
153
155
154
156
	protected void setUp() throws Exception {
155
	protected void setUp() throws Exception {
157
        if (workbook == null) {
156
		if (workbook == null) {
158
        	String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME;
157
			String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME;
159
            FileInputStream fin = new FileInputStream( filePath );
158
			FileInputStream fin = new FileInputStream( filePath );
160
            workbook = new HSSFWorkbook( fin );
159
			workbook = new HSSFWorkbook( fin );
161
            sheet = workbook.getSheetAt( 0 );
160
			sheet = workbook.getSheetAt( 0 );
162
          }
161
		  }
163
        _functionFailureCount = 0;
162
		_functionFailureCount = 0;
164
        _functionSuccessCount = 0;
163
		_functionSuccessCount = 0;
165
        _evaluationFailureCount = 0;
164
		_evaluationFailureCount = 0;
166
        _evaluationSuccessCount = 0;
165
		_evaluationSuccessCount = 0;
167
    }
166
	}
168
    
167
	
169
    public void testFunctionsFromTestSpreadsheet() {
168
	public void testFunctionsFromTestSpreadsheet() {
170
        
169
		
171
        processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null);
170
		processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null);
172
        processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null);
171
		processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null);
173
        // example for debugging individual functions/operators:
172
		// example for debugging individual functions/operators:
174
//        processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval");
173
//		processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval");
175
//        processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE");
174
//		processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE");
176
        
175
		
177
        // confirm results
176
		// confirm results
178
    	String successMsg = "There were " 
177
		String successMsg = "There were " 
179
    			+ _evaluationSuccessCount + " successful evaluation(s) and "
178
				+ _evaluationSuccessCount + " successful evaluation(s) and "
180
				+ _functionSuccessCount + " function(s) without error";
179
				+ _functionSuccessCount + " function(s) without error";
181
 		if(_functionFailureCount > 0) {
180
 		if(_functionFailureCount > 0) {
182
			String msg = _functionFailureCount + " function(s) failed in "
181
			String msg = _functionFailureCount + " function(s) failed in "
183
			+ _evaluationFailureCount + " evaluation(s).  " + successMsg;
182
			+ _evaluationFailureCount + " evaluation(s).  " + successMsg;
184
        	throw new AssertionFailedError(msg);
183
			throw new AssertionFailedError(msg);
185
        }
184
		}
186
 		if(false) { // normally no output for successful tests
185
 		if(false) { // normally no output for successful tests
187
 			System.out.println(getClass().getName() + ": " + successMsg);
186
 			System.out.println(getClass().getName() + ": " + successMsg);
188
 		}
187
 		}
189
	}
188
	}
190
189
191
    /**
190
	/**
192
     * @param startRowIndex row index in the spreadsheet where the first function/operator is found 
191
	 * @param startRowIndex row index in the spreadsheet where the first function/operator is found 
193
     * @param testFocusFunctionName name of a single function/operator to test alone. 
192
	 * @param testFocusFunctionName name of a single function/operator to test alone. 
194
     * Typically pass <code>null</code> to test all functions
193
	 * Typically pass <code>null</code> to test all functions
195
     */
194
	 */
196
	private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
195
	private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
197
 
196
 
198
		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
197
		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
199
198
200
        int rowIndex = startRowIndex;
199
		int rowIndex = startRowIndex;
201
        while (true) {
200
		while (true) {
202
            HSSFRow r = sheet.getRow(rowIndex);
201
			HSSFRow r = sheet.getRow(rowIndex);
203
            String targetFunctionName = getTargetFunctionName(r);
202
			String targetFunctionName = getTargetFunctionName(r);
204
        	if(targetFunctionName == null) {
203
			if(targetFunctionName == null) {
205
        		throw new AssertionFailedError("Test spreadsheet cell empty on row (" 
204
				throw new AssertionFailedError("Test spreadsheet cell empty on row (" 
206
        				+ (rowIndex+1) + "). Expected function name or '"
205
						+ (rowIndex+1) + "). Expected function name or '"
207
        				+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
206
						+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
208
        	}
207
			}
209
        	if(targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) {
208
			if(targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) {
210
        		// found end of functions list
209
				// found end of functions list
211
        		break;
210
				break;
212
        	}
211
			}
213
        	if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) {
212
			if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) {
214
        		
213
				
215
	        	// expected results are on the row below
214
				// expected results are on the row below
216
	            HSSFRow expectedValuesRow = sheet.getRow(rowIndex + 1);
215
				HSSFRow expectedValuesRow = sheet.getRow(rowIndex + 1);
217
	            if(expectedValuesRow == null) {
216
				if(expectedValuesRow == null) {
218
	            	int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row
217
					int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row
219
	            	throw new AssertionFailedError("Missing expected values row for function '" 
218
					throw new AssertionFailedError("Missing expected values row for function '" 
220
	            			+ targetFunctionName + " (row " + missingRowNum + ")"); 
219
							+ targetFunctionName + " (row " + missingRowNum + ")"); 
221
	            }
220
				}
222
	            switch(processFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) {
221
				switch(processFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) {
223
	            	case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
222
					case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
224
	            	case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
223
					case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
225
	            	default:
224
					default:
226
	            		throw new RuntimeException("unexpected result");
225
						throw new RuntimeException("unexpected result");
227
	            	case Result.NO_EVALUATIONS_FOUND: // do nothing
226
					case Result.NO_EVALUATIONS_FOUND: // do nothing
228
	            }
227
				}
229
        	}
228
			}
230
            rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION;
229
			rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION;
231
        }
230
		}
232
	}
231
	}
233
232
234
	/**
233
	/**
Lines 236-251 Link Here
236
	 * @return a constant from the local Result class denoting whether there were any evaluation
235
	 * @return a constant from the local Result class denoting whether there were any evaluation
237
	 * cases, and whether they all succeeded.
236
	 * cases, and whether they all succeeded.
238
	 */
237
	 */
239
    private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, 
238
	private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, 
240
    		HSSFRow formulasRow, HSSFRow expectedValuesRow) {
239
			HSSFRow formulasRow, HSSFRow expectedValuesRow) {
241
    	
240
		
242
        int result = Result.NO_EVALUATIONS_FOUND; // so far
241
		int result = Result.NO_EVALUATIONS_FOUND; // so far
243
        short endcolnum = formulasRow.getLastCellNum();
242
		short endcolnum = formulasRow.getLastCellNum();
244
        evaluator.setCurrentRow(formulasRow);
243
		evaluator.setCurrentRow(formulasRow);
245
244
246
        // iterate across the row for all the evaluation cases
245
		// iterate across the row for all the evaluation cases
247
        for (short colnum=SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) {
246
		for (short colnum=SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) {
248
            HSSFCell c = formulasRow.getCell(colnum);
247
			HSSFCell c = formulasRow.getCell(colnum);
249
			if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
248
			if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
250
				continue;
249
				continue;
251
			}
250
			}
Lines 265-277 Link Here
265
				printShortStackTrace(System.err, e);
264
				printShortStackTrace(System.err, e);
266
				result = Result.SOME_EVALUATIONS_FAILED;
265
				result = Result.SOME_EVALUATIONS_FAILED;
267
			}
266
			}
268
        }
267
		}
269
 		return result;
268
 		return result;
270
	}
269
	}
271
270
272
    /**
271
	/**
273
     * Useful to keep output concise when expecting many failures to be reported by this test case
272
	 * Useful to keep output concise when expecting many failures to be reported by this test case
274
     */
273
	 */
275
	private static void printShortStackTrace(PrintStream ps, AssertionFailedError e) {
274
	private static void printShortStackTrace(PrintStream ps, AssertionFailedError e) {
276
		StackTraceElement[] stes = e.getStackTrace();
275
		StackTraceElement[] stes = e.getStackTrace();
277
		
276
		
Lines 304-311 Link Here
304
	}
303
	}
305
304
306
	/**
305
	/**
307
     * @return <code>null</code> if cell is missing, empty or blank
306
	 * @return <code>null</code> if cell is missing, empty or blank
308
     */
307
	 */
309
	private static String getTargetFunctionName(HSSFRow r) {
308
	private static String getTargetFunctionName(HSSFRow r) {
310
		if(r == null) {
309
		if(r == null) {
311
			System.err.println("Warning - given null row, can't figure out function name");
310
			System.err.println("Warning - given null row, can't figure out function name");
(-)C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java (+1 lines)
Lines 32-37 Link Here
32
		result.addTestSuite(TestCircularReferences.class);
32
		result.addTestSuite(TestCircularReferences.class);
33
		result.addTestSuite(TestExternalFunction.class);
33
		result.addTestSuite(TestExternalFunction.class);
34
		result.addTestSuite(TestFormulasFromSpreadsheet.class);
34
		result.addTestSuite(TestFormulasFromSpreadsheet.class);
35
		result.addTestSuite(TestPercentEval.class);
35
		result.addTestSuite(TestUnaryPlusEval.class);
36
		result.addTestSuite(TestUnaryPlusEval.class);
36
		return result;
37
		return result;
37
	}
38
	}

Return to bug 44608