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