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

Example 1 with DataValidationConstraint

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

the class DataValidationEvaluator method getValidationValuesForConstraint.

/**
     * static so enums can reference it without creating a whole instance
     * @return returns an unmodifiable {@link List} of {@link ValueEval}s, which may be empty
     */
protected static List<ValueEval> getValidationValuesForConstraint(DataValidationContext context) {
    final DataValidationConstraint val = context.getValidation().getValidationConstraint();
    if (val.getValidationType() != ValidationType.LIST)
        return null;
    String formula = val.getFormula1();
    final List<ValueEval> values = new ArrayList<ValueEval>();
    if (val.getExplicitListValues() != null && val.getExplicitListValues().length > 0) {
        // assumes parsing interprets the overloaded property right for XSSF
        for (String s : val.getExplicitListValues()) {
            // constructor throws exception on null
            if (s != null)
                values.add(new StringEval(s));
        }
    } else if (formula != null) {
        // evaluate formula for cell refs then get their values
        ValueEval eval = context.getEvaluator().getWorkbookEvaluator().evaluate(formula, context.getTarget(), context.getRegion());
        // there is no way from the model to tell if the list is fixed values or formula based.
        if (eval instanceof TwoDEval) {
            TwoDEval twod = (TwoDEval) eval;
            for (int i = 0; i < twod.getHeight(); i++) {
                final ValueEval cellValue = twod.getValue(i, 0);
                values.add(cellValue);
            }
        }
    }
    return Collections.unmodifiableList(values);
}

Example 2 with DataValidationConstraint

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

the class XSSFDataValidationHelper method createValidation.

/* (non-Javadoc)
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createValidation(org.apache.poi.ss.usermodel.DataValidationConstraint, org.apache.poi.ss.util.CellRangeAddressList)
	 */
public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
    XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint) constraint;
    CTDataValidation newDataValidation = CTDataValidation.Factory.newInstance();
    int validationType = constraint.getValidationType();
    switch(validationType) {
        case DataValidationConstraint.ValidationType.LIST:
            newDataValidation.setType(STDataValidationType.LIST);
            newDataValidation.setFormula1(constraint.getFormula1());
            break;
        case DataValidationConstraint.ValidationType.ANY:
            newDataValidation.setType(STDataValidationType.NONE);
            break;
        case DataValidationConstraint.ValidationType.TEXT_LENGTH:
            newDataValidation.setType(STDataValidationType.TEXT_LENGTH);
            break;
        case DataValidationConstraint.ValidationType.DATE:
            newDataValidation.setType(STDataValidationType.DATE);
            break;
        case DataValidationConstraint.ValidationType.INTEGER:
            newDataValidation.setType(STDataValidationType.WHOLE);
            break;
        case DataValidationConstraint.ValidationType.DECIMAL:
            newDataValidation.setType(STDataValidationType.DECIMAL);
            break;
        case DataValidationConstraint.ValidationType.TIME:
            newDataValidation.setType(STDataValidationType.TIME);
            break;
        case DataValidationConstraint.ValidationType.FORMULA:
            newDataValidation.setType(STDataValidationType.CUSTOM);
            break;
        default:
            newDataValidation.setType(STDataValidationType.NONE);
    }
    if (validationType != ValidationType.ANY && validationType != ValidationType.LIST) {
        STDataValidationOperator.Enum op = XSSFDataValidation.operatorTypeMappings.get(constraint.getOperator());
        if (op != null) {
            newDataValidation.setOperator(op);
        }
        if (constraint.getFormula1() != null) {
            newDataValidation.setFormula1(constraint.getFormula1());
        }
        if (constraint.getFormula2() != null) {
            newDataValidation.setFormula2(constraint.getFormula2());
        }
    }
    CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
    List<String> sqref = new ArrayList<String>();
    for (int i = 0; i < cellRangeAddresses.length; i++) {
        CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
        sqref.add(cellRangeAddress.formatAsString());
    }
    newDataValidation.setSqref(sqref);
    newDataValidation.setAllowBlank(true);
    newDataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
    return new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation);
}

Example 3 with DataValidationConstraint

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

the class TestXSSFDataValidation method createValidation.

private XSSFDataValidation createValidation(XSSFSheet sheet) {
    //create the cell that will have the validation applied
    final Row row = sheet.createRow(0);
    row.createCell(0);
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("true");
    final XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
    return validation;
}

Example 4 with DataValidationConstraint

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

the class TestPLVRecord method testPLVRecord.

public void testPLVRecord() throws Exception {
    InputStream is = HSSFTestDataSamples.openSampleFileStream(XLS_FILENAME);
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
    DataValidationConstraint constraint = DVConstraint.createFormulaListConstraint(DV_DEFINITION);
    HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
    // This used to throw an error before
    try {
        workbook.getSheet(SHEET_NAME).addValidationData(dataValidation);
    } catch (IllegalStateException ex) {
        throw new AssertionFailedError("Identified bug 53972, PLV record breaks addDataValidation()");
    }
}

Example 5 with DataValidationConstraint

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

the class TestDataValidation method testAddToExistingSheet.

@Test
public void testAddToExistingSheet() throws Exception {
    // dvEmpty.xls is a simple one sheet workbook.  With a DataValidations header record but no 
    // DataValidations.  It's important that the example has one SHEETPROTECTION record.
    // Such a workbook can be created in Excel (2007) by adding datavalidation for one cell
    // and then deleting the row that contains the cell.
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");
    int dvRow = 0;
    Sheet sheet = wb.getSheetAt(0);
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OperatorType.EQUAL, "42", null);
    DataValidation dv = dataValidationHelper.createValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
    dv.setEmptyCellAllowed(false);
    dv.setErrorStyle(ErrorStyle.STOP);
    dv.setShowPromptBox(true);
    dv.createErrorBox("Xxx", "Yyy");
    dv.setSuppressDropDownArrow(true);
    sheet.addValidationData(dv);
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);
    byte[] wbData = baos.toByteArray();
    //		if (false) { // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)
    //
    //			ERFListener erfListener = null; // new MyERFListener();
    //			EventRecordFactory erf = new EventRecordFactory(erfListener, null);
    //			try {
    //				POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(baos.toByteArray()));
    //				erf.processRecords(fs.createDocumentInputStream("Workbook"));
    //			} catch (RecordFormatException e) {
    //				throw new RuntimeException(e);
    //			} catch (IOException e) {
    //				throw new RuntimeException(e);
    //			}
    //		}
    // else verify record ordering by navigating the raw bytes
    byte[] dvHeaderRecStart = { (byte) 0xB2, 0x01, 0x12, 0x00 };
    int dvHeaderOffset = findIndex(wbData, dvHeaderRecStart);
    assertTrue(dvHeaderOffset > 0);
    int nextRecIndex = dvHeaderOffset + 22;
    int nextSid = ((wbData[nextRecIndex + 0] << 0) & 0x00FF) + ((wbData[nextRecIndex + 1] << 8) & 0xFF00);
    if (nextSid == 0x0867) {
        fail("Identified bug 45519");
    }
    assertEquals(DVRecord.sid, nextSid);
    wb.close();
}