Examples of CellRangeAddressList

org.apache.poi.hssf.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.
@deprecated use {@link 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)
org.zkoss.poi.ss.util.CellRangeAddressList

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

  {
    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;
  }

View Full Code Here

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

    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

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

        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

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

      int rowNum = _currentRowIndex++;


      DataValidationHelper dataValidationHelper = _sheet.getDataValidationHelper();
      DataValidationConstraint dc = createConstraint(dataValidationHelper,operatorType, firstFormula, secondFormula, explicitListValues);


      DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(rowNum, rowNum, 0, 0));


      dv.setEmptyCellAllowed(allowEmpty);
      dv.setErrorStyle(errorStyle);
      dv.createErrorBox("Invalid Input", "Something is wrong - check condition!");
      dv.createPromptBox("Validated Cell", "Allowable values have been restricted");

View Full Code Here

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

    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");  
    int dvRow = 0;
    Sheet sheet = wb.getSheetAt(0);
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OP.EQUAL, "42", null);
    DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(dvRow, dvRow, 0, 0));
    
    dv.setEmptyCellAllowed(false);
    dv.setErrorStyle(ES.STOP);
    dv.setShowPromptBox(true);
    dv.createErrorBox("Xxx", "Yyy");

View Full Code Here

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


    // 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

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

    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

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

        //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

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

    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

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


    // 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

0 1 2 3 4 5

TOP

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.