Author: fanningpj
Date: Tue Nov 7 21:50:12 2023
New Revision: 1913660
URL: http://svn.apache.org/viewvc?rev=1913660&view=rev
Log:
[github-545] Make exported sheet names compatible with Excel. Thanks to
Aleksandrs Jansons. This closes #545
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java?rev=1913660&r1=1913659&r2=1913660&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/SheetNameFormatter.java
Tue Nov 7 21:50:12 2023
@@ -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;
+ }
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java?rev=1913660&r1=1913659&r2=1913660&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/TestSheetNameFormatter.java
Tue Nov 7 21:50:12 2023
@@ -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());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
