应用场景
导出效果
限制效果
实现方式实现SheetWriteHandler过滤器
SheetWriteHandler过滤器（处理器）的作用就是可以让我们自定义单元格的样式，我们常用的是其中的afterSheetCreate()方法，在该方法中，我们可以对当前所在的单元格进行样式的设置。
public class ProfessionSheetWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
}其中关键在于DataValidationHelper 实例，中文意思为 验证助手
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();DataValidationHelper 是一个接口，其中定义了各种验证接口
public interface DataValidationHelper {
// 创建公式列表约束
DataValidationConstraint createFormulaListConstraint(String var1);
// 创建显式列表约束
DataValidationConstraint createExplicitListConstraint(String[] var1);
// 创建数值约束
DataValidationConstraint createNumericConstraint(int var1, int var2, String var3, String var4);
// 创建文本长度约束
DataValidationConstraint createTextLengthConstraint(int var1, String var2, String var3);
// 创建十进制约束
DataValidationConstraint createDecimalConstraint(int var1, String var2, String var3);
// 创建整数制约束
DataValidationConstraint createIntegerConstraint(int var1, String var2, String var3);
// 创建日期制约束
DataValidationConstraint createDateConstraint(int var1, String var2, String var3, String var4);
// 创建时间约束
DataValidationConstraint createTimeConstraint(int var1, String var2, String var3);
// 创建自定义约束
DataValidationConstraint createCustomConstraint(String var1);
// 创建验证
DataValidation createValidation(DataValidationConstraint var1, CellRangeAddressList var2);
}
定义约束下拉框，且只能是下拉跨所拥有的值
public class BillSheetWriteHandler implements SheetWriteHandler {
/**
* v1 - v2 前两个值为几行到几行 v3 -v4 后两个值为几列到几列
*/
private CellRangeAddressList cellRangeAddressList;
/**
* 下拉表的值
*/
private String[] value;
/**
* 是否设置下拉选
*/
private Boolean state;
public BillSheetWriteHandler(CellRangeAddressList cellRangeAddressList, String[] value, Boolean state) {
this.cellRangeAddressList = cellRangeAddressList;
this.value = value;
this.state = state;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 定义下拉框
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = writeWorkbookHolder.getWorkbook();
workbook.createCellStyle().setLocked(true);
if (state) {
DataValidationHelper helper = sheet.getDataValidationHelper();
if (value.length == 0) {
throw new ServiceWarnException(BillExcelConstant.DROP_DOWN_ARRAY);
}
DataValidationConstraint constraint = helper.createExplicitListConstraint(value);
// 指定行列约束
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
// 阻止输入非下拉选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox(BillExcelConstant.CREATE_ERROR_BOX_HINT, BillExcelConstant.CREATE_ERROR_BOX_INCONSISTENT);
sheet.addValidationData(dataValidation);
}
// 目前不生效
sheet.createFreezePane(0, 1, 0, 1);
// 默认筛选条件
sheet.setAutoFilter(CellRangeAddress.valueOf("1:1"));
}
}使限制生效：
// 声明单元格出现列及行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
WriteSheet mainSheet = EasyExcel.writerSheet(i, sheetName.get(i)).head(classType).registerWriteHandler(new BillSheetWriteHandler(cellRangeAddressList, value, state)).build();
阿拉伯数字核心验证器：
定义整数约束，且在指定范围内
// 创建数值约束
DataValidationConstraint integerConstraint = helper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, "1", "9999999");
// 创建验证
DataValidation validation = helper.createValidation(integerConstraint, HeaderVerify.getSkillCellRangeAddressList());
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "必须输入数字,且上下限为1~999999");任何方法中涉及validationType和OperatorType参数时，需要根据DataValidationConstraint.ValidationType|DataValidationConstraint.OperatorType
源码如下：
public static final class ValidationType {
public static final int ANY = 0;
public static final int INTEGER = 1;
public static final int DECIMAL = 2;
public static final int LIST = 3;
public static final int DATE = 4;
public static final int TIME = 5;
public static final int TEXT_LENGTH = 6;
public static final int FORMULA = 7;
private ValidationType() {
}
}
public static final class OperatorType {
public static final int BETWEEN = 0;
public static final int NOT_BETWEEN = 1;
public static final int EQUAL = 2;
public static final int NOT_EQUAL = 3;
public static final int GREATER_THAN = 4;
public static final int LESS_THAN = 5;
public static final int GREATER_OR_EQUAL = 6;
public static final int LESS_OR_EQUAL = 7;
public static final int IGNORED = 0;
private OperatorType() {
}
public static void validateSecondArg(int comparisonOperator, String paramValue) {
switch (comparisonOperator) {
case 0:
case 1:
if (paramValue == null) {
throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
}
default:
}
}
}另外创建日期约束时需要使用 'Date(1999, 1, 1)' 公式
DataValidationConstraint dateConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"Date(1999, 1, 1)","Date(2023, 12, 1)","yyyy-MM-dd");
666