Apache POI - How to restrict the user to clear the

问题:

I have set up a Data validation on my sheet using Apache POI. The validation is like it allows only the decimal numbers. It was working fine without any issues. But when user clears the validations on excel sheet he will be able to enter any data which I restricted using data validations before.

How can I restrict the user to clear the data validation using Apache POI ? In other words, how should I disable clear validation option for my data validation using Apache POI ?

Any help would be appreciated.

Thanks,
Rama Krishna

回答1:

You should consider locking the sheet against changes and let only the cells unlocked which shall be editable. Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddressList;

class LockDataValidation {

 public static void main(String[] args) {
  try {

   Workbook workbook = new XSSFWorkbook();

   Sheet sheet = workbook.createSheet("Sheet1");

   sheet.createRow(0).createCell(1).setCellValue("Only numbers 10 to 100");

   //DataValidation for cell B2:
   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(
    DataValidationConstraint.ValidationType.INTEGER,
    DataValidationConstraint.OperatorType.BETWEEN,
    "10", "100");
   CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 1, 1);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
   if(validation instanceof XSSFDataValidation) {
    validation.setSuppressDropDownArrow(false);
    validation.setShowErrorBox(true);
   }
   else {
    validation.setSuppressDropDownArrow(true);
   }
   sheet.addValidationData(validation);

   //create cell B2:
   Cell cell = sheet.createRow(1).createCell(1);

   //set cell B2 unlocked:
   CellStyle cellstyle = workbook.createCellStyle();
   cellstyle.setLocked(false);
   cell.setCellStyle(cellstyle);

   //lock the sheet:
   ((XSSFSheet)sheet).enableLocking();

   FileOutputStream fileOut = new FileOutputStream("Datavalidation.xlsx");
   workbook.write(fileOut);
   fileOut.close();

  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

With this only B2 is editable and the data validation is protected against removing. Without locking the data validation will simply deleted if someone pastes a copied cell without validation into the cell with validation.

But even now one can copy/paste wrong values into B2 since data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. See https://support.office.com/en-us/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9.

There are VBA macro solutions in the wild which are trying to close even this gap. See Data validation fails when copy pasting data in excel made through apache poi.