POI在操作excel导出的时候,有时候想要设置单元格样式,再次编辑的时候只允许输入特定格式的内容。POI中提供了多种格式的有效性验证方式,下面将自己用到的验证方式以及使用方法列出:
1.日期格式的验证
//创建数据验证类DataValidationHelper helper = sheet.getDataValidationHelper();//设置验证生效的范围CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);//设置验证方式DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"1900-01-01","2099-12-31","yyyy-MM-dd");//创建验证对象DataValidation dataValidation = helper.createValidation(constraint, addressList);//错误提示信息dataValidation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期");dataValidation.setShowErrorBox(true);//验证和工作簿绑定sheet.addValidationData(dataValidation);
更正:在日期验证的时候,formula1和formula2 的书写方式,上述的代码是不正确的,需要使用Date(2000, 1, 1)方式
2.数字格式的验证
DataValidationHelper helper = sheet.getDataValidationHelper();//设置验证生效的范围CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);//设置验证方式DataValidationConstraint constraint =helper.createDecimalConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(0), String.valueOf(999));//创建验证对象DataValidation validation = helper.createValidation(constraint, addressList);//错误提示信息validation.createErrorBox("提示", "请输入【0-999】之间的两位小数");//设置是否显示错误窗口validation.setShowErrorBox(true);//验证和工作簿绑定sheet.addValidationData(validation);
helper.createDecimalConstraint(int operatorType, String formula1, String formula2)方法中,三个参数含义:operatorType,操作类型,formula1,formula2表达式1和表达式2.