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

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

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

/**
 * 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;
}
 

@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);
}
 

/**
 * Lazy load validations by sheet, since reading the CT* types is expensive
 * @param sheet
 * @return The {@link DataValidation}s for the sheet
 */
private List<? extends DataValidation> getValidations(Sheet sheet) {
    List<? extends DataValidation> dvs = validations.get(sheet.getSheetName());
    if (dvs == null && !validations.containsKey(sheet.getSheetName())) {
        dvs = sheet.getDataValidations();
        validations.put(sheet.getSheetName(), dvs);
    }
    return dvs;
}
 

/**
 * 设置 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);
}
 

/**
 * Creates a data validation object
 *
 * @param dataValidation The Data validation object settings
 */
@Override
public void addValidationData(DataValidation dataValidation) {
    if (dataValidation == null) {
        throw new IllegalArgumentException("objValidation must not be null");
    }
    HSSFDataValidation hssfDataValidation = (HSSFDataValidation) dataValidation;
    DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();

    DVRecord dvRecord = hssfDataValidation.createDVRecord(this);
    dvt.addDataValidation(dvRecord);
}
 

/**
 * 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);
}
 

/**
 * 设置 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);
}
 

/**
 * @return the dv
 */
public DataValidation getValidation() {
    return dv;
}
 

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

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

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

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();
}
 

/**
 *
 * @param dv
 * @param dve
 * @param region
 * @param target
 */
public DataValidationContext(DataValidation dv, DataValidationEvaluator dve, CellRangeAddressBase region, CellReference target) {
    this.dv = dv;
    this.dve = dve;
    this.region = region;
    this.target = target;
}
 

/**
 * Finds and returns the {@link DataValidation} 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 - use this in case the cell does not actually exist yet
 * @return the DataValidation applicable to the given cell, or null if no
 *         validation applies
 */
public DataValidation getValidationForCell(CellReference cell) {
    final DataValidationContext vc = getValidationContextForCell(cell);
    return vc == null ? null : vc.getValidation();
}