Author: centic
Date: Sun Nov 7 16:22:33 2021
New Revision: 1894817
URL: http://svn.apache.org/viewvc?rev=1894817&view=rev
Log:
Adjust trimming sheetnames > 31 chars
Define Constant MAX_SENSITIVE_SHEET_NAME_LEN = 31
Warn about trimmed sheet names
Implement sheetname trimming for HSSFWorkbook
Add use case tests
Closes #273
Modified:
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/trunk/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
Modified:
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1894817&r1=1894816&r2=1894817&view=diff
==============================================================================
---
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
(original)
+++
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
Sun Nov 7 16:22:33 2021
@@ -123,12 +123,6 @@ public class XSSFWorkbook extends POIXML
private static final Pattern COMMA_PATTERN = Pattern.compile(",");
/**
- * Excel silently truncates long sheet names to 31 chars.
- * This constant is used to ensure uniqueness in the first 31 chars
- */
- private static final int MAX_SENSITIVE_SHEET_NAME_LEN = 31;
-
- /**
* Images formats supported by XSSF but not by HSSF
*/
public static final int PICTURE_TYPE_GIF = 8;
@@ -744,10 +738,10 @@ public class XSSFWorkbook extends POIXML
// Try and find the next sheet name that is unique
String index = Integer.toString(uniqueIndex++);
String name;
- if (baseName.length() + index.length() + 2 < 31) {
+ if (baseName.length() + index.length() + 2 <
MAX_SENSITIVE_SHEET_NAME_LEN) {
name = baseName + " (" + index + ")";
} else {
- name = baseName.substring(0, 31 - index.length() - 2) + "(" +
index + ")";
+ name = baseName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN -
index.length() - 2) + "(" + index + ")";
}
//If the sheet name is unique, then set it otherwise move on to
the next number.
@@ -875,8 +869,17 @@ public class XSSFWorkbook extends POIXML
validateSheetName(sheetname);
// YK: Mimic Excel and silently truncate sheet names longer than 31
characters
- if(sheetname.length() > 31) {
- sheetname = sheetname.substring(0, 31);
+ // Issue a WARNING though in order to prevent a situation, where the
provided long sheet name is
+ // not accessible due to the trimming while we are not even aware of
the reason and continue to use
+ // the long name in generated formulas
+ if(sheetname.length() > MAX_SENSITIVE_SHEET_NAME_LEN) {
+ String trimmedSheetname = sheetname.substring(0,
MAX_SENSITIVE_SHEET_NAME_LEN);
+
+ // we still need to warn about the trimming as the
original sheet name won't be available
+ // e.g. when referenced by formulas
+ LOG.atWarn().log("Sheet '{}' will be added with a
trimmed name '{}' for MS Excel compliance.",
+ sheetname, trimmedSheetname);
+ sheetname = trimmedSheetname;
}
WorkbookUtil.validateSheetName(sheetname);
@@ -1581,8 +1584,8 @@ public class XSSFWorkbook extends POIXML
String oldSheetName = getSheetName(sheetIndex);
// YK: Mimic Excel and silently truncate sheet names longer than 31
characters
- if(sheetname.length() > 31) {
- sheetname = sheetname.substring(0, 31);
+ if(sheetname.length() > MAX_SENSITIVE_SHEET_NAME_LEN) {
+ sheetname = sheetname.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN);
}
WorkbookUtil.validateSheetName(sheetname);
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1894817&r1=1894816&r2=1894817&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
(original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
Sun Nov 7 16:22:33 2021
@@ -877,10 +877,10 @@ public final class HSSFWorkbook extends
// Try and find the next sheet name that is unique
String index = Integer.toString(uniqueIndex++);
String name;
- if (baseName.length() + index.length() + 2 < 31) {
+ if (baseName.length() + index.length() + 2 <
MAX_SENSITIVE_SHEET_NAME_LEN) {
name = baseName + " (" + index + ")";
} else {
- name = baseName.substring(0, 31 - index.length() - 2) + "(" +
index + ")";
+ name = baseName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN -
index.length() - 2) + "(" + index + ")";
}
//If the sheet name is unique, then set it otherwise move on to
the next number.
@@ -936,8 +936,22 @@ public final class HSSFWorkbook extends
throw new IllegalArgumentException("sheetName must not be null");
}
- if (workbook.doesContainsSheetName(sheetname, _sheets.size())) {
- throw new IllegalArgumentException("The workbook already contains
a sheet named '" + sheetname + "'");
+ if (workbook.doesContainsSheetName(sheetname, _sheets.size())) {
+ throw new IllegalArgumentException("The workbook
already contains a sheet named '" + sheetname + "'");
+ }
+
+ // YK: Mimic Excel and silently truncate sheet names longer
than 31 characters
+ // Issue a WARNING though in order to prevent a situation, where the
provided long sheet name is
+ // not accessible due to the trimming while we are not even aware of
the reason and continue to use
+ // the long name in generated formulas
+ if(sheetname.length() > MAX_SENSITIVE_SHEET_NAME_LEN) {
+ String trimmedSheetname = sheetname.substring(0,
MAX_SENSITIVE_SHEET_NAME_LEN);
+
+ // we still need to warn about the trimming as the
original sheet name won't be available
+ // e.g. when referenced by formulas
+ LOGGER.atWarn().log("Sheet '{}' will be added with a
trimmed name '{}' for MS Excel compliance.",
+ sheetname, trimmedSheetname);
+ sheetname = trimmedSheetname;
}
HSSFSheet sheet = new HSSFSheet(this);
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java?rev=1894817&r1=1894816&r2=1894817&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java
(original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java Sun
Nov 7 16:22:33 2021
@@ -55,6 +55,12 @@ public interface Workbook extends Closea
int PICTURE_TYPE_DIB = 7;
/**
+ * Excel silently truncates long sheet names to 31 chars.
+ * This constant is used to ensure uniqueness in the first 31 chars
+ */
+ int MAX_SENSITIVE_SHEET_NAME_LEN = 31;
+
+ /**
* Convenience method to get the active sheet. The active sheet is is the
sheet
* which is currently displayed when the workbook is viewed in Excel.
* 'Selected' sheet(s) is a distinct concept.
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java?rev=1894817&r1=1894816&r2=1894817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
Sun Nov 7 16:22:33 2021
@@ -525,7 +525,7 @@ public abstract class BaseTestWorkbook {
}
/**
- * Tests that all of the unicode capable string fields can be set, written
and then read back
+ * Tests that all the unicode capable string fields can be set, written
and then read back
*/
@Test
protected void unicodeInAll() throws IOException {
@@ -905,4 +905,17 @@ public abstract class BaseTestWorkbook {
assertEquals(1114425, anchor.getDx2()); //HSSF: 171
}
}
+
+ @Test
+ void testSheetNameTrimming() throws IOException {
+ try (Workbook workbook = _testDataProvider.createWorkbook()) {
+ Sheet sheet =
workbook.createSheet("MyVeryLongSheetName_9999999999999999");
+ assertNotNull(sheet);
+ assertEquals("MyVeryLongSheetName_99999999999",
workbook.getSheetName(0));
+
+ assertThrows(IllegalArgumentException.class,
+ () ->
workbook.createSheet("MyVeryLongSheetName_9999999999999998")
+ );
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
