Apache OpenOffice (AOO) Bugzilla – Issue 6087
ODFF: allow all characters in sheet names
Last modified: 2013-08-07 15:15:24 UTC
I attempted to name a sheet for an IP address of one of our switches ex. 192.168.0.254 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.
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 separator.
Assigning to myself.
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!! -T
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.
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?
Moving to the Spreadsheet component as a Feature Request.
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.
As an enhancement re-assigned to the requirements team. Set Prio to 3
*** Issue 52496 has been marked as a duplicate of this issue. ***
*** Issue 6732 has been marked as a duplicate of this issue. ***
*** Issue 55633 has been marked as a duplicate of this issue. ***
add the ms_interoperability keyword
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.
*** Issue 63932 has been marked as a duplicate of this issue. ***
*** Issue 68090 has been marked as a duplicate of this issue. ***
*** Issue 84615 has been marked as a duplicate of this issue. ***
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"?
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.
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 http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula 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.
For all the reasons given by others, PLEASE implement this change ASAP.
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 10.241.6.137 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. Thanks, Andrew McMillan.
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___.
Created attachment 52937 [details] how about this.
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 ?
I really like this kind of patch which removes lines of code and gives OOo more flexibility.
Yeah, nice one! Might need some tweaking in ScCompiler::NextSymbol() around parseAnyToken() to accept an embedded escaped (doubled) single quote (apostrophe). Thanks Eike
Created attachment 52974 [details] here's my take on extending this to recognize '' as an escaped literal '
Eike, please comment on new patch and if it can make into 3.0. Thanks. Regards, K. Palagin.
Will do.
I didn't want to spam everyone for every little typo, so there's a little additional fix here and there at http://cvs.fedora.redhat.com/viewcvs/*checkout*/devel/openoffice.org/openoffice.org-3.0.0.ooo6087.sc.sheetnames.patch
Caolan, 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. Kohei
Created attachment 53300 [details] additionally handle # in a sheet name
Yup, the '#' situation is now resolved. :-) Thanks.
Created attachment 53302 [details] and updated to feed a quoted chunk back on its own as a single token
Created attachment 53303 [details] current testcases
cmc->kohei: Does that resolve the ABS issue ? The testcase.ods shows the test cases that I'm aware of.
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.
Created attachment 53341 [details] slightly modified to support XL A1 and XL R1C1 syntax.
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.
Created attachment 53343 [details] fixed R1C1 parsing & invalidate certain characters.
Kohei, 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. Thanks Eike
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!
Eike, 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). Kohei
Created attachment 53527 [details] revised patch to allow all characters in sheet names
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. WBR, KP.
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project Therefore adjusting target to 3.x.
In cws odff04: sc/inc/compiler.hxx 1.37.36.1 sc/inc/document.hxx 1.112.34.1 sc/source/core/data/document.cxx 1.88.60.1 sc/source/core/tool/address.cxx 1.12.36.1 sc/source/core/tool/compiler.cxx 1.79.14.1 sc/source/filter/excel/excimp8.cxx 1.125.26.1 sc/source/filter/excel/exctools.cxx 1.61.66.1 sc/source/filter/excel/impop.cxx 1.94.62.1 sc/source/filter/excel/xicontent.cxx 1.31.64.1 sc/source/filter/excel/xilink.cxx 1.24.64.1 sc/source/filter/excel/xipivot.cxx 1.18.66.1 sc/source/filter/ftools/ftools.cxx 1.19.64.1 sc/source/filter/inc/ftools.hxx 1.23.66.1 sc/source/filter/xcl97/XclImpChangeTrack.cxx 1.32.66.1 sc/source/ui/vba/vbaworksheets.cxx 1.6.66.1 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.
DR->ER: In our modern days, there is another place that may need adjustment: oox/source/xls/worksheetbuffer.cxx, WorksheetBuffer::convertToValidSheetName
Done. oox/inc/oox/xls/worksheetbuffer.hxx 1.3.14.1 oox/source/xls/worksheetbuffer.cxx 1.4.12.1 Entirely removed method and call.
This is absolutely terrific! Kohei, Eike, thank you very much!!!
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: \ / ? * [ ]
@ufi: > 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).
Description available at http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=272
Reassigning to QA for verification.
*** Issue 91065 has been marked as a duplicate of this issue. ***
verified in internal build cws_odff04
Created attachment 54936 [details] TestCaseSpecification
Created attachment 54937 [details] Testdocuments for Test Case Specification
*** Issue 91696 has been marked as a duplicate of this issue. ***
*** Issue 93641 has been marked as a duplicate of this issue. ***
Fixed? When? This could be fixed even more quickly by simply replacing the special characters with Unicode charset!
3.0.0 build still won't allow * as a special character...
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 => http://download.openoffice.org/index.html If you want to know more about the handling of fixed/verified issues => http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues