Issue 124293 - Section Range names truncated from underscore to end in MSO .XLSX
Summary: Section Range names truncated from underscore to end in MSO .XLSX
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 4.0.0
Hardware: All Windows 7
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: interop_OOXML, regression
Depends on:
Reported: 2014-02-22 09:32 UTC by Rainer Bielefeld
Modified: 2014-03-10 13:48 UTC (History)
6 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---

Results (16.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-22 09:32 UTC, Rainer Bielefeld
no flags Details
Concept of a fix. (1.75 KB, patch)
2014-03-04 09:51 UTC, Andre
no flags Details | Diff
Better fix. (14.39 KB, patch)
2014-03-07 09:53 UTC, Andre
no flags Details | Diff
Additional changes. (2.25 KB, patch)
2014-03-10 13:48 UTC, Andre
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description Rainer Bielefeld 2014-02-22 09:32:49 UTC
Created attachment 82664 [details]

This one is a Spin Off from Bug 124256

Steps how to reproduce Reproducible with server installation of "AOO 4.1.0-Dev – English UI / German locale - [AOO410m1(Build:9750) - Rev. 1566800 - 2014-02-12]" on German WIN7 Home Premium (64bit)", own separate user profile.

1. Open attachment 82614 [details] “XLSX file containing a named range“ 
   in AOO 4.1-dev and a copy of the document in AOO 3.4.1 
2. In both do <control+f3>
3. Compare Range names in both Versions
    Bug: Crippled Names in AOO 4.1, truncated from underscore to end

Additional Info
(a) I listed results found by 
    In attached document
(b) We need to find out with what version the problem started
Comment 1 Rainer Bielefeld 2014-02-22 09:47:15 UTC
Already Reproducible with server installation of " AOO 4.0.0-Dev – English UI / German locale [AOO400m1(Build:9700) - Rev. 1457992 – Rev.1457606 ((2013-03-19))]" on German WIN7 Home Premium (64bit)", own separate user profile
Comment 2 Rainer Bielefeld 2014-02-22 09:49:22 UTC
(c) Problem seems to be limited to XLSX import. Range Names are ok when I 
    open .ods created from reporter#s sample with AOO 3.4.1 
    with AOO 4.1-dev
Comment 3 j.nitschke 2014-02-22 12:42:47 UTC
it's not a defect, more a bad and confusing user experience imo

There is a feature in xls/xlsx which allows one range name to have different meanings. Depending on what sheet you use the name, it can refer to different ranges.

I tried to explain it in bug 124256, comment 7
AOO should show the name only once and select a different range depending which sheet is shown.
AOO lists all names but shows the user somehow on what sheet what range is valid, like in name define view but better. (if you don't know about the feature the name define view is still confusing)
Comment 4 Andre 2014-02-28 15:57:34 UTC
The name box is filled in ScPosWnd::FillRangeNames() (sc/source/ui/app/inputwin.cxx).  That names are still what is listed in the XSLX file, without any underscore or anything else.
Comment 5 Andre 2014-02-28 16:01:22 UTC
The named ranges are imported in DefinedNames::importDefinedName(const AttbibuteList&) (in oox/source/xls/defnamesbuffer.cxx).
Comment 6 Rainer Bielefeld 2014-02-28 16:56:24 UTC
So it seems a "crutch" what has been used in older OOo versions no longer is necessary? In old days identical names were not allowed for sections, still so for manual entries, but now identical names are allowed for import and work?
Comment 7 Andre 2014-03-03 08:15:35 UTC
@Rainer: I have not finished my analysis yet.  Above comments are more a reminder for me that allow me to continue after the weekend.
Comment 8 Andre 2014-03-03 13:04:30 UTC
Looks like the commits for bug 120478 are responsible for this bug: the sheet id is now used internally to distinguish between the ranges.  But the UI has not been adapted.  It only sees the range names, not the sheet ids.  Therefore the sheet id can not be used to look up the range.
Comment 9 Andre 2014-03-04 09:51:54 UTC
Created attachment 82787 [details]
Concept of a fix.

This patch outlines the idea for a fix: insert entries into the ScPosWnd (that is the implementing class of the Name Box control in the formula tool bar) that encode both the range name and the sheed id, much like before the regression was introduced.  Don't change the range names in the ScRangeData structures.
Comment 10 Andre 2014-03-04 10:29:29 UTC
@all: What would be the preferred name?  Before the regression was introduced I think it was <range-name>_<sheet-id>.  At the moment it is just <range-name>.  We need a name that contains both (range-name and sheet-id).
Comment 11 Andre 2014-03-04 13:56:22 UTC
This issue was caused by commits for bug 120478.
Comment 12 Andre 2014-03-04 14:28:48 UTC
The navigator also shows the named ranges and seems to create their names with its own algorithm (also without the underscore and sheet id).
What a mess.
Comment 13 Andre 2014-03-07 09:53:13 UTC
Created attachment 82812 [details]
Better fix.

A better fix that displays ranges as "<name> (<sheet>)" both in the name box and the navigator.  Internally a different naming scheme "<name>_<sheet>" is used because that has worked before and should not interfere with other range or database names too much.

It even works to change the range via the navigator and then have the name box updated (the reverse does not work due to missing functionality in the navigator).

@Rainer: Comment 2 mentions that the named ranges work when imported from .ods files written by AOO 3.4.1.  Can you attach such a file?
Comment 14 Andre 2014-03-10 13:48:00 UTC
Created attachment 82833 [details]
Additional changes.

A few changes missing from the last patch.