org.apache.poi.ss.util.CellRangeAddressList 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 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 static void setDataValidationList(int j, int k, int l, int m, String data, XSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	// 设置数据有效性加载在哪个单元格上。
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textlist);
	// 四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
	// data_validation_list.setSuppressDropDownArrow(false);
	
	sheet.addValidationData(data_validation_list);
}
 

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 *
 * @param sheet    要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow   结束行
 * @param firstCol 开始列
 * @param endCol   结束列
 * @return 设置好的sheet.
 */
public void setXssValidation(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;
}
 

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @param sheet    要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstCol 开始列
 * @param endCol   结束列
 */
private static void setXSSFValidation(Sheet sheet, String[] textlist, int firstCol, int endCol) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(1, 100, 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 xssfWsheet
 * @param list
 * @param firstRow
 * @param lastRow
 * @param firstCol
 * @param lastCol
 */
public static void setDataValidation(SXSSFSheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
    DataValidationHelper helper = xssfWsheet.getDataValidationHelper();
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
    DataValidation dataValidation = helper.createValidation(constraint, addressList);
    dataValidation.createErrorBox(DataValidationError1, DataValidationError2);
    //  处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {
        dataValidation.setSuppressDropDownArrow(false);
    }
    xssfWsheet.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);
}
 

public DVRecord(RecordInputStream in) {

		_option_flags = in.readInt();

		_promptTitle = readUnicodeString(in);
		_errorTitle = readUnicodeString(in);
		_promptText = readUnicodeString(in);
		_errorText = readUnicodeString(in);

		int field_size_first_formula = in.readUShort();
		_not_used_1 = in.readShort();

		// "You may not use unions, intersections or array constants in Data Validation criteria"

		// read first formula data condition
		_formula1 = Formula.read(field_size_first_formula, in);

		int field_size_sec_formula = in.readUShort();
		_not_used_2 = in.readShort();

		// read sec formula data condition
		_formula2 = Formula.read(field_size_sec_formula, in);

		// read cell range address list with all affected ranges
		_regions = new CellRangeAddressList(in);
	}
 

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

private static void setDataValidationList(int j, int k, int l, int m, String data, HSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
	// 设置数据有效性加载在哪个单元格上。
	
	// 四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
	data_validation_list.setSuppressDropDownArrow(false);
	sheet.addValidationData(data_validation_list);
}
 

/**
 * Add date validation when export
 *
 * @param operatorType operatorType
 * @param expr1        Date expression 1, such as: 2019-12-12
 * @param expr2        Date expression 2(Only operation types between and notBetween are required)，such as：2019-12-24
 * @param pattern      Date pattern
 * @param sheet        Current sheet
 * @param firstRow     Start row
 * @param lastRow      End row
 * @param colIndex     Column index
 * @param showErrorBox Whether show error box
 * @param errorBoxRank Error box rank
 * @param errorTitle   Error box title
 * @param errorContent Error box value
 * @param showTip      Whether show tip
 * @param tipContent   Tip content
 * @param tipTitle     Tip title
 */
public static void addDateValid(OperatorType operatorType, String expr1, String expr2, String pattern, Sheet sheet, int firstRow, int lastRow,
                                int colIndex, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent, boolean showTip,
                                String tipTitle, String tipContent) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint;
    if (sheet instanceof SXSSFSheet) {
        dvConstraint = helper.createDateConstraint(operatorType.getType(), "date(" + expr1.replaceAll("-", ",") + ")",
                "date(" + expr2.replaceAll("-", ",") + ")", pattern);
    } else {
        dvConstraint = helper.createDateConstraint(operatorType.getType(), expr1, expr2, pattern);
    }
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
    DataValidation dataValidation = helper.createValidation(dvConstraint, regions);
    dataValidation.setShowErrorBox(showErrorBox);
    dataValidation.setErrorStyle(errorBoxRank.getRank());
    dataValidation.createErrorBox(errorTitle, errorContent);
    if (showTip) {
        dataValidation.createPromptBox(tipTitle, tipContent);
    }
    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)
{
    // 加载下拉列表内容
    DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
    sheet.addValidationData(dataValidationList);
    return sheet;
}
 

/**
 * Set cell ranges list to a single cell range and 
 * modify the enclosing cell range accordingly.
 * @param cellRanges - list of CellRange objects
 */
public void setCellRanges(CellRangeAddress[] cellRanges) {
    if(cellRanges == null) {
        throw new IllegalArgumentException("cellRanges must not be null");
    }
    CellRangeAddressList cral = new CellRangeAddressList();
    CellRangeAddress enclosingRange = null;
    for (int i = 0; i < cellRanges.length; i++) {
        CellRangeAddress cr = cellRanges[i];
        enclosingRange = CellRangeUtil.createEnclosingCellRange(cr, enclosingRange);
        cral.addCellRangeAddress(cr);
    }
    field_3_enclosing_cell_range = enclosingRange;
    field_4_cell_ranges = cral;
}
 

public int getRecordSize() {
	// a bit cheaper than the default impl
	int nRegions = _mergedRegions.size();
	if (nRegions < 1) {
		// no need to write a single empty MergeCellsRecord
		return 0;
	}
	int nMergedCellsRecords = nRegions / MAX_MERGED_REGIONS;
	int nLeftoverMergedRegions = nRegions % MAX_MERGED_REGIONS;

	int result = nMergedCellsRecords
			* (4 + CellRangeAddressList.getEncodedSize(MAX_MERGED_REGIONS)) + 4
			+ CellRangeAddressList.getEncodedSize(nLeftoverMergedRegions);
	return result;
}
 

@Override
public List<HSSFDataValidation> getDataValidations() {
    DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
    final List<HSSFDataValidation> hssfValidations = new ArrayList<HSSFDataValidation>();
    RecordVisitor visitor = new RecordVisitor() {
        private HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(getWorkbook());

        @Override
        public void visitRecord(Record r) {
            if (!(r instanceof DVRecord)) {
                return;
            }
            DVRecord dvRecord = (DVRecord) r;
            CellRangeAddressList regions = dvRecord.getCellRangeAddress().copy();
            DVConstraint constraint = DVConstraint.createDVConstraint(dvRecord, book);
            HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint);
            hssfDataValidation.setErrorStyle(dvRecord.getErrorStyle());
            hssfDataValidation.setEmptyCellAllowed(dvRecord.getEmptyCellAllowed());
            hssfDataValidation.setSuppressDropDownArrow(dvRecord.getSuppressDropdownArrow());
            hssfDataValidation.createPromptBox(dvRecord.getPromptTitle(), dvRecord.getPromptText());
            hssfDataValidation.setShowPromptBox(dvRecord.getShowPromptOnCellSelected());
            hssfDataValidation.createErrorBox(dvRecord.getErrorTitle(), dvRecord.getErrorText());
            hssfDataValidation.setShowErrorBox(dvRecord.getShowErrorOnInvalidValue());
            hssfValidations.add(hssfDataValidation);
        }
    };
    dvt.visitContainedRecords(visitor);
    return hssfValidations;
}
 

public DVRecord(int validationType, int operator, int errorStyle, boolean emptyCellAllowed,
		boolean suppressDropDownArrow, boolean isExplicitList,
		boolean showPromptBox, String promptTitle, String promptText, 
		boolean showErrorBox, String errorTitle, String errorText,
		Ptg[] formula1, Ptg[] formula2,
		CellRangeAddressList regions) {
	
	// check length-limits
	if(promptTitle != null && promptTitle.length() > 32) {
		throw new IllegalStateException("Prompt-title cannot be longer than 32 characters, but had: " + promptTitle);
	}
	if(promptText != null && promptText.length() > 255) {
		throw new IllegalStateException("Prompt-text cannot be longer than 255 characters, but had: " + promptText);
	}

	if(errorTitle != null && errorTitle.length() > 32) {
		throw new IllegalStateException("Error-title cannot be longer than 32 characters, but had: " + errorTitle);
	}
	if(errorText != null && errorText.length() > 255) {
		throw new IllegalStateException("Error-text cannot be longer than 255 characters, but had: " + errorText);
	}

	int flags = 0;
	flags = opt_data_type.setValue(flags, validationType);
	flags = opt_condition_operator.setValue(flags, operator);
	flags = opt_error_style.setValue(flags, errorStyle);
	flags = opt_empty_cell_allowed.setBoolean(flags, emptyCellAllowed);
	flags = opt_suppress_dropdown_arrow.setBoolean(flags, suppressDropDownArrow);
	flags = opt_string_list_formula.setBoolean(flags, isExplicitList);
	flags = opt_show_prompt_on_cell_selected.setBoolean(flags, showPromptBox);
	flags = opt_show_error_on_invalid_value.setBoolean(flags, showErrorBox);
	_option_flags = flags;
	_promptTitle = resolveTitleText(promptTitle);
	_promptText = resolveTitleText(promptText);
	_errorTitle = resolveTitleText(errorTitle);
	_errorText = resolveTitleText(errorText);
	_formula1 = Formula.create(formula1);
	_formula2 = Formula.create(formula2);
	_regions = regions;
}
 

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

/**
 * 设置单元格上提示
 * 
 * @param sheet 要设置的sheet.
 * @param promptTitle 标题
 * @param promptContent 内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public static Sheet setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
        int firstCol, int endCol)
{
    // 构造constraint对象
    DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
    // 四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
    dataValidationView.createPromptBox(promptTitle, promptContent);
    sheet.addValidationData(dataValidationView);
    return sheet;
}
 

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

/**
 * CellRangeAddressを文字列形式のリストに変換する。
 * @since 0.5
 * @param region
 * @return
 */
private static List<String> convertSqref(final CellRangeAddressList region) {

    List<String> sqref = new ArrayList<>();
    for(CellRangeAddress range : region.getCellRangeAddresses()) {
        sqref.add(range.formatAsString());
    }

    return sqref;

}
 

/**
 * Add a dropdown box when export
 *
 * @param combobox     Dropdown box content of 25 or less
 * @param showErrorBox Whether show error box
 * @param errorBoxRank Error box rank
 * @param errorTitle   Error box title
 * @param errorContent Error box value
 * @param workbook     Current workbook
 * @param sheet        Current sheet
 * @param firstRow     Start row
 * @param lastRow      End row
 * @param colIndex     Column index
 * @param values       The dropdown box can be large, but if it's version 07, it's limited by the window size in the Excel annotation{@link Excel}
 */
public static void addDropdownBox(String[] combobox, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent,
                                  Workbook workbook, Sheet sheet, int firstRow, int lastRow, int colIndex, String[] values) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint;
    if (values == null) {
        constraint = helper.createExplicitListConstraint(combobox);
    } else {
        Sheet explicitSheet = workbook.getSheet("explicitSheet");
        if (explicitSheet == null) {
            explicitSheet = workbook.createSheet("explicitSheet");
        }
        int valueLength = values.length;
        for (int i = 0; i < valueLength; i++) {
            Row explicitSheetRow = explicitSheet.getRow(i);
            if (explicitSheetRow == null) {
                explicitSheetRow = explicitSheet.createRow(i);
            }
            explicitSheetRow.createCell(colIndex).setCellValue(values[i]);
        }
        char colOffset = (char) ('A' + colIndex);
        constraint = helper.createFormulaListConstraint(explicitSheet.getSheetName() + "!$" + colOffset + "$1:$" + colOffset + "$" + (valueLength == 0 ? 1 : valueLength));
        workbook.setSheetHidden(workbook.getSheetIndex("explicitSheet"), true);
    }
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    dataValidation.setShowErrorBox(showErrorBox);
    dataValidation.setErrorStyle(errorBoxRank.getRank());
    dataValidation.createErrorBox(errorTitle, errorContent);
    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);
}
 

/**
 * 设置单元格上提示
 * 
 * @param sheet 要设置的sheet.
 * @param promptTitle 标题
 * @param promptContent 内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 */
public static void setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                                 int firstCol, int endCol)
{
    // 构造constraint对象
    DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
    // 四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
    dataValidationView.createPromptBox(promptTitle, promptContent);
    sheet.addValidationData(dataValidationView);
}
 

/**
 * 设置 POI XSSFSheet 单元格提示
 * @param sheet 表单
 * @param promptContent 提示内容
 * @param firstCol 开始列
 * @param endCol 结束列
 */
private static void setXSSFPrompt(Sheet sheet, String promptContent, int firstCol, int endCol) {
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = dvHelper.createCustomConstraint("DD1");
    CellRangeAddressList regions = new CellRangeAddressList(1, 100, firstCol, endCol);
    DataValidation dataValidation = dvHelper.createValidation(constraint, regions);
    dataValidation.createPromptBox("", promptContent);
    dataValidation.setShowPromptBox(true);
    sheet.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);
}
 

public CellRangeAddressList getRegions() {
	return _regions;
}
 

private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex();
    int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex();
    int step = templateRowEndIndex - templateRowStartIndex + 1;
    int rowStartIndex = sheetProcessor.getStartRowIndex();

    Set<Integer> configColIndexSet = new HashSet<Integer>();
    for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor.getFieldMapping().export().entrySet()) {
        if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) {
            continue;
        }
        for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping.getValue().entrySet()) {
            if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) {
                continue;
            }
            configColIndexSet.add(indexProcessorMapping.getKey());
        }
    }

    List<? extends DataValidation> dataValidations = sheet.getDataValidations();
    if (dataValidations != null) {
        for (DataValidation dataValidation : dataValidations) {
            if (dataValidation == null) {
                continue;
            }
            CellRangeAddressList cellRangeAddressList = dataValidation.getRegions();
            if (cellRangeAddressList == null) {
                continue;
            }

            CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
            if (cellRangeAddresses == null || cellRangeAddresses.length == 0) {
                continue;
            }

            CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList();
            boolean validationContains = false;
            for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                if (cellRangeAddress == null) {
                    continue;
                }
                if (templateRowEndIndex < cellRangeAddress.getFirstRow()
                    || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row
                    continue;
                }
                for (Integer configColIndex : configColIndexSet) {
                    if (configColIndex < cellRangeAddress.getFirstColumn()
                        || configColIndex > cellRangeAddress.getLastColumn()) {// specify column
                        continue;
                    }
                    if (templateRowStartIndex == templateRowEndIndex) {
                        newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex,
                                                                    sheet.getLastRowNum(), configColIndex);
                        validationContains = true;
                    } else {
                        int start = cellRangeAddress.getFirstRow() > templateRowStartIndex ? cellRangeAddress.getFirstRow() : templateRowStartIndex;
                        int end = cellRangeAddress.getLastRow() < templateRowEndIndex ? cellRangeAddress.getLastRow() : templateRowEndIndex;
                        long lastRow = sheet.getLastRowNum();
                        if (lastRow > end) {
                            long count = (lastRow - templateRowEndIndex) / step;
                            int i = templateRowEndIndex;
                            for (; i < count; i++) {
                                newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex,
                                                                            end + i * step, configColIndex);
                                validationContains = true;
                            }
                            long _start = start + i * step;
                            if (_start <= lastRow) {
                                long _end = end + i * step;
                                _end = _end < lastRow ? _end : lastRow;
                                newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex,
                                                                            (int) _end, configColIndex);
                                validationContains = true;
                            }
                        }
                    }
                }
            }
            if (validationContains) {
                DataValidation newDataValidation = sheet.getDataValidationHelper().createValidation(dataValidation.getValidationConstraint(),
                                                                                                    newCellRangeAddressList);
                sheet.addValidationData(newDataValidation);
            }
        }
    }
}