From e83987b35db793657e39db1f7e5d1f256efa52bf Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Tue, 7 Nov 2023 21:50:12 +0000 Subject: [PATCH] [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 --- .../poi/ss/formula/SheetNameFormatter.java | 38 +++++++ .../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()); + } } -- 2.39.5