测试Excel表格设置数据下拉列表
/**
* 功能描述: 测试Excel表格设置数据下拉列表
*
* @author Jack_Liberty
* @date -02-21 16:30
*/
@Test
public void testExcelDataValidation() {
//1、创建一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 导出 输出流
FileOutputStream fileOutputStream = null;
//表头信息
String[] headerArrays = {"学号", "姓名", "年龄", "班级", "家庭住址"};
try {
//1、创建一个工作簿
wb = new XSSFWorkbook();
//2、创建一个sheet
Sheet sheet = wb.createSheet("学生信息");
//3、创建表头
Row row = sheet.createRow(0);
for (int i = 0; i < headerArrays.length; i++) {
row.createCell(i).setCellValue(headerArrays[i]);
}
//4、下拉数据
List dropDownLDataList = new ArrayList<>();
//模拟数据
String[] classArrays = {"A001", "A002", "A003", "A004", "A005"};
List list = new ArrayList<>();
for (int i = 0; i < 300; i++) {
list.add("家庭住址" + i);
}
String[] homeAddress = new String[list.size()];
list.toArray(homeAddress);
dropDownLDataList.add(classArrays);
dropDownLDataList.add(homeAddress);
//5、生成下拉数据sheet页
generateDropDownDataSheet(wb, dropDownLDataList);
//6、给班级设置数据验证也就是添加下拉列表
String classFormula = "dropDownData!$A$1:$A$" + classArrays.length;
DataValidation classDataValidation = setDataValidation(wb, classFormula, 1, 1000, 3, 3);
sheet.addValidationData(classDataValidation);
//7、给家庭住址设置数据验证也就是添加下拉列表
String homeFormula = "dropDownData!$B$1:$B$" + homeAddress.length;
DataValidation homeDataValidation = setDataValidation(wb, homeFormula, 1, 1000, 4, 4);
sheet.addValidationData(homeDataValidation);
fileOutputStream = new FileOutputStream("E:\\Student.xlsx");
wb.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
wb.close();
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
创建sheet页并将下拉列表内容添加至该sheet页
/**
* 功能描述: 创建Excel模板下拉列表值存储工作表并设置值
*
* @param wb 工作簿
* @param list 下拉框数据
* @author Jack_Liberty
* @date -02-21 16:02
*/
public static Sheet generateDropDownDataSheet(Workbook wb, List list) {
Sheet sheet = wb.createSheet("dropDownData");
//遍历下拉数据并添加至该sheet页
for (int i = 0; i < list.size(); i++) {
//设置列宽
sheet.setDefaultColumnWidth(4000);
for (int x = 0; x < list.get(i).length; x++) {
//第一个下拉框直接创建行和列
if (i == 0) {
Row row = sheet.createRow(x);
Cell cell = row.createCell(i);
cell.setCellValue(list.get(i)[x]);
} else {
//获取行数
int lastRowNum = sheet.getLastRowNum();
//如果行已存在则直接获取行创建列并添加数据
if (x <= lastRowNum) {
Row row = sheet.getRow(x);
Cell cell = row.createCell(i);
cell.setCellValue(list.get(i)[x]);
} else {
//创建行、列添加数据
//设置列宽
sheet.setDefaultColumnWidth(4000);
sheet.createRow(x).createCell(i).setCellValue(list.get(i)[x]);
}
}
}
}
//设置隐藏sheet页
wb.setSheetHidden(wb.getSheetIndex("dropDownData"), true);
return sheet;
}
设置有效性
/**
* 功能描述: 绑定下拉列表数据
*
* @param wb 工作簿
* @param formula P公式
* @param firstRow 起始行
* @param endRow 结束行
* @param firstCol 起始列
* @param endCol 结束列
* @return org.apache.poi.ss.usermodel.DataValidation
* @author Jack_Liberty
* @date -02-21 16:17
*
* String formula = "orgInfo!$A$1:$A$59"
* 表示orgInfo工作表A列1-59行作为下拉列表来源数据
*/
public static DataValidation setDataValidation(XSSFWorkbook wb, String formula, int firstRow, int endRow, int firstCol, int endCol) {
//获取下拉框数据来源sheet
XSSFSheet sheet = wb.getSheet("dropDownData");
//指定设置下拉框的单元格范围
CellRangeAddressList cellRangeAddress = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
//创建数据验证助手
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
//创建公式列表约束
DataValidationConstraint constraint = dvHelper.createFormulaListConstraint(formula);
//创建验证 并返回
return dvHelper.createValidation(constraint, cellRangeAddress);
}
