(1)先选定一个区域;
(2)在菜单“数据数据有效性”中设置数据有效性验证(如图)。
![](https://i-blog.csdnimg.cn/blog_migrate/c5fc7ddaa0b3e4f2ac72fe611745bfbc.jpeg)
同样,利用NPOI,用代码也可以实现:
HSSFSheet sheet1
=
hssfworkbook.CreateSheet(
"
Sheet1
"
);
sheet1.CreateRow( 0 ).CreateCell( 0 ).SetCellValue( " 日期列 " );
CellRangeAddressList regions1 = new CellRangeAddressList( 1 , 65535 , 0 , 0 );
DVConstraint constraint1 = DVConstraint.CreateDateConstraint(DVConstraint.OperatorType.BETWEEN, " 1900-01-01 " , " 2999-12-31 " , " yyyy-MM-dd " );
HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1);
dataValidate1.CreateErrorBox( " error " , " You must input a date. " );
sheet1.AddValidationData(dataValidate1);
上面是一个在第一列要求输入1900-1-1至2999-12-31之间日期的有效性验证的例子,生成的Excel效果如下,当输入非法时将给出警告:
sheet1.CreateRow( 0 ).CreateCell( 0 ).SetCellValue( " 日期列 " );
CellRangeAddressList regions1 = new CellRangeAddressList( 1 , 65535 , 0 , 0 );
DVConstraint constraint1 = DVConstraint.CreateDateConstraint(DVConstraint.OperatorType.BETWEEN, " 1900-01-01 " , " 2999-12-31 " , " yyyy-MM-dd " );
HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1);
dataValidate1.CreateErrorBox( " error " , " You must input a date. " );
sheet1.AddValidationData(dataValidate1);
![](https://i-blog.csdnimg.cn/blog_migrate/14335e32cc56a5da4fb2eb4d5a58e5ae.jpeg)
下面对刚才用到的几个方法加以说明:
CellRangeAddressList类表示一个区域,构造函数中的四个参数分别表示起始行序号,终止行序号,起始列序号,终止列序号。所以第一列所在区域就表示为:
//
所有序号都从零算起,第一行标题行除外,所以第一个参数是1,65535是一个Sheet的最大行数
new CellRangeAddressList( 1 , 65535 , 0 , 0 );
另外,CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外,还可以设置成如下一些值,大家可以自己一个个去试看看效果:
new CellRangeAddressList( 1 , 65535 , 0 , 0 );
![](https://i-blog.csdnimg.cn/blog_migrate/33d0eb5a804f9c6a4bf7af0ef2588445.gif)
最后,dataValidate1.CreateErrorBox(title,text),用来创建出错时的提示信息。第一个参数表示提示框的标题,第二个参数表示提示框的内容。
理解了上面这些,创建一个整数类型的有效性验证也不难实现:
sheet1.CreateRow(
0
).CreateCell(
1
).SetCellValue(
"
数值列
"
);
CellRangeAddressList regions2 = new CellRangeAddressList( 1 , 65535 , 1 , 1 );
DVConstraint constraint2 = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, " 0 " , " 100 " );
HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2);
dataValidate2.CreateErrorBox( " error " , " You must input a numeric between 0 and 100. " );
sheet1.AddValidationData(dataValidate2);
CellRangeAddressList regions2 = new CellRangeAddressList( 1 , 65535 , 1 , 1 );
DVConstraint constraint2 = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, " 0 " , " 100 " );
HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2);
dataValidate2.CreateErrorBox( " error " , " You must input a numeric between 0 and 100. " );
sheet1.AddValidationData(dataValidate2);
生成的Excel效果为:
![](https://i-blog.csdnimg.cn/blog_migrate/929d446b32d5dbbf200ed8733cbf8161.jpeg)
下一节我们将学习利用数据有效性创建下拉列表的例子。