Java Excel 工作表名称的有效字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Valid characters for Excel sheet names
提问by Ross
In Java, we're using the following package to programmatically create excel documents:
在 Java 中，我们使用以下包以编程方式创建 excel 文档：
org.apache.poi.hssf
If you attempt to set a sheet's name (NOT file, but internal Excel sheet), you will get an error if:
如果您尝试设置工作表的名称（不是文件，而是内部 Excel 工作表），您将在以下情况下收到错误：
- The name is over 31 characters
- The name contains any of the following characters: / \ * ? [ ]
- 名称超过 31 个字符
- 该名称包含以下任何字符： / \ * ? [ ]
However, after creating a document with a sheet name of:
但是，在创建工作表名称为以下的文档后：
@#$%&()+~`"':;,.|
@#$%&()+~`"':;,.|
No error is output, and everything seems fine in Java. When you open the excel file in Office 2003, it will give you an error saying that the sheet name was invalid and that it renamed it to something generic like "Sheet 1".
没有错误输出，在 Java 中一切似乎都很好。当您在 Office 2003 中打开 Excel 文件时，它会给您一个错误，指出工作表名称无效，并将其重命名为诸如“工作表 1”之类的通用名称。
I don't know much about the previously stated package that we're using, but it looks like it's not correctly filtering invalid Excel sheet names. Any idea of how I can filter out all known invalid characters? I'm hesitant to simply filter out all non-word characters.
我对我们使用的先前声明的包知之甚少，但看起来它没有正确过滤无效的 Excel 工作表名称。知道如何过滤掉所有已知的无效字符吗？我不愿简单地过滤掉所有非单词字符。
采纳答案by BradC
I think the problem is the colon, not the exclamation point.
我认为问题是冒号，而不是感叹号。
If you open Excel and try to manually edit a sheet name, the only characters it doesn't let you type are [ ] * / \ ? :
如果您打开 Excel 并尝试手动编辑工作表名称，则它不允许您输入的唯一字符是 [ ] * / \ ? ：
If you paste one of those characters in, you get the following error: (Excel 2003)
如果您粘贴其中一个字符，则会出现以下错误：(Excel 2003)
While renaming a sheet or chart, you entered an invalid name. Try one of the following:
- Make sure the name you entered does not exceed 31 characters.
- Make sure the name does not contain any of the following characters: : \ / ? * [ or ]
- Make sure you did not leave the name blank.
重命名工作表或图表时，您输入了无效名称。尝试以下方法之一：
- 确保您输入的名称不超过 31 个字符。
- 确保名称不包含以下任何字符： : \ / ? * [ 或者 ]
- 确保您没有将名称留空。
回答by Dunc
This is what I use in C# (should be similar in Java):
这是我在 C# 中使用的（在 Java 中应该类似）：
const string invalidCharsRegex = @"[/\*'?[\]:]+";
const int maxLength = 31;
string safeName = Regex.Replace(worksheetName, invalidCharsRegex, " ")
.Replace(" ", " ")
.Trim();
if (string.IsNullOrEmpty(safeName))
{
safeName = "Default"; // cannot be empty
}
else if (safeName.Length > maxLength)
{
safeName = safeName.Substring(0, maxLength);
}
回答by Tago Fabic
You can use the Apache POI's WorkbookUtil.createSafeSheetName(String s) to safely create your Sheet Name.
您可以使用 Apache POI 的 WorkbookUtil.createSafeSheetName(String s) 来安全地创建您的工作表名称。
http://poi.apache.org/apidocs/org/apache/poi/ss/util/WorkbookUtil.html
http://poi.apache.org/apidocs/org/apache/poi/ss/util/WorkbookUtil.html
回答by Jesús Sánchez
You can use this:
你可以使用这个：
protected Sheet createSheet(XSSFWorkbook book, String nameSheet) {
return book.createSheet(WorkbookUtil.createSafeSheetName(nameSheet));
}
