View | Details | Raw Unified | Return to bug 44417
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (-3 / +3 lines)
Lines 1119-1130 Link Here
1119
	public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
1119
	public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
1120
							  int startRow, int endRow) {
1120
							  int startRow, int endRow) {
1121
1121
1122
		//using absolute references because they dont get copied and pasted anyway
1122
		//using absolute references because they don't get copied and pasted anyway
1123
		CellReference cell = new CellReference(startRow, startColumn, true, true);
1123
		CellReference cell = new CellReference(startRow, startColumn, true, true);
1124
		String reference = cell.toString();
1124
		String reference = cell.formatAsString();
1125
1125
1126
		cell = new CellReference(endRow, endColumn, true, true);
1126
		cell = new CellReference(endRow, endColumn, true, true);
1127
		reference = reference+":"+cell.toString();
1127
		reference = reference+":"+cell.formatAsString();
1128
1128
1129
		setPrintArea(sheetIndex, reference);
1129
		setPrintArea(sheetIndex, reference);
1130
	}
1130
	}
(-)src/java/org/apache/poi/hssf/util/AreaReference.java (-54 / +156 lines)
Lines 21-46 Link Here
21
import java.util.ArrayList;
21
import java.util.ArrayList;
22
import java.util.StringTokenizer;
22
import java.util.StringTokenizer;
23
23
24
public class AreaReference {
24
public final class AreaReference {
25
25
26
    /** The character (!) that separates sheet names from cell references */ 
27
    private static final char SHEET_NAME_DELIMITER = '!';
28
    /** The character (:) that separates the two cell references in a multi-cell area reference */
29
    private static final char CELL_DELIMITER = ':';
30
    /** The character (') used to quote sheet names when they contain special characters */
31
    private static final char SPECIAL_NAME_DELIMITER = '\'';
32
    
33
    private final CellReference _firstCell;
34
    private final CellReference _lastCell;
35
    private final boolean _isSingleCell;
26
36
27
private CellReference [] cells;
28
private int dim;
29
30
    /**
37
    /**
31
     * Create an area ref from a string representation.
38
     * Create an area ref from a string representation.  Sheet names containing special characters should be
32
     * The area reference must be contiguous
39
     * delimited and escaped as per normal syntax rules for formulas.<br/> 
40
     * The area reference must be contiguous (i.e. represent a single rectangle, not a union of rectangles)
33
     */
41
     */
34
    public AreaReference(String reference) {
42
    public AreaReference(String reference) {
35
        if(! isContiguous(reference)) {
43
        if(! isContiguous(reference)) {
36
            throw new IllegalArgumentException("References passed to the AreaReference must be contiguous, use generateContiguous(ref) if you have non-contiguous references");
44
            throw new IllegalArgumentException(
45
                    "References passed to the AreaReference must be contiguous, " +
46
                    "use generateContiguous(ref) if you have non-contiguous references");
37
        }
47
        }
38
48
39
        String[] refs = seperateAreaRefs(reference);
49
        String[] parts = separateAreaRefs(reference);
40
        dim = refs.length;
50
        _firstCell = new CellReference(parts[0]);
41
        cells = new CellReference[dim];
51
        
42
        for (int i=0;i<dim;i++) {
52
        if(parts.length == 2) {
43
            cells[i]=new CellReference(refs[i]);
53
            _lastCell = new CellReference(parts[1]);
54
            _isSingleCell = false;
55
        } else {
56
            _lastCell = _firstCell;
57
            _isSingleCell = true;
44
        }
58
        }
45
    }
59
    }
46
60
Lines 73-147 Link Here
73
        return (AreaReference[])refs.toArray(new AreaReference[refs.size()]);
87
        return (AreaReference[])refs.toArray(new AreaReference[refs.size()]);
74
    }
88
    }
75
89
76
    //not sure if we need to be flexible here!
90
    /**
77
    /** return the dimensions of this area
91
     * @return <code>false</code> if this area reference involves more than one cell
78
     **/
92
     */
79
    public int getDim() {
93
    public boolean isSingleCell() {
80
        return dim;
94
        return _isSingleCell;
81
    }
95
    }
82
    /** 
96
    
83
     * Return the cell references that define this area
97
    /**
84
     * (i.e. the two corners) 
98
     * @return the first cell reference which defines this area. Usually this cell is in the upper
99
     * left corner of the area (but this is not a requirement).
85
     */
100
     */
86
    public CellReference[] getCells() {
101
   public CellReference getFirstCell() {
87
        return cells;
102
        return _firstCell;
88
    }
103
    }
104
    
89
    /**
105
    /**
106
     * Note - if this area reference refers to a single cell, the return value of this method will
107
     * be identical to that of <tt>getFirstCell()</tt>
108
     * @return the second cell reference which defines this area.  For multi-cell areas, this is 
109
     * cell diagonally opposite the 'first cell'.  Usually this cell is in the lower right corner 
110
     * of the area (but this is not a requirement).
111
     */
112
    public CellReference getLastCell() {
113
        return _lastCell;
114
    }
115
    /**
90
     * Returns a reference to every cell covered by this area
116
     * Returns a reference to every cell covered by this area
91
     */
117
     */
92
    public CellReference[] getAllReferencedCells() {
118
    public CellReference[] getAllReferencedCells() {
93
    	// Special case for single cell reference
119
    	// Special case for single cell reference
94
    	if(cells.length == 1) {
120
    	if(_isSingleCell) {
95
    		return cells;
121
    		return  new CellReference[] { _firstCell, };
96
    	}
122
    	}
123
 
97
    	// Interpolate between the two
124
    	// Interpolate between the two
98
    	int minRow = Math.min(cells[0].getRow(), cells[1].getRow());
125
        int minRow = Math.min(_firstCell.getRow(), _lastCell.getRow());
99
    	int maxRow = Math.max(cells[0].getRow(), cells[1].getRow());
126
    	int maxRow = Math.max(_firstCell.getRow(), _lastCell.getRow());
100
    	int minCol = Math.min(cells[0].getCol(), cells[1].getCol());
127
    	int minCol = Math.min(_firstCell.getCol(), _lastCell.getCol());
101
    	int maxCol = Math.max(cells[0].getCol(), cells[1].getCol());
128
    	int maxCol = Math.max(_firstCell.getCol(), _lastCell.getCol());
129
        String sheetName = _firstCell.getSheetName();
102
    	
130
    	
103
    	ArrayList refs = new ArrayList();
131
    	ArrayList refs = new ArrayList();
104
    	for(int row=minRow; row<=maxRow; row++) {
132
    	for(int row=minRow; row<=maxRow; row++) {
105
    		for(int col=minCol; col<=maxCol; col++) {
133
    		for(int col=minCol; col<=maxCol; col++) {
106
    			CellReference ref = new CellReference(row, col, cells[0].isRowAbsolute(), cells[0].isColAbsolute());
134
    			CellReference ref = new CellReference(sheetName, row, col, _firstCell.isRowAbsolute(), _firstCell.isColAbsolute());
107
    			ref.setSheetName(cells[0].getSheetName());
108
    			refs.add(ref);
135
    			refs.add(ref);
109
    		}
136
    		}
110
    	}
137
    	}
111
    	return (CellReference[])refs.toArray(new CellReference[refs.size()]);
138
    	return (CellReference[])refs.toArray(new CellReference[refs.size()]);
112
    }
139
    }
113
140
114
    public String toString() {
141
    /**
115
        StringBuffer retval = new StringBuffer();
142
     *  Example return values:
116
        for (int i=0;i<dim;i++){
143
     *    <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
117
            retval.append(':');
144
     *      <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
118
            retval.append(cells[i].toString());
145
     *      <tr><td>A1:A1</td><td>Single cell area reference without sheet</td></tr>
146
     *      <tr><td>A1:$C$1</td><td>Multi-cell area reference without sheet</td></tr>
147
     *      <tr><td>Sheet1!A$1:B4</td><td>Standard sheet name</td></tr>
148
     *      <tr><td>'O''Brien''s Sales'!B5:C6'&nbsp;</td><td>Sheet name with special characters</td></tr>
149
     *    </table>
150
     * @return the text representation of this area reference as it would appear in a formula.
151
     */
152
    public String formatAsString() {
153
        StringBuffer sb = new StringBuffer(32);
154
        sb.append(_firstCell.formatAsString());
155
        if(!_isSingleCell) {
156
            sb.append(CELL_DELIMITER);
157
            if(_lastCell.getSheetName() == null) {
158
                sb.append(_lastCell.formatAsString());
159
            } else {
160
                // don't want to include the sheet name twice
161
                _lastCell.appendCellReference(sb);
162
            }
119
        }
163
        }
120
        retval.deleteCharAt(0);
164
        return sb.toString();
121
        return retval.toString();
122
    }
165
    }
166
    public String toString() {
167
        StringBuffer sb = new StringBuffer(64);
168
        sb.append(getClass().getName()).append(" [");
169
        sb.append(formatAsString());
170
        sb.append("]");
171
        return sb.toString();
172
    }
123
173
124
    /**
174
    /**
125
     * seperates Area refs in two parts and returns them as seperate elements in a
175
     * Separates Area refs in two parts and returns them as separate elements in a String array,
126
     * String array
176
     * each qualified with the sheet name (if present)
177
     * 
178
     * @return array with one or two elements. never <code>null</code>
127
     */
179
     */
128
    private String[] seperateAreaRefs(String reference) {
180
    private static String[] separateAreaRefs(String reference) {
129
        String[] retval = null;
181
        // TODO - refactor cell reference parsing logic to one place.
130
182
        // Current known incarnations: 
131
        int length = reference.length();
183
        //   FormulaParser.GetName()
132
184
        //   CellReference.separateRefParts() 
133
        int loc = reference.indexOf(':',0);
185
        //   AreaReference.separateAreaRefs() (here)
134
        if(loc == -1){
186
        //   SheetNameFormatter.format() (inverse)
135
           retval = new String[1];
187
        
136
           retval[0] = reference;
188
        
189
        int len = reference.length();
190
        int delimiterPos = -1;
191
        boolean insideDelimitedName = false;
192
        for(int i=0; i<len; i++) {
193
            switch(reference.charAt(i)) {
194
                case CELL_DELIMITER:
195
                    if(!insideDelimitedName) {
196
                        if(delimiterPos >=0) {
197
                            throw new IllegalArgumentException("More than one cell delimiter '" 
198
                                    + CELL_DELIMITER + "' appears in area reference '" + reference + "'");
199
                        }
200
                        delimiterPos = i;
201
                    }
202
                default:
203
                    continue;
204
                case SPECIAL_NAME_DELIMITER:
205
                    // fall through
206
            }
207
            if(!insideDelimitedName) {
208
                insideDelimitedName = true;
209
                continue;
210
            }
211
            
212
            if(i >= len-1) {
213
                // reference ends with the delimited name. 
214
                // Assume names like: "Sheet1!'A1'" are never legal.
215
                throw new IllegalArgumentException("Area reference '" + reference 
216
                        + "' ends with special name delimiter '"  + SPECIAL_NAME_DELIMITER + "'");
217
            }
218
            if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
219
                // two consecutive quotes is the escape sequence for a single one
220
                i++; // skip this and keep parsing the special name
221
            } else {
222
                // this is the end of the delimited name
223
                insideDelimitedName = false;
224
            }
137
        }
225
        }
138
        else{
226
        if(delimiterPos < 0) {
139
           retval = new String[2];
227
            return new String[] { reference, };
140
           int sheetStart = reference.indexOf("!");
228
        }
141
229
142
           retval[0] = reference.substring(0, sheetStart+1) + reference.substring(sheetStart + 1,loc);
230
        String partA = reference.substring(0, delimiterPos);
143
           retval[1] = reference.substring(0, sheetStart+1) + reference.substring(loc+1);
231
        String partB = reference.substring(delimiterPos+1);
232
        if(partB.indexOf(SHEET_NAME_DELIMITER) >=0) {
233
            // TODO - are references like "Sheet1!A1:Sheet1:B2" ever valid?  
234
            // FormulaParser has code to handle that.
235
            
236
            throw new RuntimeException("Unexpected " + SHEET_NAME_DELIMITER 
237
                    + " in second cell reference of '" + reference + "'");
144
        }
238
        }
145
        return retval;
239
        
240
        int plingPos = partA.lastIndexOf(SHEET_NAME_DELIMITER);
241
        if(plingPos < 0) {
242
            return new String [] { partA, partB, };
243
        }
244
        
245
        String sheetName = partA.substring(0, plingPos + 1); // +1 to include delimiter
246
        
247
        return new String [] { partA, sheetName + partB, };
146
    }
248
    }
147
}
249
}
(-)src/java/org/apache/poi/hssf/util/CellReference.java (-69 / +159 lines)
Lines 15-89 Link Here
15
   limitations under the License.
15
   limitations under the License.
16
==================================================================== */
16
==================================================================== */
17
17
18
19
package org.apache.poi.hssf.util;
18
package org.apache.poi.hssf.util;
20
19
20
import org.apache.poi.hssf.record.formula.SheetNameFormatter;
21
21
/**
22
/**
22
 *
23
 *
23
 * @author  Avik Sengupta
24
 * @author  Avik Sengupta
24
 * @author  Dennis Doubleday (patch to seperateRowColumns())
25
 * @author  Dennis Doubleday (patch to seperateRowColumns())
25
 */
26
 */
26
public class CellReference {
27
public final class CellReference {
28
    /** The character ($) that signifies a row or column value is absolute instead of relative */ 
29
    private static final char ABSOLUTE_REFERENCE_MARKER = '$';
30
    /** The character (!) that separates sheet names from cell references */ 
31
    private static final char SHEET_NAME_DELIMITER = '!';
32
    /** The character (') used to quote sheet names when they contain special characters */
33
    private static final char SPECIAL_NAME_DELIMITER = '\'';
34
    
27
35
28
    /** Creates new CellReference */
36
    private final int _rowIndex;
29
    private int row;
37
    private final int _colIndex;
30
    private int col;
38
    private final String _sheetName;
31
    private String sheetName;
39
    private final boolean _isRowAbs;
32
    private boolean rowAbs;
40
    private final boolean _isColAbs;
33
    private boolean colAbs;
34
41
42
    /**
43
     * Create an cell ref from a string representation.  Sheet names containing special characters should be
44
     * delimited and escaped as per normal syntax rules for formulas.
45
     */
35
    public CellReference(String cellRef) {
46
    public CellReference(String cellRef) {
36
        String[] parts = separateRefParts(cellRef);
47
        String[] parts = separateRefParts(cellRef);
37
        sheetName = parts[0];
48
        _sheetName = parts[0];
38
        String ref = parts[1]; 
49
        String colRef = parts[1]; 
39
        if ((ref == null)||("".equals(ref)))
50
        if (colRef.length() < 1) {
40
        	throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
51
            throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
41
        if (ref.charAt(0) == '$') {
42
            colAbs=true;
43
            ref=ref.substring(1);
44
        }
52
        }
45
        col = convertColStringToNum(ref);
53
        _isColAbs = colRef.charAt(0) == '$';
46
        ref=parts[2];
54
        if (_isColAbs) {
47
        if ((ref == null)||("".equals(ref)))
55
            colRef=colRef.substring(1);
48
        	throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
49
        if (ref.charAt(0) == '$') {
50
            rowAbs=true;
51
            ref=ref.substring(1);
52
        }
56
        }
53
        row = Integer.parseInt(ref)-1;
57
        _colIndex = convertColStringToNum(colRef);
58
        
59
        String rowRef=parts[2];
60
        if (rowRef.length() < 1) {
61
            throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
62
        }
63
        _isRowAbs = rowRef.charAt(0) == '$';
64
        if (_isRowAbs) {
65
            rowRef=rowRef.substring(1);
66
        }
67
        _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
54
    }
68
    }
55
69
56
    public CellReference(int pRow, int pCol) {
57
        this(pRow,pCol,false,false);
58
    }
59
60
    public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
70
    public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
61
        row=pRow;col=pCol;
71
        this(null, pRow, pCol, pAbsRow, pAbsCol);
62
        rowAbs = pAbsRow;
63
        colAbs=pAbsCol;
64
65
    }
72
    }
66
73
    public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
67
    public int getRow(){return row;}
74
        _sheetName = pSheetName;
68
    public short getCol(){return (short) col;}
75
        _rowIndex=pRow;
69
    public boolean isRowAbsolute(){return rowAbs;}
76
        _colIndex=pCol;
70
    public boolean isColAbsolute(){return colAbs;}
77
        _isRowAbs = pAbsRow;
71
    public String getSheetName(){return sheetName;}
78
        _isColAbs=pAbsCol;
72
    
73
    protected void setSheetName(String sheetName) {
74
    	this.sheetName = sheetName;
75
    }
79
    }
76
80
81
    public int getRow(){return _rowIndex;}
82
    public short getCol(){return (short) _colIndex;}
83
    public boolean isRowAbsolute(){return _isRowAbs;}
84
    public boolean isColAbsolute(){return _isColAbs;}
77
    /**
85
    /**
86
      * @return possibly <code>null</code> if this is a 2D reference.  Special characters are not
87
      * escaped or delimited
88
      */
89
    public String getSheetName(){
90
        return _sheetName;
91
    }
92
    
93
    /**
78
     * takes in a column reference portion of a CellRef and converts it from
94
     * takes in a column reference portion of a CellRef and converts it from
79
     * ALPHA-26 number format to 0-based base 10.
95
     * ALPHA-26 number format to 0-based base 10.
80
     */
96
     */
81
    private int convertColStringToNum(String ref) {
97
    private int convertColStringToNum(String ref) {
82
        int len = ref.length();
98
        int lastIx = ref.length()-1;
83
        int retval=0;
99
        int retval=0;
84
        int pos = 0;
100
        int pos = 0;
85
101
86
        for (int k = ref.length()-1; k > -1; k--) {
102
        for (int k = lastIx; k > -1; k--) {
87
            char thechar = ref.charAt(k);
103
            char thechar = ref.charAt(k);
88
            if ( pos == 0) {
104
            if ( pos == 0) {
89
                retval += (Character.getNumericValue(thechar)-9);
105
                retval += (Character.getNumericValue(thechar)-9);
Lines 97-132 Link Here
97
113
98
114
99
    /**
115
    /**
100
     * Seperates the row from the columns and returns an array.  Element in
116
     * Separates the row from the columns and returns an array of three Strings.  The first element
101
     * position one is the substring containing the columns still in ALPHA-26
117
     * is the sheet name. Only the first element may be null.  The second element in is the column 
102
     * number format.
118
     * name still in ALPHA-26 number format.  The third element is the row.
103
     */
119
     */
104
    private String[] separateRefParts(String reference) {
120
    private static String[] separateRefParts(String reference) {
121
        
122
        int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
123
        String sheetName = parseSheetName(reference, plingPos);
124
        int start = plingPos+1;
105
125
106
        // Look for end of sheet name. This will either set
107
        // start to 0 (if no sheet name present) or the
108
        // index after the sheet reference ends.
109
        String retval[] = new String[3];
110
111
        int start = reference.indexOf("!");
112
        if (start != -1) retval[0] = reference.substring(0, start);
113
        start += 1;
114
115
        int length = reference.length();
126
        int length = reference.length();
116
127
117
128
118
        char[] chars = reference.toCharArray();
119
        int loc = start;
129
        int loc = start;
120
        if (chars[loc]=='$') loc++;
130
        // skip initial dollars 
121
        for (; loc < chars.length; loc++) {
131
        if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
122
            if (Character.isDigit(chars[loc]) || chars[loc] == '$') {
132
            loc++;
133
        }
134
        // step over column name chars until first digit (or dollars) for row number.
135
        for (; loc < length; loc++) {
136
            char ch = reference.charAt(loc);
137
            if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
123
                break;
138
                break;
124
            }
139
            }
125
        }
140
        }
141
        return new String[] {
142
           sheetName,
143
           reference.substring(start,loc),
144
           reference.substring(loc),
145
        };
146
    }
126
147
127
        retval[1] = reference.substring(start,loc);
148
    private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
128
        retval[2] = reference.substring(loc);
149
        if(indexOfSheetNameDelimiter < 0) {
129
        return retval;
150
            return null;
151
        }
152
        
153
        boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
154
        if(!isQuoted) {
155
            return reference.substring(0, indexOfSheetNameDelimiter);
156
        }
157
        int lastQuotePos = indexOfSheetNameDelimiter-1;
158
        if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
159
            throw new RuntimeException("Mismatched quotes: (" + reference + ")");
160
        }
161
162
        // TODO - refactor cell reference parsing logic to one place.
163
        // Current known incarnations: 
164
        //   FormulaParser.GetName()
165
        //   CellReference.parseSheetName() (here)
166
        //   AreaReference.separateAreaRefs() 
167
        //   SheetNameFormatter.format() (inverse)
168
        
169
        StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
170
        
171
        for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
172
            char ch = reference.charAt(i);
173
            if(ch != SPECIAL_NAME_DELIMITER) {
174
                sb.append(ch);
175
                continue;
176
            }
177
            if(i < lastQuotePos) {
178
                if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
179
                    // two consecutive quotes is the escape sequence for a single one
180
                    i++; // skip this and keep parsing the special name
181
                    sb.append(ch);
182
                    continue;
183
                }
184
            }
185
            throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
186
        }
187
        return sb.toString();
130
    }
188
    }
131
189
132
    /**
190
    /**
Lines 148-161 Link Here
148
        return retval;
206
        return retval;
149
    }
207
    }
150
208
151
209
    /**
210
     *  Example return values:
211
     *    <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
212
     *      <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
213
     *      <tr><td>A1</td><td>Cell reference without sheet</td></tr>
214
     *      <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
215
     *      <tr><td>'O''Brien''s Sales'!A1'&nbsp;</td><td>Sheet name with special characters</td></tr>
216
     *    </table>
217
     * @return the text representation of this cell reference as it would appear in a formula.
218
     */
219
    public String formatAsString() {
220
        StringBuffer sb = new StringBuffer(32);
221
        if(_sheetName != null) {
222
            SheetNameFormatter.appendFormat(sb, _sheetName);
223
            sb.append(SHEET_NAME_DELIMITER);
224
        }
225
        appendCellReference(sb);
226
        return sb.toString();
227
    }
228
    
152
    public String toString() {
229
    public String toString() {
153
        StringBuffer retval = new StringBuffer();
230
        StringBuffer sb = new StringBuffer(64);
154
        retval.append( (colAbs)?"$":"");
231
        sb.append(getClass().getName()).append(" [");
155
        retval.append( convertNumToColString(col));
232
        sb.append(formatAsString());
156
        retval.append((rowAbs)?"$":"");
233
        sb.append("]");
157
        retval.append(row+1);
234
        return sb.toString();
235
    }
158
236
159
    return retval.toString();
237
    /**
238
     * Appends cell reference with '$' markers for absolute values as required.
239
     * Sheet name is not included.
240
     */
241
    /* package */ void appendCellReference(StringBuffer sb) {
242
        if(_isColAbs) {
243
            sb.append(ABSOLUTE_REFERENCE_MARKER);
244
        }
245
        sb.append( convertNumToColString(_colIndex));
246
        if(_isRowAbs) {
247
            sb.append(ABSOLUTE_REFERENCE_MARKER);
248
        }
249
        sb.append(_rowIndex+1);
160
    }
250
    }
161
}
251
}
(-)src/java/org/apache/poi/hssf/record/NameRecord.java (-1 / +1 lines)
Lines 726-732 Link Here
726
        	for(int i=0; i<refs.length; i++) {
726
        	for(int i=0; i<refs.length; i++) {
727
	            ptg = new Area3DPtg();
727
	            ptg = new Area3DPtg();
728
	            ((Area3DPtg) ptg).setExternSheetIndex(externSheetIndex);
728
	            ((Area3DPtg) ptg).setExternSheetIndex(externSheetIndex);
729
	            ((Area3DPtg) ptg).setArea(refs[i].toString());
729
	            ((Area3DPtg) ptg).setArea(refs[i].formatAsString());
730
	            field_13_name_definition.push(ptg);
730
	            field_13_name_definition.push(ptg);
731
	            this.setDefinitionTextLength( (short)(getDefinitionLength() + ptg.getSize()) );
731
	            this.setDefinitionTextLength( (short)(getDefinitionLength() + ptg.getSize()) );
732
        	}
732
        	}
(-)src/java/org/apache/poi/hssf/record/formula/AreaPtg.java (-10 / +12 lines)
Lines 53-66 Link Here
53
   
53
   
54
    public AreaPtg(String arearef) {
54
    public AreaPtg(String arearef) {
55
        AreaReference ar = new AreaReference(arearef);
55
        AreaReference ar = new AreaReference(arearef);
56
        setFirstRow((short)ar.getCells()[0].getRow());
56
        CellReference firstCell = ar.getFirstCell();
57
        setFirstColumn((short)ar.getCells()[0].getCol());
57
        CellReference lastCell = ar.getLastCell();
58
        setLastRow((short)ar.getCells()[1].getRow());
58
        setFirstRow((short)firstCell.getRow());
59
        setLastColumn((short)ar.getCells()[1].getCol());
59
        setFirstColumn(firstCell.getCol());
60
        setFirstColRelative(!ar.getCells()[0].isColAbsolute());
60
        setLastRow((short)lastCell.getRow());
61
        setLastColRelative(!ar.getCells()[1].isColAbsolute());
61
        setLastColumn(lastCell.getCol());
62
        setFirstRowRelative(!ar.getCells()[0].isRowAbsolute());
62
        setFirstColRelative(!firstCell.isColAbsolute());
63
        setLastRowRelative(!ar.getCells()[1].isRowAbsolute());        
63
        setLastColRelative(!lastCell.isColAbsolute());
64
        setFirstRowRelative(!firstCell.isRowAbsolute());
65
        setLastRowRelative(!lastCell.isRowAbsolute());        
64
    }
66
    }
65
    
67
    
66
    public AreaPtg(short firstRow, short lastRow, short firstColumn, short lastColumn, boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative) {
68
    public AreaPtg(short firstRow, short lastRow, short firstColumn, short lastColumn, boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative) {
Lines 282-289 Link Here
282
284
283
    public String toFormulaString(Workbook book)
285
    public String toFormulaString(Workbook book)
284
    {
286
    {
285
         return (new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).toString() + ":" +
287
         return (new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).formatAsString() + ":" +
286
                (new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).toString();
288
                (new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).formatAsString();
287
    }
289
    }
288
290
289
    public byte getDefaultOperandClass() {
291
    public byte getDefaultOperandClass() {
(-)src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java (-1 / +1 lines)
Lines 26-32 Link Here
26
 * 
26
 * 
27
 * @author Josh Micich
27
 * @author Josh Micich
28
 */
28
 */
29
final class SheetNameFormatter {
29
public final class SheetNameFormatter {
30
	
30
	
31
	private static final String BIFF8_LAST_COLUMN = "IV";
31
	private static final String BIFF8_LAST_COLUMN = "IV";
32
	private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
32
	private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
(-)src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java (-1 / +1 lines)
Lines 191-197 Link Here
191
    public String toFormulaString(Workbook book)
191
    public String toFormulaString(Workbook book)
192
    {
192
    {
193
        //TODO -- should we store a cellreference instance in this ptg?? but .. memory is an issue, i believe!
193
        //TODO -- should we store a cellreference instance in this ptg?? but .. memory is an issue, i believe!
194
        return (new CellReference(getRowAsInt(),getColumn(),!isRowRelative(),!isColRelative())).toString();
194
        return (new CellReference(getRowAsInt(),getColumn(),!isRowRelative(),!isColRelative())).formatAsString();
195
    }
195
    }
196
    
196
    
197
    public byte getDefaultOperandClass() {
197
    public byte getDefaultOperandClass() {
(-)src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java (-13 / +9 lines)
Lines 243-263 Link Here
243
	public void setArea( String ref )
243
	public void setArea( String ref )
244
	{
244
	{
245
		AreaReference ar = new AreaReference( ref );
245
		AreaReference ar = new AreaReference( ref );
246
		CellReference[] crs = ar.getCells();
247
		
246
		
248
		CellReference firstCell = crs[0];
247
		CellReference frstCell = ar.getFirstCell();
249
		CellReference lastCell = firstCell;
248
		CellReference lastCell = ar.getLastCell();
250
		if(crs.length > 1) {
251
			lastCell = crs[1];
252
		}
253
249
254
		setFirstRow(    (short) firstCell.getRow() );
250
		setFirstRow(    (short) frstCell.getRow() );
255
		setFirstColumn( (short) firstCell.getCol() );
251
		setFirstColumn(         frstCell.getCol() );
256
		setLastRow(     (short) lastCell.getRow() );
252
		setLastRow(     (short) lastCell.getRow() );
257
		setLastColumn(  (short) lastCell.getCol() );
253
		setLastColumn(          lastCell.getCol() );
258
		setFirstColRelative( !firstCell.isColAbsolute() );
254
		setFirstColRelative( !frstCell.isColAbsolute() );
259
		setLastColRelative(  !lastCell.isColAbsolute() );
255
		setLastColRelative(  !lastCell.isColAbsolute() );
260
		setFirstRowRelative( !firstCell.isRowAbsolute() );
256
		setFirstRowRelative( !frstCell.isRowAbsolute() );
261
		setLastRowRelative(  !lastCell.isRowAbsolute() );
257
		setLastRowRelative(  !lastCell.isRowAbsolute() );
262
	}
258
	}
263
259
Lines 273-281 Link Here
273
			SheetNameFormatter.appendFormat(retval, sheetName);
269
			SheetNameFormatter.appendFormat(retval, sheetName);
274
			retval.append( '!' );
270
			retval.append( '!' );
275
		}
271
		}
276
		retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).toString() );
272
		retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).formatAsString() );
277
		retval.append( ':' );
273
		retval.append( ':' );
278
		retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).toString() );
274
		retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).formatAsString() );
279
		return retval.toString();
275
		return retval.toString();
280
	}
276
	}
281
277
(-)src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java (-1 / +1 lines)
Lines 183-189 Link Here
183
            SheetNameFormatter.appendFormat(retval, sheetName);
183
            SheetNameFormatter.appendFormat(retval, sheetName);
184
            retval.append( '!' );
184
            retval.append( '!' );
185
        }
185
        }
186
        retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).toString()); 
186
        retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).formatAsString()); 
187
        return retval.toString();
187
        return retval.toString();
188
    }
188
    }
189
189
(-)src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java (-22 / +23 lines)
Lines 21-34 Link Here
21
import java.util.Iterator;
21
import java.util.Iterator;
22
import java.util.List;
22
import java.util.List;
23
23
24
import junit.framework.TestCase;
25
24
import org.apache.poi.hssf.record.FormulaRecord;
26
import org.apache.poi.hssf.record.FormulaRecord;
25
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
27
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
26
import org.apache.poi.hssf.record.formula.ExpPtg;
28
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
27
import org.apache.poi.hssf.util.CellReference;
29
import org.apache.poi.hssf.util.CellReference;
28
30
29
import junit.framework.TestCase;
31
public final class TestBug42464 extends TestCase {
30
31
public class TestBug42464 extends TestCase {
32
	String dirname;
32
	String dirname;
33
33
34
	protected void setUp() throws Exception {
34
	protected void setUp() throws Exception {
Lines 68-93 Link Here
68
		Iterator it = row.cellIterator();
68
		Iterator it = row.cellIterator();
69
		while(it.hasNext()) {
69
		while(it.hasNext()) {
70
			HSSFCell cell = (HSSFCell)it.next();
70
			HSSFCell cell = (HSSFCell)it.next();
71
			if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
71
			if(cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
72
				FormulaRecordAggregate record = (FormulaRecordAggregate)
72
			    continue;
73
					cell.getCellValueRecord();
73
			}
74
				FormulaRecord r = record.getFormulaRecord();
74
			FormulaRecordAggregate record = (FormulaRecordAggregate) cell.getCellValueRecord();
75
				List ptgs = r.getParsedExpression();
75
			FormulaRecord r = record.getFormulaRecord();
76
				
76
			List ptgs = r.getParsedExpression();
77
				String cellRef = (new CellReference(row.getRowNum(), cell.getCellNum())).toString();
77
			
78
				if(cellRef.equals("BP24")) {
78
			String cellRef = new CellReference(row.getRowNum(), cell.getCellNum(), false, false).formatAsString();
79
					System.out.print(cellRef);
79
			if(false && cellRef.equals("BP24")) { // TODO - replace System.out.println()s with asserts
80
					System.out.println(" - has " + r.getNumberOfExpressionTokens() + " ptgs over " + r.getExpressionLength()  + " tokens:");
80
				System.out.print(cellRef);
81
					for(int i=0; i<ptgs.size(); i++) {
81
				System.out.println(" - has " + r.getNumberOfExpressionTokens() 
82
						String c = ptgs.get(i).getClass().toString();
82
				        + " ptgs over " + r.getExpressionLength()  + " tokens:");
83
						System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
83
				for(int i=0; i<ptgs.size(); i++) {
84
					}
84
					String c = ptgs.get(i).getClass().toString();
85
					System.out.println("-> " + cell.getCellFormula());
85
					System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
86
				}
86
				}
87
				
87
				System.out.println("-> " + cell.getCellFormula());
88
				eval.evaluate(cell);
89
				
90
			}
88
			}
89
			
90
			CellValue evalResult = eval.evaluate(cell);
91
			assertNotNull(evalResult);
91
		}
92
		}
92
	}
93
	}
93
}
94
}
(-)src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java (-12 / +10 lines)
Lines 578-595 Link Here
578
        
578
        
579
        // retrieve the cell at the named range and test its contents
579
        // retrieve the cell at the named range and test its contents
580
        AreaReference aref = new AreaReference(aNamedCell.getReference());
580
        AreaReference aref = new AreaReference(aNamedCell.getReference());
581
        CellReference[] crefs = aref.getCells();
581
        assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell());
582
        assertNotNull(crefs);
582
        
583
        assertEquals("Should be exactly 1 cell in the named cell :'" +cellName+"'", 1, crefs.length);
583
        CellReference cref = aref.getFirstCell();
584
        for (int i=0, iSize=crefs.length; i<iSize; i++) {
584
        assertNotNull(cref);
585
            CellReference cref = crefs[i];
585
        HSSFSheet s = wb.getSheet(cref.getSheetName());
586
            assertNotNull(cref);
586
        assertNotNull(s);
587
            HSSFSheet s = wb.getSheet(cref.getSheetName());
587
        HSSFRow r = sheet.getRow(cref.getRow());
588
            HSSFRow r = sheet.getRow(cref.getRow());
588
        HSSFCell c = r.getCell(cref.getCol());
589
            HSSFCell c = r.getCell(cref.getCol());
589
        String contents = c.getRichStringCellValue().getString();
590
            String contents = c.getStringCellValue();
590
        assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
591
            assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
592
        }
593
    }
591
    }
594
592
595
    /**
593
    /**
(-)src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java (-8 / +8 lines)
Lines 302-311 Link Here
302
                }
302
                }
303
                
303
                
304
                c = r.getCell((short) y);
304
                c = r.getCell((short) y);
305
                CellReference cr= new CellReference(refx1,refy1);
305
                CellReference cr= new CellReference(refx1,refy1, false, false);
306
                ref=cr.toString();
306
                ref=cr.formatAsString();
307
                cr=new CellReference(refx2,refy2);
307
                cr=new CellReference(refx2,refy2, false, false);
308
                ref2=cr.toString();
308
                ref2=cr.formatAsString();
309
309
310
                c = r.createCell((short) y);
310
                c = r.createCell((short) y);
311
                c.setCellFormula("" + ref + operator + ref2);
311
                c.setCellFormula("" + ref + operator + ref2);
Lines 379-388 Link Here
379
                }
379
                }
380
380
381
                c = r.getCell((short) y);
381
                c = r.getCell((short) y);
382
                CellReference cr= new CellReference(refx1,refy1);
382
                CellReference cr= new CellReference(refx1, refy1, false, false);
383
                ref=cr.toString();
383
                ref=cr.formatAsString();
384
                cr=new CellReference(refx2,refy2);
384
                cr=new CellReference(refx2,refy2, false, false);
385
                ref2=cr.toString();
385
                ref2=cr.formatAsString();
386
                
386
                
387
                
387
                
388
                assertTrue("loop Formula is as expected "+ref+operator+ref2+"!="+c.getCellFormula(),(
388
                assertTrue("loop Formula is as expected "+ref+operator+ref2+"!="+c.getCellFormula(),(
(-)src/testcases/org/apache/poi/hssf/HSSFTests.java (-10 / +2 lines)
Lines 100-110 Link Here
100
import org.apache.poi.hssf.usermodel.TestSanityChecker;
100
import org.apache.poi.hssf.usermodel.TestSanityChecker;
101
import org.apache.poi.hssf.usermodel.TestSheetShiftRows;
101
import org.apache.poi.hssf.usermodel.TestSheetShiftRows;
102
import org.apache.poi.hssf.usermodel.TestWorkbook;
102
import org.apache.poi.hssf.usermodel.TestWorkbook;
103
import org.apache.poi.hssf.util.TestAreaReference;
103
import org.apache.poi.hssf.util.AllHSSFUtilTests;
104
import org.apache.poi.hssf.util.TestCellReference;
105
import org.apache.poi.hssf.util.TestRKUtil;
106
import org.apache.poi.hssf.util.TestRangeAddress;
107
import org.apache.poi.hssf.util.TestSheetReferences;
108
104
109
/**
105
/**
110
 * Test Suite for running just HSSF tests.  Mostly
106
 * Test Suite for running just HSSF tests.  Mostly
Lines 202-212 Link Here
202
        suite.addTest(new TestSuite(TestUnitsRecord.class));
198
        suite.addTest(new TestSuite(TestUnitsRecord.class));
203
        suite.addTest(new TestSuite(TestValueRangeRecord.class));
199
        suite.addTest(new TestSuite(TestValueRangeRecord.class));
204
        suite.addTest(new TestSuite(TestRowRecordsAggregate.class));
200
        suite.addTest(new TestSuite(TestRowRecordsAggregate.class));
205
        suite.addTest(new TestSuite(TestAreaReference.class));
201
        suite.addTest(AllHSSFUtilTests.suite());
206
        suite.addTest(new TestSuite(TestCellReference.class));
207
		  suite.addTest(new TestSuite(TestRangeAddress.class));		
208
        suite.addTest(new TestSuite(TestRKUtil.class));
209
        suite.addTest(new TestSuite(TestSheetReferences.class));
210
        
202
        
211
        
203
        
212
        suite.addTest(AllFormulaTests.suite());
204
        suite.addTest(AllFormulaTests.suite());
(-)src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (-99 / +90 lines)
Lines 1-4 Link Here
1
2
/* ====================================================================
1
/* ====================================================================
3
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
4
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
Lines 29-57 Link Here
29
import org.apache.poi.hssf.record.formula.UnionPtg;
28
import org.apache.poi.hssf.record.formula.UnionPtg;
30
29
31
import java.io.FileInputStream;
30
import java.io.FileInputStream;
31
import java.io.IOException;
32
import java.io.InputStream;
32
import java.io.InputStream;
33
import java.util.List;
33
import java.util.List;
34
34
35
public class TestAreaReference extends TestCase {
35
public final class TestAreaReference extends TestCase {
36
     public TestAreaReference(String s) {
36
37
        super(s);
38
    }
39
    public void testAreaRef1() {
37
    public void testAreaRef1() {
40
        AreaReference ar = new AreaReference("$A$1:$B$2");
38
        AreaReference ar = new AreaReference("$A$1:$B$2");
41
        assertTrue("Two cells expected",ar.getCells().length == 2);
39
        assertFalse("Two cells expected", ar.isSingleCell());
42
        CellReference cf = ar.getCells()[0];
40
        CellReference cf = ar.getFirstCell();
43
        assertTrue("row is 4",cf.getRow()==0);
41
        assertTrue("row is 4",cf.getRow()==0);
44
        assertTrue("col is 1",cf.getCol()==0);
42
        assertTrue("col is 1",cf.getCol()==0);
45
        assertTrue("row is abs",cf.isRowAbsolute());
43
        assertTrue("row is abs",cf.isRowAbsolute());
46
        assertTrue("col is abs",cf.isColAbsolute());
44
        assertTrue("col is abs",cf.isColAbsolute());
47
        assertTrue("string is $A$1",cf.toString().equals("$A$1"));
45
        assertTrue("string is $A$1",cf.formatAsString().equals("$A$1"));
48
        
46
        
49
        cf = ar.getCells()[1];
47
        cf = ar.getLastCell();
50
        assertTrue("row is 4",cf.getRow()==1);
48
        assertTrue("row is 4",cf.getRow()==1);
51
        assertTrue("col is 1",cf.getCol()==1);
49
        assertTrue("col is 1",cf.getCol()==1);
52
        assertTrue("row is abs",cf.isRowAbsolute());
50
        assertTrue("row is abs",cf.isRowAbsolute());
53
        assertTrue("col is abs",cf.isColAbsolute());
51
        assertTrue("col is abs",cf.isColAbsolute());
54
        assertTrue("string is $B$2",cf.toString().equals("$B$2"));
52
        assertTrue("string is $B$2",cf.formatAsString().equals("$B$2"));
55
        
53
        
56
        CellReference[] refs = ar.getAllReferencedCells();
54
        CellReference[] refs = ar.getAllReferencedCells();
57
        assertEquals(4, refs.length);
55
        assertEquals(4, refs.length);
Lines 78-139 Link Here
78
     * Reported by Arne.Clauss@gedas.de
76
     * Reported by Arne.Clauss@gedas.de
79
     */
77
     */
80
    public void testReferenceWithSheet() {
78
    public void testReferenceWithSheet() {
81
    	String ref = "Tabelle1!B5";
79
        AreaReference ar;
82
		AreaReference myAreaReference = new AreaReference(ref);
80
        
83
		CellReference[] myCellReference = myAreaReference.getCells();
81
        ar = new AreaReference("Tabelle1!B5");
84
82
        assertTrue(ar.isSingleCell());
85
		assertEquals(1, myCellReference.length);
83
        TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, false, false, "Tabelle1!B5");
86
		assertNotNull("cell reference not null : "+myCellReference[0]);
84
        
87
    	assertEquals("Not Column B", (short)1,myCellReference[0].getCol());
85
        assertEquals(1, ar.getAllReferencedCells().length);
88
		assertEquals("Not Row 5", 4,myCellReference[0].getRow());
86
        
89
		assertEquals("Shouldn't be absolute", false, myCellReference[0].isRowAbsolute());
87
        
90
		assertEquals("Shouldn't be absolute", false, myCellReference[0].isColAbsolute());
88
        ar = new AreaReference("Tabelle1!$B$5:$B$7");
91
		
89
        assertFalse(ar.isSingleCell());
92
		assertEquals(1, myAreaReference.getAllReferencedCells().length);
90
        
93
		
91
        TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
94
		
92
        TestCellReference.confirmCell(ar.getLastCell(), "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
95
		ref = "Tabelle1!$B$5:$B$7";
93
        
96
		myAreaReference = new AreaReference(ref);
94
        // And all that make it up
97
		myCellReference = myAreaReference.getCells();
95
        CellReference[] allCells = ar.getAllReferencedCells();
98
		assertEquals(2, myCellReference.length);
96
        assertEquals(3, allCells.length);
99
		
97
        TestCellReference.confirmCell(allCells[0], "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
100
		assertEquals("Tabelle1", myCellReference[0].getSheetName());
98
        TestCellReference.confirmCell(allCells[1], "Tabelle1", 5, 1, true, true, "Tabelle1!$B$6");
101
		assertEquals(4, myCellReference[0].getRow());
99
        TestCellReference.confirmCell(allCells[2], "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
102
		assertEquals(1, myCellReference[0].getCol());
103
		assertTrue(myCellReference[0].isRowAbsolute());
104
		assertTrue(myCellReference[0].isColAbsolute());
105
		
106
		assertEquals("Tabelle1", myCellReference[1].getSheetName());
107
		assertEquals(6, myCellReference[1].getRow());
108
		assertEquals(1, myCellReference[1].getCol());
109
		assertTrue(myCellReference[1].isRowAbsolute());
110
		assertTrue(myCellReference[1].isColAbsolute());
111
		
112
		// And all that make it up
113
		myCellReference = myAreaReference.getAllReferencedCells();
114
		assertEquals(3, myCellReference.length);
115
		
116
		assertEquals("Tabelle1", myCellReference[0].getSheetName());
117
		assertEquals(4, myCellReference[0].getRow());
118
		assertEquals(1, myCellReference[0].getCol());
119
		assertTrue(myCellReference[0].isRowAbsolute());
120
		assertTrue(myCellReference[0].isColAbsolute());
121
		
122
		assertEquals("Tabelle1", myCellReference[1].getSheetName());
123
		assertEquals(5, myCellReference[1].getRow());
124
		assertEquals(1, myCellReference[1].getCol());
125
		assertTrue(myCellReference[1].isRowAbsolute());
126
		assertTrue(myCellReference[1].isColAbsolute());
127
		
128
		assertEquals("Tabelle1", myCellReference[2].getSheetName());
129
		assertEquals(6, myCellReference[2].getRow());
130
		assertEquals(1, myCellReference[2].getCol());
131
		assertTrue(myCellReference[2].isRowAbsolute());
132
		assertTrue(myCellReference[2].isColAbsolute());
133
    }
100
    }
134
101
135
    private static class HSSFWB extends HSSFWorkbook {
102
    private static class HSSFWB extends HSSFWorkbook {
136
        private HSSFWB(InputStream in) throws Exception {
103
        public HSSFWB(InputStream in) throws IOException {
137
            super(in);
104
            super(in);
138
        }
105
        }
139
        public Workbook getWorkbook() {
106
        public Workbook getWorkbook() {
Lines 176-217 Link Here
176
143
177
        refs = AreaReference.generateContiguous(refSimple);
144
        refs = AreaReference.generateContiguous(refSimple);
178
        assertEquals(1, refs.length);
145
        assertEquals(1, refs.length);
179
        assertEquals(1, refs[0].getDim());
146
        assertTrue(refs[0].isSingleCell());
180
        assertEquals("$C$10", refs[0].toString());
147
        assertEquals("$C$10", refs[0].formatAsString());
181
148
182
        refs = AreaReference.generateContiguous(ref2D);
149
        refs = AreaReference.generateContiguous(ref2D);
183
        assertEquals(1, refs.length);
150
        assertEquals(1, refs.length);
184
        assertEquals(2, refs[0].getDim());
151
        assertFalse(refs[0].isSingleCell());
185
        assertEquals("$C$10:$D$11", refs[0].toString());
152
        assertEquals("$C$10:$D$11", refs[0].formatAsString());
186
153
187
        refs = AreaReference.generateContiguous(refDCSimple);
154
        refs = AreaReference.generateContiguous(refDCSimple);
188
        assertEquals(3, refs.length);
155
        assertEquals(3, refs.length);
189
        assertEquals(1, refs[0].getDim());
156
        assertTrue(refs[0].isSingleCell());
190
        assertEquals(1, refs[1].getDim());
157
        assertTrue(refs[1].isSingleCell());
191
        assertEquals(1, refs[2].getDim());
158
        assertTrue(refs[2].isSingleCell());
192
        assertEquals("$C$10", refs[0].toString());
159
        assertEquals("$C$10", refs[0].formatAsString());
193
        assertEquals("$D$12", refs[1].toString());
160
        assertEquals("$D$12", refs[1].formatAsString());
194
        assertEquals("$E$14", refs[2].toString());
161
        assertEquals("$E$14", refs[2].formatAsString());
195
162
196
        refs = AreaReference.generateContiguous(refDC2D);
163
        refs = AreaReference.generateContiguous(refDC2D);
197
        assertEquals(3, refs.length);
164
        assertEquals(3, refs.length);
198
        assertEquals(2, refs[0].getDim());
165
        assertFalse(refs[0].isSingleCell());
199
        assertEquals(1, refs[1].getDim());
166
        assertTrue(refs[1].isSingleCell());
200
        assertEquals(2, refs[2].getDim());
167
        assertFalse(refs[2].isSingleCell());
201
        assertEquals("$C$10:$C$11", refs[0].toString());
168
        assertEquals("$C$10:$C$11", refs[0].formatAsString());
202
        assertEquals("$D$12", refs[1].toString());
169
        assertEquals("$D$12", refs[1].formatAsString());
203
        assertEquals("$E$14:$E$20", refs[2].toString());
170
        assertEquals("$E$14:$E$20", refs[2].formatAsString());
204
171
205
        refs = AreaReference.generateContiguous(refDC3D);
172
        refs = AreaReference.generateContiguous(refDC3D);
206
        assertEquals(2, refs.length);
173
        assertEquals(2, refs.length);
207
        assertEquals(2, refs[0].getDim());
174
        assertFalse(refs[0].isSingleCell());
208
        assertEquals(2, refs[1].getDim());
175
        assertFalse(refs[0].isSingleCell());
209
        assertEquals("$C$10:$C$14", refs[0].toString());
176
        assertEquals("Tabelle1!$C$10:$C$14", refs[0].formatAsString());
210
        assertEquals("$D$10:$D$12", refs[1].toString());
177
        assertEquals("Tabelle1!$D$10:$D$12", refs[1].formatAsString());
211
        assertEquals("Tabelle1", refs[0].getCells()[0].getSheetName());
178
        assertEquals("Tabelle1", refs[0].getFirstCell().getSheetName());
212
        assertEquals("Tabelle1", refs[0].getCells()[1].getSheetName());
179
        assertEquals("Tabelle1", refs[0].getLastCell().getSheetName());
213
        assertEquals("Tabelle1", refs[1].getCells()[0].getSheetName());
180
        assertEquals("Tabelle1", refs[1].getFirstCell().getSheetName());
214
        assertEquals("Tabelle1", refs[1].getCells()[1].getSheetName());
181
        assertEquals("Tabelle1", refs[1].getLastCell().getSheetName());
215
    }
182
    }
216
183
217
    public void testDiscontinousReference() throws Exception {
184
    public void testDiscontinousReference() throws Exception {
Lines 261-282 Link Here
261
        assertFalse(AreaReference.isContiguous(aNamedCell.getReference()));
228
        assertFalse(AreaReference.isContiguous(aNamedCell.getReference()));
262
        AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
229
        AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
263
        assertEquals(2, arefs.length);
230
        assertEquals(2, arefs.length);
264
        assertEquals(rawRefA, arefs[0].toString());
231
        assertEquals(refA, arefs[0].formatAsString());
265
        assertEquals(rawRefB, arefs[1].toString());
232
        assertEquals(refB, arefs[1].formatAsString());
266
233
267
        for(int i=0; i<arefs.length; i++) {
234
        for(int i=0; i<arefs.length; i++) {
268
            CellReference[] crefs = arefs[i].getCells();
235
            AreaReference ar = arefs[i];
269
            for (int j=0; j<crefs.length; j++) {
236
            confirmResolveCellRef(wb, ar.getFirstCell());
270
                // Check it turns into real stuff
237
            confirmResolveCellRef(wb, ar.getLastCell());
271
                HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
272
                HSSFRow r = s.getRow(crefs[j].getRow());
273
                HSSFCell c = r.getCell(crefs[j].getCol());
274
            }
275
        }
238
        }
276
    }
239
    }
240
241
    private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
242
        HSSFSheet s = wb.getSheet(cref.getSheetName());
243
        HSSFRow r = s.getRow(cref.getRow());
244
        HSSFCell c = r.getCell(cref.getCol());
245
        assertNotNull(c);
246
    }
277
    
247
    
278
    public static void main(java.lang.String[] args) {
248
    public void testSpecialSheetNames() {
279
		junit.textui.TestRunner.run(TestAreaReference.class);
249
        AreaReference ar;
280
	}
250
        ar = new AreaReference("'Sheet A'!A1");
251
        confirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
281
        
252
        
253
        ar = new AreaReference("'Hey! Look Here!'!A1");
254
        confirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
255
        
256
        ar = new AreaReference("'O''Toole'!A1:B2");
257
        confirmAreaSheetName(ar, "O'Toole", "'O''Toole'!A1:B2");
258
        
259
        ar = new AreaReference("'one:many'!A1:B2");
260
        confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
261
    }
262
263
    private static void confirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText) {
264
        CellReference[] cells = ar.getAllReferencedCells();
265
        assertEquals(sheetName, cells[0].getSheetName());
266
        assertEquals(expectedFullText, ar.formatAsString());
267
    }
268
    
269
    public static void main(String[] args) {
270
        junit.textui.TestRunner.run(TestAreaReference.class);
271
    }
272
        
282
}
273
}
(-)src/testcases/org/apache/poi/hssf/util/TestCellReference.java (-46 / +32 lines)
Lines 1-4 Link Here
1
2
/* ====================================================================
1
/* ====================================================================
3
   Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
4
   contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
Lines 22-108 Link Here
22
import junit.framework.TestCase;
21
import junit.framework.TestCase;
23
22
24
23
25
public class TestCellReference extends TestCase {
24
public final class TestCellReference extends TestCase {
26
    public TestCellReference(String s) {
27
        super(s);
28
    }
29
    
25
    
30
    public void testAbsRef1(){
26
    public void testAbsRef1(){
31
        CellReference cf = new CellReference("$B$5");
27
        CellReference cf = new CellReference("$B$5");
32
        assertTrue("row is 4",cf.getRow()==4);
28
        confirmCell(cf, null, 4, 1, true, true, "$B$5");
33
        assertTrue("col is 1",cf.getCol()==1);
34
        assertTrue("row is abs",cf.isRowAbsolute());
35
        assertTrue("col is abs",cf.isColAbsolute());
36
        assertTrue("string is $B$5",cf.toString().equals("$B$5"));
37
    }
29
    }
38
    
30
    
39
    public void  testAbsRef2(){
31
    public void  testAbsRef2(){
40
        CellReference cf = new CellReference(4,1,true,true);
32
        CellReference cf = new CellReference(4,1,true,true);
41
        assertTrue("row is 4",cf.getRow()==4);
33
        confirmCell(cf, null, 4, 1, true, true, "$B$5");
42
        assertTrue("col is 1",cf.getCol()==1);
43
        assertTrue("row is abs",cf.isRowAbsolute());
44
        assertTrue("col is abs",cf.isColAbsolute());
45
        assertTrue("string is $B$5",cf.toString().equals("$B$5"));
46
    }
34
    }
47
35
48
    public void  testAbsRef3(){
36
    public void  testAbsRef3(){
49
        CellReference cf = new CellReference("B$5");
37
        CellReference cf = new CellReference("B$5");
50
        assertTrue("row is 4",cf.getRow()==4);
38
        confirmCell(cf, null, 4, 1, true, false, "B$5");
51
        assertTrue("col is 1",cf.getCol()==1);
52
        assertTrue("row is abs",cf.isRowAbsolute());
53
        assertTrue("col is rel",!cf.isColAbsolute());
54
        assertTrue("string is B$5",cf.toString().equals("B$5"));
55
    }
39
    }
56
    
40
    
57
    public void  testAbsRef4(){
41
    public void  testAbsRef4(){
58
        CellReference cf = new CellReference(4,1,true,false);
42
        CellReference cf = new CellReference(4,1,true,false);
59
        assertTrue("row is 4",cf.getRow()==4);
43
        confirmCell(cf, null, 4, 1, true, false, "B$5");
60
        assertTrue("col is 1",cf.getCol()==1);
61
        assertTrue("row is abs",cf.isRowAbsolute());
62
        assertTrue("col is rel",!cf.isColAbsolute());
63
        assertTrue("string is B$5",cf.toString().equals("B$5"));
64
    }
44
    }
65
    
45
    
66
    public void  testAbsRef5(){
46
    public void  testAbsRef5(){
67
        CellReference cf = new CellReference("$B5");
47
        CellReference cf = new CellReference("$B5");
68
        assertTrue("row is 4",cf.getRow()==4);
48
        confirmCell(cf, null, 4, 1, false, true, "$B5");
69
        assertTrue("col is 1",cf.getCol()==1);
70
        assertTrue("row is abs",!cf.isRowAbsolute());
71
        assertTrue("col is rel",cf.isColAbsolute());
72
        assertTrue("string is B$5",cf.toString().equals("$B5"));
73
    }
49
    }
74
    
50
    
75
    public void  testAbsRef6(){
51
    public void  testAbsRef6(){
76
        CellReference cf = new CellReference(4,1,false,true);
52
        CellReference cf = new CellReference(4,1,false,true);
77
        assertTrue("row is 4",cf.getRow()==4);
53
        confirmCell(cf, null, 4, 1, false, true, "$B5");
78
        assertTrue("col is 1",cf.getCol()==1);
79
        assertTrue("row is abs",!cf.isRowAbsolute());
80
        assertTrue("col is rel",cf.isColAbsolute());
81
        assertTrue("string is B$5",cf.toString().equals("$B5"));
82
    }
54
    }
83
55
84
    public void  testAbsRef7(){
56
    public void  testAbsRef7(){
85
        CellReference cf = new CellReference("B5");
57
        CellReference cf = new CellReference("B5");
86
        assertTrue("row is 4",cf.getRow()==4);
58
        confirmCell(cf, null, 4, 1, false, false, "B5");
87
        assertTrue("col is 1",cf.getCol()==1);
88
        assertTrue("row is abs",!cf.isRowAbsolute());
89
        assertTrue("col is rel",!cf.isColAbsolute());
90
        assertTrue("string is B$5",cf.toString().equals("B5"));
91
    }
59
    }
92
    
60
    
93
    public void  testAbsRef8(){
61
    public void  testAbsRef8(){
94
        CellReference cf = new CellReference(4,1,false,false);
62
        CellReference cf = new CellReference(4,1,false,false);
95
        assertTrue("row is 4",cf.getRow()==4);
63
        confirmCell(cf, null, 4, 1, false, false, "B5");
96
        assertTrue("col is 1",cf.getCol()==1);
97
        assertTrue("row is abs",!cf.isRowAbsolute());
98
        assertTrue("col is rel",!cf.isColAbsolute());
99
        assertTrue("string is B$5",cf.toString().equals("B5"));
100
    }
64
    }
65
    
66
    public void testSpecialSheetNames() {
67
        CellReference cf;
68
        cf = new CellReference("'profit + loss'!A1");
69
        confirmCell(cf, "profit + loss", 0, 0, false, false, "'profit + loss'!A1");
70
        
71
        cf = new CellReference("'O''Brien''s Sales'!A1");
72
        confirmCell(cf, "O'Brien's Sales", 0, 0, false, false, "'O''Brien''s Sales'!A1");
73
        
74
        cf = new CellReference("'Amazing!'!A1");
75
        confirmCell(cf, "Amazing!", 0, 0, false, false, "'Amazing!'!A1");
76
    }
101
77
102
    
78
    
79
    /* package */ static void confirmCell(CellReference cf, String expSheetName, int expRow, 
80
            int expCol, boolean expIsRowAbs, boolean expIsColAbs, String expText) {
81
        
82
        assertEquals(expSheetName, cf.getSheetName());
83
        assertEquals("row index is wrong", expRow, cf.getRow());
84
        assertEquals("col index is wrong", expCol, cf.getCol());
85
        assertEquals("isRowAbsolute is wrong", expIsRowAbs, cf.isRowAbsolute());
86
        assertEquals("isColAbsolute is wrong", expIsColAbs, cf.isColAbsolute());
87
        assertEquals("text is wrong", expText, cf.formatAsString());
88
    }
89
103
    public static void main(String [] args) {
90
    public static void main(String [] args) {
104
        System.out.println("Testing org.apache.poi.hssf.util.TestCellReference");
91
        System.out.println("Testing org.apache.poi.hssf.util.TestCellReference");
105
        junit.textui.TestRunner.run(TestCellReference.class);
92
        junit.textui.TestRunner.run(TestCellReference.class);
106
    }
93
    }
107
    
108
}
94
}

Return to bug 44417