Examples with DataValidationHelper org.apache.poi.ss.usermodel.DataValidationHelper used on opensource projects

Example 11 with DataValidationHelper

use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.

the class TestDataValidation method testGetDataValidationsIntegerValue.

@Test
public void testGetDataValidationsIntegerValue() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    List<HSSFDataValidation> list = sheet.getDataValidations();
    assertEquals(0, list.size());
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = dataValidationHelper.createIntegerConstraint(OperatorType.BETWEEN, "100", "200");
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
    sheet.addValidationData(validation);
    // <-- works
    list = sheet.getDataValidations();
    assertEquals(1, list.size());
    HSSFDataValidation dv = list.get(0);
    DVConstraint c = dv.getConstraint();
    assertEquals(ValidationType.INTEGER, c.getValidationType());
    assertEquals(OperatorType.BETWEEN, c.getOperator());
    assertEquals(null, c.getFormula1());
    assertEquals(null, c.getFormula2());
    assertEquals(new Double("100"), c.getValue1());
    assertEquals(new Double("200"), c.getValue2());
    wb.close();
}

Example 12 with DataValidationHelper

use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.

the class TestXSSFDataValidation method testAddValidations.

@Test
public void testAddValidations() throws Exception {
    XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("DataValidations-49244.xlsx");
    Sheet sheet = wb1.getSheetAt(0);
    List<XSSFDataValidation> dataValidations = ((XSSFSheet) sheet).getDataValidations();
    /**
 * 		For each validation type, there are two cells with the same validation. This tests
 * 		application of a single validation definition to multiple cells.
 * 		
 * 		For list ( 3 validations for explicit and 3 for formula )
 * 			- one validation that allows blank. 
 * 			- one that does not allow blank.
 * 			- one that does not show the drop down arrow.
 * 		= 2
 * 
 * 		For number validations ( integer/decimal and text length ) with 8 different types of operators.
 *		= 50  
 * 
 * 		= 52 ( Total )
 */
    assertEquals(52, dataValidations.size());
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
    int[] singleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL };
    int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN };
    BigDecimal value = new BigDecimal("10"), value2 = new BigDecimal("20");
    BigDecimal dvalue = new BigDecimal("10.001"), dvalue2 = new BigDecimal("19.999");
    final int lastRow = sheet.getLastRowNum();
    int offset = lastRow + 3;
    int lastKnownNumValidations = dataValidations.size();
    Row row = sheet.createRow(offset++);
    Cell cell = row.createCell(0);
    DataValidationConstraint explicitListValidation = dataValidationHelper.createExplicitListConstraint(new String[] { "MA", "MI", "CA" });
    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
    cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
    DataValidation dataValidation = dataValidationHelper.createValidation(explicitListValidation, cellRangeAddressList);
    setOtherValidationParameters(dataValidation);
    sheet.addValidationData(dataValidation);
    lastKnownNumValidations++;
    row = sheet.createRow(offset++);
    cell = row.createCell(0);
    cellRangeAddressList = new CellRangeAddressList();
    cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
    Cell firstCell = row.createCell(1);
    firstCell.setCellValue("UT");
    Cell secondCell = row.createCell(2);
    secondCell.setCellValue("MN");
    Cell thirdCell = row.createCell(3);
    thirdCell.setCellValue("IL");
    int rowNum = row.getRowNum() + 1;
    String listFormula = new StringBuilder("$B$").append(rowNum).append(":").append("$D$").append(rowNum).toString();
    DataValidationConstraint formulaListValidation = dataValidationHelper.createFormulaListConstraint(listFormula);
    dataValidation = dataValidationHelper.createValidation(formulaListValidation, cellRangeAddressList);
    setOtherValidationParameters(dataValidation);
    sheet.addValidationData(dataValidation);
    lastKnownNumValidations++;
    offset++;
    offset++;
    for (int i = 0; i < validationTypes.length; i++) {
        int validationType = validationTypes[i];
        offset = offset + 2;
        final Row row0 = sheet.createRow(offset++);
        Cell cell_10 = row0.createCell(0);
        cell_10.setCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "Integer" : "Text Length");
        offset++;
        for (int j = 0; j < singleOperandOperatorTypes.length; j++) {
            int operatorType = singleOperandOperatorTypes[j];
            final Row row1 = sheet.createRow(offset++);
            //For Integer (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
            final Row row2 = i == 0 && j < 2 ? sheet.createRow(offset++) : null;
            cell_10 = row1.createCell(0);
            cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
            Cell cell_11 = row1.createCell(1);
            Cell cell_21 = row1.createCell(2);
            Cell cell_22 = i == 0 && j < 2 ? row2.createCell(2) : null;
            Cell cell_13 = row1.createCell(3);
            cell_13.setCellType(CellType.NUMERIC);
            cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
            //First create value based validation;
            DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value.toString(), null);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
            DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            //Now create real formula based validation.
            String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
            constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, null);
            if (i == 0 && j == 0) {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            } else if (i == 0 && j == 1) {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            } else {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            }
        }
        for (int operatorType : doubleOperandOperatorTypes) {
            final Row row1 = sheet.createRow(offset++);
            cell_10 = row1.createCell(0);
            cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
            Cell cell_11 = row1.createCell(1);
            Cell cell_21 = row1.createCell(2);
            Cell cell_13 = row1.createCell(3);
            Cell cell_14 = row1.createCell(4);
            String value1String = validationType == ValidationType.DECIMAL ? dvalue.toString() : value.toString();
            cell_13.setCellType(CellType.NUMERIC);
            cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
            String value2String = validationType == ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
            cell_14.setCellType(CellType.NUMERIC);
            cell_14.setCellValue(validationType == ValidationType.DECIMAL ? dvalue2.doubleValue() : value2.intValue());
            //First create value based validation;
            DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value1String, value2String);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
            DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            //Now create real formula based validation.
            String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
            String formula2 = new CellReference(cell_14.getRowIndex(), cell_14.getColumnIndex()).formatAsString();
            constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, formula2);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
            validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
        }
    }
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    Sheet sheetAt = wb2.getSheetAt(0);
    assertEquals(lastKnownNumValidations, ((XSSFSheet) sheetAt).getDataValidations().size());
    wb2.close();
}

Example 13 with DataValidationHelper

use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.

the class TestXSSFDataValidation method test53965.

@Test
public void test53965() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    try {
        XSSFSheet sheet = wb.createSheet();
        //<-- works
        List<XSSFDataValidation> lst = sheet.getDataValidations();
        assertEquals(0, lst.size());
        //create the cell that will have the validation applied
        sheet.createRow(0).createCell(0);
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("SUM($A$1:$A$1) <= 3500");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);
        // this line caused XmlValueOutOfRangeException , see Bugzilla 3965
        lst = sheet.getDataValidations();
        assertEquals(1, lst.size());
    } finally {
        wb.close();
    }
}