Yeah, it appears that the setSuppressDropDownArrow() method is backwards from what I would have expected (and what worked for HSSF). Thank you for your help!
Jason -----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Wednesday, September 22, 2010 7:19 AM To: POI Users List Cc: '[email protected]' Subject: Re: XSSF Data Validation in POI 3.7 Jason: I just got this working two days ago. Here is my code (with my business stuff removed): List[] allowedValues = sd.getAllowedValues(); DataValidationConstraint constraint = null; DataValidation dataValidation = null; DataValidationHelper validationHelper = null; if(wbType==WB_TYPE_XSSF) { validationHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); } else { validationHelper = new org.apache.poi.hssf.usermodel.HSSFDataValidationHelper((org.apache.poi.hssf.usermodel.HSSFSheet)sheet); } if(allowedValues!=null&&allowedValues.length>0) { for (int i = 0; i < allowedValues.length; i++) { if(allowedValues[i]==null)continue; org.apache.poi.ss.util.CellRangeAddressList addressList = new org.apache.poi.ss.util.CellRangeAddressList(1, dummyRowMax, i, i); constraint = validationHelper.createExplicitListConstraint((String[])allowedValues[i].toArray()); dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(true); dataValidation.createPromptBox("Valid Values", "The following values are valid for this cell:" + allowedValues[i]); dataValidation.setShowPromptBox(true); dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP); dataValidation.createErrorBox("Validation Error", "The following values are valid for this cell:" + allowedValues[i]); sheet.addValidationData(dataValidation); } } The allowedValues list array is a business object so my developers can add validations as they see fit. Basically, it is just an array of lists for every column on the sheet. Notice the setSuppressDropDownArrow(true) - this is where it took me a bit - the examples have it as false. Also, I am still trying to get the error box to appear - it may not be an issue - I just added it yesterday, but have not had a chance to look into it. Let me know if you have any problems with it. I have only tested it with XSSF. -Lou "Goris, Jason" <[email protected]> wrote on 09/21/2010 04:05:07 PM: > Has anyone been able to successfully get an XSSF Data Validation > example working using POI 3.7 (beta 2) in Java? I'm trying to > create a simple drop down list of names in cell A1... > > I have tried something like this: > > final XSSFWorkbook wb = new XSSFWorkbook(); > final XSSFSheet sheet = wb.createSheet("Project Data"); > final Row r = sheet.createRow(0); > final Cell cell = r.createCell(0); > > final String[] excelListValues = new String[] { "Robin", "Chris", > "Jason", "Rajat", "Greg" }; > final XSSFDataValidationHelper h = new XSSFDataValidationHelper(sheet); > final CellRangeAddressList addressList = new CellRangeAddressList(0,0, 0, 0); > final DataValidationConstraint dvConstraint = h. > createExplicitListConstraint(excelListValues); > final DataValidation dataValidation = h. > createValidation(dvConstraint, addressList); > dataValidation.setSuppressDropDownArrow(false); > sheet.addValidationData(dataValidation); > > and wrote out the file, but when I open it in Excel, there is no > data validation. If I literally change the "XSSF"s to "HSSF"s > above, it works fine when reading it back to Excel...e.g. the list shows up. > > Any help/examples would be appreciated. > > Thanks, > > Jason > > > > This email may contain confidential or privileged material. Use or > disclosure of it by anyone other than the recipient is unauthorized. > If you are not an intended recipient, please delete this email. > This email may contain confidential or privileged material. Use or disclosure of it by anyone other than the recipient is unauthorized. If you are not an intended recipient, please delete this email. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
