Examples of org.apache.poi.ss.util.CellRangeAddressList

org.apache.poi.ss.util.CellRangeAddressList
Implementation of the cell range address lists,like is described in OpenOffice.org's Excel Documentation: excelfileformat.pdf sec 2.5.14 - 'Cell Range Address List' In BIFF8 there is a common way to store absolute cell range address lists in several records (not formulas). A cell range address list consists of a field with the number of ranges and the list of the range addresses. Each cell range address (called an ADDR structure) contains 4 16-bit-values.
@author Dragos Buleandra (dragos.buleandra@trade2b.ro)

    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(Cell.CELL_TYPE_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 j = 0; j < doubleOperandOperatorTypes.length; j++) {
        int operatorType = doubleOperandOperatorTypes[j];
        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(Cell.CELL_TYPE_NUMERIC);        
        cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());


        String value2String = validationType==ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
        cell_14.setCellType(Cell.CELL_TYPE_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());

View Full Code Here

        //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();

View Full Code Here

    public List<XSSFDataValidation> getDataValidations() {
      List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
      CTDataValidations dataValidations = this.worksheet.getDataValidations();
      if( dataValidations!=null && dataValidations.getCount() > 0 ) {
        for (CTDataValidation ctDataValidation : dataValidations.getDataValidationList()) {
          CellRangeAddressList addressList = new CellRangeAddressList();
          
          @SuppressWarnings("unchecked")
          List<String> sqref = ctDataValidation.getSqref();
          for (String stRef : sqref) {
            String[] regions = stRef.split(" ");
            for (int i = 0; i < regions.length; i++) {
            String[] parts = regions[i].split(":");
            CellReference begin = new CellReference(parts[0]);
            CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
            CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
            addressList.addCellRangeAddress(cellRangeAddress);
          }
        }
        XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
        xssfValidations.add(xssfDataValidation);
      }

View Full Code Here


    // 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);
  }

View Full Code Here

        HSSFSheet sheet = workbook.createSheet("Sheet1");
        sheet.protectSheet("secret");


        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dvc = dataValidationHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, "10", "100");
        CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
        DataValidation dv = dataValidationHelper.createValidation(dvc,numericCellAddressList);
        try {
            sheet.addValidationData(dv);
        } catch (IllegalStateException e) {
            String expMsg = "Unexpected (org.apache.poi.hssf.record.PasswordRecord) while looking for DV Table insert pos";

View Full Code Here

    public List<XSSFDataValidation> getDataValidations() {
      List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
      CTDataValidations dataValidations = this.worksheet.getDataValidations();
      if( dataValidations!=null && dataValidations.getCount() > 0 ) {
        for (CTDataValidation ctDataValidation : dataValidations.getDataValidationArray()) {
          CellRangeAddressList addressList = new CellRangeAddressList();
          
          @SuppressWarnings("unchecked")
          List<String> sqref = ctDataValidation.getSqref();
          for (String stRef : sqref) {
            String[] regions = stRef.split(" ");
            for (int i = 0; i < regions.length; i++) {
            String[] parts = regions[i].split(":");
            CellReference begin = new CellReference(parts[0]);
            CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
            CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
            addressList.addCellRangeAddress(cellRangeAddress);
          }
        }
        XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
        xssfValidations.add(xssfDataValidation);
      }

View Full Code Here

    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(Cell.CELL_TYPE_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 j = 0; j < doubleOperandOperatorTypes.length; j++) {
        int operatorType = doubleOperandOperatorTypes[j];
        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(Cell.CELL_TYPE_NUMERIC);        
        cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());


        String value2String = validationType==ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
        cell_14.setCellType(Cell.CELL_TYPE_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());

View Full Code Here


    // 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);
  }

View Full Code Here

        Workbook workbook = null;
        Sheet sheet = null;
        DataValidationHelper dvHelper = null;
        DataValidationConstraint dvConstraint = null;
        DataValidation validation = null;
        CellRangeAddressList addressList = null;
        try {


            // Using the ss.usermodel allows this class to support both binary
            // and xml based workbooks. The choice of which one to create is
            // made by checking the file extension.
            if (workbookName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook();
            } else {
                workbook = new HSSFWorkbook();
            }
            
            // Build the sheet that will hold the data for the validations. This
            // must be done first as it will create names that are referenced 
            // later.
            sheet = workbook.createSheet("Linked Validations");
            LinkedDropDownLists.buildDataSheet(sheet);


            // Build the first data validation to occupy cell A1. Note
            // that it retrieves it's data from the named area or region called
            // CHOICES. Further information about this can be found in the
            // static buildDataSheet() method below.
            addressList = new CellRangeAddressList(0, 0, 0, 0);
            dvHelper = sheet.getDataValidationHelper();
            dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
            validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
            
            // Now, build the linked or dependent drop down list that will
            // occupy cell B1. The key to the whole process is the use of the
            // INDIRECT() function. In the buildDataSheet(0 method, a series of
            // named regions are created and the names of three of them mirror
            // the options available to the user in the first drop down list
            // (in cell A1). Using the INDIRECT() function makes it possible
            // to convert the selection the user makes in that first drop down
            // into the addresses of a named region of cells and then to use
            // those cells to populate the second drop down list.
            addressList = new CellRangeAddressList(0, 0, 1, 1);
            dvConstraint = dvHelper.createFormulaListConstraint(
                    "INDIRECT(UPPER($A$1))");
            validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);

View Full Code Here

        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);


        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

View Full Code Here

0 1 2 3 4 5 6

TOP

Related Classes of org.apache.poi.ss.util.CellRangeAddressList

com.asakusafw.testdata.generator.excel.SheetEditor

com.asakusafw.testtools.templategen.ExcelBookBuilder

org.apache.poi.hssf.record.CFHeaderRecord

org.apache.poi.hssf.record.DVRecord

org.apache.poi.hssf.record.TestPLVRecord

org.apache.poi.hssf.usermodel.HSSFSheet

org.apache.poi.hssf.usermodel.TestDataValidation

org.apache.poi.hssf.usermodel.TestDataValidation$ValidationAdder

org.apache.poi.hssf.usermodel.TestHSSFSheet

org.apache.poi.ss.examples.LinkedDropDownLists

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.