如何使用java中的apache poi将数据验证添加到Excel工作表的整个列？

I have a requirement Where I need to add data validation to an entire column rather than a specific cell. I went through the documentation of Apache poi and found the example below

我有一个要求我需要在整个列而不是特定单元格中添加数据验证。我浏览了Apache poi的文档并找到了下面的例子

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
        new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation
        (addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

But the above example creates a drop down datavalidation for a specific cell. In this case row 0, column 0. The rest of the cells in a column doesn't have validation. But in actual excel file we can do it so it should be possible. I tried and searched a lot but could not come to a solution. Please help..

但是上面的示例为特定单元格创建了下拉数据验证。在这种情况下,第0行,第0列。列中的其余单元格没有验证。但是在实际的excel文件中我们可以这样做,所以它应该是可能的。我经常尝试和搜索,但无法找到解决方案。请帮忙..

2 个解决方案

#1

Chetan all the four parameters of the constructor CellRangeAddressList as below

Chetan构造函数CellRangeAddressList的所有四个参数如下所示

CellRangeAddressList(index_of_starting_row, index_of_ending_row, index_of_starting_column,index_of_ending_column);

so eg. if there are 10 rows and 5 columns, and if you want to add drop down list in 2nd column throughout the all rows means in entire 2nd column then use below code for cell address

所以,例如。如果有10行和5列,并且如果要在第2列中添加下拉列表,则在所有行中表示整个第2列,然后使用下面的代码作为单元格地址

CellRangeAddressList addressList = new CellRangeAddressList(0,9,1,1);

based on your code this will add drop down with the values 10,20,30 in entire 2nd column if you add the above line of code by replacing your code.

根据您的代码,如果您通过替换代码添加上面的代码行,则会在整个第二列中添加值为10,20,30的下拉列表。

hope this will clear the concept and you would get the desire result.

希望这将清除这个概念,你会得到渴望的结果。

#2

Another way to get the validation on the whole column you can also use -1 for both row parameters like:

另一种获取整个列验证的方法,您也可以使用-1作为两个行参数,例如:

CellRangeAddressList columnRange = new CellRangeAddressList(-1, -1, columnIndex, columnIndex);

Tested this in POI 3.16

在POI 3.16中测试过

#1