Issue 124293

Summary: Section Range names truncated from underscore to end in MSO .XLSX
Product: Calc Reporter: Rainer Bielefeld <rainerbielefeld_ooo_qa>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: awf.aoo, elish, issues, j.nitschke, oliver.brinzing, rainerbielefeld_ooo_qa
Version: 4.0.0Keywords: interop_OOXML, regression
Target Milestone: ---   
Hardware: All   
OS: Windows 7   
Issue Type: DEFECT Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---
Attachments:
Description Flags
Results
none
Concept of a fix.
none
Better fix.
none
Additional changes. none

Description Rainer Bielefeld 2014-02-22 09:32:49 UTC 
Created attachment 82664 [details]
Results

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 
    <https://issues.apache.org/ooo/show_bug.cgi?id=124256#c3>
    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.
or
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.