org.apache.poi.ss.usermodel.DataValidationConstraint Java Examples

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
}
 

private String setDropDownList(Td td, Sheet sheet, String content) {
    if (content.length() > 250) {
        throw new IllegalArgumentException("The total number of words in the drop-down list should not exceed 250.");
    }
    CellRangeAddressList addressList = new CellRangeAddressList(
            td.getRow(), td.getRowBound(), td.getCol(), td.getColBound());
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    String[] list = content.split(",");
    DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(list);
    DataValidation validation = dvHelper.createValidation(
            dvConstraint, addressList);
    if (validation instanceof XSSFDataValidation) {
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
    } else {
        validation.setSuppressDropDownArrow(false);
    }
    sheet.addValidationData(validation);
    if (list.length > 0) {
        return list[0];
    }
    return null;
}
 

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
        int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
    return sheet;
}
 

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
}
 

/**
 * 指定した範囲のセルに制約を追加する。
 * <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);
}
 

@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());

    // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
    DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
    DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
    DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
    writeSheetHolder.getSheet().addValidationData(dataValidation);
}
 

/**
 * 设置 POI XSSFSheet 单元格提示
 * 
 * @param sheet 表单
 * @param promptTitle 提示标题
 * @param promptContent 提示内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 */
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
        int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    dataValidation.createPromptBox(promptTitle, promptContent);
    dataValidation.setShowPromptBox(true);
    sheet.addValidationData(dataValidation);
}
 

/**
 * テンプレートの入力規則の制約「リスト」を式形式で追加する。
 * <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);
}
 

/**
 * テンプレートの入力規則の制約「リスト」を追加する。
 * <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);

}
 

/**
 * create restrictions on the data cells
 * 
 * @param mainsheet sheet with data
 * @param restrictionsheet sheet with restriction values
 * @param column index of column (starting with zero)
 * @param nbofchoices number of choices (starting with zero)
 * @param nbofrows number of rows (starting with zero)
 */
public static  void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
	DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
	String columnletter =  CellReference.convertNumToColString(column);
	String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
	DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
	CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
	
	DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	dataValidation.setSuppressDropDownArrow(true);
	mainsheet.addValidationData(dataValidation);
}
 

/**
 * 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()) {
            if (s != null) values.add(new StringEval(s)); // constructor throws exception on null
        }
    } else if (formula != null) {
        // evaluate formula for cell refs then get their values
        // note this should return the raw formula result, not the "unwrapped" version that returns a single value.
        ValueEval eval = context.getEvaluator().getWorkbookEvaluator().evaluateList(formula, context.getTarget(), context.getRegion());
        // formula is a StringEval if the validation is by a fixed list.  Use the explicit list later.
        // 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);
}
 

/**
 * 设置 POI XSSFSheet 单元格提示
 * 
 * @param sheet 表单
 * @param promptTitle 提示标题
 * @param promptContent 提示内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 */
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
        int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    dataValidation.createPromptBox(promptTitle, promptContent);
    dataValidation.setShowPromptBox(true);
    sheet.addValidationData(dataValidation);
}
 

public DataValidationConstraint createFormulaListConstraint(String listFormula) {
	return DVConstraint.createFormulaListConstraint(listFormula);
}
 

public DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2) {
	return DVConstraint.createNumericConstraint(validationType, operatorType, formula1, formula2);
}
 

public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) {
	return DVConstraint.createNumericConstraint(ValidationType.INTEGER, operatorType, formula1, formula2);
}
 

public DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2) {
	return DVConstraint.createNumericConstraint(ValidationType.DECIMAL, operatorType, formula1, formula2);
}
 

public DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2) {
	return DVConstraint.createNumericConstraint(ValidationType.TEXT_LENGTH, operatorType, formula1, formula2);
}
 

public DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2) {
	return DVConstraint.createTimeConstraint(operatorType, formula1, formula2);
}
 

public DataValidationConstraint createCustomConstraint(String formula) {
	return DVConstraint.createCustomFormulaConstraint(formula);
}
 

public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
	return new HSSFDataValidation(cellRangeAddressList, constraint); 
}
 

public DataValidationConstraint getValidationConstraint() {
	return _constraint;
}
 

public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) {
	return DVConstraint.createExplicitListConstraint(listOfValues);
}
 

public DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2, String dateFormat) {
	return DVConstraint.createDateConstraint(operatorType, formula1, formula2, dateFormat);
}
 

/**
 * Constructor which initializes the cell range on which this object will be
 * applied
 *
 * @param regions A list of regions where the constraint is validated.
 * @param constraint The constraints to apply for this validation.
 */
public HSSFDataValidation(CellRangeAddressList regions, DataValidationConstraint constraint) {
	_regions = regions;
	
	//FIXME: This cast can be avoided.
	_constraint = (DVConstraint)constraint;
}