import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @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;
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
/**
 * 指定した範囲のセルに制約を追加する。
 * <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);
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
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
	 */
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
private void insertDropBox(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col, String ref) {
	if (dvHelper != null) {
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(ref);
		CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
		XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
		validation.setShowErrorBox(true);
		validation.setSuppressDropDownArrow(true);
		validation.setShowPromptBox(true);
		sheetTracing.addValidationData(validation);
	}
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
private void insertCondition(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col, String min, String max) {
	if (dvHelper != null) {
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createIntegerConstraint(OperatorType.BETWEEN, min, max);
		// dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"0000011", "0000021", "0000031"});
		CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
		XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
		validation.setShowErrorBox(true);
		validation.setSuppressDropDownArrow(true);
		validation.setShowPromptBox(true);
		sheetTracing.addValidationData(validation);
	}
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
private void insertDecCondition(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col) {
	if (dvHelper != null) {
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createDecimalConstraint(OperatorType.GREATER_OR_EQUAL, "0", "");
		// dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"0000011", "0000021", "0000031"});
		CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
		XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
		validation.setShowErrorBox(true);
		validation.setSuppressDropDownArrow(true);
		validation.setShowPromptBox(true);
		sheetTracing.addValidationData(validation);			
	}
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
/**
 * 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;
    
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
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);
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
private static void setupRegions(InputRule rule) {
	CellRangeAddressList list = new CellRangeAddressList();
	for (String str : rule.regionsStr) {
		list.addCellRangeAddress(CellRangeAddress.valueOf(str));
	}
	rule.regions = list;
}
 

import org.apache.poi.ss.util.CellRangeAddressList; //导入依赖的package包/类
/**
 * 文字列形式のセルの範囲が同じかどうか比較する。
 * @since 0.5
 * @param region1
 * @param region2
 * @return
 */
public static boolean equalsRegion(final CellRangeAddressList region1, final CellRangeAddressList region2) {
    
    return equalsSqref(convertSqref(region1), convertSqref(region2));
    
}