Issue 6087 - ODFF: allow all characters in sheet names
Summary: ODFF: allow all characters in sheet names
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: All All
: P3 Trivial with 22 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@documentation
Keywords: ms_interoperability
: 6732 52496 55633 63932 68090 84615 91065 91696 93641 (view as issue list)
Depends on:
Blocks: 66592 90743
  Show dependency tree
Reported: 2002-06-24 13:11 UTC by treborg2
Modified: 2013-08-07 15:15 UTC (History)
19 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

how about this. (12.41 KB, patch)
2008-04-16 19:30 UTC, caolanm
no flags Details | Diff
here's my take on extending this to recognize '' as an escaped literal ' (14.60 KB, patch)
2008-04-17 19:50 UTC, caolanm
no flags Details | Diff
additionally handle # in a sheet name (13.73 KB, patch)
2008-05-01 14:31 UTC, caolanm
no flags Details | Diff
and updated to feed a quoted chunk back on its own as a single token (14.09 KB, patch)
2008-05-01 16:55 UTC, caolanm
no flags Details | Diff
current testcases (6.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-05-01 16:56 UTC, caolanm
no flags Details
slightly modified to support XL A1 and XL R1C1 syntax. (18.42 KB, patch)
2008-05-04 02:01 UTC, kyoshida
no flags Details | Diff
fixed R1C1 parsing & invalidate certain characters. (20.02 KB, patch)
2008-05-04 04:32 UTC, kyoshida
no flags Details | Diff
revised patch to allow all characters in sheet names (19.34 KB, patch)
2008-05-10 16:52 UTC, kyoshida
no flags Details | Diff
TestCaseSpecification (6.71 KB, text/html)
2008-07-04 11:04 UTC, oc
no flags Details
Testdocuments for Test Case Specification (37.64 KB, application/x-compressed)
2008-07-04 12:04 UTC, oc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description treborg2 2002-06-24 13:11:50 UTC
I attempted to name a sheet for an IP address of one of our switches

it gave me invalid sheet name as the error.  when I clicked the "Help" button 
in the "Rename" box window (right click the sheet's tab at bottom, select 
rename).. when I clicked the help button it told me how to put a sheet name in, 
but no examples of valid or invalid naming.

I would expect problems with :\|?/'" but not . (dot)   (white space) or other 
odd characters ! @ # $ % * ( ) _ + etc etc etc.. well maybe not $ * () ... but 
I certainly didn't think . (dot) was an illegal character.
Comment 1 scarr 2002-07-01 20:02:56 UTC
Under Inserting Sheets, it states you can enter both letters and
numbers.  Doesn't say anything about special characters ;-) 

I'll check into this to for a little more detail as to what characters
are available.  It turns out that a Period is used as a Sheet.Cell
Comment 2 scarr 2002-07-01 20:07:24 UTC
Assigning to myself.
Comment 3 treborg2 2002-07-02 08:15:30 UTC
got the email note.. thanks.. tried to reply thinking it would go 
in... but it didn't... :)

what I'd said in the reply was simply thanks... and that I noticed 
too that excel sheets that had used (and allowed) a period were auto-
magically converted to underscores.

The real problem I guess is that the documentation for renaming 
(since you've said its under the "Insert" option) would be the same 
as is listed under Insert.  I'd see it that way because there were 
already three sheets (the default) there and the help associated 
didn't link back to inserting sheets .. if I recall correctly.

Anyway.. I'd further make the case if possible to add . - and other 
characters to allow more free flowing naming..... namely.. I use 
dashes or dots for date separation on sheets because you can't 
usually use /

Thanks !!

And OO is pretty wild... concidering microsoft's bloat and speed 
every time they *upgrade* their versions... I think OO has the unique 
spot and could within the next year or two steal many away from MS.  
Which I think is a good thing!... anyway.. Thanks Again!!
Comment 4 scarr 2002-10-06 20:33:06 UTC
In talking with the engineer, they said there was no reason to not
allow these characters in a future version.

The documentation is already stated as far as what is allowed, so I
believe this will make a good feature request for the Calc componant.
Comment 5 Unknown 2003-02-11 18:26:38 UTC
As a related comment (perhaps), I had problems when saving a template
in Impress with a period.  OO essentially truncated everything after
the period when saving the actual .sti file.  Would this problem be
solved at the same time, or should I think about issuing a report?
Comment 6 scarr 2003-06-05 15:44:48 UTC
Moving to the Spreadsheet component as a Feature Request.
Comment 7 scarr 2004-08-13 20:59:09 UTC
Email from Charles <private email don't have permission to post>:

There are several special characters that MS Excel has no problem
with when used in worksheet names.  Excel allows a '-', for example,
but upon opening a .xls file, OO will substitute a '_'.   This
bug should be classed as something more than a feature request
if it is desirable for OO to open Excel files with as little damage
as possible.

Mabye issue should be reclassified.
Comment 8 frank 2005-08-04 13:42:33 UTC
As an enhancement re-assigned to the requirements team.

Set Prio to 3
Comment 9 frank 2005-08-04 13:43:22 UTC
*** Issue 52496 has been marked as a duplicate of this issue. ***
Comment 10 frank 2005-10-26 12:01:48 UTC
*** Issue 6732 has been marked as a duplicate of this issue. ***
Comment 11 frank 2005-10-26 12:03:26 UTC
*** Issue 55633 has been marked as a duplicate of this issue. ***
Comment 12 lars 2005-11-15 19:23:56 UTC
add the ms_interoperability keyword
Comment 13 lars 2006-04-03 18:14:44 UTC
note that illegal characters are causing problems when importing an Excel 
spreadsheet. Characters like a dot, parentheses etc. in sheet names are 
converted to an underscore, and then functions like Address() don't work because 
the sheet names don't match what they expect. Therefore adding 
ms_interoperability keyword.
Comment 14 lars 2006-04-03 18:15:11 UTC
*** Issue 63932 has been marked as a duplicate of this issue. ***
Comment 15 frank 2006-08-02 21:55:50 UTC
*** Issue 68090 has been marked as a duplicate of this issue. ***
Comment 16 kyoshida 2007-12-16 17:31:28 UTC
*** Issue 84615 has been marked as a duplicate of this issue. ***
Comment 17 dma2002 2007-12-22 15:37:59 UTC
Still "OOo Later"?! So, what is "later" then? This issue exists more than five
years - still "later". Six duplicates - still "later". Last comment is dated the
3-rd April 2006 - still "later". 23 votes... And so on.
Maybe you should think about fixing this issue to OOo 3.0. Or one more "later"?
Comment 18 kmakarov 2007-12-26 08:13:06 UTC
I tried to manually edit xml file for saved Spreadsheet document to set sheet
name for a name containing problem characters and it worked. Calc can load
document with 'invalid' sheet names if was changed manually in the content.xml 

So, I thought it is an artificial problem. Maybe OOo Basic code will not work
with 'invalid' sheet names, but in that case I do not want Basic code work, I
want sheet name contain ! @ # $ % * ( ) :\|?/'"  etc.

If I can not set Sheet name interactively why I does not allowed setting sheet
name with 'invalid' characters by code with COM interface from other programs? I
am sure I need exactly that name when I am setting it programmatically.

For example, Microsoft Excel allows setting sheet name using 'invalid'
characters programmatically while disallows it interactively.
Comment 19 ooo 2008-01-07 16:37:25 UTC
The requirements are quite clear and ODFF does not restrict what characters a
sheet name may consist of. So this has to be implemented. The only constraint
that is specified is that in a stored ODFF formula the sheet name must be:

SheetName ::= QuotedSheetName | '$'? [^\. #$']+
QuotedSheetName ::= '$'? SingleQuoted
SingleQuoted ::= "'" ([^'] | "''")+ "'"

See sections "5.2 Basic Expressions" and "5.8 References" in the latest
available OpenDocument-formula-*.odt of

This must be implemented in the formula parser, and the UI allow any character
(except control characters) for sheet names.

Note that in ODFF a reference is always surrounded by square brackets, e.g.
[Sheet1.A1], so there are no ambiguities what parts make up the sheet name.
However, for UI purposes when parsing a sheet name in formula context the sheet
name must be enclosed in single quotes as soon as it contains a character that
otherwise would lead to ambiguity, for example a sheet name "this/that" can't 
be used as =this/that.A1 for obvious reasons, but as ='this/that'.A1 instead.
Comment 20 lindsaygraham 2008-01-25 10:05:47 UTC
For all the reasons given by others, PLEASE implement this change ASAP.
Comment 21 karora 2008-03-06 08:24:39 UTC
This is marked OOo 3.x, but when 'a developer' was asked in 2003 it apparently
wasn't hard.  For 37 votes perhaps it is worth getting a more modern developer
to take a quick look?

Some example sheet names of the sorts of things that I regularly see elsewhere
are stuff like:
 2007 / 2008
 Fred & Wilma

One workaround is to open the .ods document in Gnumeric, rename the sheets (all
of the above work fine in Gnumeric) save it and re-open it again in OOo.

Admittedly any _references_ into those sheets may work less than brilliantly for
the IP address example above (which works in Gnumeric), but it seems to work
fine for the '2007 / 2008' and 'Fred & Wilma' examples.

It would be nice to know whether perhaps the current restrictions on sheet names
can be relaxed in some small ways without requiring deeper changes to the code.
 Even just allowing spaces would be an improvement.

Andrew McMillan.
Comment 22 robbk 2008-03-06 08:43:08 UTC
We regularly download files with names like PA0### from an IBM i5 using the
Excel export format. Then we open the Excel file in Calc and the sheet is
renamed to PA0___.
Comment 23 caolanm 2008-04-16 19:30:48 UTC
Created attachment 52937 [details]
how about this.
Comment 24 caolanm 2008-04-16 19:32:12 UTC
I'm no calc guy, but the above seems to work for me. Are there more subtleties
to this that I'd need to additionally cover ?
Comment 25 mloiseleur 2008-04-16 22:10:01 UTC
I really like this kind of patch which removes lines of code and gives OOo more
Comment 26 ooo 2008-04-17 12:14:29 UTC
Yeah, nice one!
Might need some tweaking in ScCompiler::NextSymbol() around parseAnyToken() to
accept an embedded escaped (doubled) single quote (apostrophe).
Comment 27 caolanm 2008-04-17 19:50:29 UTC
Created attachment 52974 [details]
here's my take on extending this to recognize '' as an escaped literal '
Comment 28 kpalagin 2008-04-25 19:01:31 UTC
please comment on new patch and if it can make into 3.0.

K. Palagin.
Comment 29 ooo 2008-04-28 10:38:33 UTC
Will do.
Comment 30 caolanm 2008-04-28 10:54:13 UTC
I didn't want to spam everyone for every little typo, so there's a little
additional fix here and there at*checkout*/devel/

Comment 31 kyoshida 2008-04-30 05:50:17 UTC

I just did some testing on your latest patch linked from the redhat cvs repo. 
The patch still fails to handle a cell reference when the sheet name contains
'#'.  Also, when a quoted sheet name is used inside a function, it throws a
'#NAME?' error e.g. =ABS('Quoted Sheet'.A1).  This was working prior to applying
this patch.

The problem with '#' is probably the first if statement block in
lcl_ScAddress_Parse_OOo where the code searches for a '#' in case of a document
reference, but fails to skip the characters inside the quotes.  I'm still not
sure what's causing the second one, though.

Comment 32 caolanm 2008-05-01 14:31:09 UTC
Created attachment 53300 [details]
additionally handle # in a sheet name
Comment 33 kyoshida 2008-05-01 15:38:37 UTC
Yup, the '#' situation is now resolved. :-)

Comment 34 caolanm 2008-05-01 16:55:26 UTC
Created attachment 53302 [details]
and updated to feed a quoted chunk back on its own as a single token
Comment 35 caolanm 2008-05-01 16:56:56 UTC
Created attachment 53303 [details]
current testcases
Comment 36 caolanm 2008-05-01 16:58:49 UTC
cmc->kohei: Does that resolve the ABS issue ? The testcase.ods shows the test
cases that I'm aware of.
Comment 37 kyoshida 2008-05-01 17:21:28 UTC
kohei->cmc: yup, it resolves the issue I had with ABS (and other functions).  It
also works in the function wizard dialog.  The patch looks good to me.
Comment 38 kyoshida 2008-05-04 02:01:56 UTC
Created attachment 53341 [details]
slightly modified to support XL A1 and XL R1C1 syntax.
Comment 39 kyoshida 2008-05-04 02:11:12 UTC
I've slightly modified Caolan's patch to cover XL A1 and XL R1C1 style address
conventions (since those conventions are enabled in ooo-build).  So far no
problems have been observed, but I'm still on my toes for any potential problems
that might arise.

There is one issue to consider: Excel doesn't allow any of : \ / ? * [ ]
characters, but the patch allows them.  So, when saving as an Excel file and
opening it in Excel creates a slight problem if a sheet name contains those
characters.  Excel XP can repair such documents on loading by renaming those
sheets with offending names, but we should probably disallow those characters
for smoother interoperability.
Comment 40 kyoshida 2008-05-04 04:32:24 UTC
Created attachment 53343 [details]
fixed R1C1 parsing & invalidate certain characters.
Comment 41 ooo 2008-05-10 01:03:27 UTC
I'm not happy with the latest change of excluding those DOS file name and legacy
workbook management characters that Excel doesn't allow. The ODFF specification
does not have these restrictions and we should allow all characters according to
the specification. If we did want these restrictions for interoperability
reasons we should give a proper definition in the ODFF specification. Please
let's discuss this on the OASIS ODF formula subcommittee's list.
Comment 42 jlynx 2008-05-10 06:01:05 UTC
I'm agreed that xls shouldn't make any restrictions for ODF.
May be it's possible to make an error message if user tried to save a file in
xls format? Something like this: "Sheet(s) name contains not valid for xls
symbols. Would you like to replace it by "_"?", and two buttons: "Yes", "No".
I think in this case we could save both independence of ODF and interoperability
with xls.

Best regards,
 John Lynx

P.S.: big thanks for the patch! this bug is truly exasperating!
Comment 43 kyoshida 2008-05-10 16:10:20 UTC

You can remove the extra restriction I put in, to allow all characters in a
sheet name.  I don't have a strong opinion either way.  I think it's more
important that the patch be accepted (without the name restriction) than
deferring it until the consensus is reached on the formula sub-committee.  (We
can raise the issue there and discuss it, but let's not delay this patch because
of it).

Comment 44 kyoshida 2008-05-10 16:52:18 UTC
Created attachment 53527 [details]
revised patch to allow all characters in sheet names
Comment 45 kpalagin 2008-05-11 16:41:25 UTC
I suggest implementing restricted policy first - relaxing tight policy is so 
much easier (from publicity point of view), than tightening relaxed policy. 
Besides, our users already got used to existing restrictions, so disallowing : 
\ / ? * [ ] would not be a big usage problem.
If later ODF formula subcommittee's decides that compatibility with that other 
spreadsheet is not required we can easily relax our naming convention.

And by all means please target this for 3.0.

Comment 46 frank.loehmann 2008-05-22 09:33:43 UTC
This issue is important and listed on the quarterly review for Calc:
Therefore adjusting target to 3.x.
Comment 47 ooo 2008-06-17 22:20:07 UTC
In cws odff04:


I applied the last patch, but for now again added the restriction of characters
that Excel does not accept, we may want to remove that later. Plus reintroduced
automatic quoting of pure ASCII numeric names in ScCompiler::CheckTabQuotes() to
prevent any possible confusion that might arise when using those. The patch did
remove that.
Comment 48 daniel.rentz 2008-06-18 08:41:08 UTC
DR->ER: In our modern days, there is another place that may need adjustment:

oox/source/xls/worksheetbuffer.cxx, WorksheetBuffer::convertToValidSheetName
Comment 49 ooo 2008-06-18 13:11:22 UTC


Entirely removed method and call.
Comment 50 kpalagin 2008-06-19 12:07:53 UTC
This is absolutely terrific!

Kohei, Eike,
thank you very much!!!
Comment 51 Uwe Fischer 2008-06-19 13:46:09 UTC
So I assume when the odff04 CWS is integrated, I can change the Help to
something like the following. Please correct me if I'm wrong:

Sheet names can contain almost any printable character. 
If you use the sheet name in a reference and the sheet name contains a character
that is not a letter or numeral, you must enclose the sheet name in single quotes. 
If the sheet name in a reference contains a single quote, you must type two
consecutive single quotes in the reference.
Note: To allow smooth export to Excel format, the following characters are not
allowed in sheet names: \ / ? * [ ] 
Comment 52 ooo 2008-06-24 15:14:30 UTC
> Note: To allow smooth export to Excel format, the following characters
> are not allowed in sheet names: \ / ? * [ ] 

Almost ;-)  Additionally not allowed is the colon ':', and a single
quote is not allowed if it is the first or last character (leading or
trailing single quote).
Comment 53 ooo 2008-06-25 18:48:46 UTC
Description available at
Comment 54 ooo 2008-06-25 18:50:17 UTC
Reassigning to QA for verification.
Comment 55 oc 2008-07-01 12:44:01 UTC
*** Issue 91065 has been marked as a duplicate of this issue. ***
Comment 56 oc 2008-07-03 14:31:51 UTC
verified in internal build cws_odff04
Comment 57 oc 2008-07-04 11:04:03 UTC
Created attachment 54936 [details]
Comment 58 oc 2008-07-04 12:04:25 UTC
Created attachment 54937 [details]
Testdocuments for Test Case Specification
Comment 59 oc 2008-07-16 09:00:42 UTC
*** Issue 91696 has been marked as a duplicate of this issue. ***
Comment 60 Rainer Bielefeld 2008-09-09 07:27:11 UTC
*** Issue 93641 has been marked as a duplicate of this issue. ***
Comment 61 corigo 2008-09-09 09:56:30 UTC
Fixed? When? This could be fixed even more quickly by simply replacing the 
special characters with Unicode charset!
Comment 62 corigo 2009-04-15 11:17:55 UTC
3.0.0 build still won't allow * as a special character...
Comment 63 thorsten.ziehm 2009-07-20 14:40:30 UTC
This issue is closed automatically and wasn't rechecked in a current version of
OOo. This fixed issue should be integrated in OOo since more than half a year.
If you think this issue isn't fixed in a current version (OOo 3.1), please
reopen it and change the field 'Target Milestone' accordingly.

If you want to download a current version of OOo =>
If you want to know more about the handling of fixed/verified issues =>