java excel xlsx 增加数据验证

关注

原创最新推荐文章于 2026-05-30 10:16:03 发布 519 阅读 AI 写同款· GEO 优化 ›

如何在Java中将Excel（XLSX）转换为Word（DOCX）当需要将数据从工作表导出到Word文档中的表时，Excel到Word的转换可能会很有用。为了自动执行此转换，本文介绍了如何使用Java将Excel XLSX文件转换为Word DOCX格式。此外，还讨论了其他选项，以自定义XLSX到DOCX的转换。在Java中将XLSX转换为DOCX 在Java中自定义XLSX到DOCX的转换 Aspose.Cells for Java是电子表格处理API，可让您生成，处理和处理Excel文件。此外，API还可以将Excel XLSX文件高质量转换为其他格式，包括DO 阅读详情

隐藏表下拉框

   // 创建隐藏工作表存储下拉框数据
            String hiddenSheetName = "HiddenSheet"+System.currentTimeMillis();
            Sheet hiddenSheet =  workbook.createSheet(hiddenSheetName);
            //设置隐藏sheet
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), true);
            for (int i = 0; i < dropdownList.size(); i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cityCellHidden = row.createCell(0);
                cityCellHidden.setCellValue(dropdownList.get(i));
            }
            // 获取DataValidationHelper对象
            DataValidationHelper helper = mainSheet.getDataValidationHelper();
            // 定义数据验证的范围
            CellRangeAddressList addressList = new CellRangeAddressList(startRow,  65535, columnIndex, columnIndex);
            // 创建数据验证约束 公式可以写成"Sheet2!$A$1:$A$10"。
            DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenSheetName+"!$A$1:$A$"+dropdownList.size());
            //生成验证规则
            DataValidation validation = helper.createValidation(constraint, addressList);
            // 设置错误提示信息
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);

下拉框

   DataValidationHelper helper = mainSheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(
                    dropdownList.toArray(new String[0]));
            CellRangeAddressList addressList = new CellRangeAddressList(startRow, 65535, columnIndex, columnIndex);
            DataValidation validation = helper.createValidation(constraint, addressList);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            mainSheet.addValidationData(validation);

时间格式及范围验证

       // 创建数据验证助手
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList dateAddressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
        // 获取 LocalDateTime 的最小值
        LocalDateTime minLocalDateTime = LocalDateTime.MIN;
        // 获取 LocalDateTime 的最大值
        LocalDateTime maxLocalDateTime = LocalDateTime.MAX;
        DataValidationConstraint dataValidationConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,
                minLocalDateTime.format(DateTimeFormatter.ofPattern(dateFormat)),
                maxLocalDateTime.format(DateTimeFormatter.ofPattern(dateFormat)),
                dateFormat);
        DataValidation validation = helper.createValidation(dataValidationConstraint, dateAddressList);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        validation.createErrorBox("标题","内容");
        sheet.addValidationData(validation);

integer 验证

// 创建数据验证助手
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList intAddressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
        // 获取 LocalDateTime 的最大值
        LocalDateTime maxLocalDateTime = LocalDateTime.MAX;
        DataValidationConstraint dataValidationConstraint = helper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN,
                String.valueOf(Integer.MIN_VALUE),
                String.valueOf(Integer.MAX_VALUE));
        DataValidation validation = helper.createValidation(dataValidationConstraint, intAddressList);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(validation);