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.