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

Example 1 with DataValidation

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

the class DataValidationEvaluator method getValidationContextForCell.

/**
     * Finds and returns the {@link DataValidationContext} for the cell, if there is
     * one. Lookup is based on the first match from
     * {@link DataValidation#getRegions()} for the cell's sheet. DataValidation
     * regions must be in the same sheet as the DataValidation. Allowed values
     * expressions may reference other sheets, however.
     * 
     * @param cell reference to check
     * @return the DataValidationContext applicable to the given cell, or null if no
     *         validation applies
     */
public DataValidationContext getValidationContextForCell(CellReference cell) {
    final Sheet sheet = workbook.getSheet(cell.getSheetName());
    if (sheet == null)
        return null;
    final List<? extends DataValidation> dataValidations = getValidations(sheet);
    if (dataValidations == null)
        return null;
    for (DataValidation dv : dataValidations) {
        final CellRangeAddressList regions = dv.getRegions();
        if (regions == null)
            return null;
        // current implementation can't return null
        for (CellRangeAddressBase range : regions.getCellRangeAddresses()) {
            if (range.isInRange(cell)) {
                return new DataValidationContext(dv, this, range, cell);
            }
        }
    }
    return null;
}

Example 2 with DataValidation

use of org.apache.poi.ss.usermodel.DataValidation 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();
}

Example 3 with DataValidation

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

the class TestDataValidation method testGetDataValidationsDecimal.

@Test
public void testGetDataValidationsDecimal() 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.createDecimalConstraint(OperatorType.BETWEEN, "=A2", "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.DECIMAL, c.getValidationType());
    assertEquals(OperatorType.BETWEEN, c.getOperator());
    assertEquals("A2", c.getFormula1());
    assertEquals(null, c.getFormula2());
    assertEquals(null, c.getValue1());
    assertEquals(new Double("200"), c.getValue2());
    wb.close();
}

Example 4 with DataValidation

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

the class TestDataValidation method testGetDataValidationsListFormula.

@Test
public void testGetDataValidationsListFormula() 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.createFormulaListConstraint("A2");
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
    validation.setSuppressDropDownArrow(true);
    sheet.addValidationData(validation);
    // <-- works
    list = sheet.getDataValidations();
    assertEquals(1, list.size());
    HSSFDataValidation dv = list.get(0);
    assertEquals(true, dv.getSuppressDropDownArrow());
    DVConstraint c = dv.getConstraint();
    assertEquals(ValidationType.LIST, c.getValidationType());
    assertEquals("A2", c.getFormula1());
    assertEquals(null, c.getFormula2());
    assertEquals(null, c.getValue1());
    assertEquals(null, c.getValue2());
    wb.close();
}

Example 5 with DataValidation

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

the class TestDataValidation method testGetDataValidationsFormula.

@Test
public void testGetDataValidationsFormula() 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.createCustomConstraint("A2:A3");
    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.FORMULA, c.getValidationType());
    assertEquals("A2:A3", c.getFormula1());
    assertEquals(null, c.getFormula2());
    assertEquals(null, c.getValue1());
    assertEquals(null, c.getValue2());
    wb.close();
}