How to add Data validation to entire column of an excel sheet using apache poi in java?

6
votes

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

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..

3
javaexcelapache-poivalidation
What happens if you set a cell range that runs from row 0 to row (quite a lot)? – Gagravarr
if you do it to a quite a lot of row numbers then it will apply data validation to all the mentioned rows, but after that it will not – Chetan

3 Answers

8
votes

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

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

Tested this in POI 3.16

5
votes

Chetan all the four parameters of the constructor CellRangeAddressList as below

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

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.

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

0
votes

I'm trying to add below data validation but 1-3 is getting converted to 3-Jan in excel drop down. Is there a way to fix this?

DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"1-3", "2", "3"});

Note - Before adding data validation, I'm using code in the first answer here - How to set a cell format to Text to set data type of 2nd column to text. Else, on entering 1-3 in excel cell, it was getting converted to 3-Jan automatically.

This solved issue of entering 1-3 in excel cell directly, but in drop down, I'm still seeing 3-Jan.