Issue 124256 - Named ranges not imported from Microsoft XLSX file
Summary: Named ranges not imported from Microsoft XLSX file
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 4.1.0-dev
Hardware: PC All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2014-02-18 11:57 UTC by Michael Rutherford
Modified: 2017-05-20 09:54 UTC (History)
5 users (show)

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

XLSX file containing a named range (12.57 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-02-18 11:57 UTC, Michael Rutherford
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Michael Rutherford 2014-02-18 11:57:50 UTC
Created attachment 82614 [details]
XLSX file containing a named range

I have an Microsoft Excel XLSX file (attached) that contains a named range.  When I import this file in to Calc, this named range does not show up (Data -> Select Range).

Possible related issue:
Comment 1 Michael Rutherford 2014-02-18 12:06:08 UTC
Build installed:  17 Feb development snapshot
Comment 2 Edwin Sharp 2014-02-18 12:26:21 UTC
Data - Select Range... -> empty although Sheet1 F2:J10 is data.
AOO410m1(Build:9750)  -  Rev. 1565724
Win 7
Comment 3 j.nitschke 2014-02-18 14:53:36 UTC
I think there is a misunderstanding. Named cells/ranges are not database ranges.
You can select them with the name box next to the formula bar.
Or Insert -> Names.. -> Define..

but there is indeed an issue:

attachment 82614 [details] seems to have 6 named ranges, dissected:
<definedName name="data" localSheetId="0">Sheet1!$F$2:$J$10</definedName>
<definedName name="data" localSheetId="1">Sheet1!$L$2:$P$10</definedName>
<definedName name="data">Sheet1!$A$2:$D$10</definedName>
<definedName name="test" localSheetId="0">Sheet1!$F$1</definedName>
<definedName name="test" localSheetId="1">Sheet1!$L$1</definedName>
<definedName name="test">Sheet1!$A$1</definedName>

*AOO 3.4.1* imports
data $Sheet1.$F$2:$J$10
data_1 $Sheet1.$L$2:$P$10
data_2 $Sheet1.$A$2:$D$10
test $Sheet1.$F$1
test_1 $Sheet1.$L$1
test_2 $Sheet1.$A$1
which are all available through the name box

*AOO 4.1* (rev. 1568575) imports
data $Sheet1.$A$2:$D$10
data    (Sheet1) $Sheet1.$F$2:$J$10
data    (Sheet2) $Sheet1.$L$2:$P$10
test $Sheet1.$A$1
test  (Sheet1) $Sheet1.$F$1
test_1 (Sheet2) $Sheet1.$L$1
data and test are available through the name box
but data and test are listed 3 times the (Sheetx)-part doesn't show
and it always selects the first range

the original report is not an issue but
the faulty is import is a regression
Comment 4 Michael Rutherford 2014-02-18 15:00:55 UTC
Ahh, yes, I see this now....thanks for the clarification regarding named ranges.  Shall I keep this bug report open for the regression issue you mention?  It would probably make sense to change the one liner description of we do.
Comment 5 Edwin Sharp 2014-02-18 15:16:40 UTC
Thank you j.nitschke

IMHO this bug should be used as an enhancement for better wording to reduce chance for confusion with databases.
The findings of comment 3 should be a new bug.
Comment 6 Oliver Brinzing 2014-02-19 11:54:06 UTC
Comment 7 j.nitschke 2014-02-19 13:09:22 UTC
I digged a bit into the localSheetId feature and checked how it should work:

<definedName name="test" localSheetId="0">Sheet1!$F$1</definedName>
<definedName name="test" localSheetId="1">Sheet1!$L$1</definedName>
<definedName name="test">Sheet1!$A$1</definedName>

If you use the named range "test" in Sheet1 it should use the values in $Sheet1.$F$1 (sheet1 has localSheetId ="0")

Using "test" in Sheet2 it uses the value $Sheet1.$L$1 (sheet2 has localSheetId ="1")

in all other sheets the value from $Sheet1.$A$1 is used

This works *perfect* thanks to Bug 120478 - Name Scope enhancement for spreadsheet

there are only 2 problems
* the names are listed more than once (minor problem)
* if you pick a name it always marks the cell without localSheetId
  it should check on which sheet you are and show the named range used for this sheet, else this feature is very confusing

i.e. if you select test from the name box in Sheet1 it should jump to Sheet1!$F$1
  on Sheet2 it jumps to $Sheet1.$L$1 and on Sheet3 it jumps to Sheet1!$A$1

If ODF specs don't have a similar feature i would call this an enhancement.

This said it's not a faulty import and not a regression.
Comment 8 Rainer Bielefeld 2014-02-22 07:56:29 UTC
No Idea what the problem here might be. Do we only have a wrong description of an existing bug in report? Or in Comment 2? Or are's observations something completely different? Further research required.
Comment 9 Rainer Bielefeld 2014-02-22 09:35:53 UTC
I agree with Edwin's Comment 5:
(a) I close this one because it's not an issue, but user error
(b) Submitted new Bug Report for's results in Comment 3:
    "Bug 124293 - Section Range names truncated from underscore to end 
    in MSO .XLSX"

Can you please check whether I correctly cited all your results?