https://issues.apache.org/bugzilla/show_bug.cgi?id=57475
--- Comment #2 from [email protected] --- import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.testng.Assert; import org.testng.annotations.Test; /** * Test the Match function */ public class MatchTest { /** * Sets the cell value. * @param cell * @param value a Number or a String */ private static void setCellValue(XSSFCell cell, Object value) { if (value instanceof String) { cell.setCellValue((String)value); } else if (value instanceof Number) { cell.setCellValue(((Number)value).doubleValue()); } else { throw new IllegalArgumentException("Unsupported type " + value.getClass().getName()); } } /** * Evaluates Assert.assertEquals(MATCH(lookup, A1:C1, 0), expected). * <pre> * test("2", 1, 2, 3, 2) is * Assert.assertEquals(MATCH(2, A1:C1, 0), 2) * where A1 = 1, B1 = 2, C1 = 3 * </pre> * * @param lookup the lookup value. Add double quotes around the value for strings. * @param A1 the content of A1 * @param B1 ... * @param C1 ... * @param expected the expected value. */ private static void test(String lookup, Object A1, Object B1, Object C1, int expected) { try { //Create a new Workbook try (XSSFWorkbook workbook = new XSSFWorkbook()) { //Create a blank sheet XSSFSheet sheet = workbook.createSheet("MatchTest"); XSSFRow row1 = sheet.createRow(0); XSSFCell a1 = row1.createCell(0); setCellValue(a1, A1); XSSFCell b1 = row1.createCell(1); setCellValue(b1, B1); XSSFCell c1 = row1.createCell(2); setCellValue(c1, C1); XSSFCell d1 = row1.createCell(3); d1.setCellType(Cell.CELL_TYPE_FORMULA); d1.setCellFormula("MATCH(" + lookup + ", A1:C1, 0)"); XSSFFormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); int typeOfFormulaResult = evaluator.evaluateFormulaCell(d1); Assert.assertEquals(typeOfFormulaResult, Cell.CELL_TYPE_NUMERIC); double value = d1.getNumericCellValue(); Assert.assertEquals(value, expected, 0.0); } } catch (IOException ex) { throw new RuntimeException(ex.getMessage(), ex); } } /** * OK * Assert.assertEquals(MATCH(2, {1, 2, 3}, 0), 2) */ @Test public static void testNumerics() { test("2", 1, 2, 3, 2); } /** * OK * Assert.assertEquals(MATCH("2", {"1", "2", "3"}, 0), 2) */ @Test public static void testStrings() { test("\"2\"", "1", "2", "3", 2); } /** * Fails * Assert.assertEquals(MATCH("2", {1, 2, 3}, 0), 2) */ @Test public static void testStringNumerics() { test("\"2\"", 1, 2, 3, 2); } /** * Fails * Assert.assertEquals(MATCH(2, {"1", "2", "3"}, 0), 2) */ @Test public static void testNumericStrings() { test("2", "1", "2", "3", 2); } } -- You are receiving this mail because: You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
