From 326ce1b2e86c521beebf46ef3fe29731d17b11e1 Mon Sep 17 00:00:00 2001 From: Symeon Huang Date: Wed, 11 Dec 2019 17:25:54 +0000 Subject: [PATCH] [Bug 63998] Fix the bugs with special chars in the sheet names --- .../org/apache/poi/ss/util/AreaReference.java | 45 +++++++++---- .../poi/hssf/util/TestAreaReference.java | 67 ++++++++++++------- 2 files changed, 76 insertions(+), 36 deletions(-) diff --git a/src/java/org/apache/poi/ss/util/AreaReference.java b/src/java/org/apache/poi/ss/util/AreaReference.java index 4dd4771be..a832fec6b 100644 --- a/src/java/org/apache/poi/ss/util/AreaReference.java +++ b/src/java/org/apache/poi/ss/util/AreaReference.java @@ -22,6 +22,7 @@ import java.util.List; import java.util.StringTokenizer; import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.util.StringUtil; public class AreaReference { @@ -166,14 +167,7 @@ public class AreaReference { * {@link #generateContiguous(SpreadsheetVersion, String)}) */ public static boolean isContiguous(String reference) { - // If there's a sheet name, strip it off - int sheetRefEnd = reference.indexOf('!'); - if(sheetRefEnd != -1) { - reference = reference.substring(sheetRefEnd); - } - - // Check for the , as a sign of non-coniguous - return !reference.contains(","); + return splitAreaReferences(reference).length == 1; } public static AreaReference getWholeRow(SpreadsheetVersion version, String start, String end) { @@ -220,11 +214,9 @@ public class AreaReference { version = DEFAULT_SPREADSHEET_VERSION; // how the code used to behave. } List refs = new ArrayList<>(); - StringTokenizer st = new StringTokenizer(reference, ","); - while(st.hasMoreTokens()) { - refs.add( - new AreaReference(st.nextToken(), version) - ); + String[] splitReferences = splitAreaReferences(reference); + for (String ref : splitReferences) { + refs.add(new AreaReference(ref, version)); } return refs.toArray(new AreaReference[0]); } @@ -405,4 +397,31 @@ public class AreaReference { 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 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]); + } } diff --git a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java index 4594cf565..0f6dc8959 100644 --- a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java +++ b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java @@ -48,15 +48,15 @@ public final class TestAreaReference extends TestCase { 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()); + 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()); + 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(); @@ -80,8 +80,8 @@ public final class TestAreaReference extends TestCase { } /** - * References failed when sheet names were being used - * Reported by Arne.Clauss@gedas.de + * References failed when sheet names were being used Reported by + * Arne.Clauss@gedas.de */ public void testReferenceWithSheet() { AreaReference ar; @@ -92,7 +92,6 @@ public final class TestAreaReference extends TestCase { assertEquals(1, ar.getAllReferencedCells().length); - ar = new AreaReference("Tabelle1!$B$5:$B$7", SpreadsheetVersion.EXCEL97); assertFalse(ar.isSingleCell()); @@ -113,6 +112,8 @@ public final class TestAreaReference extends TestCase { 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)); @@ -120,6 +121,8 @@ public final class TestAreaReference extends TestCase { 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); @@ -127,20 +130,20 @@ public final class TestAreaReference extends TestCase { try { new AreaReference(refDCSimple, SpreadsheetVersion.EXCEL97); fail("expected IllegalArgumentException"); - } catch(IllegalArgumentException e) { - // expected during successful test + } catch (IllegalArgumentException e) { + // expected during successful test } try { new AreaReference(refDC2D, SpreadsheetVersion.EXCEL97); fail("expected IllegalArgumentException"); - } catch(IllegalArgumentException e) { - // expected during successful test + } catch (IllegalArgumentException e) { + // expected during successful test } try { new AreaReference(refDC3D, SpreadsheetVersion.EXCEL97); fail("expected IllegalArgumentException"); - } catch(IllegalArgumentException e) { - // expected during successful test + } catch (IllegalArgumentException e) { + // expected during successful test } // Test that we split as expected @@ -184,6 +187,21 @@ public final class TestAreaReference extends TestCase { 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 { @@ -205,13 +223,13 @@ public final class TestAreaReference extends TestCase { assertNotNull(nr); assertEquals("test", nr.getNameText()); - Ptg[] def =nr.getNameDefinition(); + 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]; + 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)); @@ -245,7 +263,7 @@ public final class TestAreaReference extends TestCase { 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()); + HSSFCell c = r.getCell((int) cref.getCol()); assertNotNull(c); } @@ -262,6 +280,9 @@ public final class TestAreaReference extends TestCase { 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) { @@ -275,15 +296,15 @@ public final class TestAreaReference extends TestCase { 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) { + + 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) { + private static void confirmCell(CellReference cell, int row, int col, boolean isRowAbs, boolean isColAbs) { assertEquals(row, cell.getRow()); assertEquals(col, cell.getCol()); assertEquals(isRowAbs, cell.isRowAbsolute()); -- 2.21.0 (Apple Git-122.2)