下面列出了org.apache.poi.ss.usermodel.DataValidationConstraint#org.apache.poi.ss.util.CellRangeAddressList 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation)
{
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
else
{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation)
{
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
else
{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public void setXssValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* Add date validation when export
*
* @param operatorType operatorType
* @param expr1 Date expression 1, such as: 2019-12-12
* @param expr2 Date expression 2(Only operation types between and notBetween are required)，such as：2019-12-24
* @param pattern Date pattern
* @param sheet Current sheet
* @param firstRow Start row
* @param lastRow End row
* @param colIndex Column index
* @param showErrorBox Whether show error box
* @param errorBoxRank Error box rank
* @param errorTitle Error box title
* @param errorContent Error box value
* @param showTip Whether show tip
* @param tipContent Tip content
* @param tipTitle Tip title
*/
public static void addDateValid(OperatorType operatorType, String expr1, String expr2, String pattern, Sheet sheet, int firstRow, int lastRow,
int colIndex, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent, boolean showTip,
String tipTitle, String tipContent) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint;
if (sheet instanceof SXSSFSheet) {
dvConstraint = helper.createDateConstraint(operatorType.getType(), "date(" + expr1.replaceAll("-", ",") + ")",
"date(" + expr2.replaceAll("-", ",") + ")", pattern);
} else {
dvConstraint = helper.createDateConstraint(operatorType.getType(), expr1, expr2, pattern);
}
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
DataValidation dataValidation = helper.createValidation(dvConstraint, regions);
dataValidation.setShowErrorBox(showErrorBox);
dataValidation.setErrorStyle(errorBoxRank.getRank());
dataValidation.createErrorBox(errorTitle, errorContent);
if (showTip) {
dataValidation.createPromptBox(tipTitle, tipContent);
}
sheet.addValidationData(dataValidation);
}
/**
* 功能描述:下拉列表
*
* @param xssfWsheet
* @param list
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
public static void setDataValidation(SXSSFSheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
DataValidationHelper helper = xssfWsheet.getDataValidationHelper();
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.createErrorBox(DataValidationError1, DataValidationError2);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
xssfWsheet.addValidationData(dataValidation);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstCol 开始列
* @param endCol 结束列
*/
private static void setXSSFValidation(Sheet sheet, String[] textlist, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1, 100, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation){
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}else{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation)
{
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
else
{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
return sheet;
}
private static void setDataValidationList(int j, int k, int l, int m, String data, HSSFSheet sheet) {
// 设置下拉列表的内容
String[] textlist = data.split(",");
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
/* CellRangeAddressList regions = new CellRangeAddressList(
6,5, 6,5);*/
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
data_validation_list.setSuppressDropDownArrow(false);
sheet.addValidationData(data_validation_list);
}
private static void setDataValidationList(int j, int k, int l, int m, String data, XSSFSheet sheet) {
// 设置下拉列表的内容
String[] textlist = data.split(",");
// 加载下拉列表内容
// 设置数据有效性加载在哪个单元格上。
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textlist);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
/* CellRangeAddressList regions = new CellRangeAddressList(
6,5, 6,5);*/
// 数据有效性对象
XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
// data_validation_list.setSuppressDropDownArrow(false);
sheet.addValidationData(data_validation_list);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public void setXssfValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* Finds and returns the {@link DataValidationContext} for the cell, if there is
* one. Lookup is based on the first match from
* {@link DataValidation#getRegions()} for the cell's sheet. DataValidation
* regions must be in the same sheet as the DataValidation. Allowed values
* expressions may reference other sheets, however.
*
* @param cell reference to check
* @return the DataValidationContext applicable to the given cell, or null if no
* validation applies
*/
public DataValidationContext getValidationContextForCell(CellReference cell) {
final Sheet sheet = workbook.getSheet(cell.getSheetName());
if (sheet == null) return null;
final List<? extends DataValidation> dataValidations = getValidations(sheet);
if (dataValidations == null) return null;
for (DataValidation dv : dataValidations) {
final CellRangeAddressList regions = dv.getRegions();
if (regions == null) return null;
// current implementation can't return null
for (CellRangeAddressBase range : regions.getCellRangeAddresses()) {
if (range.isInRange(cell)) {
return new DataValidationContext(dv, this, range, cell);
}
}
}
return null;
}
public DVRecord(RecordInputStream in) {
_option_flags = in.readInt();
_promptTitle = readUnicodeString(in);
_errorTitle = readUnicodeString(in);
_promptText = readUnicodeString(in);
_errorText = readUnicodeString(in);
int field_size_first_formula = in.readUShort();
_not_used_1 = in.readShort();
// "You may not use unions, intersections or array constants in Data Validation criteria"
// read first formula data condition
_formula1 = Formula.read(field_size_first_formula, in);
int field_size_sec_formula = in.readUShort();
_not_used_2 = in.readShort();
// read sec formula data condition
_formula2 = Formula.read(field_size_sec_formula, in);
// read cell range address list with all affected ranges
_regions = new CellRangeAddressList(in);
}
private String setDropDownList(Td td, Sheet sheet, String content) {
if (content.length() > 250) {
throw new IllegalArgumentException("The total number of words in the drop-down list should not exceed 250.");
}
CellRangeAddressList addressList = new CellRangeAddressList(
td.getRow(), td.getRowBound(), td.getCol(), td.getColBound());
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
String[] list = content.split(",");
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(list);
DataValidation validation = dvHelper.createValidation(
dvConstraint, addressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
if (list.length > 0) {
return list[0];
}
return null;
}
/**
* 指定した範囲のセルに制約を追加する。
* <p>POI-3.7以上が必要。
* @param sheet シート
* @param constraint 制約
* @param startPosition 設定するセルの開始位置
* @param endPosition 設定するセルの終了位置
*/
public static void setupConstaint(final Sheet sheet, final DataValidationConstraint constraint,
final Point startPosition, final Point endPosition) {
ArgUtils.notNull(sheet, "sheet");
ArgUtils.notNull(constraint, "constraint");
ArgUtils.notNull(startPosition, "startPosition");
ArgUtils.notNull(endPosition, "endPosition");
final DataValidationHelper helper = sheet.getDataValidationHelper();
final CellRangeAddressList region = new CellRangeAddressList(
startPosition.y, endPosition.y,
startPosition.x, endPosition.x
);
final DataValidation dataValidation = helper.createValidation(constraint, region);
sheet.addValidationData(dataValidation);
}
/**
* 设置 POI XSSFSheet 单元格提示
*
* @param sheet 表单
* @param promptTitle 提示标题
* @param promptContent 提示内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol)
{
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.createPromptBox(promptTitle, promptContent);
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
}
/**
* create restrictions on the data cells
*
* @param mainsheet sheet with data
* @param restrictionsheet sheet with restriction values
* @param column index of column (starting with zero)
* @param nbofchoices number of choices (starting with zero)
* @param nbofrows number of rows (starting with zero)
*/
public static void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
String columnletter = CellReference.convertNumToColString(column);
String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setSuppressDropDownArrow(true);
mainsheet.addValidationData(dataValidation);
}
/**
* 设置 POI XSSFSheet 单元格提示
*
* @param sheet 表单
* @param promptTitle 提示标题
* @param promptContent 提示内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol)
{
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.createPromptBox(promptTitle, promptContent);
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
}
/**
* 设置单元格上提示
*
* @param sheet 要设置的sheet.
* @param promptTitle 标题
* @param promptContent 内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static Sheet setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol)
{
// 构造constraint对象
DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
dataValidationView.createPromptBox(promptTitle, promptContent);
sheet.addValidationData(dataValidationView);
return sheet;
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidationList);
return sheet;
}
/**
* Add a dropdown box when export
*
* @param combobox Dropdown box content of 25 or less
* @param showErrorBox Whether show error box
* @param errorBoxRank Error box rank
* @param errorTitle Error box title
* @param errorContent Error box value
* @param workbook Current workbook
* @param sheet Current sheet
* @param firstRow Start row
* @param lastRow End row
* @param colIndex Column index
* @param values The dropdown box can be large, but if it's version 07, it's limited by the window size in the Excel annotation{@link Excel}
*/
public static void addDropdownBox(String[] combobox, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent,
Workbook workbook, Sheet sheet, int firstRow, int lastRow, int colIndex, String[] values) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint;
if (values == null) {
constraint = helper.createExplicitListConstraint(combobox);
} else {
Sheet explicitSheet = workbook.getSheet("explicitSheet");
if (explicitSheet == null) {
explicitSheet = workbook.createSheet("explicitSheet");
}
int valueLength = values.length;
for (int i = 0; i < valueLength; i++) {
Row explicitSheetRow = explicitSheet.getRow(i);
if (explicitSheetRow == null) {
explicitSheetRow = explicitSheet.createRow(i);
}
explicitSheetRow.createCell(colIndex).setCellValue(values[i]);
}
char colOffset = (char) ('A' + colIndex);
constraint = helper.createFormulaListConstraint(explicitSheet.getSheetName() + "!$" + colOffset + "$1:$" + colOffset + "$" + (valueLength == 0 ? 1 : valueLength));
workbook.setSheetHidden(workbook.getSheetIndex("explicitSheet"), true);
}
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.setShowErrorBox(showErrorBox);
dataValidation.setErrorStyle(errorBoxRank.getRank());
dataValidation.createErrorBox(errorTitle, errorContent);
sheet.addValidationData(dataValidation);
}
/**
* 设置 POI XSSFSheet 单元格提示
* @param sheet 表单
* @param promptContent 提示内容
* @param firstCol 开始列
* @param endCol 结束列
*/
private static void setXSSFPrompt(Sheet sheet, String promptContent, int firstCol, int endCol) {
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dvHelper.createCustomConstraint("DD1");
CellRangeAddressList regions = new CellRangeAddressList(1, 100, firstCol, endCol);
DataValidation dataValidation = dvHelper.createValidation(constraint, regions);
dataValidation.createPromptBox("", promptContent);
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
}
/**
* 设置单元格上提示
*
* @param sheet 要设置的sheet.
* @param promptTitle 标题
* @param promptContent 内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public static void setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol)
{
// 构造constraint对象
DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
dataValidationView.createPromptBox(promptTitle, promptContent);
sheet.addValidationData(dataValidationView);
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
// 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
@Override
public List<HSSFDataValidation> getDataValidations() {
DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
final List<HSSFDataValidation> hssfValidations = new ArrayList<HSSFDataValidation>();
RecordVisitor visitor = new RecordVisitor() {
private HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(getWorkbook());
@Override
public void visitRecord(Record r) {
if (!(r instanceof DVRecord)) {
return;
}
DVRecord dvRecord = (DVRecord) r;
CellRangeAddressList regions = dvRecord.getCellRangeAddress().copy();
DVConstraint constraint = DVConstraint.createDVConstraint(dvRecord, book);
HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint);
hssfDataValidation.setErrorStyle(dvRecord.getErrorStyle());
hssfDataValidation.setEmptyCellAllowed(dvRecord.getEmptyCellAllowed());
hssfDataValidation.setSuppressDropDownArrow(dvRecord.getSuppressDropdownArrow());
hssfDataValidation.createPromptBox(dvRecord.getPromptTitle(), dvRecord.getPromptText());
hssfDataValidation.setShowPromptBox(dvRecord.getShowPromptOnCellSelected());
hssfDataValidation.createErrorBox(dvRecord.getErrorTitle(), dvRecord.getErrorText());
hssfDataValidation.setShowErrorBox(dvRecord.getShowErrorOnInvalidValue());
hssfValidations.add(hssfDataValidation);
}
};
dvt.visitContainedRecords(visitor);
return hssfValidations;
}
public int getRecordSize() {
// a bit cheaper than the default impl
int nRegions = _mergedRegions.size();
if (nRegions < 1) {
// no need to write a single empty MergeCellsRecord
return 0;
}
int nMergedCellsRecords = nRegions / MAX_MERGED_REGIONS;
int nLeftoverMergedRegions = nRegions % MAX_MERGED_REGIONS;
int result = nMergedCellsRecords
* (4 + CellRangeAddressList.getEncodedSize(MAX_MERGED_REGIONS)) + 4
+ CellRangeAddressList.getEncodedSize(nLeftoverMergedRegions);
return result;
}
/**
* Set cell ranges list to a single cell range and
* modify the enclosing cell range accordingly.
* @param cellRanges - list of CellRange objects
*/
public void setCellRanges(CellRangeAddress[] cellRanges) {
if(cellRanges == null) {
throw new IllegalArgumentException("cellRanges must not be null");
}
CellRangeAddressList cral = new CellRangeAddressList();
CellRangeAddress enclosingRange = null;
for (int i = 0; i < cellRanges.length; i++) {
CellRangeAddress cr = cellRanges[i];
enclosingRange = CellRangeUtil.createEnclosingCellRange(cr, enclosingRange);
cral.addCellRangeAddress(cr);
}
field_3_enclosing_cell_range = enclosingRange;
field_4_cell_ranges = cral;
}
public DVRecord(int validationType, int operator, int errorStyle, boolean emptyCellAllowed,
boolean suppressDropDownArrow, boolean isExplicitList,
boolean showPromptBox, String promptTitle, String promptText,
boolean showErrorBox, String errorTitle, String errorText,
Ptg[] formula1, Ptg[] formula2,
CellRangeAddressList regions) {
// check length-limits
if(promptTitle != null && promptTitle.length() > 32) {
throw new IllegalStateException("Prompt-title cannot be longer than 32 characters, but had: " + promptTitle);
}
if(promptText != null && promptText.length() > 255) {
throw new IllegalStateException("Prompt-text cannot be longer than 255 characters, but had: " + promptText);
}
if(errorTitle != null && errorTitle.length() > 32) {
throw new IllegalStateException("Error-title cannot be longer than 32 characters, but had: " + errorTitle);
}
if(errorText != null && errorText.length() > 255) {
throw new IllegalStateException("Error-text cannot be longer than 255 characters, but had: " + errorText);
}
int flags = 0;
flags = opt_data_type.setValue(flags, validationType);
flags = opt_condition_operator.setValue(flags, operator);
flags = opt_error_style.setValue(flags, errorStyle);
flags = opt_empty_cell_allowed.setBoolean(flags, emptyCellAllowed);
flags = opt_suppress_dropdown_arrow.setBoolean(flags, suppressDropDownArrow);
flags = opt_string_list_formula.setBoolean(flags, isExplicitList);
flags = opt_show_prompt_on_cell_selected.setBoolean(flags, showPromptBox);
flags = opt_show_error_on_invalid_value.setBoolean(flags, showErrorBox);
_option_flags = flags;
_promptTitle = resolveTitleText(promptTitle);
_promptText = resolveTitleText(promptText);
_errorTitle = resolveTitleText(errorTitle);
_errorText = resolveTitleText(errorText);
_formula1 = Formula.create(formula1);
_formula2 = Formula.create(formula2);
_regions = regions;
}
/**
* CellRangeAddressを文字列形式のリストに変換する。
* @since 0.5
* @param region
* @return
*/
private static List<String> convertSqref(final CellRangeAddressList region) {
List<String> sqref = new ArrayList<>();
for(CellRangeAddress range : region.getCellRangeAddresses()) {
sqref.add(range.formatAsString());
}
return sqref;
}
/**
* Add numeric validation when export,
*
* @param validType validType
* @param operatorType operatorType
* @param expr1 Expression 1, such as: 1
* @param expr2 Expression 2(Only operation types between and notBetween are required)，such as：2
* @param sheet Current sheet
* @param firstRow Start row
* @param lastRow End row
* @param colIndex Column index
* @param showErrorBox Whether show error box
* @param errorBoxRank Error box rank
* @param errorTitle Error box title
* @param errorContent Error box value
* @param showTip Whether show tip
* @param tipContent Tip content
* @param tipTitle Tip title
*/
public static void addNumericValid(ValidType validType, OperatorType operatorType, String expr1, String expr2, Sheet sheet, int firstRow, int lastRow,
int colIndex, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent, boolean showTip,
String tipTitle, String tipContent) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint numericConstraint = helper.createNumericConstraint(validType.getType(),
operatorType.getType(), expr1, "".equals(expr2) ? null : expr2);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
DataValidation dataValidation = helper.createValidation(numericConstraint, regions);
dataValidation.setShowErrorBox(showErrorBox);
dataValidation.setErrorStyle(errorBoxRank.getRank());
dataValidation.createErrorBox(errorTitle, errorContent);
if (showTip) {
dataValidation.createPromptBox(tipTitle, tipContent);
}
sheet.addValidationData(dataValidation);
}
public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
return new HSSFDataValidation(cellRangeAddressList, constraint);
}