I'm trying to make some excel cells mandatory so that a message can be displayed, if they are left blank by the user.
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Data Validation");
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint lengthConstraint = dataValidationHelper.createTextLengthConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "2", "45");
CellRangeAddressList cellList = new CellRangeAddressList(0, 0, 1, 1);
DataValidation validation = dataValidationHelper.createValidation(lengthConstraint, cellList);
validation.setErrorStyle(ErrorStyle.STOP);
validation.createErrorBox("Error", "The length must be between 2 and 45.");
validation.setEmptyCellAllowed(false);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(false);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(true);
}
sheet.addValidationData(validation);
Row row = sheet.createRow(0);
Cell cell = row.createCell(1);
cell.setCellValue("Text");
I used validation.setEmptyCellAllowed(false);
and expected that it should prevent cells from being emptied but it does not work. The cell/s on which this constraint is enforced are however, validated for length which is between 2 and 45 characters.
Why does validation.setEmptyCellAllowed(false);
not work in this case? How to make a cell mandatory so that it cannot be left blank?
When the validation.setEmptyCellAllowed(false);
method is used, it seems the validation constraint is correctly applied in Excel.
The checkbox Ignore blank is unchecked. Excel still allows the cells on which this constraint is enforced to be left blank.
The purpose of Ignore blank in Excel may be different that I do not understand. Anyway I need to make certain cells mandatory. If an attempt is made to empty them then, it should be rejected and an appropriate error message should be displayed. It may require some Visual Basic tricks/code to be embedded in Excel.