UserWarning:Discarded range with reserved name & Corrupted saved Workbook

Issue #501 resolved
Benoit created an issue

I am using Anaconda 64x (Python v.2.7) and openpyxl==2.2.4 I open an xlsx file with openpyxl read_wb = openpyxl.load_workbook(z)

Playing with it, cells values and formula get out just fine: so far so good.

Then when I save the file I get the 'classic' discarded range with reserved name prompt (looked at the user forum of openpyxl)

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

Looking at the saved file, it lost some 300k in weight compared to the original.

Opening up the saved file, I get an error message that some content needs to be repaired

Excel does what it can and of course the file ends up badly messed up

Now I traced back the calculations down to a missing name range called 'tR' which is an empty single cell

Curiously enough, I call on openpyxl to get me 'tR' properties with
print read_wb.get_named_range('tR') I get:

<Worksheet "Depreciation">!$A

Uhm, no figure, no number, just !$A

So then I unzipped the .xlsx, went into \xl\workbook.xml to check it out:

<definedName name="tR" localSheetId="3">Depreciation!$A1</definedName>

Then the name manager in Excel tells me that tR refers to Depreciation!$A7 !!!!

So it seems that something low level overrides the instruction of workbook.xml, but despite looking at all the xml files one by one I cannot figure out what...

Comments (6)

  1. Benoit reporter

    whilst I'm at it, a beginner question:

    if I pull out the named ranges from the file like this; read_named_ranges = read_wb.get_named_ranges

    I get this kind of result: >>> <NamedRange "<Worksheet "InputData">!$G$14">, <NamedRange "<Worksheet "InputData">!$G$23">]

    But it doesn't give me the name of the range like e.g. 'tR'

    How could I fetch it?

  2. CharlieC

    Sorry, without the file it's really difficult to say. Basically the quickest thing would be to write your own parser based around the elements you need to extract.

  3. Log in to comment