Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
  /* ====================================================================
     Licensed to the Apache Software Foundation (ASF) under one or more
     contributor license agreements.  See the NOTICE file distributed with
     this work for additional information regarding copyright ownership.
     The ASF licenses this file to You under the Apache License, Version 2.0
     (the "License"); you may not use this file except in compliance with
     the License.  You may obtain a copy of the License at
  
         http://www.apache.org/licenses/LICENSE-2.0
 
    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.
 ==================================================================== */
 
 package org.apache.poi.ss.util;
 
 
 
 public class AreaReference {

    
The character (!) that separates sheet names from cell references
  
     private static final char SHEET_NAME_DELIMITER = '!';
    
The character (:) that separates the two cell references in a multi-cell area reference
 
     private static final char CELL_DELIMITER = ':';
    
The character (') used to quote sheet names when they contain special characters
 
     private static final char SPECIAL_NAME_DELIMITER = '\'';
     
     private final CellReference _firstCell;
     private final CellReference _lastCell;
     private final boolean _isSingleCell;

    
Create an area ref from a string representation. Sheet names containing special characters should be delimited and escaped as per normal syntax rules for formulas.
The area reference must be contiguous (i.e. represent a single rectangle, not a union of rectangles)
 
     public AreaReference(String reference) {
         if(! isContiguous(reference)) {
             throw new IllegalArgumentException(
                     "References passed to the AreaReference must be contiguous, " +
                     "use generateContiguous(ref) if you have non-contiguous references");
         }
 
         String[] parts = separateAreaRefs(reference);
         String part0 = parts[0];
         if (parts.length == 1) {
             // TODO - probably shouldn't initialize area ref when text is really a cell ref
             // Need to fix some named range stuff to get rid of this
              = new CellReference(part0);
             
              = ;
              = true;
             return;
         }
         if (parts.length != 2) {
             throw new IllegalArgumentException("Bad area ref '" + reference + "'");
         }
         
         String part1 = parts[1];
         if (isPlainColumn(part0)) {
             if (!isPlainColumn(part1)) {
                 throw new RuntimeException("Bad area ref '" + reference + "'");
             }
             // Special handling for whole-column references
             // Represented internally as x$1 to x$65536
             //  which is the maximum range of rows
 
             boolean firstIsAbs = CellReference.isPartAbsolute(part0);
             boolean lastIsAbs = CellReference.isPartAbsolute(part1);
             
             int col0 = CellReference.convertColStringToIndex(part0);
             int col1 = CellReference.convertColStringToIndex(part1);
             
              = new CellReference(0, col0truefirstIsAbs);
              = new CellReference(0xFFFF, col1truelastIsAbs);
              = false;
             // TODO - whole row refs
         } else {
              = new CellReference(part0);
              = new CellReference(part1);
              = part0.equals(part1);
        }
      }
     
     private boolean isPlainColumn(String refPart) {
         for(int i=refPart.length()-1; i>=0; i--) {
             int ch = refPart.charAt(i);
             if (ch == '$' && i==0) {
                 continue;
             }
             if (ch < 'A' || ch > 'Z') {
                 return false;
             }
        }
        return true;
    }

    
Creates an area ref from a pair of Cell References.
    public AreaReference(CellReference topLeftCellReference botRight) {
        boolean swapRows = topLeft.getRow() > botRight.getRow();
        boolean swapCols = topLeft.getCol() > botRight.getCol();
        if (swapRows || swapCols) {
            int firstRow
            int lastRow
            int firstColumn
            int lastColumn;
            boolean firstRowAbs
            boolean lastRowAbs
            boolean firstColAbs;
            boolean lastColAbs;   
            if (swapRows) {
                firstRow = botRight.getRow();
                firstRowAbs = botRight.isRowAbsolute();
                lastRow = topLeft.getRow();
                lastRowAbs = topLeft.isRowAbsolute();
            } else {
                firstRow = topLeft.getRow();
                firstRowAbs = topLeft.isRowAbsolute();
                lastRow = botRight.getRow();
                lastRowAbs = botRight.isRowAbsolute();
            }
            if (swapCols) {
                firstColumn = botRight.getCol();
                firstColAbs = botRight.isColAbsolute();
                lastColumn = topLeft.getCol();
                lastColAbs = topLeft.isColAbsolute();
            } else {
                firstColumn = topLeft.getCol();
                firstColAbs = topLeft.isColAbsolute();
                lastColumn = botRight.getCol();
                lastColAbs = botRight.isColAbsolute();
            }
             = new CellReference(firstRowfirstColumnfirstRowAbsfirstColAbs);
             = new CellReference(lastRowlastColumnlastRowAbslastColAbs);
        } else {
             = topLeft;
             = botRight;
        }
         = false;
    }

    
Is the reference for a contiguous (i.e. unbroken) area, or is it made up of several different parts? (If it is, you will need to call generateContiguous(java.lang.String))
    public static boolean isContiguous(String reference) {
       // If there's a sheet name, strip it off
       int sheetRefEnd = reference.indexOf('!'); 
       if(sheetRefEnd != -1) {
          reference = reference.substring(sheetRefEnd);
       }
       // Check for the , as a sign of non-coniguous
       if(reference.indexOf(',') == -1) {
          return true;
       }
       return false;
    }
    public static AreaReference getWholeRow(String startString end) {
        return new AreaReference("$A" + start + ":$IV" + end);
    }
    public static AreaReference getWholeColumn(String startString end) {
        return new AreaReference(start + "$1:" + end + "$65536");
    }

    
Is the reference for a whole-column reference, such as C:C or D:G ?
    public static boolean isWholeColumnReference(CellReference topLeftCellReference botRight) {
        // These are represented as something like
        //   C$1:C$65535 or D$1:F$0
        // i.e. absolute from 1st row to 0th one
        if(topLeft.getRow() == 0 && topLeft.isRowAbsolute() &&
            botRight.getRow() == ..getLastRowIndex() && botRight.isRowAbsolute()) {
            return true;
        }
        return false;
    }
    public boolean isWholeColumnReference() {
        return isWholeColumnReference();
    }

    
Takes a non-contiguous area reference, and returns an array of contiguous area references.
    public static AreaReference[] generateContiguous(String reference) {
        ArrayList refs = new ArrayList();
        StringTokenizer st = new StringTokenizer(reference",");
        while(st.hasMoreTokens()) {
            refs.add(
                    new AreaReference(st.nextToken())
            );
        }
        return (AreaReference[])refs.toArray(new AreaReference[refs.size()]);
    }

    

Returns:
false if this area reference involves more than one cell
    public boolean isSingleCell() {
        return ;
    }
    
    

Returns:
the first cell reference which defines this area. Usually this cell is in the upper left corner of the area (but this is not a requirement).
   public CellReference getFirstCell() {
        return ;
    }
    
    
Note - if this area reference refers to a single cell, the return value of this method will be identical to that of getFirstCell()

Returns:
the second cell reference which defines this area. For multi-cell areas, this is cell diagonally opposite the 'first cell'. Usually this cell is in the lower right corner of the area (but this is not a requirement).
    public CellReference getLastCell() {
        return ;
    }
    
Returns a reference to every cell covered by this area
    public CellReference[] getAllReferencedCells() {
        // Special case for single cell reference
        if() {
            return  new CellReference[] { , };
        }
 
        // Interpolate between the two
        int minRow = Math.min(.getRow(), .getRow());
        int maxRow = Math.max(.getRow(), .getRow());
        int minCol = Math.min(.getCol(), .getCol());
        int maxCol = Math.max(.getCol(), .getCol());
        String sheetName = .getSheetName();
        
        ArrayList refs = new ArrayList();
        for(int row=minRowrow<=maxRowrow++) {
            for(int col=minColcol<=maxColcol++) {
                CellReference ref = new CellReference(sheetNamerowcol.isRowAbsolute(), .isColAbsolute());
                refs.add(ref);
            }
        }
        return (CellReference[])refs.toArray(new CellReference[refs.size()]);
    }

    
Returns a text representation of this area reference.

Example return values:

ResultComment
A1:A1Single cell area reference without sheet
A1:$C$1Multi-cell area reference without sheet
Sheet1!A$1:B4Standard sheet name
'O''Brien''s Sales'!B5:C6' Sheet name with special characters

Returns:
the text representation of this area reference as it would appear in a formula.
    public String formatAsString() {
        // Special handling for whole-column references
        if(isWholeColumnReference()) {
            return
                CellReference.convertNumToColString(.getCol())
                + ":" +
                CellReference.convertNumToColString(.getCol());
        }
        
        StringBuffer sb = new StringBuffer(32);
        sb.append(.formatAsString());
        if(!) {
            sb.append();
            if(.getSheetName() == null) {
                sb.append(.formatAsString());
            } else {
                // don't want to include the sheet name twice
                .appendCellReference(sb);
            }
        }
        return sb.toString();
    }
    public String toString() {
        StringBuffer sb = new StringBuffer(64);
        sb.append(getClass().getName()).append(" [");
        sb.append(formatAsString());
        sb.append("]");
        return sb.toString();
    }

    
Separates Area refs in two parts and returns them as separate elements in a String array, each qualified with the sheet name (if present)

Returns:
array with one or two elements. never null
    private static String[] separateAreaRefs(String reference) {
        // TODO - refactor cell reference parsing logic to one place.
        // Current known incarnations: 
        //   FormulaParser.GetName()
        //   CellReference.separateRefParts() 
        //   AreaReference.separateAreaRefs() (here)
        //   SheetNameFormatter.format() (inverse)
        
        
        int len = reference.length();
        int delimiterPos = -1;
        boolean insideDelimitedName = false;
        for(int i=0; i<leni++) {
            switch(reference.charAt(i)) {
                case :
                    if(!insideDelimitedName) {
                        if(delimiterPos >=0) {
                            throw new IllegalArgumentException("More than one cell delimiter '" 
                                    +  + "' appears in area reference '" + reference + "'");
                        }
                        delimiterPos = i;
                    }
                default:
                    continue;
                case :
                    // fall through
            }
            if(!insideDelimitedName) {
                insideDelimitedName = true;
                continue;
            }
            
            if(i >= len-1) {
                // reference ends with the delimited name. 
                // Assume names like: "Sheet1!'A1'" are never legal.
                throw new IllegalArgumentException("Area reference '" + reference 
                        + "' ends with special name delimiter '"  +  + "'");
            }
            if(reference.charAt(i+1) == ) {
                // two consecutive quotes is the escape sequence for a single one
                i++; // skip this and keep parsing the special name
            } else {
                // this is the end of the delimited name
                insideDelimitedName = false;
            }
        }
        if(delimiterPos < 0) {
            return new String[] { reference, };
        }
        String partA = reference.substring(0, delimiterPos);
        String partB = reference.substring(delimiterPos+1);
        if(partB.indexOf() >=0) {
            // TODO - are references like "Sheet1!A1:Sheet1:B2" ever valid?  
            // FormulaParser has code to handle that.
            
            throw new RuntimeException("Unexpected " +  
                    + " in second cell reference of '" + reference + "'");
        }
        
        int plingPos = partA.lastIndexOf();
        if(plingPos < 0) {
            return new String [] { partApartB, };
        }
        
        String sheetName = partA.substring(0, plingPos + 1); // +1 to include delimiter
        
        return new String [] { partAsheetName + partB, };
    }
New to GrepCode? Check out our FAQ X