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

Weimer

What happens if you set a cell range that runs from row 0 to row (quite a lot)? – Myrick

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 – Weimer

Answer 1

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.

Milliken

this would add the validation only on the 10 rows. Though I might have data for only ten rows, I would like to add the validation to entire column – Weimer

@Weimer i have just given an eg. if you want to apply in entire column then pass 1048576 instead of 10 (ie. 2nd parameter) as there are total no. of rows in MS Excel 2007 is 1,048,576 and if it is Ms Excel 2003 then pass 65536 as total no. of rows in Excel 2003 is 65,536 – Milliken

The max no of rows solves the purpose and the values which you have indicated really helped a lot – Weimer

Answer 2

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

Dibs

Confirmed for POI 3.17 – Lyra

Answer 3

Also this method could be used in case you need to skip top rows:

int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();

CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(
    2, // skipping top rows
    lastRow, 
    4, // firstCol 
    4  // lastCol
);

See this answer for details.

Floorwalker