poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 57475] The Match function does not perform implicit conversion
Date Thu, 29 Jan 2015 03:48:22 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=57475

--- Comment #2 from cquezel@gmail.com ---
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message