All,
I recently upgraded to the latest version of POI and started generating XLSX
files instead of XLS.
After generating XLSX files if I have a cell with list (Restricted values) then
the list shows up fine in excel but the cell can be overridden to type in a
value or a formula. Excel shows me an error if try to do the same in XLS file
generated from POI.
Here is the code I am using now:
public String yesNoArray[] = {"Yes", "No"};
DataValidationHelper validationHelper = new
XSSFDataValidationHelper((XSSFSheet)excelUtils.sheet);
CellRangeAddressList cellRange = new
CellRangeAddressList(excelUtils.cell.getRowIndex(),
excelUtils.cell.getRowIndex(), excelUtils.cell.getColumnIndex(),
excelUtils.cell.getColumnIndex());
DataValidationConstraint constraint =
validationHelper.createExplicitListConstraint(yesNoArray);
DataValidation dataValidation dataValidation =
validationHelper.createValidation(constraint, cellRange);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Invalid input", "Only Yes/No values are
allowed.");
sheet.addValidationData(dataValidation);
Here is what I used for generating XLS files:
DataValidationConstraint constraint =
DVConstraint.createExplicitListConstraint(yesNoArray);
CellRangeAddressList cellRange = new
CellRangeAddressList(excelUtils.cell.getRowIndex(),
excelUtils.cell.getRowIndex(), excelUtils.cell.getColumnIndex(),
excelUtils.cell.getColumnIndex());
HSSFDataValidation dv = new HSSFDataValidation(cellRange, constraint);
dv.setEmptyCellAllowed(true);
dv.setShowPromptBox(false);
dv.createErrorBox("Invalid input", "Only Yes/No values are allowed.");
sheet.addValidationData(dv);
I get an alert if I create a worksheet with restricted list directly in Excel
(XLSX) and try to type a value in the cell.
Has anyone faced this issue before? Any help/guidance will be greatly
appreciated.
Thanks
Saurabh
===============================================================================
Please refer to http://www.kkr.com/legal/email_disclaimer.php
for important disclosures regarding this electronic communication.
===============================================================================
