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

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @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;
}

/**
 * 指定した範囲のセルに制約を追加する。
 * <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 void loadWorkbook(Workbook workbook) {
    String cellValue = null;
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        System.out.println("sheet: " + workbook.getSheetName(i));
        // 有效值
        List<? extends DataValidation> dataValidations = sheet.getDataValidations();
        if (dataValidations != null && dataValidations.size() > 0) {
            System.out.println("data validations: ");
            for (DataValidation validation : dataValidations) {
                System.out.println(validation.toString());
            }
        }
        // 表格行
        for (Row row : sheet) {
            // 行单元格
            for (Cell cell : row) {
                cellValue = "";
                CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
                if (cellType == CellType.STRING) {
                    cellValue = cell.getRichStringCellValue().getString();
                } else if (cellType == CellType.NUMERIC) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = String.valueOf(cell.getDateCellValue());
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                } else if (cellType == CellType.BOOLEAN) {
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                } else if (cellType == CellType.FORMULA) {
                    cellValue = String.valueOf(cell.getCellFormula());
                } else if (cellType == CellType.BLANK) {
                    cellValue = String.valueOf(" ");
                }
                System.out.print(cellValue);
                System.out.print("  ");
            }
            System.out.println("");
        }
    }
}

public void addValidationData(DataValidation dataValidation) {
    sheet.addValidationData(dataValidation);
}

/**
 * Not supported
 */
@Override
public List<? extends DataValidation> getDataValidations() {
  throw new UnsupportedOperationException();
}

/**
 * Not supported
 */
@Override
public void addValidationData(DataValidation dataValidation) {
  throw new UnsupportedOperationException();
}