Java使用poi做excel导出时,可能要添加数据验证。
/**
* 设置excel数据有效性验证
* @param workbook
* @param map<Integer,String[]> 列号;验证数据
* @return
*/
public static Workbook setDataValidation(Workbook workbook, Map<Integer,String[]> map){
Sheet sheet2 = workbook.createSheet("数据验证");
int maxRow = 0;
for (String[] data : map.values()) {
maxRow = Math.max(maxRow, data.length);
}
for (int i = 0; i < maxRow; i++) {
Row row = sheet2.createRow(i);
for (int key : map.keySet()) {
String[] value = map.get(key);
if(i < value.length){
Cell cell = row.createCell(key);
cell.setCellValue(value[i]);
}
}
}
for (int key : map.keySet()) {
// [参考本人另一边文章](https://blog.csdn.net/weixin_42576654/article/details/120568228)
String keyChar = getCharByNum(key);
String strFormula = "数据验证!$"+ keyChar +"$1:$"+ keyChar +"$"+ map.get(key).length;
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1,65535, key, key);
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheetAt(0));
DataValidation validation = help.createValidation(constraint, regions);
workbook.getSheetAt(0).addValidationData(validation);
}
workbook.setSheetHidden(workbook.getSheetIndex("数据验证"),true);
return workbook;
}