Java 类org.apache.poi.ss.usermodel.DataValidationConstraint 实例源码

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol   开始列
 * @param endCol  结束列
 * @return 设置好的sheet.
 */
public static SXSSFSheet setHSSFValidation(SXSSFSheet sheet,
                                          String[] textlist, int firstRow, int endRow, int firstCol,
                                          int endCol) {
    DataValidationHelper validationHelper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint explicitListConstraint = validationHelper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation validation = validationHelper.createValidation(explicitListConstraint, regions);
    validation.setSuppressDropDownArrow(true);
    validation.createErrorBox("tip","请从下拉列表选取");
    //错误警告框
    validation.setShowErrorBox(true);
    sheet.addValidationData(validation);
    return sheet;
}

private List<Object> getReferenceList(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
    List<Object> references = new LinkedList<>();

    AreaReference areaRef = new AreaReference(validationConstraint.getFormula1());
    CellReference[] cellRefs = areaRef.getAllReferencedCells();
    for (CellReference cellRef : cellRefs) {
        XSSFSheet referenceListSheet;
        if (cellRef.getSheetName() != null) {
            referenceListSheet = sheet.getWorkbook().getSheet(cellRef.getSheetName());
        } else {
            referenceListSheet = sheet;
        }
        Row row = referenceListSheet.getRow(cellRef.getRow());
        if (row != null) {
            Cell cell = row.getCell(cellRef.getCol());
            if (cell != null) {
                Object cellValue = CellValueParser.getCellValue(cell);
                references.add(cellValue);
            }
        }
    }
    return references;
}

/**
 * 指定した範囲のセルに制約を追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param constraint 制約
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 */
public static void setupConstaint(final Sheet sheet, final DataValidationConstraint constraint,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notNull(constraint, "constraint");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();

    final CellRangeAddressList region = new CellRangeAddressList(
            startPosition.y, endPosition.y,
            startPosition.x, endPosition.x
            );
    final DataValidation dataValidation = helper.createValidation(constraint, region);
    sheet.addValidationData(dataValidation);
}

private Validator buildValidator(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
    int validationType = validationConstraint.getValidationType();

    Validator validator;
    switch (validationType) {
    case ValidationType.ANY:
        validator = new NullValidator();
        break;
    case ValidationType.DATE:
        validator = new DateValidator(buildOperator(sheet, validationConstraint));
        break;
    case ValidationType.DECIMAL:
        validator = new DecimalValidator(buildOperator(sheet, validationConstraint));
        break;
    case ValidationType.FORMULA:
        validator = new FormulaValidator(null);// TODO how to do?
        break;
    case ValidationType.INTEGER:
        validator = new NumericValidator(buildOperator(sheet, validationConstraint));
        break;
    case ValidationType.LIST:
        validator = new ListValidator(buildReferenceList(sheet, validationConstraint));
        break;
    case ValidationType.TEXT_LENGTH:
        validator = new TextLengthValidator(buildOperator(sheet, validationConstraint));
        break;
    case ValidationType.TIME:
        validator = new TimeValidator(buildOperator(sheet, validationConstraint));
        break;
    default:
        throw new UnsupportedOperationException("Validation Type is not supported: " + validationType);
    }
    return validator;
}

private Operator buildOperator(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
    int operatorType = validationConstraint.getOperator();
    String formula1 = validationConstraint.getFormula1();
    String formula2 = validationConstraint.getFormula2();

    Operator operator;
    switch (operatorType) {
    case OperatorType.BETWEEN:
        operator = new BetweenOperator(getSingleValue(sheet, formula1), getSingleValue(sheet, formula2));
        break;
    case OperatorType.NOT_BETWEEN:
        operator = new NotBetweenOperator(getSingleValue(sheet, formula1), getSingleValue(sheet, formula2));
        break;
    case OperatorType.EQUAL:
        operator = new EqualOperator(getSingleValue(sheet, formula1));
        break;
    case OperatorType.NOT_EQUAL:
        operator = new NotEqualOperator(getSingleValue(sheet, formula1));
        break;
    case OperatorType.GREATER_OR_EQUAL:
        operator = new GreaterOrEqualOperator(getSingleValue(sheet, formula1));
        break;
    case OperatorType.GREATER_THAN:
        operator = new GreaterThanOperator(getSingleValue(sheet, formula1));
        break;
    case OperatorType.LESS_OR_EQUAL:
        operator = new LessOrEqualOperator(getSingleValue(sheet, formula1));
        break;
    case OperatorType.LESS_THAN:
        operator = new LessThanOperator(getSingleValue(sheet, formula1));
        break;
    // OperatorType.IGNORED = Operator.BETWEEN
    default:
        throw new UnsupportedOperationException("Operation Type is not supported: " + operatorType);
    }
    return operator;
}

@SuppressWarnings({ "unchecked", "rawtypes" })
private List<Object> buildReferenceList(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
    List listValues;
    String formula1 = validationConstraint.getFormula1();
    if (formula1.contains("$")) {
        listValues = getReferenceList(sheet, validationConstraint);
    } else {
        listValues = parseListValues(formula1);
    }
    return listValues;
}

/**
 * テンプレートの入力規則の制約「リスト」を追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param constraints 制約とするリストの中身
 * @param startPosition 開始位置
 * @param endPosition 終了位置
 */
public static void setupExplicitListConstaint(final Sheet sheet, final String[] constraints,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notEmpty(constraints, "constraints");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();
    final DataValidationConstraint constraint = helper.createExplicitListConstraint(constraints);
    setupConstaint(sheet, constraint, startPosition, endPosition);

}

/**
 * テンプレートの入力規則の制約「リスト」を式形式で追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param listFormula 入力規則の式('='は含まない)
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 */
public static void setupFormulaListConstaint(final Sheet sheet, final String listFormula,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notEmpty(listFormula, "listFormula");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();
    final DataValidationConstraint constraint = helper.createFormulaListConstraint("=" + listFormula);
    setupConstaint(sheet, constraint, startPosition, endPosition);
}

public InputRule(Sheet sheet, XSSFDataValidation dv) {
    empty = dv.getEmptyCellAllowed();
    if (dv.getShowErrorBox()) {
        errTitle = dv.getErrorBoxTitle();
        errText = dv.getErrorBoxText();
        errStyle = dv.getErrorStyle();
    }
    if (dv.getShowPromptBox()) {
        pmTitle = dv.getPromptBoxTitle();
        pmText = dv.getPromptBoxText();
    }

    regions = dv.getRegions();
    regionsStr = new String[regions.countRanges()];
    int idx = 0;
    for (CellRangeAddress cell : regions.getCellRangeAddresses()) {
        regionsStr[idx++] = cell.formatAsString();
    }

    DataValidationConstraint vc = dv.getValidationConstraint();
    f1 = vc.getFormula1();
    f2 = vc.getFormula2();
    op = vc.getOperator();
    vt = vc.getValidationType();
    if (vt == DataValidationConstraint.ValidationType.LIST) {
        list = buildList(sheet, f1);
    }
}

private InputRule(Sheet sheet, Element el) {
    empty = "1".equals(el.getAttribute("allowBlank"));
    if ("1".equals(el.getAttribute("showErrorMessage"))) {
        errTitle = checkNull(el.getAttribute("errorTitle"));
        errText = checkNull(el.getAttribute("error"));
        //errStyle = ???
    }
    if ("1".equals(el.getAttribute("showInputMessage"))) {
        pmTitle = checkNull(el.getAttribute("promptTitle"));
        pmText = checkNull(el.getAttribute("prompt"));
    }
    String region = null;
    Node node = el.getFirstChild();
    while (node != null) {
        String name = node.getLocalName();
        if ("formula1".equals(name)) {
            f1 = getChildText((Element)node);
        } else if ("formula2".equals(name)) {
            f2 = getChildText((Element)node);
        } else if ("sqref".equals(name)) {
            region = getChildText((Element)node);
        }
        node = node.getNextSibling();
    }
    if (f1 == null || region == null) {
        throw new IllegalArgumentException("Invalid element: " + el.getNodeName());
    }
    regionsStr = new String[1];
    regionsStr[0] = region;
    regions = new CellRangeAddressList();
    regions.addCellRangeAddress(CellRangeAddress.valueOf(region));

    //op = ???
    vt = DataValidationConstraint.ValidationType.LIST;
    list = buildList(sheet, f1);
}

public List<ValidationResult> validateSheet(XSSFSheet sheet) {
    List<ValidationResult> results = new LinkedList<ValidationResult>();

    List<XSSFDataValidation> dataValidations = sheet.getDataValidations();
    for (XSSFDataValidation xssfDataValidation : dataValidations) {
        DataValidationConstraint validationConstraint = xssfDataValidation.getValidationConstraint();
        CellRangeAddressList regions = xssfDataValidation.getRegions();
        CellRangeAddress[] cellRangeAddresses = regions.getCellRangeAddresses();
        Validator validator = buildValidator(sheet, validationConstraint);
        for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
            int firstRow = Math.max(cellRangeAddress.getFirstRow(), sheet.getFirstRowNum());
            int lastRow = Math.min(cellRangeAddress.getLastRow(), sheet.getLastRowNum());

            for (int i = firstRow; i <= lastRow; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null || row.getFirstCellNum() < 0) {
                    continue;
                }

                int firstColumn = Math.max(cellRangeAddress.getFirstColumn(), row.getFirstCellNum());
                int lastColumn = Math.min(cellRangeAddress.getLastColumn(), row.getLastCellNum());

                for (int j = firstColumn; j <= lastColumn; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    boolean inRange = cellRangeAddress.isInRange(cell.getRowIndex(), cell.getColumnIndex());
                    if (inRange) {
                        ValidationResult result = validator.validate(cell);
                        if (result != null) {
                            results.add(result);
                        }
                    }
                }
            }
        }
    }
    return results;
    /*
     * TODO think about splitting this function into 2 parts: 1.
     * getAllValidators 2. validate with all validators
     */
}