java excel 导入 加校验_Java通过POI为Excel添加数据验证

String path = "d:\\success.xlsx";

String sheetName = "sheetlist";

XSSFWorkbook wb = null;

XSSFSheet sheetlist = null;

File inputFile = new File(path);

if (inputFile.exists()) {

wb = new XSSFWorkbook(new FileInputStream(path));

} else {

wb = new XSSFWorkbook();// excel文件对象

}

if (wb.getSheet(sheetName) == null) {

sheetlist = wb.createSheet(sheetName);// 工作表对象

} else {

sheetlist = wb.getSheet(sheetName);// 工作表对象

}

DataValidationHelper helper = sheetlist.getDataValidationHelper();

List dataValidations = sheetlist.getDataValidations();

for (XSSFDataValidation dv : dataValidations) {

// 已有的验证

}

//

CellRangeAddressList dstAddrList = new CellRangeAddressList(0, 500, 0, 0);// 规则一单元格范围

String[] textlist = { "列表1", "列表2", "列表3", "列表4", "列表5" };

DataValidation dstDataValidation = helper.createValidation(helper.createExplicitListConstraint(textlist),

dstAddrList);

dstDataValidation.createPromptBox("提示头", "提示内容");

dstDataValidation.setShowErrorBox(true);

dstDataValidation.setShowPromptBox(true);

dstDataValidation.setEmptyCellAllowed(false);

sheetlist.addValidationData(dstDataValidation);

CellRangeAddressList dstAddrList2 = new CellRangeAddressList(0, 500, 1, 1);// 规则二单元格范围

DataValidationConstraint dvc = helper.createNumericConstraint(DVConstraint.ValidationType.INTEGER,

DVConstraint.OperatorType.BETWEEN, "0", "9999999999");

DataValidation dstDataValidation2 = helper.createValidation(dvc, dstAddrList2);

dstDataValidation2.createErrorBox("填错输啦！", "只能填那个啥啥啥");

dstDataValidation2.setEmptyCellAllowed(false);

dstDataValidation2.setShowErrorBox(true);

sheetlist.addValidationData(dstDataValidation2);

FileOutputStream out = new FileOutputStream(path);

wb.write(out);

out.close();