How to make excel cells mandatory?

Question

I am trying to make some excel cells mandatory so that a message can be displayed if they are left empty 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 he should prevent cell cleansing, but it does not work. However, the / s cell on which this restriction is satisfied is checked for a length of 2 to 45 characters.

Why validation.setEmptyCellAllowed(false); not working in this case? How to make a cell mandatory so that it cannot be left blank?

---

When the validation.setEmptyCellAllowed(false); method is used validation.setEmptyCellAllowed(false); It seems that the validation constraint is correctly applied in Excel.

The "Ignore empty" checkbox is not selected. Excel still allows cells that enforce this restriction to remain empty.

The purpose of Ignore blank in Excel may be different from what I don't understand. In any case, I need to make certain cells mandatory. If an attempt is made to delete them then, it should be rejected and a corresponding error message should be displayed. For Excel, you may need to use some Visual Basic tricks / code.

Answer 1

Ignore the space does not do what you want. In a situation where you are describing when a user types into a cell, he does not allow him to create an empty entry. However, it does NOT prevent the exit from data entry mode (for example, using ESC ), which will also leave the cell empty. This also does not prevent him from selecting a cell and clicking Delete without going into data entry.

Depending on what you want to do, you can use the VBA event macro to check if the cell is empty after a specific event. Whether this is a Worksheet_SelectionChange event, a Worksheet_Deactivate event, a Workbook_Close event, or one or more other events, depends on the specifics of your project.

Answer 2

Try the following: it worked for me !!!!

 DataValidationConstraint lengthConstraint = validationHelper.createTextLengthConstraint( DataValidationConstraint.OperatorType.GREATER_THAN, "0", "");