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

(-)src/java/org/apache/poi/ss/util/AreaReference.java (-9 / +20 lines)
Lines 115-125 Link Here
115
115
116
    /**
116
    /**
117
     * Creates an area ref from a pair of Cell References.
117
     * Creates an area ref from a pair of Cell References.
118
     * @deprecated since October 2015. Use {@link #AreaReference(CellReference, CellReference, SpreadsheetVersion)} instead.
118
     */
119
     */
120
    @Deprecated
119
    public AreaReference(CellReference topLeft, CellReference botRight) {
121
    public AreaReference(CellReference topLeft, CellReference botRight) {
120
        _version = DEFAULT_SPREADSHEET_VERSION;
122
        this(topLeft, botRight, DEFAULT_SPREADSHEET_VERSION);
121
        boolean swapRows = topLeft.getRow() > botRight.getRow();
123
    }
122
        boolean swapCols = topLeft.getCol() > botRight.getCol();
124
    
125
    /**
126
     * Creates an area ref from a pair of Cell References
127
     */
128
    public AreaReference(CellReference topLeft, CellReference botRight, SpreadsheetVersion version) {
129
        _version = (null != version) ? version : DEFAULT_SPREADSHEET_VERSION;
130
        final boolean swapRows = topLeft.getRow() > botRight.getRow();
131
        final boolean swapCols = topLeft.getCol() > botRight.getCol();
123
        if (swapRows || swapCols) {
132
        if (swapRows || swapCols) {
124
            int firstRow; 
133
            int firstRow; 
125
            int lastRow; 
134
            int lastRow; 
Lines 169-175 Link Here
169
     */
178
     */
170
    public static boolean isContiguous(String reference) {
179
    public static boolean isContiguous(String reference) {
171
       // If there's a sheet name, strip it off
180
       // If there's a sheet name, strip it off
172
       int sheetRefEnd = reference.indexOf('!'); 
181
       int sheetRefEnd = reference.indexOf(SHEET_NAME_DELIMITER); 
173
       if(sheetRefEnd != -1) {
182
       if(sheetRefEnd != -1) {
174
          reference = reference.substring(sheetRefEnd);
183
          reference = reference.substring(sheetRefEnd);
175
       }
184
       }
Lines 185-191 Link Here
185
        if (null == version) {
194
        if (null == version) {
186
            version = DEFAULT_SPREADSHEET_VERSION;
195
            version = DEFAULT_SPREADSHEET_VERSION;
187
        }
196
        }
188
        return new AreaReference("$A" + start + ":$" + version.getLastColumnName() + end, version);
197
        return new AreaReference("$A" + start + CELL_DELIMITER + "$" + version.getLastColumnName() + end, version);
189
    }
198
    }
190
199
191
    public static AreaReference getWholeColumn(SpreadsheetVersion version, String start, String end) {
200
    public static AreaReference getWholeColumn(SpreadsheetVersion version, String start, String end) {
Lines 192-198 Link Here
192
        if (null == version) {
201
        if (null == version) {
193
            version = DEFAULT_SPREADSHEET_VERSION;
202
            version = DEFAULT_SPREADSHEET_VERSION;
194
        }
203
        }
195
        return new AreaReference(start + "$1:" + end + "$" + version.getMaxRows(), version);
204
        return new AreaReference(start + "$1" + CELL_DELIMITER + end + "$" + version.getMaxRows(), version);
196
    }
205
    }
197
206
198
    /**
207
    /**
Lines 271-279 Link Here
271
        int maxRow = Math.max(_firstCell.getRow(), _lastCell.getRow());
280
        int maxRow = Math.max(_firstCell.getRow(), _lastCell.getRow());
272
        int minCol = Math.min(_firstCell.getCol(), _lastCell.getCol());
281
        int minCol = Math.min(_firstCell.getCol(), _lastCell.getCol());
273
        int maxCol = Math.max(_firstCell.getCol(), _lastCell.getCol());
282
        int maxCol = Math.max(_firstCell.getCol(), _lastCell.getCol());
283
        final int size = (maxRow-minRow+1) * (maxCol-minCol+1);
274
        String sheetName = _firstCell.getSheetName();
284
        String sheetName = _firstCell.getSheetName();
275
        
285
        
276
        List<CellReference> refs = new ArrayList<CellReference>();
286
        List<CellReference> refs = new ArrayList<CellReference>(size);
277
        for(int row=minRow; row<=maxRow; row++) {
287
        for(int row=minRow; row<=maxRow; row++) {
278
            for(int col=minCol; col<=maxCol; col++) {
288
            for(int col=minCol; col<=maxCol; col++) {
279
                CellReference ref = new CellReference(sheetName, row, col, _firstCell.isRowAbsolute(), _firstCell.isColAbsolute());
289
                CellReference ref = new CellReference(sheetName, row, col, _firstCell.isRowAbsolute(), _firstCell.isColAbsolute());
Lines 280-286 Link Here
280
                refs.add(ref);
290
                refs.add(ref);
281
            }
291
            }
282
        }
292
        }
283
        return refs.toArray(new CellReference[refs.size()]);
293
        assert size == refs.size();
294
        return refs.toArray(new CellReference[size]);
284
    }
295
    }
285
296
286
    /**
297
    /**
Lines 301-307 Link Here
301
        if(isWholeColumnReference()) {
312
        if(isWholeColumnReference()) {
302
            return
313
            return
303
                CellReference.convertNumToColString(_firstCell.getCol())
314
                CellReference.convertNumToColString(_firstCell.getCol())
304
                + ":" +
315
                + CELL_DELIMITER +
305
                CellReference.convertNumToColString(_lastCell.getCol());
316
                CellReference.convertNumToColString(_lastCell.getCol());
306
        }
317
        }
307
        
318
        
(-)src/testcases/org/apache/poi/ss/util/TestAreaReference.java (-14 / +166 lines)
Lines 16-22 Link Here
16
==================================================================== */
16
==================================================================== */
17
package org.apache.poi.ss.util;
17
package org.apache.poi.ss.util;
18
18
19
import org.apache.poi.ss.SpreadsheetVersion;
19
import static org.apache.poi.ss.SpreadsheetVersion.EXCEL97;
20
import static org.apache.poi.ss.SpreadsheetVersion.EXCEL2007;
20
21
21
import junit.framework.TestCase;
22
import junit.framework.TestCase;
22
23
Lines 26-65 Link Here
26
 * @author David North
27
 * @author David North
27
 */
28
 */
28
public class TestAreaReference extends TestCase {
29
public class TestAreaReference extends TestCase {
30
31
    public void testConstructors() {
32
        final CellReference topLeft = new CellReference("A1");
33
        final CellReference bottomRight = new CellReference("B23");
34
        assertEquals("A1:B23", new AreaReference(topLeft, bottomRight, EXCEL97).formatAsString());
35
        assertEquals("A1:B23", new AreaReference("A1:B23", EXCEL97).formatAsString());
36
        assertEquals("A1:B23", new AreaReference("Sheet1!A1:B23", EXCEL97).formatAsString()); //Sheet name is not stored in AreaReference object
37
        assertEquals("A1:B23", new AreaReference("Sheet1!A1:Sheet1!B23", EXCEL97).formatAsString()); //Same sheet names
38
        assertEquals("A1:B23", new AreaReference("Sheet1!A1:'Sheet1'!B23", EXCEL97).formatAsString()); //non-required single quotes don't change the name of the sheet
39
40
        // Check for NPE's        
41
        AreaReference ref;
42
        try {
43
            final String reference = null;
44
            ref = new AreaReference(reference, EXCEL97);
45
            fail("reference string may not be null");
46
        } catch (final NullPointerException e) { } //expected
47
        try {
48
            ref = new AreaReference(topLeft, null, EXCEL97);
49
            fail("topLeft and bottomRight CellReferences may not be null");
50
        } catch (final NullPointerException e) { } //expected
51
        try {
52
            ref = new AreaReference(null, bottomRight, EXCEL97);
53
            fail("topLeft and bottomRight CellReferences may not be null");
54
        } catch (final NullPointerException e) { } //expected
55
        try {
56
            ref = new AreaReference(null, null, EXCEL97);
57
            fail("topLeft and bottomRight CellReferences may not be null");
58
        } catch (final NullPointerException e) { } //expected
59
60
        // Check for illegal arguments passed to constructor
61
        try {
62
            ref = new AreaReference("Sheet1!A1:Sheet2!B23", EXCEL97);
63
            fail("reference cannot span multiple sheets");
64
        } catch (final RuntimeException e) { } //expected
65
        try {
66
            // This could be valid if the reference is stored somewhere in Sheet1
67
            // This is too complicated for POI, so POI should throw an error
68
            ref = new AreaReference("A1:Sheet1!B23", EXCEL97);
69
            fail("mixed implicit and explict sheet in area reference is not allowed");
70
        } catch (final RuntimeException e) { } //expected
71
        try {
72
            ref = new AreaReference("A1:B23,C8:D9", EXCEL97);
73
            fail("AreaReferences instances cannot hold discontiguous regions.");
74
        } catch (final IllegalArgumentException e) { } //expected
75
        try {
76
            ref = new AreaReference("Sheet1:'A1'", EXCEL97);
77
            fail("single-quotes cannot exist around cell reference part");
78
        } catch (final RuntimeException e) { } //expected
79
        try {
80
            ref = new AreaReference("'Sheet1:A1'", EXCEL97);
81
            fail("partially-quoted sheet name");
82
        } catch (final RuntimeException e) { } //expected
83
        try {
84
            ref = new AreaReference("'Sheet1:A1'!A1:B23", EXCEL97);
85
            fail("illegal ':' character in sheet name");
86
        } catch (final RuntimeException e) { } //expected
87
    }
88
89
    public void testIsContiguous() {
90
        assertTrue("Contiguous area reference", AreaReference.isContiguous("A1:B23"));
91
        assertTrue("3D reference with non-quoted sheet name", AreaReference.isContiguous("Sheet1!A1:B23"));
92
        assertTrue("3D reference with quoted sheet name", AreaReference.isContiguous("'Sheet 1'!A1:B23"));
93
94
        // What's the correct behavior here?
95
        assertFalse("Discontiguous reference that could be simplified as one continuous region",
96
                AreaReference.isContiguous("A1:B23,C1:C23"));
97
98
        assertFalse("Discontiguous area reference", AreaReference.isContiguous("A1:B23,C8:D9"));
99
        assertFalse("Discontiguous 3D reference with non-quoted sheet name",
100
                AreaReference.isContiguous("'Sheet1'!C8:D9"));
101
        assertFalse("Discontiguous 3D reference with quoted sheet name",
102
                AreaReference.isContiguous("'Sheet 1'!A1:B23,C8:D9"));
103
    }
104
105
    public void testIsWholeColumnReference() {
106
        // whole column = first to last row in workbook
107
        assertTrue(new AreaReference("A1:B65536", EXCEL97).isWholeColumnReference());
108
        assertFalse(new AreaReference("A1:B65536", EXCEL2007).isWholeColumnReference());
109
        assertTrue(new AreaReference("A1:B1048576", EXCEL2007).isWholeColumnReference());
110
111
        // alternative ways to express whole column
112
        assertTrue("use dollar signs", new AreaReference("C$1:C$65535", EXCEL97).isWholeColumnReference());
113
        assertTrue("use row zero", new AreaReference("D$1:F$0", EXCEL97).isWholeColumnReference());
114
115
        // number-only cell references
116
        assertFalse("whole row", new AreaReference("1:2", EXCEL97).isWholeColumnReference());
117
        assertFalse("whole row", new AreaReference("3:3", EXCEL97).isWholeColumnReference());
118
        assertFalse("whole row", new AreaReference("$3:$3", EXCEL97).isWholeColumnReference());
119
120
        // letter-only cell references
121
        assertTrue(new AreaReference("A:B", EXCEL97).isWholeColumnReference());
122
        assertTrue(new AreaReference("C:C", EXCEL97).isWholeColumnReference());
123
        assertTrue(new AreaReference("$C:$C", EXCEL97).isWholeColumnReference());
124
125
        // entire worksheet
126
        assertTrue(new AreaReference("A:IW", EXCEL97).isWholeColumnReference());
127
        assertTrue(new AreaReference("A:XFD", EXCEL2007).isWholeColumnReference());
128
        assertTrue(new AreaReference("1:65536", EXCEL97).isWholeColumnReference());
129
        assertTrue(new AreaReference("1:1048576", EXCEL2007).isWholeColumnReference());
130
    }
131
132
    public void testGetFirstCell() {
133
        // TopLeft-BottomRight order
134
        assertEquals(new CellReference("A1"), new AreaReference("A1:B23").getFirstCell());
135
        assertEquals(new CellReference("A$1"), new AreaReference("A$1:B23").getFirstCell());
136
        assertEquals(new CellReference("$A$1"), new AreaReference("$A$1:B23").getFirstCell());
137
138
        //Constructor manipulates initial reference to determine top-left cell
139
        assertEquals(new CellReference("A1"), new AreaReference("B23:A1").getFirstCell());
140
        assertEquals(new CellReference("A1"), new AreaReference("B1:A23").getFirstCell());
141
        assertEquals(new CellReference("A$1"), new AreaReference("$B$1:A23").getFirstCell());
142
        assertEquals(new CellReference("A1"), new AreaReference("A23:B1").getFirstCell());
143
    }
144
145
    public void testGetLastCell() {
146
        // TopLeft-BottomRight order
147
        assertEquals(new CellReference("B23"), new AreaReference("A1:B23").getLastCell());
148
        assertEquals(new CellReference("B$23"), new AreaReference("A1:B$23").getLastCell());
149
        assertEquals(new CellReference("$B$23"), new AreaReference("A1:$B$23").getLastCell());
150
151
        //Constructor manipulates initial reference to determine top-left cell
152
        assertEquals(new CellReference("B23"), new AreaReference("B23:A1").getLastCell());
153
        assertEquals(new CellReference("B23"), new AreaReference("B1:A23").getLastCell());
154
        assertEquals(new CellReference("B$23"), new AreaReference("B1:$A$23").getLastCell());
155
        assertEquals(new CellReference("B23"), new AreaReference("A23:B1").getLastCell());
156
    }
157
158
    public void testIsSingleCell() {
159
        assertTrue(new AreaReference("A1", EXCEL97).isSingleCell());
160
        assertTrue(new AreaReference("A$1", EXCEL97).isSingleCell());
161
        assertTrue(new AreaReference("'Sheet 1'!A1", EXCEL97).isSingleCell());
162
163
        assertFalse(new AreaReference("A1:A1", EXCEL97).isSingleCell());
164
        assertFalse(new AreaReference("A1:A$1", EXCEL97).isSingleCell());
165
        assertFalse(new AreaReference("A1:B23", EXCEL97).isSingleCell());
166
        assertFalse(new AreaReference("'Sheet 1'!A1:B23", EXCEL97).isSingleCell());
167
168
        assertFalse(new AreaReference(new CellReference("A1"), new CellReference("A1"), EXCEL97).isSingleCell());
169
    }
29
    
170
    
30
    public void testWholeColumn() {
171
    public void testGetWholeColumn() {
31
        AreaReference oldStyle = AreaReference.getWholeColumn(SpreadsheetVersion.EXCEL97, "A", "B");
172
        AreaReference oldStyle = AreaReference.getWholeColumn(EXCEL97, "A", "B");
32
        assertEquals(0, oldStyle.getFirstCell().getCol());
173
        assertEquals(0, oldStyle.getFirstCell().getCol());
33
        assertEquals(0, oldStyle.getFirstCell().getRow());
174
        assertEquals(0, oldStyle.getFirstCell().getRow());
34
        assertEquals(1, oldStyle.getLastCell().getCol());
175
        assertEquals(1, oldStyle.getLastCell().getCol());
35
        assertEquals(SpreadsheetVersion.EXCEL97.getLastRowIndex(), oldStyle.getLastCell().getRow());
176
        assertEquals(EXCEL97.getLastRowIndex(), oldStyle.getLastCell().getRow());
36
        assertTrue(oldStyle.isWholeColumnReference());
177
        assertTrue(oldStyle.isWholeColumnReference());
37
178
38
        AreaReference oldStyleNonWholeColumn = new AreaReference("A1:B23", SpreadsheetVersion.EXCEL97);
179
        AreaReference oldStyleNonWholeColumn = new AreaReference("A1:B23", EXCEL97);
39
        assertFalse(oldStyleNonWholeColumn.isWholeColumnReference());
180
        assertFalse(oldStyleNonWholeColumn.isWholeColumnReference());
40
181
41
        AreaReference newStyle = AreaReference.getWholeColumn(SpreadsheetVersion.EXCEL2007, "A", "B");
182
        AreaReference newStyle = AreaReference.getWholeColumn(EXCEL2007, "A", "B");
42
        assertEquals(0, newStyle.getFirstCell().getCol());
183
        assertEquals(0, newStyle.getFirstCell().getCol());
43
        assertEquals(0, newStyle.getFirstCell().getRow());
184
        assertEquals(0, newStyle.getFirstCell().getRow());
44
        assertEquals(1, newStyle.getLastCell().getCol());
185
        assertEquals(1, newStyle.getLastCell().getCol());
45
        assertEquals(SpreadsheetVersion.EXCEL2007.getLastRowIndex(), newStyle.getLastCell().getRow());
186
        assertEquals(EXCEL2007.getLastRowIndex(), newStyle.getLastCell().getRow());
46
        assertTrue(newStyle.isWholeColumnReference());
187
        assertTrue(newStyle.isWholeColumnReference());
47
188
48
        AreaReference newStyleNonWholeColumn = new AreaReference("A1:B23", SpreadsheetVersion.EXCEL2007);
189
        AreaReference newStyleNonWholeColumn = new AreaReference("A1:B23", EXCEL2007);
49
        assertFalse(newStyleNonWholeColumn.isWholeColumnReference());
190
        assertFalse(newStyleNonWholeColumn.isWholeColumnReference());
50
    }
191
    }
51
    
192
    
52
    public void testWholeRow() {
193
    public void testGetWholeRow() {
53
        AreaReference oldStyle = AreaReference.getWholeRow(SpreadsheetVersion.EXCEL97, "1", "2");
194
        AreaReference oldStyle = AreaReference.getWholeRow(EXCEL97, "1", "2");
54
        assertEquals(0, oldStyle.getFirstCell().getCol());
195
        assertEquals(0, oldStyle.getFirstCell().getCol());
55
        assertEquals(0, oldStyle.getFirstCell().getRow());
196
        assertEquals(0, oldStyle.getFirstCell().getRow());
56
        assertEquals(SpreadsheetVersion.EXCEL97.getLastColumnIndex(), oldStyle.getLastCell().getCol());
197
        assertEquals(EXCEL97.getLastColumnIndex(), oldStyle.getLastCell().getCol());
57
        assertEquals(1, oldStyle.getLastCell().getRow());
198
        assertEquals(1, oldStyle.getLastCell().getRow());
58
        
199
        
59
        AreaReference newStyle = AreaReference.getWholeRow(SpreadsheetVersion.EXCEL2007, "1", "2");
200
        AreaReference newStyle = AreaReference.getWholeRow(EXCEL2007, "1", "2");
60
        assertEquals(0, newStyle.getFirstCell().getCol());
201
        assertEquals(0, newStyle.getFirstCell().getCol());
61
        assertEquals(0, newStyle.getFirstCell().getRow());
202
        assertEquals(0, newStyle.getFirstCell().getRow());
62
        assertEquals(SpreadsheetVersion.EXCEL2007.getLastColumnIndex(), newStyle.getLastCell().getCol());
203
        assertEquals(EXCEL2007.getLastColumnIndex(), newStyle.getLastCell().getCol());
63
        assertEquals(1, newStyle.getLastCell().getRow());
204
        assertEquals(1, newStyle.getLastCell().getRow());
64
    }
205
    }
65
206
Lines 66-71 Link Here
66
    @SuppressWarnings("deprecation") // deliberate test for behaviour if deprecated constructor used.
207
    @SuppressWarnings("deprecation") // deliberate test for behaviour if deprecated constructor used.
67
    public void testFallbackToExcel97IfVersionNotSupplied() {
208
    public void testFallbackToExcel97IfVersionNotSupplied() {
68
        assertTrue(new AreaReference("A:B").isWholeColumnReference());
209
        assertTrue(new AreaReference("A:B").isWholeColumnReference());
69
        assertTrue(AreaReference.isWholeColumnReference(null, new CellReference("A$1"), new CellReference("A$" + SpreadsheetVersion.EXCEL97.getMaxRows())));
210
        assertTrue(AreaReference.isWholeColumnReference(null, new CellReference("A$1"), new CellReference("A$" + EXCEL97.getMaxRows())));
70
    }
211
    }
212
213
    public void testGetAllReferencedCells() {
214
        final CellReference[] expectedRefCells = new CellReference[]{
215
            new CellReference("A1"), new CellReference("B1"),
216
            new CellReference("A2"), new CellReference("B2"),
217
            new CellReference("A3"), new CellReference("B3")};
218
        // Function returns cell addresses (no absolute/relativeness) represented as row-relative column-relative CellReference objects
219
        final CellReference[] actualRefCells = new AreaReference("$A$1:$B3").getAllReferencedCells();
220
        assertEquals(6, actualRefCells.length);
221
        assertEquals(expectedRefCells, actualRefCells);
222
    }
71
}
223
}

Return to bug 58402