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