diff options
author | PJ Fanning <fanningpj@apache.org> | 2023-11-07 21:50:12 +0000 |
---|---|---|
committer | PJ Fanning <fanningpj@apache.org> | 2023-11-07 21:50:12 +0000 |
commit | e83987b35db793657e39db1f7e5d1f256efa52bf (patch) | |
tree | e665ecb7d93d95dbc97ec326ec0b1496039195fb /poi | |
parent | 76b1a6fee4c78ec334fbdd729ae5c9d2957ae9c5 (diff) | |
download | poi-e83987b35db793657e39db1f7e5d1f256efa52bf.tar.gz poi-e83987b35db793657e39db1f7e5d1f256efa52bf.zip |
[github-545] Make exported sheet names compatible with Excel. Thanks to Aleksandrs Jansons. This closes #545
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1913660 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'poi')
-rw-r--r-- | poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java | 38 | ||||
-rw-r--r-- | poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java | 101 |
2 files changed, 93 insertions, 46 deletions
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java b/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java index 26eca119c1..c6b5e82a95 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java @@ -164,6 +164,9 @@ public final class SheetNameFormatter { if (nameLooksLikeBooleanLiteral(rawSheetName)) { return true; } + if (nameStartsWithR1C1CellReference(rawSheetName)) { + return true; + } // Error constant literals all contain '#' and other special characters // so they don't get this far return false; @@ -264,4 +267,39 @@ public final class SheetNameFormatter { String numbersSuffix = matcher.group(2); return cellReferenceIsWithinRange(lettersPrefix, numbersSuffix); } + + /** + * Checks if the sheet name starts with R1C1 style cell reference. + * If this is the case Excel requires the sheet name to be enclosed in single quotes. + * @return {@code true} if the specified rawSheetName starts with R1C1 style cell reference + */ + static boolean nameStartsWithR1C1CellReference(String rawSheetName) { + int len = rawSheetName.length(); + char firstChar = rawSheetName.charAt(0); + if (firstChar == 'R' || firstChar == 'r') { + if (len > 1) { + char secondChar = rawSheetName.charAt(1); + if (secondChar == 'C' || secondChar == 'c') { + if (len > 2) { + char thirdChar = rawSheetName.charAt(2); + return Character.isDigit(thirdChar); + } else { + return true; + } + } else { + return Character.isDigit(secondChar); + } + } else { + return true; + } + } else if (firstChar == 'C' || firstChar == 'c') { + if (len > 1) { + char secondChar = rawSheetName.charAt(1); + return Character.isDigit(secondChar); + } else { + return true; + } + } + return false; + } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java b/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java index bfd2c05492..3bc0595469 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java @@ -48,26 +48,16 @@ final class TestSheetNameFormatter { confirmFormat("A12220", "'A12220'"); confirmFormat("TAXRETURN19980415", "TAXRETURN19980415"); - confirmFormat(null, "#REF"); - } - - private static void confirmFormat(String rawSheetName, String expectedSheetNameEncoding) { - // test all variants - - assertEquals(expectedSheetNameEncoding, SheetNameFormatter.format(rawSheetName)); - - StringBuilder sb = new StringBuilder(); - SheetNameFormatter.appendFormat(sb, rawSheetName); - assertEquals(expectedSheetNameEncoding, sb.toString()); - - sb = new StringBuilder(); - SheetNameFormatter.appendFormat((Appendable)sb, rawSheetName); - assertEquals(expectedSheetNameEncoding, sb.toString()); + confirmFormat("RC9Sheet", "'RC9Sheet'"); // starts with R1C1 style ref ('RC9') + confirmFormat("r", "'r'"); // R1C1 style ref + confirmFormat("rc", "'rc'"); // R1C1 style ref + confirmFormat("C", "'C'"); // R1C1 style ref + confirmFormat("rCsheet", "rCsheet"); // 'rc' + character is not qualified as R1C1 style ref + confirmFormat("ra", "ra"); // 'r' + character is not qualified as R1C1 style ref + confirmFormat("r1a", "'r1a'"); // 'r1' is R1C1 style ref + confirmFormat("Rc1sheet", "'Rc1sheet'"); // 'rc1' is R1C1 style ref - StringBuffer sbf = new StringBuffer(); - //noinspection deprecation - SheetNameFormatter.appendFormat(sbf, rawSheetName); - assertEquals(expectedSheetNameEncoding, sbf.toString()); + confirmFormat(null, "#REF"); } @Test @@ -92,23 +82,6 @@ final class TestSheetNameFormatter { confirmFormat(null, null, "[#REF]#REF"); } - private static void confirmFormat(String workbookName, String rawSheetName, String expectedSheetNameEncoding) { - // test all variants - - StringBuilder sb = new StringBuilder(); - SheetNameFormatter.appendFormat(sb, workbookName, rawSheetName); - assertEquals(expectedSheetNameEncoding, sb.toString()); - - sb = new StringBuilder(); - SheetNameFormatter.appendFormat((Appendable)sb, workbookName, rawSheetName); - assertEquals(expectedSheetNameEncoding, sb.toString()); - - StringBuffer sbf = new StringBuffer(); - //noinspection deprecation - SheetNameFormatter.appendFormat(sbf, workbookName, rawSheetName); - assertEquals(expectedSheetNameEncoding, sbf.toString()); - } - @Test void testFormatException() { Appendable mock = new Appendable() { @@ -143,10 +116,6 @@ final class TestSheetNameFormatter { confirmFormat("No", "No"); } - private static void confirmCellNameMatch(String rawSheetName, boolean expected) { - assertEquals(expected, SheetNameFormatter.nameLooksLikePlainCellReference(rawSheetName)); - } - /** * Tests functionality to determine whether a sheet name containing only letters and digits * would look (to Excel) like a cell name. @@ -164,12 +133,6 @@ final class TestSheetNameFormatter { confirmCellNameMatch("SALES20080101", false); // out of range } - private static void confirmCellRange(String text, int numberOfPrefixLetters, boolean expected) { - String prefix = text.substring(0, numberOfPrefixLetters); - String suffix = text.substring(numberOfPrefixLetters); - assertEquals(expected, SheetNameFormatter.cellReferenceIsWithinRange(prefix, suffix)); - } - /** * Tests exact boundaries for names that look very close to cell names (i.e. contain 1 or more * letters followed by one or more digits). @@ -188,4 +151,50 @@ final class TestSheetNameFormatter { confirmCellRange("iV65536", 2, true); // max cell in Excel 97-2003 confirmCellRange("IW65537", 2, false); } + + private static void confirmCellNameMatch(String rawSheetName, boolean expected) { + assertEquals(expected, SheetNameFormatter.nameLooksLikePlainCellReference(rawSheetName)); + } + + private static void confirmCellRange(String text, int numberOfPrefixLetters, boolean expected) { + String prefix = text.substring(0, numberOfPrefixLetters); + String suffix = text.substring(numberOfPrefixLetters); + assertEquals(expected, SheetNameFormatter.cellReferenceIsWithinRange(prefix, suffix)); + } + + private static void confirmFormat(String workbookName, String rawSheetName, String expectedSheetNameEncoding) { + // test all variants + + StringBuilder sb = new StringBuilder(); + SheetNameFormatter.appendFormat(sb, workbookName, rawSheetName); + assertEquals(expectedSheetNameEncoding, sb.toString()); + + sb = new StringBuilder(); + SheetNameFormatter.appendFormat((Appendable)sb, workbookName, rawSheetName); + assertEquals(expectedSheetNameEncoding, sb.toString()); + + StringBuffer sbf = new StringBuffer(); + //noinspection deprecation + SheetNameFormatter.appendFormat(sbf, workbookName, rawSheetName); + assertEquals(expectedSheetNameEncoding, sbf.toString()); + } + + private static void confirmFormat(String rawSheetName, String expectedSheetNameEncoding) { + // test all variants + + assertEquals(expectedSheetNameEncoding, SheetNameFormatter.format(rawSheetName)); + + StringBuilder sb = new StringBuilder(); + SheetNameFormatter.appendFormat(sb, rawSheetName); + assertEquals(expectedSheetNameEncoding, sb.toString()); + + sb = new StringBuilder(); + SheetNameFormatter.appendFormat((Appendable)sb, rawSheetName); + assertEquals(expectedSheetNameEncoding, sb.toString()); + + StringBuffer sbf = new StringBuffer(); + //noinspection deprecation + SheetNameFormatter.appendFormat(sbf, rawSheetName); + assertEquals(expectedSheetNameEncoding, sbf.toString()); + } } |