一般情况下,输入列表的提示使用字符串数组,一般情况使用如下方法
private static void createComboList(Sheet sheet, List<ExcelCode> allowedValues, int firstRow, int endRow, int col) {
if(ObjectUtils.isNotEmpty(allowedValues) ) {
List<String> listOfTitles = allowedValues.stream().map(excelColumnDto -> excelColumnDto.getText()).collect(Collectors.toList());
String[] listOfValues = listOfTitles.toArray(new String[listOfTitles.size()]);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, col, col);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(listOfValues);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
但是打开保存文件有时候会提示验证加载失败的错误,而输入提示就是一个validation。
在poi保存文件时,其实把数组合并为一个字符串,用一个字符串保存在xml中,再次打开Excel,Excel加载时,估计字符串的最大长度不超过255,如果整个数组合并字符串后长度超过255左右的字符,就整个sheet都无法加载下拉列表输入提示了。
1、检查创建下拉列表的接口,发现另外一个方法;
public interface DataValidationHelper {
DataValidationConstraint createFormulaListConstraint(String var1);
DataValidationConstraint createExplicitListConstraint(String[] var1);
DataValidationConstraint createNumericConstraint(int var1, int var2, String var3, String var4);
DataValidationConstraint createTextLengthConstraint(int var1, String var2, String var3);
DataValidationConstraint createDecimalConstraint(int var1, String var2, String var3);
DataValidationConstraint createIntegerConstraint(int var1, String var2, String var3);
DataValidationConstraint createDateConstraint(int var1, String var2, String var3, String var4);
DataValidationConstraint createTimeConstraint(int var1, String var2, String var3);
DataValidationConstraint createCustomConstraint(String var1);
DataValidation createValidation(DataValidationConstraint var1, CellRangeAddressList var2);
}
2、在Excel中,查看单元格输入提示的设置,其数据来源可以是数组(也就是上面的方法),也可以是源自其他单元格的数据;
所以上面两点有关联,但还是要求助baidu,搜索createFormulaListConstraint
找到如下解决办法
private static void createLargeCountList(Workbook workbook, Sheet sheet, String colName, List<ExcelCode> allowedValues, int firstRow, int endRow, int col) {
if(ObjectUtils.isEmpty(allowedValues) ) {
return;
}
String name = colName.replaceAll("\\*","");
String sheetName = name + "_Sheet";
String nameName = name + "_hidden";
Sheet hideSheet = workbook.createSheet(sheetName);
for (int i = 0; i < allowedValues.size(); i++) {
hideSheet.createRow(i).createCell(0).setCellValue(allowedValues.get(i).getText());
}
Name category1Name = workbook.createName();
category1Name.setNameName(nameName);
category1Name.setRefersToFormula(sheetName + "!" + "$A$1:$A$" + allowedValues.size());
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(nameName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, col, col);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
}