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

(-)a/src/java/org/apache/poi/ss/util/AreaReference.java (-13 / +32 lines)
Lines 22-27 import java.util.List; Link Here
22
import java.util.StringTokenizer;
22
import java.util.StringTokenizer;
23
23
24
import org.apache.poi.ss.SpreadsheetVersion;
24
import org.apache.poi.ss.SpreadsheetVersion;
25
import org.apache.poi.util.StringUtil;
25
26
26
public class AreaReference {
27
public class AreaReference {
27
28
Lines 166-179 public class AreaReference { Link Here
166
     *  {@link #generateContiguous(SpreadsheetVersion, String)})
167
     *  {@link #generateContiguous(SpreadsheetVersion, String)})
167
     */
168
     */
168
    public static boolean isContiguous(String reference) {
169
    public static boolean isContiguous(String reference) {
169
       // If there's a sheet name, strip it off
170
        return splitAreaReferences(reference).length == 1;
170
       int sheetRefEnd = reference.indexOf('!'); 
171
       if(sheetRefEnd != -1) {
172
          reference = reference.substring(sheetRefEnd);
173
       }
174
175
       // Check for the , as a sign of non-coniguous
176
       return !reference.contains(",");
177
    }
171
    }
178
172
179
    public static AreaReference getWholeRow(SpreadsheetVersion version, String start, String end) {
173
    public static AreaReference getWholeRow(SpreadsheetVersion version, String start, String end) {
Lines 220-230 public class AreaReference { Link Here
220
            version = DEFAULT_SPREADSHEET_VERSION; // how the code used to behave. 
214
            version = DEFAULT_SPREADSHEET_VERSION; // how the code used to behave. 
221
        }
215
        }
222
        List<AreaReference> refs = new ArrayList<>();
216
        List<AreaReference> refs = new ArrayList<>();
223
        StringTokenizer st = new StringTokenizer(reference, ",");
217
        String[] splitReferences = splitAreaReferences(reference);
224
        while(st.hasMoreTokens()) {
218
        for (String ref : splitReferences) {
225
            refs.add(
219
            refs.add(new AreaReference(ref, version));
226
                    new AreaReference(st.nextToken(), version)
227
            );
228
        }
220
        }
229
        return refs.toArray(new AreaReference[0]);
221
        return refs.toArray(new AreaReference[0]);
230
    }
222
    }
Lines 405-408 public class AreaReference { Link Here
405
        
397
        
406
        return new String [] { partA, sheetName + partB, };
398
        return new String [] { partA, sheetName + partB, };
407
    }
399
    }
400
401
    /**
402
     * Splits a comma-separated area references string into an array of
403
     * individual references
404
     * @param reference Area references, i.e. A1:B2, 'Sheet1'!A1:B2
405
     * @return Area references in an array, size >= 1
406
     */
407
    private static String[] splitAreaReferences(String reference) {
408
        List<String> results = new ArrayList<>();
409
        String currentSegment = "";
410
        StringTokenizer st = new StringTokenizer(reference, ",");
411
        while(st.hasMoreTokens()) {
412
            if (currentSegment.length() > 0) {
413
                currentSegment += ",";
414
            }
415
            currentSegment += st.nextToken();
416
            int numSingleQuotes = StringUtil.countMatches(currentSegment, '\'');
417
            if (numSingleQuotes == 0 || numSingleQuotes == 2) {
418
                results.add(currentSegment);
419
                currentSegment = "";
420
            }
421
        }
422
        if (currentSegment.length() > 0) {
423
            results.add(currentSegment);
424
        }
425
        return results.toArray(new String[0]);
426
    }
408
}
427
}
(-)a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (-24 / +44 lines)
Lines 48-62 public final class TestAreaReference extends TestCase { Link Here
48
        CellReference cf = ar.getFirstCell();
48
        CellReference cf = ar.getFirstCell();
49
        assertEquals("row is 4", 0, cf.getRow());
49
        assertEquals("row is 4", 0, cf.getRow());
50
        assertEquals("col is 1", 0, cf.getCol());
50
        assertEquals("col is 1", 0, cf.getCol());
51
        assertTrue("row is abs",cf.isRowAbsolute());
51
        assertTrue("row is abs", cf.isRowAbsolute());
52
        assertTrue("col is abs",cf.isColAbsolute());
52
        assertTrue("col is abs", cf.isColAbsolute());
53
        assertEquals("string is $A$1", "$A$1", cf.formatAsString());
53
        assertEquals("string is $A$1", "$A$1", cf.formatAsString());
54
54
55
        cf = ar.getLastCell();
55
        cf = ar.getLastCell();
56
        assertEquals("row is 4", 1, cf.getRow());
56
        assertEquals("row is 4", 1, cf.getRow());
57
        assertEquals("col is 1", 1, cf.getCol());
57
        assertEquals("col is 1", 1, cf.getCol());
58
        assertTrue("row is abs",cf.isRowAbsolute());
58
        assertTrue("row is abs", cf.isRowAbsolute());
59
        assertTrue("col is abs",cf.isColAbsolute());
59
        assertTrue("col is abs", cf.isColAbsolute());
60
        assertEquals("string is $B$2", "$B$2", cf.formatAsString());
60
        assertEquals("string is $B$2", "$B$2", cf.formatAsString());
61
61
62
        CellReference[] refs = ar.getAllReferencedCells();
62
        CellReference[] refs = ar.getAllReferencedCells();
Lines 80-87 public final class TestAreaReference extends TestCase { Link Here
80
    }
80
    }
81
81
82
    /**
82
    /**
83
     * References failed when sheet names were being used
83
     * References failed when sheet names were being used Reported by
84
     * Reported by Arne.Clauss@gedas.de
84
     * Arne.Clauss@gedas.de
85
     */
85
     */
86
    public void testReferenceWithSheet() {
86
    public void testReferenceWithSheet() {
87
        AreaReference ar;
87
        AreaReference ar;
Lines 92-98 public final class TestAreaReference extends TestCase { Link Here
92
92
93
        assertEquals(1, ar.getAllReferencedCells().length);
93
        assertEquals(1, ar.getAllReferencedCells().length);
94
94
95
96
        ar = new AreaReference("Tabelle1!$B$5:$B$7", SpreadsheetVersion.EXCEL97);
95
        ar = new AreaReference("Tabelle1!$B$5:$B$7", SpreadsheetVersion.EXCEL97);
97
        assertFalse(ar.isSingleCell());
96
        assertFalse(ar.isSingleCell());
98
97
Lines 113-118 public final class TestAreaReference extends TestCase { Link Here
113
        String refDCSimple = "$C$10:$C$10,$D$12:$D$12,$E$14:$E$14";
112
        String refDCSimple = "$C$10:$C$10,$D$12:$D$12,$E$14:$E$14";
114
        String refDC2D = "$C$10:$C$11,$D$12:$D$12,$E$14:$E$20";
113
        String refDC2D = "$C$10:$C$11,$D$12:$D$12,$E$14:$E$20";
115
        String refDC3D = "Tabelle1!$C$10:$C$14,Tabelle1!$D$10:$D$12";
114
        String refDC3D = "Tabelle1!$C$10:$C$14,Tabelle1!$D$10:$D$12";
115
        String refComma = "'A,Sheet'!$A$1:$A$1,'A,Sheet'!$A$4:$A$5";
116
        String refCommaExp = "'!Sheet,Comma!'!$A$1:$B$1";
116
117
117
        // Check that we detect as contiguous properly
118
        // Check that we detect as contiguous properly
118
        assertTrue(AreaReference.isContiguous(refSimple));
119
        assertTrue(AreaReference.isContiguous(refSimple));
Lines 120-125 public final class TestAreaReference extends TestCase { Link Here
120
        assertFalse(AreaReference.isContiguous(refDCSimple));
121
        assertFalse(AreaReference.isContiguous(refDCSimple));
121
        assertFalse(AreaReference.isContiguous(refDC2D));
122
        assertFalse(AreaReference.isContiguous(refDC2D));
122
        assertFalse(AreaReference.isContiguous(refDC3D));
123
        assertFalse(AreaReference.isContiguous(refDC3D));
124
        assertFalse(AreaReference.isContiguous(refComma));
125
        assertTrue(AreaReference.isContiguous(refCommaExp));
123
126
124
        // Check we can only create contiguous entries
127
        // Check we can only create contiguous entries
125
        new AreaReference(refSimple, SpreadsheetVersion.EXCEL97);
128
        new AreaReference(refSimple, SpreadsheetVersion.EXCEL97);
Lines 127-146 public final class TestAreaReference extends TestCase { Link Here
127
        try {
130
        try {
128
            new AreaReference(refDCSimple, SpreadsheetVersion.EXCEL97);
131
            new AreaReference(refDCSimple, SpreadsheetVersion.EXCEL97);
129
            fail("expected IllegalArgumentException");
132
            fail("expected IllegalArgumentException");
130
        } catch(IllegalArgumentException e) {
133
        } catch (IllegalArgumentException e) {
131
        	// expected during successful test
134
            // expected during successful test
132
        }
135
        }
133
        try {
136
        try {
134
            new AreaReference(refDC2D, SpreadsheetVersion.EXCEL97);
137
            new AreaReference(refDC2D, SpreadsheetVersion.EXCEL97);
135
            fail("expected IllegalArgumentException");
138
            fail("expected IllegalArgumentException");
136
        } catch(IllegalArgumentException e) {
139
        } catch (IllegalArgumentException e) {
137
        	// expected during successful test
140
            // expected during successful test
138
        }
141
        }
139
        try {
142
        try {
140
            new AreaReference(refDC3D, SpreadsheetVersion.EXCEL97);
143
            new AreaReference(refDC3D, SpreadsheetVersion.EXCEL97);
141
            fail("expected IllegalArgumentException");
144
            fail("expected IllegalArgumentException");
142
        } catch(IllegalArgumentException e) {
145
        } catch (IllegalArgumentException e) {
143
        	// expected during successful test
146
            // expected during successful test
144
        }
147
        }
145
148
146
        // Test that we split as expected
149
        // Test that we split as expected
Lines 184-189 public final class TestAreaReference extends TestCase { Link Here
184
        assertEquals("Tabelle1", refs[0].getLastCell().getSheetName());
187
        assertEquals("Tabelle1", refs[0].getLastCell().getSheetName());
185
        assertEquals("Tabelle1", refs[1].getFirstCell().getSheetName());
188
        assertEquals("Tabelle1", refs[1].getFirstCell().getSheetName());
186
        assertEquals("Tabelle1", refs[1].getLastCell().getSheetName());
189
        assertEquals("Tabelle1", refs[1].getLastCell().getSheetName());
190
191
        refs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL97, refComma);
192
        assertEquals(2, refs.length);
193
        System.out.println(refs[0].formatAsString());
194
        assertTrue(refs[0].isSingleCell());
195
        assertEquals("'A,Sheet'!$A$1", refs[0].formatAsString());
196
        assertEquals("A,Sheet", refs[0].getLastCell().getSheetName());
197
        assertEquals("'A,Sheet'!$A$4:$A$5", refs[1].formatAsString());
198
        assertEquals("A,Sheet", refs[1].getLastCell().getSheetName());
199
200
        refs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL97, refCommaExp);
201
        assertEquals(1, refs.length);
202
        assertFalse(refs[0].isSingleCell());
203
        assertEquals("'!Sheet,Comma!'!$A$1:$B$1", refs[0].formatAsString());
204
        assertEquals("!Sheet,Comma!", refs[0].getLastCell().getSheetName());
187
    }
205
    }
188
206
189
    public void testDiscontinousReference() throws Exception {
207
    public void testDiscontinousReference() throws Exception {
Lines 205-217 public final class TestAreaReference extends TestCase { Link Here
205
        assertNotNull(nr);
223
        assertNotNull(nr);
206
        assertEquals("test", nr.getNameText());
224
        assertEquals("test", nr.getNameText());
207
225
208
        Ptg[] def =nr.getNameDefinition();
226
        Ptg[] def = nr.getNameDefinition();
209
        assertEquals(4, def.length);
227
        assertEquals(4, def.length);
210
228
211
        MemFuncPtg ptgA = (MemFuncPtg)def[0];
229
        MemFuncPtg ptgA = (MemFuncPtg) def[0];
212
        Area3DPtg ptgB = (Area3DPtg)def[1];
230
        Area3DPtg ptgB = (Area3DPtg) def[1];
213
        Area3DPtg ptgC = (Area3DPtg)def[2];
231
        Area3DPtg ptgC = (Area3DPtg) def[2];
214
        UnionPtg ptgD = (UnionPtg)def[3];
232
        UnionPtg ptgD = (UnionPtg) def[3];
215
        assertEquals("", ptgA.toFormulaString());
233
        assertEquals("", ptgA.toFormulaString());
216
        assertEquals(refA, ptgB.toFormulaString(eb));
234
        assertEquals(refA, ptgB.toFormulaString(eb));
217
        assertEquals(refB, ptgC.toFormulaString(eb));
235
        assertEquals(refB, ptgC.toFormulaString(eb));
Lines 245-251 public final class TestAreaReference extends TestCase { Link Here
245
    private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
263
    private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
246
        HSSFSheet s = wb.getSheet(cref.getSheetName());
264
        HSSFSheet s = wb.getSheet(cref.getSheetName());
247
        HSSFRow r = s.getRow(cref.getRow());
265
        HSSFRow r = s.getRow(cref.getRow());
248
        HSSFCell c = r.getCell((int)cref.getCol());
266
        HSSFCell c = r.getCell((int) cref.getCol());
249
        assertNotNull(c);
267
        assertNotNull(c);
250
    }
268
    }
251
269
Lines 262-267 public final class TestAreaReference extends TestCase { Link Here
262
280
263
        ar = new AreaReference("'one:many'!A1:B2", SpreadsheetVersion.EXCEL97);
281
        ar = new AreaReference("'one:many'!A1:B2", SpreadsheetVersion.EXCEL97);
264
        confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
282
        confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
283
284
        ar = new AreaReference("'O,Comma'!A1:B1", SpreadsheetVersion.EXCEL97);
285
        confirmAreaSheetName(ar, "O,Comma", "'O,Comma'!A1:B1");
265
    }
286
    }
266
287
267
    private static void confirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText) {
288
    private static void confirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText) {
Lines 275-289 public final class TestAreaReference extends TestCase { Link Here
275
        confirmWholeColumnRef("$C:D", 2, 3, true, false);
296
        confirmWholeColumnRef("$C:D", 2, 3, true, false);
276
        confirmWholeColumnRef("AD:$AE", 29, 30, false, true);
297
        confirmWholeColumnRef("AD:$AE", 29, 30, false, true);
277
    }
298
    }
278
    
299
279
    private static void confirmWholeColumnRef(String ref, int firstCol, int lastCol, boolean firstIsAbs, boolean lastIsAbs) {
300
    private static void confirmWholeColumnRef(String ref, int firstCol, int lastCol, boolean firstIsAbs,
301
            boolean lastIsAbs) {
280
        AreaReference ar = new AreaReference(ref, SpreadsheetVersion.EXCEL97);
302
        AreaReference ar = new AreaReference(ref, SpreadsheetVersion.EXCEL97);
281
        confirmCell(ar.getFirstCell(), 0, firstCol, true, firstIsAbs);
303
        confirmCell(ar.getFirstCell(), 0, firstCol, true, firstIsAbs);
282
        confirmCell(ar.getLastCell(), 0xFFFF, lastCol, true, lastIsAbs);
304
        confirmCell(ar.getLastCell(), 0xFFFF, lastCol, true, lastIsAbs);
283
    }
305
    }
284
306
285
    private static void confirmCell(CellReference cell, int row, int col, boolean isRowAbs,
307
    private static void confirmCell(CellReference cell, int row, int col, boolean isRowAbs, boolean isColAbs) {
286
            boolean isColAbs) {
287
        assertEquals(row, cell.getRow());
308
        assertEquals(row, cell.getRow());
288
        assertEquals(col, cell.getCol());
309
        assertEquals(col, cell.getCol());
289
        assertEquals(isRowAbs, cell.isRowAbsolute());
310
        assertEquals(isRowAbs, cell.isRowAbsolute());
290
- 

Return to bug 63998