Apache OpenOffice (AOO) Bugzilla – Issue 126748
Export to Microsoft Excel 97/2000/XP .xls format should truncate long sheet names
Last modified: 2016-04-12 16:47:39 UTC
When I export an ODS file with long sheet names to a Microsoft Excel 97/2000/XP XLS file, the sheet names appear not to be truncated to a length acceptable to MS Excel, thus causing problems in opening the file.
What do you means by "long name"? Could you provide a sample document to reproduce? Please report back these informations, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.
Created attachment 85218 [details] Test file which, when saved as an Excel file, poses problem due to long sheet names It appears that the issue does not arise when one sheet has a long file name, but it does arise when two or more do.
Created attachment 85219 [details] File resulting from saving test.ods in Excel format
Created attachment 85414 [details] Sheet name in MS-Excel is limited at 31 characters So not a bug for AOO but maybe an enhancement for MS-Excel.
Closed
(In reply to oooforum (fr) from comment #5) > Created attachment 85414 [details] > Sheet name in MS-Excel is limited at 31 characters > > So not a bug for AOO but maybe an enhancement for MS-Excel. How can we claim a limitation of .xls that we do not honor is not a bug for us? That is not our format to define. Truncating the names would be an appropriate workaround, although not a great one. (It is possible to create duplicates that way, and that would have to be avoided too.) We already provide a warning that not all OpenOffice Calc features are preserved. Re-opening.
It will be interesting to see how Excel handles an .ods that has long sheet names.
Created attachment 85415 [details] Excel Truncation of Long .ods Sheet Names This is Attachment 85218 [details] opened in Excel 2016 to demonstrate that, for import of ODF Spreadsheets produced by AOO Calc, a sheet name longer than 31 characters will be truncated to 31 characters.
Created attachment 85416 [details] Test with Two Sheets whose Names collide on trucation This is a version of Attachment 85218 [details] that has two long sheet names that collide when truncated to 31 characters. This may be the actual complaint with exports to .xls format.
Created attachment 85417 [details] Excel Objection to Collision of Truncated Sheet Names This is Excel's complaint when Attachment 85416 [details] (an .ods) is opened in Excel 2016 as an ODF Spreadsheet. Note that there is an offer to attempt repair. In this case, Excel cannot handle the import of the .ods because of its internal limitation on sheet names. Because the .ods is valid ODF, that is not an OpenOffice Calc problem. Producing a 31-character collision in an .xls file is.
Created attachment 85418 [details] Repaired .ods Import to Excel This is the result of opening Attachment 85416 [details] after requesting that repair be attempted when the message of Attachment 85417 [details] occurs. It is apparent that the second sheet has had its name changed completely.
Created attachment 85419 [details] Confirmation that only sheet name changed by Excel This is the same Excel session as Attachment 85418 [details], but with the second sheet selected. This reveals the full name and confirms that the data has been preserved. It appears that Excel will truncate sheet names that exceed its limitation on their length, but it has a problem if the result is a collision. The solution that will applied, when permission is given, is to rename all but one of the truncation-produced duplicates. We can do two things: 1. Add some caveats around Calc - Excel interop to indicate that sheet names are truncated via .xls or .ods and should be unique in the first 31 characters. 2. On saving to .xls, provide some sort of truncation to honor the sheet-name length constraint. That may be difficult and will certainly not happen any time soon. In the meantime, this is a confirmed open issue.