From 7f4378bda0fa37393e9b801187678f824cab20e1 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sun, 5 Dec 2021 09:50:06 +0000 Subject: [PATCH] add xlookup tests git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1895594 13f79535-47bb-0310-9956-ffa450edef68 --- .../ss/formula/atp/TestXLookupFunction.java | 38 ++++++++++++++++++- 1 file changed, 36 insertions(+), 2 deletions(-) diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java index 2a3a09da9b..761d5b1fef 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java @@ -95,11 +95,32 @@ public class TestXLookupFunction { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(1).createCell(6); assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,1)", 0.24); - assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,2)", 0.24); assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,-1)", 0.24); } } + @Test + void testBinarySearch() throws IOException { + try (HSSFWorkbook wb = initWorkbook4()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(1).createCell(6); + assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,2)", 0.24); + assertDouble(fe, cell, "XLOOKUP(39475,C2:C7,B2:B7,0,0,2)", 0.22); + assertDouble(fe, cell, "XLOOKUP(39474,C2:C7,B2:B7,0,0,2)", 0); + } + } + + @Test + void testReverseBinarySearch() throws IOException { + try (HSSFWorkbook wb = initReverseWorkbook4()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(1).createCell(6); + assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,-2)", 0.24); + assertDouble(fe, cell, "XLOOKUP(39475,C2:C7,B2:B7,0,0,-2)", 0.22); + assertDouble(fe, cell, "XLOOKUP(39474,C2:C7,B2:B7,0,0,-2)", 0); + } + } + private HSSFWorkbook initWorkbook1() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); @@ -145,7 +166,20 @@ public class TestXLookupFunction { addRow(sheet, 3, null, 0.24, 84200); addRow(sheet, 4, null, 0.32, 160726); addRow(sheet, 5, null, 0.35, 204100); - addRow(sheet, 5, null, 0.37, 510300); + addRow(sheet, 6, null, 0.37, 510300); + return wb; + } + + private HSSFWorkbook initReverseWorkbook4() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, null, "Tax Rate", "Max Income", null, "Income", "Tax Rate"); + addRow(sheet, 1, null, 0.37, 510300, null, 46523); + addRow(sheet, 2, null, 0.35, 204100); + addRow(sheet, 3, null, 0.32, 160726); + addRow(sheet, 4, null, 0.24, 84200); + addRow(sheet, 5, null, 0.22, 39475); + addRow(sheet, 6, null, 0.10, 9700); return wb; } -- 2.39.5