如何强制使用excel单元？

I'm trying to make some excel cells mandatory so that a message can be displayed, if they are left blank by the user.

我正在尝试强制使用某些excel单元格，以便在用户将其留空时显示消息。

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.

我用了validation.setEmptyCellAllowed（false）;并期望它应该防止细胞被清空，但它不起作用。但是，强制执行此约束的单元格的长度为2到45个字符。

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

为什么validation.setEmptyCellAllowed（false）;在这种情况下不工作？如何使一个单元格成为必需的，以便它不能留空？

---

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

当validation.setEmptyCellAllowed（false）;使用方法，似乎验证约束在Excel中正确应用。

The checkbox Ignore blank is unchecked. Excel still allows the cells on which this constraint is enforced to be left blank.

未选中“忽略空白”复选框。 Excel仍允许将强制执行此约束的单元格留空。

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.

在Excel中忽略空白的目的可能与我不理解的不同。无论如何，我需要强制某些细胞。如果尝试清空它们，则应该拒绝它并显示相应的错误消息。它可能需要在Excel中嵌入一些Visual Basic技巧/代码。

2 个解决方案

#1

Ignore blank does not accomplish what you want it to. In a situation as you describe, when the user is typing into the cell, it prevents him creating a blank entry. However, it does NOT prevent him exiting data entry mode (e.g. using ESC) which will also leave the cell blank. It also does not prevent him from selecting the cell, and hitting Delete without going into data entry.

忽略空白无法达到您想要的效果。在您描述的情况下，当用户键入单元格时，它会阻止他创建空白条目。但是，它不会阻止他退出数据输入模式（例如使用ESC），这也会使单元格留空。它也不会阻止他选择单元格，并在不进入数据输入的情况下点击删除。

Depending on exactly what you want to do, you can use a VBA event macro to test whether the cell is blank after a certain event. Whether this should be a Worksheet_SelectionChange event, Worksheet_Deactivate event, Workbook_Close event, or one or more other events, depends on the specifics of your project.

根据您要执行的操作，您可以使用VBA事件宏来测试某个事件后单元格是否为空。这应该是Worksheet_SelectionChange事件，Worksheet_Deactivate事件，Workbook_Close事件还是一个或多个其他事件，取决于项目的细节。

#2

Try with the following, it worked for me!!!!

尝试以下，它对我有用!!!!

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