java excel xlsx 增加数据验证

隐藏表下拉框

   // 创建隐藏工作表存储下拉框数据String hiddenSheetName = "HiddenSheet"+System.currentTimeMillis();Sheet hiddenSheet =  workbook.createSheet(hiddenSheetName);//设置隐藏sheetworkbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), true);for (int i = 0; i < dropdownList.size(); i++) {Row row = hiddenSheet.createRow(i);Cell cityCellHidden = row.createCell(0);cityCellHidden.setCellValue(dropdownList.get(i));}// 获取DataValidationHelper对象DataValidationHelper helper = mainSheet.getDataValidationHelper();// 定义数据验证的范围CellRangeAddressList addressList = new CellRangeAddressList(startRow,  65535, columnIndex, columnIndex);// 创建数据验证约束 公式可以写成"Sheet2!$A$1:$A$10"。DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenSheetName+"!$A$1:$A$"+dropdownList.size());//生成验证规则DataValidation validation = helper.createValidation(constraint, addressList);// 设置错误提示信息validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);

下拉框

   DataValidationHelper helper = mainSheet.getDataValidationHelper();DataValidationConstraint constraint = helper.createExplicitListConstraint(dropdownList.toArray(new String[0]));CellRangeAddressList addressList = new CellRangeAddressList(startRow, 65535, columnIndex, columnIndex);DataValidation validation = helper.createValidation(constraint, addressList);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);mainSheet.addValidationData(validation);

时间格式及范围验证

       // 创建数据验证助手DataValidationHelper helper = sheet.getDataValidationHelper();CellRangeAddressList dateAddressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);// 获取 LocalDateTime 的最小值LocalDateTime minLocalDateTime = LocalDateTime.MIN;// 获取 LocalDateTime 的最大值LocalDateTime maxLocalDateTime = LocalDateTime.MAX;DataValidationConstraint dataValidationConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,minLocalDateTime.format(DateTimeFormatter.ofPattern(dateFormat)),maxLocalDateTime.format(DateTimeFormatter.ofPattern(dateFormat)),dateFormat);DataValidation validation = helper.createValidation(dataValidationConstraint, dateAddressList);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);validation.createErrorBox("标题","内容");sheet.addValidationData(validation);

integer 验证

// 创建数据验证助手DataValidationHelper helper = sheet.getDataValidationHelper();CellRangeAddressList intAddressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);// 获取 LocalDateTime 的最大值LocalDateTime maxLocalDateTime = LocalDateTime.MAX;DataValidationConstraint dataValidationConstraint = helper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN,String.valueOf(Integer.MIN_VALUE),String.valueOf(Integer.MAX_VALUE));DataValidation validation = helper.createValidation(dataValidationConstraint, intAddressList);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);sheet.addValidationData(validation);