diff options
Diffstat (limited to 'poi')
| -rw-r--r-- | poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java | 22 | ||||
| -rw-r--r-- | poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java | 35 |
2 files changed, 53 insertions, 4 deletions
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java index fd27a8dc8a..5f9b1528db 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java @@ -105,9 +105,15 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { try { ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex); TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval); - int matchedRow; + LookupUtils.ValueVector vector; + if (tableArray.isColumn()) { + vector = LookupUtils.createColumnVector(tableArray, 0); + } else { + vector = LookupUtils.createRowVector(tableArray, 0); + } + int matchedIdx; try { - matchedRow = LookupUtils.xlookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), matchMode, searchMode); + matchedIdx = LookupUtils.xlookupIndexOfValue(lookupValue, vector, matchMode, searchMode); } catch (EvaluationException e) { if (ErrorEval.NA.equals(e.getErrorEval())) { if (notFound != BlankEval.instance) { @@ -130,9 +136,17 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { if (returnEval instanceof AreaEval) { AreaEval area = (AreaEval)returnEval; if (isSingleValue) { - return area.getRelativeValue(matchedRow, 0); + if (tableArray.isColumn()) { + return area.getRelativeValue(matchedIdx, 0); + } else { + return area.getRelativeValue(0, matchedIdx); + } + } + if (tableArray.isColumn()) { + return area.offset(matchedIdx, matchedIdx,0, area.getWidth() - 1); + } else { + return area.offset(0, area.getHeight() - 1,matchedIdx, matchedIdx); } - return area.offset(matchedRow, matchedRow,0, area.getWidth() - 1); } else { return returnEval; } 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 0f03e35eef..d81fb516a4 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 @@ -100,6 +100,18 @@ public class TestXLookupFunction { } @Test + void testMicrosoftExample5() throws IOException { + try (HSSFWorkbook wb = initWorkbook5()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(2).createCell(3); + assertDouble(fe, cell, "XLOOKUP(D2,$B6:$B17,$C6:$C17)", 25000); + assertDouble(fe, cell, "XLOOKUP($C3,$C5:$G5,$C6:$G17)", 50000); + //TODO next test fails + //assertDouble(fe, cell, "XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))", 25000); + } + } + + @Test void testBinarySearch() throws IOException { try (HSSFWorkbook wb = initWorkbook4()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); @@ -207,4 +219,27 @@ public class TestXLookupFunction { return wb; } + private HSSFWorkbook initWorkbook5() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0); + addRow(sheet, 1, null, null, "Quarter", "Gross Profit", "Net Profit", "Profit %"); + addRow(sheet, 2, null, null, "Qtr1"); + addRow(sheet, 3); + addRow(sheet, 4, null, "Income Statement", "Qtr1", "Qtr2", "Qtr3", "Qtr4", "Total"); + addRow(sheet, 5, null, "Total Sales", 50000, 78200, 89500, 91250, 308950); + addRow(sheet, 6, null, "Cost of Sales", -25000, -42050, -59450, -60450, -186950); + addRow(sheet, 7, null, "Gross Profit", 25000, 37150, -30050, -30450, 122000); + addRow(sheet, 8); + addRow(sheet, 9, null, "Depreciation", -899, -791, -202, -412, -2304); + addRow(sheet, 10, null, "Interest", -513, -853, -150, -956, -2472); + addRow(sheet, 11, null, "Earnings before Tax", 23588, 34506, 29698, 29432, 117224); + addRow(sheet, 12); + addRow(sheet, 13, null, "Tax", -4246, -6211, -5346, -5298, -21100); + addRow(sheet, 14); + addRow(sheet, 15, null, "Net Profit", 19342, 28293, 24352, 24134, 96124); + addRow(sheet, 15, null, "Profit %", .293, .278, .234, .236, .269); + return wb; + } + } |