"safe" reserved ranges are not read from workbooks

Issue #481 resolved
Rick Pelletier
created an issue

When reading a workbook, if there are any reserved ranges defined here, the read will discard them.

This breaks the ability to read autofilters properly, as the _xlmn.filterDatabase named range is used to hold the data for the filters.

From reading the Google Groups threads, it sounds like the discarding of reserved ranges was added as a protection from some kind of issue with the reserved ranges used for the printing-related functionality. I think there should be a finer-grained approach to discarding the ranges.

Comments (9)

  1. Charlie Clark

    The warning is there to say inform users that the file cannot be preserved as is. Defined Names like this are generally linked to blobs in the archive that are not preserved. This reduces the chance that the resulting file is unreadable.

  2. Rick Pelletier reporter

    Are you aware of any further documentation on how specifically the '_xlmn.filterDatabase' range is used? Or of any describing whether user-created ranges can refer to blobs? I suspect that this specific range, despite being generated internally by Excel, will never refer to anything but a cell range.

    Or another idea - maybe this functionality makes more sense to perform when writing spreadsheets back out instead of on read? Then the users could examine each defined name and decide for themselves what to do with it.

  3. Charlie Clark

    I have no idea about any of the reserved names. I suspect some of them could be like some of the formula extensions and essentially harmless in openpyxl if passed through. Not going to start taking pot luck, though.

    From 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 repeat at 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. """

  4. Rick Pelletier reporter

    Yeah.. it sounds like they should all be pretty safe range definitions, but I understand not wanting to mess with a potentially fragile system (especially when the PR submitter doesn't include tests :) ).

    I'll probably try to set my specific project up to install from my branch, then try out 2.4 when it arrives.


  5. Charlie Clark

    Support for this kind of stuff would benefit from someone prepared to champion it and with access to relevant files. It's mainly due to lack of this that support is currently so poor.

    Whether something is in a particular future version is largely down to whether code and tests are supplied.

  6. Log in to comment