excel - Apache POI Excel:同一列中的多个数据验证
问题描述
我正在使用 Poi 3.16 版。我正在用 Groovy 编写代码。我尝试在同一列中添加 2 个不同的数据验证。第一个被接受,但第二个失败。有人有想法做对吗?到目前为止非常感谢。
// Something like that
FileOutputStream fileOut = new FileOutputStream("pois-test.xls");
def workbook = new XSSFWorkbook();
def sheet = workbook.createSheet("Sheet1");
// Create 2 Dropdownlists
addMyValidation(1, 10, 3, 3, new String[]{"One", "Two", "Three"});
addMyValidation(15, 25, 3, 3, new String[]{"A", "B", "C"}); // second one failed without errormessage
Boolean addMyValidation(int firstRow, int lastRow, int firstCol, int lastCol, String[] listOfValue)
{
def helper = new XSSFDataValidationHelper(sheet);
def constraint = helper.createExplicitListConstraint(listOfValue);
CellRangeAddressList range = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
def validation = helper.createValidation(constraint, range);
validation.setErrorStyle(ErrorStyle.STOP);
validation.setSuppressDropDownArrow(true);
validation.setEmptyCellAllowed(false);
validation.setShowPromptBox(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
return (true);
}
解决方案
为我使用Java
和apache poi
. 不过,也应该使用您的Groovy
代码。
可能您不完全确定第 15 行实际上在哪里,因为行 ID 和列 ID 都是基于 0 的?
以下完整代码有效:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;
public class CreateExcelDataValidation {
static Sheet sheet = null;
static boolean addMyValidation(int firstRow, int lastRow, int firstCol, int lastCol, String[] listOfValue) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(listOfValue);
CellRangeAddressList range = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation validation = helper.createValidation(constraint, range);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setSuppressDropDownArrow(true);
validation.setEmptyCellAllowed(false);
validation.setShowPromptBox(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
return true;
}
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook();
sheet = workbook.createSheet("Data Validation");
sheet.createRow(0).createCell(3).setCellValue("Col 3");
sheet.createRow(1).createCell(0).setCellValue("Row 1");
sheet.createRow(10).createCell(0).setCellValue("Row 10");
sheet.createRow(15).createCell(0).setCellValue("Row 15");
sheet.createRow(25).createCell(0).setCellValue("Row 25");
addMyValidation(1, 10, 3, 3, new String[]{"One", "Two", "Three"});
addMyValidation(15, 25, 3, 3, new String[]{"A", "B", "C"});
FileOutputStream out = new FileOutputStream("CreateExcelDataValidation.xlsx");
workbook.write(out);
workbook.close();
out.close();
}
}
推荐阅读
- powershell - 如何从 HPC 集群获取完整的作业名称
- c# - 聚类非矩阵字符串数组
- android - NOT NULL 约束失败:Facture.Mode
- r - 如何删除列中的可变字符?
- java - 将编辑文本值从片段传递到活动
- c++ - 静态变量在 .dll/.lib 中显示不同的行为
- javascript - 如何将信息从 javascript 文件来回发送到 python 文件
- angularjs - 错误:AngularJS 中的 [$injector:unpr]
- dart - 在执行动作飞镖之前等待一个功能完成时出错
- java - Spring Boot HTTP 状态不抛出异常