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

Question

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

Answer 1

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

Answer 2

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.