POI(二)
Java POI-3.17
数据有效性
Excel中单元格的数据有效性验证功能通过DataValidation接口支持。HSSFWorkbook和XSSFWorkbook设置数据有效性的API略有差异。
-
HSSFWorkbook
// 数据有效性的单元格范围 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); // 数据有效性类型 DVConstraint constraint = DVConstraint.create...Constraint(...); // 实例化数据有效性 DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
-
XSSFWorkbook
// 数据有效性的单元格范围 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); // 数据有效性类型 DataValidationConstraint dvConstraint = dvHelper.create...Constraint(...); // 实例化数据有效性 DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
-
作用到Sheet
DataValidation dataValidation = ...;// 实例化数据绑定 sheet.addValidationData(dataValidation);
日期有效性
/**
* 获取日期数据有效性
* 日期固定在1900-1-1 ~ 2999-12-31
*
* @param fileType
* @param sheet
* @param firstRow
* @param lastRow
* @param firstCow
* @param lastCow
* @return
*/
public static DataValidation getDataValidationByDateValidation(String fileType, Sheet sheet, int firstRow,
int lastRow, int firstCow,
int lastCow) {
DataValidation dataValidation = null;
if (Objects.nonNull(fileType) && "xls".equals(fileType)) {
// 设置第key列的1-65535行为下拉列表
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow);
// 创建下拉列表数据
DVConstraint constraint = DVConstraint.createDateConstraint(
DataValidationConstraint.OperatorType.BETWEEN,
"1900-1-1", "2099-12-31", "yyyy-MM-dd");
// 绑定
dataValidation = new HSSFDataValidation(regions, constraint);
} else if (Objects.nonNull(fileType) && "xlsx".equals(fileType)) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)",
"Date(2099, 12, 31)", DEFAULT_DATE_FORMAT);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow);
dataValidation = dvHelper.createValidation(dvConstraint, regions);
}
return dataValidation;
}
时间有效性
/**
* 获取时间数据有效性
* 日期固定在00:00:00 ~ 23:59:59
*
* @param fileType
* @param sheet
* @param firstRow
* @param lastRow
* @param firstCow
* @param lastCow
* @return
*/
public static DataValidation getDataValidationByDateValidation(String fileType, Sheet sheet, int firstRow,
int lastRow, int firstCow,
int lastCow) {
DataValidation dataValidation = null;
if (Objects.nonNull(fileType) && "xls".equals(fileType)) {
// 设置第key列的1-65535行为下拉列表
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow);
// 创建下拉列表数据
DVConstraint constraint = DVConstraint
.createTimeConstraint(DataValidationConstraint.OperatorType.BETWEEN,
"00:00:00", "23:59:59");
// 绑定
dataValidation = new HSSFDataValidation(regions, constraint);
} else if (Objects.nonNull(fileType) && "xlsx".equals(fileType)) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
DataValidationConstraint dvConstraint = dvHelper .createTimeConstraint(DataValidationConstraint.OperatorType.BETWEEN, "00:00:00", "23:59:59");
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow);
dataValidation = dvHelper.createValidation(dvConstraint, regions);
}
return dataValidation;
}
序列有效性(下拉框)
序列数据有效性生成后会已下拉框的形式进行展示,不足是POI只能支持单选下拉框。多选下拉框功能查阅资料,需要借助vbs脚本进行实现(T_T)。
-
ExplicitList
如果已经有明确的下拉序列就可以使用这种ExplicitListConstraint数据有效性。
/** * 获取下拉样式 * * @param fileType * @param sheet * @param firstRow * @param lastRow * @param firstCow * @param lastCow * @param explicitList * @return */ public static DataValidation getDataValidation(String fileType, Sheet sheet, int firstRow, int lastRow, int firstCow, int lastCow, String[] explicitList) { DataValidation dataValidation = null; if (Objects.nonNull(fileType) && "xls".equals(fileType)) { CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); DVConstraint constraint = DVConstraint. createExplicitListConstraint(explicitList); dataValidation = new HSSFDataValidation(regions, constraint); } else if (Objects.nonNull(fileType) && "xlsx".equals(fileType)) { XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); DataValidationConstraint dvConstraint = dvHelper .createExplicitListConstraint(explicitList); CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); dataValidation = dvHelper.createValidation(dvConstraint, addressList); } return dataValidation; }
-
FormulaList
FormulaList是基于公式进行序列生成,比ExplicitList更加强大。联级下拉框的实现是通过该方法进行实现。
public static DataValidation getDataValidation(String fileType, Sheet sheet, int firstRow, int lastRow, int firstCow, int lastCow, String formulaList) { DataValidation dataValidation = null; if (Objects.nonNull(fileType) && "xls".equals(fileType)) { CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaList); dataValidation = new HSSFDataValidation(regions, constraint); } else if (Objects.nonNull(fileType) && "xlsx".equals(fileType)) { XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper .createFormulaListConstraint(formulaList); CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCow, lastCow); dataValidation = dvHelper.createValidation(dvConstraint, regions); } return dataValidation; }
formulaList参数示例:
‘sheet1’! A 2 : A2: A2:A20 :选择sheet名为sheet1的A列第2行至第20行,绑定后下拉框相应展示这些内容。
其他有效性
其他有效性API也是类似上面的,只要按要求放入参数即可。
数据有效性的辅助功能
-
允许单元格为空
DataValidation dataValidation = ...; dataValidation.setEmptyCellAllowed(true);
-
提供下拉箭头
DataValidation dataValidation = ...; dataValidation.setSuppressDropDownArrow(true);
-
输入提示
让绑定了数据有效性的单元格支持输入提示。该功能也属于DataValidation。
DataValidation dataValidation = ...; dataValidation.setShowPromptBox(true); dataValidation.createPromptBox(TITLE, CONTENT);//标题-TITLE,内容-CONTENT
-
错误提示
-
不对错误数据进行验证
DataValidation dataValidation = ...; dataValidation1.setShowErrorBox(false);
-
对错误进行验证并提示
错误提示有三种错误样式:停止、警告、信息。在停止样式下,错误数据将无法录入单元格。
DataValidation dataValidation = ...; dataValidation1.setShowErrorBox(true); dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);//错误样式 dataValidation.createErrorBox(TITLE, CONTENT);//标题-TITLE,内容-CONTENT
-