Adding validation dropdown in spreadsheet in ColdFusion

CFSpreadsheet is I think most usefull tag/function while working with enterprise project. CFSpreadsheet and related functions provide mostly all kind of basic functionality require to export data into spreadsheet. Recently one my project I had little bit advance requirement when need to add data validation and restrict user to choose value from dropdown. I think most of us are familiar with cfspreadsheet built on very popular Apache POI project and coldfusion on JAVA. So basically I am going to write some java code in ColdFusion :).

	//Create New Spreadsheet
	SpreadsheetObj = spreadsheetNew("testsheet");

//Get Workbook object
	workbook = SpreadsheetObj.getWorkBook();

//Get sheet by name where you liek to add list validation
	sheet = workbook.getSheet("testsheet");


	//Create object of required class
	dvconstraint = createObject("java","org.apache.poi.hssf.usermodel.DVConstraint");
	cellRangeList = createObject("java","org.apache.poi.ss.util.CellRangeAddressList");
	dataValidation = createObject("java","org.apache.poi.hssf.usermodel.HSSFDataValidation");


	//Define cell list rowstart, rowend, column start, column end
	addressList = cellRangeList.init(0, 9, 0, 0);//First 10 rows in first column
	dvConstraint = dvconstraint.createExplicitListConstraint(["10", "20", "30"]); //set contraint value
	dataValidation = dataValidation.init(addressList, dvConstraint); //apply validation on address list
	dataValidation.setSuppressDropDownArrow(false);//Enable/disable dropdown arrow.
	sheet.addValidationData(dataValidation);//Add validation to sheet.

//write spreadsheet object
	spreadsheetwrite(SpreadsheetObj,"#expandpath('./downloadsheet.xls')#",true);
	

We  need to create some of POI object to make thing possible. First we need to define cell range when we want to apply validation and create constaraint to apply on those cell. I have passed constraint value in array of string. Both these object will be passed dataValidation object which will later added to our sheet object. And last write spreadhseet object in file using spreadsheetwrite() function

that's it and we are down and below is how my spreadsheet will look like.