用NPOI操作EXCEL--生成下拉列表
设置步骤为:
(1)选定一个要生成下拉列表的区域;
(2)设置数据有效性为序列,并在来源中填充可选下拉的值,用“,”隔开(如图)。
对应的效果为:
同样,利用NPOI代码也可以实现上面的效果:
下面对代码作一下简要说明:
(1)选定一个要生成下拉列表的区域;
(2)设置数据有效性为序列,并在来源中填充可选下拉的值,用“,”隔开(如图)。
对应的效果为:
同样,利用NPOI代码也可以实现上面的效果:
HSSFSheet sheet1
=
hssfworkbook.CreateSheet(
"
Sheet1
"
);
CellRangeAddressList regions = new CellRangeAddressList( 0 , 65535 , 0 , 0 );
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( new string [] { " itemA " , " itemB " , " itemC " });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
CellRangeAddressList regions = new CellRangeAddressList( 0 , 65535 , 0 , 0 );
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( new string [] { " itemA " , " itemB " , " itemC " });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
先设置一个需要提供下拉的区域,关于CellRangeAddressList构造函数参数的说明请参见 上一节:
CellRangeAddressList regions
=
new
CellRangeAddressList(
0
,
65535
,
0
,
0
);
然后将下拉项作为一个数组传给CreateExplicitListConstraint作为参数创建一个约束,根据要控制的区域和约束创建数据有效性就可以了。
但是这样会有一个问题:Excel中允许输入的序列来源长度最大为255个字符,也就是说当下拉项的总字符串长度超过255是将会出错。那么如果下拉项很多的情况下应该怎么处理呢?答案是通过引用的方式。步骤如下:
先创建一个Sheet专门用于存储下拉项的值,并将各下拉项的值写入其中:
HSSFSheet sheet2
=
hssfworkbook.CreateSheet(
"
ShtDictionary
"
);
sheet2.CreateRow( 0 ).CreateCell( 0 ).SetCellValue( " itemA " );
sheet2.CreateRow( 1 ).CreateCell( 0 ).SetCellValue( " itemB " );
sheet2.CreateRow( 2 ).CreateCell( 0 ).SetCellValue( " itemC " );
然后定义一个名称,指向刚才创建的下拉项的区域:
sheet2.CreateRow( 0 ).CreateCell( 0 ).SetCellValue( " itemA " );
sheet2.CreateRow( 1 ).CreateCell( 0 ).SetCellValue( " itemB " );
sheet2.CreateRow( 2 ).CreateCell( 0 ).SetCellValue( " itemC " );
HSSFName range
=
hssfworkbook.CreateName();
range.Reference = " ShtDictionary!$A1:$A3 " ;
range.NameName = " dicRange " ;
最后,设置数据约束时指向这个名称而不是字符数组:
range.Reference = " ShtDictionary!$A1:$A3 " ;
range.NameName = " dicRange " ;
HSSFSheet sheet1
=
hssfworkbook.CreateSheet(
"
Sheet1
"
);
CellRangeAddressList regions = new CellRangeAddressList( 0 , 65535 , 0 , 0 );
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint( " dicRange " );
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
CellRangeAddressList regions = new CellRangeAddressList( 0 , 65535 , 0 , 0 );
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint( " dicRange " );
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);