Java设置Excel数据验证(下拉列表)

在这里插入代码片

首先在pom.xml中引入以下包，两个包一定要都引入，因为这两个包有依赖关系：

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

测试Excel表格设置数据下拉列表

	/**
     * 功能描述: 测试Excel表格设置数据下拉列表
     *
     * @author Jack_Liberty
     * @date 2021-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<String[]> dropDownLDataList = new ArrayList<>();
            //模拟数据
            String[] classArrays = {"A001", "A002", "A003", "A004", "A005"};
            List<String> 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 2021-02-21 16:02
     */
    public static void generateDropDownDataSheet(Workbook wb, List<String[]> 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);
    }

设置有效性

	/**
     * 功能描述: 绑定下拉列表数据
     *
     * @param wb       工作簿
     * @param formula  P公式
     * @param firstRow 起始行
     * @param endRow   结束行
     * @param firstCol 起始列
     * @param endCol   结束列
     * @return org.apache.poi.ss.usermodel.DataValidation
     * @author Jack_Liberty
     * @date 2021-02-21 16:17
     * <p>
     * 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);
    }