Range with reserved name

[Laurent Bristiel]

Hello,

I am loading an xlsx document (complex, that I did not produce myself) with a simple:
    wb = load_workbook(filename='myfile.xlsx')
but I get this error:
    openpyxl/workbook/names/named_range.py:121: UserWarning: Discarded range with reserved name
    warnings.warn("Discarded range with reserved name")

So checking the code I see we exclude some ranges which have name containing some specific chars:
    DISCARDED_RANGES = re.compile("^[_xnlm.]")

I did not find out how to locate those named range yet with LibreOffice,
but I wondered what was the rational behind this exclusion... anyone knows?
(and if anyone know how I can locate those named ranges in my doc, I am interested as well...)

Thanks,
Laurent

[Charlie Clark]

Am .11.2014, 21:30 Uhr, schrieb Laurent Bristiel <lau...@bristiel.com>:

> I did not find out how to locate those named range yet with LibreOffice,
> but I wondered what was the rational behind this exclusion... anyone
> knows?
> (and if anyone know how I can locate those named ranges in my doc, I am
> interested as well...)

From the § 18.2.5 in the specification:
"""
Specifies the name that appears in the user interface for the defined
name. This attribute is required. The following built-in names are defined
in this SpreadsheetML specification:

* Print
* _xlnm .Print_Area: this defined name specifies the workbook's print area.
* _xlnm .Print_Titles: this defined name specifies the row(s) or column(s)
to repeatat the top of each printed page.

* Filter & Advanced Filter
* _xlnm .Criteria: this defined name refers to a range containing the
criteria values to be used in applying an advanced filter to a range of
data.
* _xlnm ._FilterDatabase: can be one of the following
a. this defined name refers to a range to which an advanced filter has
been applied. This represents the source data range, unfiltered.
b. This defined name refers to a range to which an AutoFilter has been
applied.
* _xlnm .Extract: this defined name refers to the range containing the
filtered output values resulting from applying an advanced filter criteria
to a source range.

* Miscellaneous
* _xlnm .Consolidate_Area: the defined name refers to a consolidation area.
* _xlnm .Database: the range specified in the defined name is from a
database
data source.
* _xlnm .Sheet_Title: the defined name refers to a sheet title.

Built-in names reserved by SpreadsheetML begin with "_xlnm.". End users
shall not use this string for custom names in the user interface.
"""

We exclude these because we can't resolve them, though I think we should
probably pass them through. Range names in Excel are very poorly defined.
See my post from 13th October for more information.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Laurent Bristiel]

ok, thanks for you very quick answer.
That helped me to see that the named range I have is "_xlnm ._FilterDatabase" coming from "a range to which an advanced filter has been applied".
So to avoid this warning on loading my workbook, I should give an alternate name to this range?
I understand that this is a reserved name for a specific internal need of Excel, but nnot why it causes a problem for the lib, and nor how I can workaround this situation.

Thanks,
Laurent

PS: and sorry to jump in this group with not much knowledge about XLS and your lib. That's just happens to be the first problem I encounter with the lib (which worked great besides that so far. Thanks for that!)

[Charlie Clark]

Am .11.2014, 22:20 Uhr, schrieb Laurent Bristiel <lau...@bristiel.com>:

> ok, thanks for you very quick answer.
> That helped me to see that the named range I have is "_xlnm
> ._FilterDatabase" coming from "a range to which an advanced filter has
> been
> applied".

> So to avoid this warning on loading my workbook, I should give an
> alternate
> name to this range?

No and I assume there is a reason why your file has this range.

> I understand that this is a reserved name for a specific internal need of
> Excel, but nnot why it causes a problem for the lib, and nor how I can
> workaround this situation.

It's a warning that the range is being ignored by openpyxl. Previously it
was being silently ignored.

[fpga...@gmail.com]

Hi All,

I'm having a similar problem with an excel spreadsheet; but for me this code rejects a named range that just starts with the letter "l" (lower case "L").

Isn't that what:

   DISCARDED_RANGES = re.compile("^[_xnlm.]")

would do? Reject any name that begins with any of the characters in the []? I think that's too broad a set of names, considering the requirement Charlie pointed out.

In my local copy I've changed the code to:

   DISCARDED_RANGES = re.compile("^_xnlm.")

does that seem right? I have a spreadsheet with both a named range of "_xnlm.foo" and one of "lsync_found", instead of both being rejected, now only "_xnlm.foo" is rejected.

Regards,

- Jay

[patrick...@gmail.com]

I agree with this correction.  I've changed the python file on my computer and it now works as advertised.

Thanks Jay for pointing this out!

[Charlie Clark]

Am .08.2015, 01:21 Uhr, schrieb <patrick...@gmail.com>:

> I agree with this correction. I've changed the python file on my
> computer
> and it now works as advertised.
> Thanks Jay for pointing this out!

Having looked again at the original e-mail. I've corrected the regex and
added a test to resolve. It's really helpful if people submit bug reports
for this kind of thing and, especially, where the solution is known,
submit a pull request with a relevant unit test. A bug report means it's
less likely to be forgotten about and pull requests with tests are always
favourably received.

[Thomas Nygårds]

Hi!

I get this warning
...\openpyxl\workbook\names\named_range.py:125: UserWarning: Discarded range with reserved name

warnings.warn("Discarded range with reserved name")

although I open a file that only has been written with openpyxl and not touched by Excel.

The reserved name that as rejected was:
_xlnm._FilterDatabase

For information one of the sheets contains an autofilter created by openpyxl.
Should the warning really be given for such a file?

Regards
Thomas

[Charlie Clark]

Am .04.2016, 14:05 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> Hi!

> I get this warning
> ...\openpyxl\workbook\names\named_range.py:125: UserWarning: Discarded
> range with reserved name warnings.warn("Discarded range with reserved
> name")
> although I open a file that only has been written with openpyxl and not
> touched by Excel.

> The reserved name that as rejected was:
> _xlnm._FilterDatabase

> For information one of the sheets contains an autofilter created by
> openpyxl.

> Should the warning really be given for such a file?

Well, seeing as the range is being dropped, yes, I think the warning is
fair.

The problem is related to the implementation of definedNames per 2.4: the
code attempted to treat them all as ranges within individual worksheets.
Whereas they are, in fact, just an assortment of constants. This
particular constant is created by Excel when filters are added although
I'm not sure it's strictly necessary: it contains no information that is
not already in the worksheet.

In 2.4 I've completely rewritten the code so that a), this kind of warning
won't popup again and b) all relevant special constants (including print
titles and areas) are properly supported and will thus survive
roundtripping.