Valid characters for Excel sheet names

Question

In Java, we're using the following package to programmatically create excel documents:

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:

• The name is over 31 characters
• The name contains any of the following characters: / \ * ? [ ]

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".

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.

Answer 1

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 [ ] * / \ ? :

If you paste one of those characters in, you get the following error: (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.

Answer 2

You can use the Apache POI's WorkbookUtil.createSafeSheetName(String s) to safely create your Sheet Name.

http://poi.apache.org/apidocs/org/apache/poi/ss/util/WorkbookUtil.html