From a6e9421d18ef296f4d67e707a467354bf37c2a70 Mon Sep 17 00:00:00 2001 From: Dominik Stadler Date: Fri, 28 Dec 2018 11:17:46 +0000 Subject: [PATCH] Adjust/enhance tests for bug 62275 some more git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1849836 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/TestVlookup.java | 112 ++++++++++++++++-- .../usermodel/TestXSSFFormulaEvaluation.java | 30 +---- 2 files changed, 110 insertions(+), 32 deletions(-) diff --git a/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestVlookup.java b/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestVlookup.java index 31769a37a4..0dc0655a1f 100644 --- a/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestVlookup.java +++ b/src/ooxml/testcases/org/apache/poi/ss/formula/functions/TestVlookup.java @@ -17,26 +17,122 @@ package org.apache.poi.ss.formula.functions; +import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; -import junit.framework.TestCase; +import java.io.IOException; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; /** * Test the VLOOKUP function */ -public class TestVlookup extends TestCase { +public class TestVlookup { + + @Test + public void testFullColumnAreaRef61841() throws IOException { + try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("VLookupFullColumn.xlsx")) { + FormulaEvaluator feval = wb.getCreationHelper().createFormulaEvaluator(); + feval.evaluateAll(); + assertEquals("Wrong lookup value", "Value1", + feval.evaluate(wb.getSheetAt(0).getRow(3).getCell(1)).getStringValue()); + assertEquals("Lookup should return #N/A", + CellType.ERROR, feval.evaluate(wb.getSheetAt(0).getRow(4).getCell(1)).getCellType()); + } + } + + @Test + public void bug62275_true() throws IOException { + try (Workbook wb = new XSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + + Cell cell = row.createCell(0); + cell.setCellFormula("vlookup(A2,B1:B5,2,true)"); + + CreationHelper createHelper = wb.getCreationHelper(); + FormulaEvaluator eval = createHelper.createFormulaEvaluator(); + CellValue value = eval.evaluate(cell); + + assertFalse(value.getBooleanValue()); + } + } + + @Test + public void bug62275_false() throws IOException { + try (Workbook wb = new XSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + + Cell cell = row.createCell(0); + cell.setCellFormula("vlookup(A2,B1:B5,2,false)"); + + CreationHelper crateHelper = wb.getCreationHelper(); + FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); + CellValue value = eval.evaluate(cell); + + assertFalse(value.getBooleanValue()); + } + } @Test - public void testFullColumnAreaRef61841() { - final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("VLookupFullColumn.xlsx"); - FormulaEvaluator feval = wb.getCreationHelper().createFormulaEvaluator(); - feval.evaluateAll(); - assertEquals("Wrong lookup value", "Value1", feval.evaluate(wb.getSheetAt(0).getRow(3).getCell(1)).getStringValue()); - assertEquals("Lookup should return #N/A", CellType.ERROR, feval.evaluate(wb.getSheetAt(0).getRow(4).getCell(1)).getCellType()); + public void bug62275_empty_3args() throws IOException { + try (Workbook wb = new XSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + + Cell cell = row.createCell(0); + cell.setCellFormula("vlookup(A2,B1:B5,2,)"); + + CreationHelper crateHelper = wb.getCreationHelper(); + FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); + CellValue value = eval.evaluate(cell); + + assertFalse(value.getBooleanValue()); + } } + @Test + public void bug62275_empty_2args() throws IOException { + try (Workbook wb = new XSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + + Cell cell = row.createCell(0); + cell.setCellFormula("vlookup(A2,B1:B5,,)"); + + CreationHelper crateHelper = wb.getCreationHelper(); + FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); + CellValue value = eval.evaluate(cell); + + assertFalse(value.getBooleanValue()); + } + } + + @Test + public void bug62275_empty_1arg() throws IOException { + try (Workbook wb = new XSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + + Cell cell = row.createCell(0); + cell.setCellFormula("vlookup(A2,,,)"); + + CreationHelper crateHelper = wb.getCreationHelper(); + FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); + CellValue value = eval.evaluate(cell); + + assertFalse(value.getBooleanValue()); + } + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java index 418eb268e8..9f4479240c 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -30,7 +30,6 @@ import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; -import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; @@ -253,7 +252,7 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { */ @Test @Ignore - public void testCachedReferencesToOtherWorkbooks() throws Exception { + public void testCachedReferencesToOtherWorkbooks() { // TODO fail("unit test not written yet"); } @@ -388,7 +387,7 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { verifyAllFormulasInWorkbookCanBeEvaluated("StructuredRefs-lots-with-lookups.xlsx"); } - // FIXME: use junit4 parameterization + // FIXME: use junit4 parametrization private static void verifyAllFormulasInWorkbookCanBeEvaluated(String sampleWorkbook) throws IOException { XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(sampleWorkbook); XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); @@ -442,33 +441,16 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { // assertEquals("D 67.10", cell.getStringCellValue()); CellValue value = evaluator.evaluate(cell); - assertEquals("D 67.10", value.getStringValue()); + assertEquals("D 67.10", + value.getStringValue()); - assertEquals("D 0,068", evaluator.evaluate(wb.getSheetAt(0).getRow(1).getCell(1))); + assertEquals("D 0,068", + evaluator.evaluate(wb.getSheetAt(0).getRow(1).getCell(1)).getStringValue()); } - /** - * see bug 62275 - * @throws IOException - */ - @Test - public void testBug62275() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - Sheet sheet = wb.createSheet(); - Row row = sheet.createRow(0); - - Cell cell = row.createCell(0); - cell.setCellFormula("vlookup(A2,B1:B5,2,true)"); - - CreationHelper createHelper = wb.getCreationHelper(); - FormulaEvaluator eval = createHelper.createFormulaEvaluator(); - eval.evaluate(cell); - } - } /** * see bug 62834, handle when a shared formula range doesn't contain only formula cells - * @throws IOException */ @Test public void testBug62834() throws IOException { -- 2.39.5