Attachment #36947 for bug #63998

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




import java.util.StringTokenizer;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.util.StringUtil;

public class AreaReference {


     *  {@link #generateContiguous(SpreadsheetVersion, String)})
     */
    public static boolean isContiguous(String reference) {
        return splitAreaReferences(reference).length == 1;
       int sheetRefEnd = reference.indexOf('!'); 
       if(sheetRefEnd != -1) {
          reference = reference.substring(sheetRefEnd);
       }

       // Check for the , as a sign of non-coniguous
       return !reference.contains(",");
    }

    public static AreaReference getWholeRow(SpreadsheetVersion version, String start, String end) {

            version = DEFAULT_SPREADSHEET_VERSION; // how the code used to behave. 
        }
        List<AreaReference> refs = new ArrayList<>();
        String[] splitReferences = splitAreaReferences(reference);
        for (String ref : splitReferences) {
            refs.add(new AreaReference(ref, version));
                    new AreaReference(st.nextToken(), version)
            );
        }
        return refs.toArray(new AreaReference[0]);
    }

        
        return new String [] { partA, sheetName + partB, };
    }

    /**
     * Splits a comma-separated area references string into an array of
     * individual references
     * @param reference Area references, i.e. A1:B2, 'Sheet1'!A1:B2
     * @return Area references in an array, size >= 1
     */
    private static String[] splitAreaReferences(String reference) {
        List<String> results = new ArrayList<>();
        String currentSegment = "";
        StringTokenizer st = new StringTokenizer(reference, ",");
        while(st.hasMoreTokens()) {
            if (currentSegment.length() > 0) {
                currentSegment += ",";
            }
            currentSegment += st.nextToken();
            int numSingleQuotes = StringUtil.countMatches(currentSegment, '\'');
            if (numSingleQuotes == 0 || numSingleQuotes == 2) {
                results.add(currentSegment);
                currentSegment = "";
            }
        }
        if (currentSegment.length() > 0) {
            results.add(currentSegment);
        }
        return results.toArray(new String[0]);
    }
}




        CellReference cf = ar.getFirstCell();
        assertEquals("row is 4", 0, cf.getRow());
        assertEquals("col is 1", 0, cf.getCol());
        assertTrue("row is abs", cf.isRowAbsolute());
        assertTrue("col is abs", cf.isColAbsolute());
        assertEquals("string is $A$1", "$A$1", cf.formatAsString());

        cf = ar.getLastCell();
        assertEquals("row is 4", 1, cf.getRow());
        assertEquals("col is 1", 1, cf.getCol());
        assertTrue("row is abs", cf.isRowAbsolute());
        assertTrue("col is abs", cf.isColAbsolute());
        assertEquals("string is $B$2", "$B$2", cf.formatAsString());

        CellReference[] refs = ar.getAllReferencedCells();

    }

    /**
     * References failed when sheet names were being used Reported by
     * Arne.Clauss@gedas.de
     */
    public void testReferenceWithSheet() {
        AreaReference ar;


        assertEquals(1, ar.getAllReferencedCells().length);


        ar = new AreaReference("Tabelle1!$B$5:$B$7", SpreadsheetVersion.EXCEL97);
        assertFalse(ar.isSingleCell());


        String refDCSimple = "$C$10:$C$10,$D$12:$D$12,$E$14:$E$14";
        String refDC2D = "$C$10:$C$11,$D$12:$D$12,$E$14:$E$20";
        String refDC3D = "Tabelle1!$C$10:$C$14,Tabelle1!$D$10:$D$12";
        String refComma = "'A,Sheet'!$A$1:$A$1,'A,Sheet'!$A$4:$A$5";
        String refCommaExp = "'!Sheet,Comma!'!$A$1:$B$1";

        // Check that we detect as contiguous properly
        assertTrue(AreaReference.isContiguous(refSimple));

        assertFalse(AreaReference.isContiguous(refDCSimple));
        assertFalse(AreaReference.isContiguous(refDC2D));
        assertFalse(AreaReference.isContiguous(refDC3D));
        assertFalse(AreaReference.isContiguous(refComma));
        assertTrue(AreaReference.isContiguous(refCommaExp));

        // Check we can only create contiguous entries
        new AreaReference(refSimple, SpreadsheetVersion.EXCEL97);

        try {
            new AreaReference(refDCSimple, SpreadsheetVersion.EXCEL97);
            fail("expected IllegalArgumentException");
        } catch (IllegalArgumentException e) {
            // expected during successful test
        }
        try {
            new AreaReference(refDC2D, SpreadsheetVersion.EXCEL97);
            fail("expected IllegalArgumentException");
        } catch (IllegalArgumentException e) {
            // expected during successful test
        }
        try {
            new AreaReference(refDC3D, SpreadsheetVersion.EXCEL97);
            fail("expected IllegalArgumentException");
        } catch (IllegalArgumentException e) {
            // expected during successful test
        }

        // Test that we split as expected

        assertEquals("Tabelle1", refs[0].getLastCell().getSheetName());
        assertEquals("Tabelle1", refs[1].getFirstCell().getSheetName());
        assertEquals("Tabelle1", refs[1].getLastCell().getSheetName());

        refs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL97, refComma);
        assertEquals(2, refs.length);
        System.out.println(refs[0].formatAsString());
        assertTrue(refs[0].isSingleCell());
        assertEquals("'A,Sheet'!$A$1", refs[0].formatAsString());
        assertEquals("A,Sheet", refs[0].getLastCell().getSheetName());
        assertEquals("'A,Sheet'!$A$4:$A$5", refs[1].formatAsString());
        assertEquals("A,Sheet", refs[1].getLastCell().getSheetName());

        refs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL97, refCommaExp);
        assertEquals(1, refs.length);
        assertFalse(refs[0].isSingleCell());
        assertEquals("'!Sheet,Comma!'!$A$1:$B$1", refs[0].formatAsString());
        assertEquals("!Sheet,Comma!", refs[0].getLastCell().getSheetName());
    }

    public void testDiscontinousReference() throws Exception {

        assertNotNull(nr);
        assertEquals("test", nr.getNameText());

        Ptg[] def = nr.getNameDefinition();
        assertEquals(4, def.length);

        MemFuncPtg ptgA = (MemFuncPtg) def[0];
        Area3DPtg ptgB = (Area3DPtg) def[1];
        Area3DPtg ptgC = (Area3DPtg) def[2];
        UnionPtg ptgD = (UnionPtg) def[3];
        assertEquals("", ptgA.toFormulaString());
        assertEquals(refA, ptgB.toFormulaString(eb));
        assertEquals(refB, ptgC.toFormulaString(eb));

    private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
        HSSFSheet s = wb.getSheet(cref.getSheetName());
        HSSFRow r = s.getRow(cref.getRow());
        HSSFCell c = r.getCell((int) cref.getCol());
        assertNotNull(c);
    }



        ar = new AreaReference("'one:many'!A1:B2", SpreadsheetVersion.EXCEL97);
        confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");

        ar = new AreaReference("'O,Comma'!A1:B1", SpreadsheetVersion.EXCEL97);
        confirmAreaSheetName(ar, "O,Comma", "'O,Comma'!A1:B1");
    }

    private static void confirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText) {

        confirmWholeColumnRef("$C:D", 2, 3, true, false);
        confirmWholeColumnRef("AD:$AE", 29, 30, false, true);
    }

    private static void confirmWholeColumnRef(String ref, int firstCol, int lastCol, boolean firstIsAbs,
            boolean lastIsAbs) {
        AreaReference ar = new AreaReference(ref, SpreadsheetVersion.EXCEL97);
        confirmCell(ar.getFirstCell(), 0, firstCol, true, firstIsAbs);
        confirmCell(ar.getLastCell(), 0xFFFF, lastCol, true, lastIsAbs);
    }

    private static void confirmCell(CellReference cell, int row, int col, boolean isRowAbs, boolean isColAbs) {
            boolean isColAbs) {
        assertEquals(row, cell.getRow());
        assertEquals(col, cell.getCol());
        assertEquals(isRowAbs, cell.isRowAbsolute());
- 

Return to bug 63998

Lines 22-27 import java.util.List; Link Here

(-)a/src/java/org/apache/poi/ss/util/AreaReference.java (-13 / +32 lines)
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	}

Lines 48-62 public final class TestAreaReference extends TestCase { Link Here

(-)a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (-24 / +44 lines)
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	-