注意是:XSSFDataValidationHelper,也即:XSSFDataValidationConstraint(4, operatorType, formula1, formula2)有Bug,HSSF*的没有问题,POI 3.8-POI3.12都试过了,都有此问题
/**
* 设置ExcelSheet数据填写有效性约束
* @param sheet 当前Sheet
* @param validationType 验证类型
* @param operatorType 操作符
* @param rowIndex 行索引
* @param colIndex 列索引
* @param vMin 最小值
* @param vMax 最大值
* @param errorTitle 出错提示标题
* @param errorMsg 出错提示信息
*/
public static void setDataValidationCheck(Sheet sheet, int validationType, int operatorType, int rowIndex, int colIndex, String vMin, String vMax, String errorTitle, String errorMsg) {
if(null==sheet || rowIndex<0 || colIndex <0) {
return;
}
//两个值都为空则返回
vMin = null == vMin ? "" : vMin.trim();
vMax = null == vMax ? "" : vMax.trim();
if("".equals(vMin) && "".equals(vMax)) {
return;
}
//
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = null;
if(validationType == DataValidationConstraint.ValidationType.TEXT_LENGTH) {
dvConstraint = dvHelper.createTextLengthConstraint(operatorType, vMin, vMax);
} else if(validationType == DataValidationConstraint.ValidationType.DECIMAL) {
dvConstraint = dvHelper.createDecimalConstraint(operatorType, vMin, vMax);
} else if(validationType == DataValidationConstraint.ValidationType.INTEGER) {
dvConstraint = dvHelper.createIntegerConstraint(operatorType, vMin, vMax);
} else if(validationType == DataValidationConstraint.ValidationType.DATE) {
dvConstraint = dvHelper.createDateConstraint(operatorType, vMin, vMax, null);
} else if(validationType == DataValidationConstraint.ValidationType.TIME) {
dvConstraint = dvHelper.createTimeConstraint(operatorType, vMin, vMax);
}
if(null == dvConstraint) {
return;
}
CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, getLastRowIndex(sheet.getWorkbook()), colIndex, colIndex);
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
//设置出错提示信息
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
setDataValidationErrorMessage(validation, errorTitle, errorMsg);
sheet.addValidationData(validation);
}
官方不知是否已发现这个Bug,没找到POI论坛发表。