use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.
the class TestXSSFDataValidation method createValidation.
private XSSFDataValidation createValidation(XSSFSheet sheet) {
//create the cell that will have the validation applied
final Row row = sheet.createRow(0);
row.createCell(0);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("true");
final XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
return validation;
}
use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.
the class TestDataValidation method testAddToExistingSheet.
@Test
public void testAddToExistingSheet() throws Exception {
// dvEmpty.xls is a simple one sheet workbook. With a DataValidations header record but no
// DataValidations. It's important that the example has one SHEETPROTECTION record.
// Such a workbook can be created in Excel (2007) by adding datavalidation for one cell
// and then deleting the row that contains the cell.
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
Sheet sheet = wb.getSheetAt(0);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OperatorType.EQUAL, "42", null);
DataValidation dv = dataValidationHelper.createValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
dv.setEmptyCellAllowed(false);
dv.setErrorStyle(ErrorStyle.STOP);
dv.setShowPromptBox(true);
dv.createErrorBox("Xxx", "Yyy");
dv.setSuppressDropDownArrow(true);
sheet.addValidationData(dv);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
byte[] wbData = baos.toByteArray();
// if (false) { // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)
//
// ERFListener erfListener = null; // new MyERFListener();
// EventRecordFactory erf = new EventRecordFactory(erfListener, null);
// try {
// POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(baos.toByteArray()));
// erf.processRecords(fs.createDocumentInputStream("Workbook"));
// } catch (RecordFormatException e) {
// throw new RuntimeException(e);
// } catch (IOException e) {
// throw new RuntimeException(e);
// }
// }
// else verify record ordering by navigating the raw bytes
byte[] dvHeaderRecStart = { (byte) 0xB2, 0x01, 0x12, 0x00 };
int dvHeaderOffset = findIndex(wbData, dvHeaderRecStart);
assertTrue(dvHeaderOffset > 0);
int nextRecIndex = dvHeaderOffset + 22;
int nextSid = ((wbData[nextRecIndex + 0] << 0) & 0x00FF) + ((wbData[nextRecIndex + 1] << 8) & 0xFF00);
if (nextSid == 0x0867) {
fail("Identified bug 45519");
}
assertEquals(DVRecord.sid, nextSid);
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.
the class TestDataValidation method testGetDataValidationsDecimal.
@Test
public void testGetDataValidationsDecimal() 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.createDecimalConstraint(OperatorType.BETWEEN, "=A2", "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.DECIMAL, c.getValidationType());
assertEquals(OperatorType.BETWEEN, c.getOperator());
assertEquals("A2", c.getFormula1());
assertEquals(null, c.getFormula2());
assertEquals(null, c.getValue1());
assertEquals(new Double("200"), c.getValue2());
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.
the class TestDataValidation method testGetDataValidationsListFormula.
@Test
public void testGetDataValidationsListFormula() 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.createFormulaListConstraint("A2");
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("A2", c.getFormula1());
assertEquals(null, c.getFormula2());
assertEquals(null, c.getValue1());
assertEquals(null, c.getValue2());
wb.close();
}
use of org.apache.poi.ss.usermodel.DataValidationHelper in project poi by apache.
the class TestDataValidation method testGetDataValidationsFormula.
@Test
public void testGetDataValidationsFormula() 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.createCustomConstraint("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.FORMULA, c.getValidationType());
assertEquals("A2:A3", c.getFormula1());
assertEquals(null, c.getFormula2());
assertEquals(null, c.getValue1());
assertEquals(null, c.getValue2());
wb.close();
}
Aggregations