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

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

Return to bug 58402