Attachment #33262 for bug #58591

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




package org.apache.poi.ss.formula;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertSame;
import static org.junit.Assert.fail;

import java.io.IOException;

import org.junit.Ignore;
import org.junit.Test;

import org.apache.poi.hssf.HSSFTestDataSamples;

        
        wb.close();
    }
    
// Test IF-Equals Formula Evaluation (bug 58591)
    
    private Workbook testIFEqualsFormulaEvaluation_setup(String formula, int a1CellType) {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("IFEquals");
        Row row = sheet.createRow(0);
        Cell A1 = row.createCell(0);
        Cell B1 = row.createCell(1);
        Cell C1 = row.createCell(2);
        Cell D1 = row.createCell(3);
        
        switch (a1CellType) {
            case Cell.CELL_TYPE_NUMERIC:
                A1.setCellValue(1.0);
                // "A1=1" should return true
                break;
            case Cell.CELL_TYPE_STRING:
                A1.setCellValue("1");
                // "A1=1" should return false
                // "A1=\"1\"" should return true
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                A1.setCellValue(true);
                // "A1=1" should return true
                break;
            case Cell.CELL_TYPE_FORMULA:
                A1.setCellFormula("1");
                // "A1=1" should return true
                break;
            case Cell.CELL_TYPE_BLANK:
                A1.setCellValue((String) null);
                // "A1=1" should return false
                break;
        }
        B1.setCellValue(2.0);
        C1.setCellValue(3.0);
        D1.setCellFormula(formula);
        
        return wb;
    }
    
    private void testIFEqualsFormulaEvaluation_teardown(Workbook wb) {
        try {
            wb.close();
        } catch (final IOException e) {
            fail("Unable to close workbook");
        }
    }
    
    
    
    private void testIFEqualsFormulaEvaluation_evaluate(
        String formula, int cellType, String expectedFormula, double expectedResult) {
        Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
        Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
        
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        CellValue result = eval.evaluate(D1);
        
        // Call should not modify the contents
        assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
        assertEquals(expectedFormula, D1.getCellFormula());
        
        assertEquals(Cell.CELL_TYPE_NUMERIC, result.getCellType());
        assertEquals(expectedResult, result.getNumberValue(), EPSILON);
        
        testIFEqualsFormulaEvaluation_teardown(wb);
    }
    
    private void testIFEqualsFormulaEvaluation_eval(
            final String formula, final int cellType, final String expectedFormula, final double expectedValue) {
        testIFEqualsFormulaEvaluation_evaluate(formula, cellType, expectedFormula, expectedValue);
        testIFEqualsFormulaEvaluation_evaluateFormulaCell(formula, cellType, expectedFormula, expectedValue);
        testIFEqualsFormulaEvaluation_evaluateInCell(formula, cellType, expectedFormula, expectedValue);
        testIFEqualsFormulaEvaluation_evaluateAll(formula, cellType, expectedFormula, expectedValue);
        testIFEqualsFormulaEvaluation_evaluateAllFormulaCells(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_NumericLiteral() {
        final String formula = "IF(A1=1, 2, 3)";
        final int cellType = Cell.CELL_TYPE_NUMERIC;
        final String expectedFormula = "IF(A1=1,2,3)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_Numeric() {
        final String formula = "IF(A1=1, B1, C1)";
        final int cellType = Cell.CELL_TYPE_NUMERIC;
        final String expectedFormula = "IF(A1=1,B1,C1)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_String() {
        final String formula = "IF(A1=1, B1, C1)";
        final int cellType = Cell.CELL_TYPE_STRING;
        final String expectedFormula = "IF(A1=1,B1,C1)";
        final double expectedValue = 3.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_StringCompareToString() {
        final String formula = "IF(A1=\"1\", B1, C1)";
        final int cellType = Cell.CELL_TYPE_STRING;
        final String expectedFormula = "IF(A1=\"1\",B1,C1)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Ignore("this test currently fails")
    @Test
    public void testIFEqualsFormulaEvaluation_Boolean() {
        final String formula = "IF(A1=1, B1, C1)";
        final int cellType = Cell.CELL_TYPE_BOOLEAN;
        final String expectedFormula = "IF(A1=1,B1,C1)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_Formula() {
        final String formula = "IF(A1=1, B1, C1)";
        final int cellType = Cell.CELL_TYPE_FORMULA;
        final String expectedFormula = "IF(A1=1,B1,C1)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_Blank() {
        final String formula = "IF(A1=1, B1, C1)";
        final int cellType = Cell.CELL_TYPE_BLANK;
        final String expectedFormula = "IF(A1=1,B1,C1)";
        final double expectedValue = 3.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    @Test
    public void testIFEqualsFormulaEvaluation_BlankCompareToZero() {
        final String formula = "IF(A1=0, B1, C1)";
        final int cellType = Cell.CELL_TYPE_BLANK;
        final String expectedFormula = "IF(A1=0,B1,C1)";
        final double expectedValue = 2.0;
        testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
    }
    
    
    private void testIFEqualsFormulaEvaluation_evaluateFormulaCell(
            String formula, int cellType, String expectedFormula, double expectedResult) {
        Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
        Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
        
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        int resultCellType = eval.evaluateFormulaCell(D1);
        
        // Call should modify the contents, but leave the formula intact
        assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
        assertEquals(expectedFormula, D1.getCellFormula());
        assertEquals(Cell.CELL_TYPE_NUMERIC, resultCellType);
        assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
        assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
        
        testIFEqualsFormulaEvaluation_teardown(wb);
    }
    
    private void testIFEqualsFormulaEvaluation_evaluateInCell(
            String formula, int cellType, String expectedFormula, double expectedResult) {
        Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
        Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
        
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        Cell result = eval.evaluateInCell(D1);
        
        // Call should modify the contents and replace the formula with the result
        assertSame(D1, result); // returns the same cell that was provided as an argument so that calls can be chained.
        try {
            D1.getCellFormula();
            fail("cell formula should be overwritten with formula result");
        } catch (final IllegalStateException expected) { }
        assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCellType());
        assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
        
        testIFEqualsFormulaEvaluation_teardown(wb);
    }
    
    private void testIFEqualsFormulaEvaluation_evaluateAll(
            String formula, int cellType, String expectedFormula, double expectedResult) {
        Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
        Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
        
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
        eval.evaluateAll();
        
        // Call should modify the contents
        assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
        assertEquals(expectedFormula, D1.getCellFormula());
        
        assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
        assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
        
        testIFEqualsFormulaEvaluation_teardown(wb);
    }
    
    private void testIFEqualsFormulaEvaluation_evaluateAllFormulaCells(
            String formula, int cellType, String expectedFormula, double expectedResult) {
        Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
        Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
        
        HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        
        // Call should modify the contents
        assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
        // whitespace gets deleted because formula is parsed and re-rendered
        assertEquals(expectedFormula, D1.getCellFormula());
        
        assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
        assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
        
        testIFEqualsFormulaEvaluation_teardown(wb);
    }
}

Return to bug 58591

Lines 18-27 Link Here

(-)src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (+228 lines)
18	package org.apache.poi.ss.formula;	18	package org.apache.poi.ss.formula;
19		19
20	import static org.junit.Assert.assertEquals;	20	import static org.junit.Assert.assertEquals;
		21	import static org.junit.Assert.assertSame;
21	import static org.junit.Assert.fail;	22	import static org.junit.Assert.fail;
22		23
23	import java.io.IOException;	24	import java.io.IOException;
24		25
		26	import org.junit.Ignore;
25	import org.junit.Test;	27	import org.junit.Test;
26		28
27	import org.apache.poi.hssf.HSSFTestDataSamples;	29	import org.apache.poi.hssf.HSSFTestDataSamples;
Lines 300-303 Link Here
300		302
301	wb.close();	303	wb.close();
302	}	304	}
		305
		306	// Test IF-Equals Formula Evaluation (bug 58591)
		307
		308	private Workbook testIFEqualsFormulaEvaluation_setup(String formula, int a1CellType) {
		309	Workbook wb = new HSSFWorkbook();
		310	Sheet sheet = wb.createSheet("IFEquals");
		311	Row row = sheet.createRow(0);
		312	Cell A1 = row.createCell(0);
		313	Cell B1 = row.createCell(1);
		314	Cell C1 = row.createCell(2);
		315	Cell D1 = row.createCell(3);
		316
		317	switch (a1CellType) {
		318	case Cell.CELL_TYPE_NUMERIC:
		319	A1.setCellValue(1.0);
		320	// "A1=1" should return true
		321	break;
		322	case Cell.CELL_TYPE_STRING:
		323	A1.setCellValue("1");
		324	// "A1=1" should return false
		325	// "A1=\"1\"" should return true
		326	break;
		327	case Cell.CELL_TYPE_BOOLEAN:
		328	A1.setCellValue(true);
		329	// "A1=1" should return true
		330	break;
		331	case Cell.CELL_TYPE_FORMULA:
		332	A1.setCellFormula("1");
		333	// "A1=1" should return true
		334	break;
		335	case Cell.CELL_TYPE_BLANK:
		336	A1.setCellValue((String) null);
		337	// "A1=1" should return false
		338	break;
		339	}
		340	B1.setCellValue(2.0);
		341	C1.setCellValue(3.0);
		342	D1.setCellFormula(formula);
		343
		344	return wb;
		345	}
		346
		347	private void testIFEqualsFormulaEvaluation_teardown(Workbook wb) {
		348	try {
		349	wb.close();
		350	} catch (final IOException e) {
		351	fail("Unable to close workbook");
		352	}
		353	}
		354
		355
		356
		357	private void testIFEqualsFormulaEvaluation_evaluate(
		358	String formula, int cellType, String expectedFormula, double expectedResult) {
		359	Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
		360	Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
		361
		362	FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
		363	CellValue result = eval.evaluate(D1);
		364
		365	// Call should not modify the contents
		366	assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
		367	assertEquals(expectedFormula, D1.getCellFormula());
		368
369	assertEquals(Cell.CELL_TYPE_NUMERIC, result.getCellType());
370	assertEquals(expectedResult, result.getNumberValue(), EPSILON);
371
372	testIFEqualsFormulaEvaluation_teardown(wb);
373	}
374
375	private void testIFEqualsFormulaEvaluation_eval(
376	final String formula, final int cellType, final String expectedFormula, final double expectedValue) {
377	testIFEqualsFormulaEvaluation_evaluate(formula, cellType, expectedFormula, expectedValue);
378	testIFEqualsFormulaEvaluation_evaluateFormulaCell(formula, cellType, expectedFormula, expectedValue);
379	testIFEqualsFormulaEvaluation_evaluateInCell(formula, cellType, expectedFormula, expectedValue);
380	testIFEqualsFormulaEvaluation_evaluateAll(formula, cellType, expectedFormula, expectedValue);
381	testIFEqualsFormulaEvaluation_evaluateAllFormulaCells(formula, cellType, expectedFormula, expectedValue);
382	}
383
384	@Test
385	public void testIFEqualsFormulaEvaluation_NumericLiteral() {
386	final String formula = "IF(A1=1, 2, 3)";
387	final int cellType = Cell.CELL_TYPE_NUMERIC;
388	final String expectedFormula = "IF(A1=1,2,3)";
389	final double expectedValue = 2.0;
390	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
391	}
392
393	@Test
394	public void testIFEqualsFormulaEvaluation_Numeric() {
395	final String formula = "IF(A1=1, B1, C1)";
396	final int cellType = Cell.CELL_TYPE_NUMERIC;
397	final String expectedFormula = "IF(A1=1,B1,C1)";
398	final double expectedValue = 2.0;
399	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
400	}
401
402	@Test
403	public void testIFEqualsFormulaEvaluation_String() {
404	final String formula = "IF(A1=1, B1, C1)";
405	final int cellType = Cell.CELL_TYPE_STRING;
406	final String expectedFormula = "IF(A1=1,B1,C1)";
407	final double expectedValue = 3.0;
408	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
409	}
410
411	@Test
412	public void testIFEqualsFormulaEvaluation_StringCompareToString() {
413	final String formula = "IF(A1=\"1\", B1, C1)";
414	final int cellType = Cell.CELL_TYPE_STRING;
415	final String expectedFormula = "IF(A1=\"1\",B1,C1)";
416	final double expectedValue = 2.0;
417	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
418	}
419
420	@Ignore("this test currently fails")
421	@Test
422	public void testIFEqualsFormulaEvaluation_Boolean() {
423	final String formula = "IF(A1=1, B1, C1)";
424	final int cellType = Cell.CELL_TYPE_BOOLEAN;
425	final String expectedFormula = "IF(A1=1,B1,C1)";
426	final double expectedValue = 2.0;
427	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
428	}
429
430	@Test
431	public void testIFEqualsFormulaEvaluation_Formula() {
432	final String formula = "IF(A1=1, B1, C1)";
433	final int cellType = Cell.CELL_TYPE_FORMULA;
434	final String expectedFormula = "IF(A1=1,B1,C1)";
435	final double expectedValue = 2.0;
436	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
437	}
438
439	@Test
440	public void testIFEqualsFormulaEvaluation_Blank() {
441	final String formula = "IF(A1=1, B1, C1)";
442	final int cellType = Cell.CELL_TYPE_BLANK;
443	final String expectedFormula = "IF(A1=1,B1,C1)";
444	final double expectedValue = 3.0;
445	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
446	}
447
448	@Test
449	public void testIFEqualsFormulaEvaluation_BlankCompareToZero() {
450	final String formula = "IF(A1=0, B1, C1)";
451	final int cellType = Cell.CELL_TYPE_BLANK;
452	final String expectedFormula = "IF(A1=0,B1,C1)";
453	final double expectedValue = 2.0;
454	testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
455	}
456
457
458	private void testIFEqualsFormulaEvaluation_evaluateFormulaCell(
459	String formula, int cellType, String expectedFormula, double expectedResult) {
460	Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
461	Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
462
463	FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
464	int resultCellType = eval.evaluateFormulaCell(D1);
465
466	// Call should modify the contents, but leave the formula intact
467	assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
468	assertEquals(expectedFormula, D1.getCellFormula());
469	assertEquals(Cell.CELL_TYPE_NUMERIC, resultCellType);
470	assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
471	assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
472
473	testIFEqualsFormulaEvaluation_teardown(wb);
474	}
475
476	private void testIFEqualsFormulaEvaluation_evaluateInCell(
477	String formula, int cellType, String expectedFormula, double expectedResult) {
478	Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
479	Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
480
481	FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
482	Cell result = eval.evaluateInCell(D1);
483
484	// Call should modify the contents and replace the formula with the result
485	assertSame(D1, result); // returns the same cell that was provided as an argument so that calls can be chained.
486	try {
487	D1.getCellFormula();
488	fail("cell formula should be overwritten with formula result");
489	} catch (final IllegalStateException expected) { }
490	assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCellType());
491	assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
492
493	testIFEqualsFormulaEvaluation_teardown(wb);
494	}
495
496	private void testIFEqualsFormulaEvaluation_evaluateAll(
497	String formula, int cellType, String expectedFormula, double expectedResult) {
498	Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
499	Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
500
501	FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
502	eval.evaluateAll();
503
504	// Call should modify the contents
505	assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
506	assertEquals(expectedFormula, D1.getCellFormula());
507
508	assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
509	assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
510
511	testIFEqualsFormulaEvaluation_teardown(wb);
512	}
513
514	private void testIFEqualsFormulaEvaluation_evaluateAllFormulaCells(
515	String formula, int cellType, String expectedFormula, double expectedResult) {
516	Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
517	Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
518
519	HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
520
521	// Call should modify the contents
522	assertEquals(Cell.CELL_TYPE_FORMULA, D1.getCellType());
523	// whitespace gets deleted because formula is parsed and re-rendered
524	assertEquals(expectedFormula, D1.getCellFormula());
525
526	assertEquals(Cell.CELL_TYPE_NUMERIC, D1.getCachedFormulaResultType());
527	assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
528
529	testIFEqualsFormulaEvaluation_teardown(wb);
530	}
303	}	531	}