use of org.apache.poi.ss.usermodel.DataValidationConstraint in project poi by apache.
the class TestSXSSFDataValidation method test53965.
@Test
public void test53965() throws Exception {
SXSSFWorkbook wb = new SXSSFWorkbook();
try {
Sheet sheet = wb.createSheet();
//<-- works
List<? extends DataValidation> lst = sheet.getDataValidations();
assertEquals(0, lst.size());
//create the cell that will have the validation applied
sheet.createRow(0).createCell(0);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
sheet.addValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.getDataValidations();
assertEquals(1, lst.size());
} finally {
wb.close();
}
}
use of org.apache.poi.ss.usermodel.DataValidationConstraint in project poi by apache.
the class TestDataValidation method testGetDataValidationsIntegerFormula.
@Test
public void testGetDataValidationsIntegerFormula() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<HSSFDataValidation> list = sheet.getDataValidations();
assertEquals(0, list.size());
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createIntegerConstraint(OperatorType.BETWEEN, "=A2", "=A3");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
sheet.addValidationData(validation);
// <-- works
list = sheet.getDataValidations();
assertEquals(1, list.size());
HSSFDataValidation dv = list.get(0);
DVConstraint c = dv.getConstraint();
assertEquals(ValidationType.INTEGER, c.getValidationType());
assertEquals(OperatorType.BETWEEN, c.getOperator());
assertEquals("A2", c.getFormula1());
assertEquals("A3", c.getFormula2());
assertEquals(null, c.getValue1());
assertEquals(null, c.getValue2());
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationConstraint in project poi by apache.
the class TestDataValidation method testGetDataValidationsListExplicit.
@Test
public void testGetDataValidationsListExplicit() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<HSSFDataValidation> list = sheet.getDataValidations();
assertEquals(0, list.size());
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(new String[] { "aaa", "bbb", "ccc" });
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
validation.setSuppressDropDownArrow(true);
sheet.addValidationData(validation);
// <-- works
list = sheet.getDataValidations();
assertEquals(1, list.size());
HSSFDataValidation dv = list.get(0);
assertEquals(true, dv.getSuppressDropDownArrow());
DVConstraint c = dv.getConstraint();
assertEquals(ValidationType.LIST, c.getValidationType());
assertEquals(null, c.getFormula1());
assertEquals(null, c.getFormula2());
assertEquals(null, c.getValue1());
assertEquals(null, c.getValue2());
String[] values = c.getExplicitListValues();
assertEquals(3, values.length);
assertEquals("aaa", values[0]);
assertEquals("bbb", values[1]);
assertEquals("ccc", values[2]);
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationConstraint in project poi by apache.
the class TestDataValidation method testGetDataValidationsIntegerValue.
@Test
public void testGetDataValidationsIntegerValue() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<HSSFDataValidation> list = sheet.getDataValidations();
assertEquals(0, list.size());
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createIntegerConstraint(OperatorType.BETWEEN, "100", "200");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
sheet.addValidationData(validation);
// <-- works
list = sheet.getDataValidations();
assertEquals(1, list.size());
HSSFDataValidation dv = list.get(0);
DVConstraint c = dv.getConstraint();
assertEquals(ValidationType.INTEGER, c.getValidationType());
assertEquals(OperatorType.BETWEEN, c.getOperator());
assertEquals(null, c.getFormula1());
assertEquals(null, c.getFormula2());
assertEquals(new Double("100"), c.getValue1());
assertEquals(new Double("200"), c.getValue2());
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationConstraint in project poi by apache.
the class TestXSSFDataValidation method testAddValidations.
@Test
public void testAddValidations() throws Exception {
XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("DataValidations-49244.xlsx");
Sheet sheet = wb1.getSheetAt(0);
List<XSSFDataValidation> dataValidations = ((XSSFSheet) sheet).getDataValidations();
/**
* For each validation type, there are two cells with the same validation. This tests
* application of a single validation definition to multiple cells.
*
* For list ( 3 validations for explicit and 3 for formula )
* - one validation that allows blank.
* - one that does not allow blank.
* - one that does not show the drop down arrow.
* = 2
*
* For number validations ( integer/decimal and text length ) with 8 different types of operators.
* = 50
*
* = 52 ( Total )
*/
assertEquals(52, dataValidations.size());
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
int[] singleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL };
int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN };
BigDecimal value = new BigDecimal("10"), value2 = new BigDecimal("20");
BigDecimal dvalue = new BigDecimal("10.001"), dvalue2 = new BigDecimal("19.999");
final int lastRow = sheet.getLastRowNum();
int offset = lastRow + 3;
int lastKnownNumValidations = dataValidations.size();
Row row = sheet.createRow(offset++);
Cell cell = row.createCell(0);
DataValidationConstraint explicitListValidation = dataValidationHelper.createExplicitListConstraint(new String[] { "MA", "MI", "CA" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
DataValidation dataValidation = dataValidationHelper.createValidation(explicitListValidation, cellRangeAddressList);
setOtherValidationParameters(dataValidation);
sheet.addValidationData(dataValidation);
lastKnownNumValidations++;
row = sheet.createRow(offset++);
cell = row.createCell(0);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
Cell firstCell = row.createCell(1);
firstCell.setCellValue("UT");
Cell secondCell = row.createCell(2);
secondCell.setCellValue("MN");
Cell thirdCell = row.createCell(3);
thirdCell.setCellValue("IL");
int rowNum = row.getRowNum() + 1;
String listFormula = new StringBuilder("$B$").append(rowNum).append(":").append("$D$").append(rowNum).toString();
DataValidationConstraint formulaListValidation = dataValidationHelper.createFormulaListConstraint(listFormula);
dataValidation = dataValidationHelper.createValidation(formulaListValidation, cellRangeAddressList);
setOtherValidationParameters(dataValidation);
sheet.addValidationData(dataValidation);
lastKnownNumValidations++;
offset++;
offset++;
for (int i = 0; i < validationTypes.length; i++) {
int validationType = validationTypes[i];
offset = offset + 2;
final Row row0 = sheet.createRow(offset++);
Cell cell_10 = row0.createCell(0);
cell_10.setCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "Integer" : "Text Length");
offset++;
for (int j = 0; j < singleOperandOperatorTypes.length; j++) {
int operatorType = singleOperandOperatorTypes[j];
final Row row1 = sheet.createRow(offset++);
//For Integer (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
final Row row2 = i == 0 && j < 2 ? sheet.createRow(offset++) : null;
cell_10 = row1.createCell(0);
cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
Cell cell_11 = row1.createCell(1);
Cell cell_21 = row1.createCell(2);
Cell cell_22 = i == 0 && j < 2 ? row2.createCell(2) : null;
Cell cell_13 = row1.createCell(3);
cell_13.setCellType(CellType.NUMERIC);
cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
//First create value based validation;
DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value.toString(), null);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, null);
if (i == 0 && j == 0) {
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
} else if (i == 0 && j == 1) {
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
} else {
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
}
}
for (int operatorType : doubleOperandOperatorTypes) {
final Row row1 = sheet.createRow(offset++);
cell_10 = row1.createCell(0);
cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
Cell cell_11 = row1.createCell(1);
Cell cell_21 = row1.createCell(2);
Cell cell_13 = row1.createCell(3);
Cell cell_14 = row1.createCell(4);
String value1String = validationType == ValidationType.DECIMAL ? dvalue.toString() : value.toString();
cell_13.setCellType(CellType.NUMERIC);
cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
String value2String = validationType == ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
cell_14.setCellType(CellType.NUMERIC);
cell_14.setCellValue(validationType == ValidationType.DECIMAL ? dvalue2.doubleValue() : value2.intValue());
//First create value based validation;
DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value1String, value2String);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
String formula2 = new CellReference(cell_14.getRowIndex(), cell_14.getColumnIndex()).formatAsString();
constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, formula2);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
setOtherValidationParameters(validation);
sheet.addValidationData(validation);
assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
}
}
XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
wb1.close();
Sheet sheetAt = wb2.getSheetAt(0);
assertEquals(lastKnownNumValidations, ((XSSFSheet) sheetAt).getDataValidations().size());
wb2.close();
}
Aggregations