What causes "UserWarning: Discarded range with reserved name" - openpyxl

Question

I have a simple EXCEL-sheet with names of cities in column A and I want to extract them and put them in a list:

def getCityfromEXCEL():
    wb = load_workbook(filename='test.xlsx', read_only=True)
    ws = wb['Sheet1']
    cityList = []

    for i in range(2, ws.get_highest_row()+1):
        acell = "A"+str(i)
        cityString = ws[acell].value
        city = ftfy.fix_text_encoding(cityString)            
        cityList.append(city)

getCityfromEXCEL()

With a small file that worked perfectly (70 rows). Now I'm processing a big file (8300 rows) and it gives me this error:

/Library/Python/2.7/site-packages/openpyxl/workbook/names/named_range.py:121: UserWarning: Discarded range with reserved name
  warnings.warn("Discarded range with reserved name")

but it does not abort. It just does not seem to continue anymore. Can someone tell me what might cause the error? Is it something in the .xlsx? Any special hints what I can look for?

score 17 · Accepted Answer · answered May 11 '15 at 15:57

17

It's supposed to be a friendly warning letting you know that some of the defined names are being lost when reading the file. Warnings in Python are not exceptions but informational notices.

Support for defined names is essentially limited to references to cell ranges in openpyxl at the moment. But they can refer to lots of other things like printing settings. However, if the objects/values they refer to are not preserved by openpyxl and the file is saved and later opened by Excel it might complain about the missing objects.

answered May 11 '15 at 15:57

Charlie Clark

18,477
4
49
55

1

How can I find out where this might have happened? – steph May 11 '15 at 18:02
Well, I found out that sheets exported from Google spreadsheets trigger this warning. If you copy/ paste the content the warning does not appear. So I suppose Google includes some presettings openpyxl doesn't like?! ... – steph May 12 '15 at 06:44
3

If you look at the source for the workbook (an Excel file is just a zip archive) you'll see the names that are defined. We exclude all names that start '_xlnm'. At a guess I'd suggest that Google is including some printer settings by default. In any case the warning is informative in case something is missing and you want to know why. – Charlie Clark May 12 '15 at 07:29
2

To find what was discarded, I unzipped the excel spreadsheet file to its own directory, then used grep: `grep -ri _xlnm C:\the\unzipped\dir` – nmz787 Aug 18 '15 at 22:33
@nmz787, thanks for that. I did that too for my Excel file, but it turns out I kind of need these values, so I think I cannot use openpyxl. – Prof. Falken Aug 31 '15 at 08:23

score 14 · Answer 2 · answered Jul 13 '16 at 16:00

14

If you want to ignore it:

import warnings
warnings.simplefilter("ignore")
wb = load_workbook(path)
warnings.simplefilter("default")

answered Jul 13 '16 at 16:00

e18r

7,578
4
45
40

score 9 · Answer 3 · answered Oct 30 '15 at 19:39

9

In my case this warning shows up when filtering is on one of my worksheets. I wanted to suppress the warning so that it didn't bother my users and I just put this line in my code before the openpyxl.load_workbook call:

warnings.simplefilter("ignore")

answered Oct 30 '15 at 19:39

intrepidhero

701
7
10

I got this warning too when my worksheet contains the auto_filter. Is it a bug of openpyxl? @Charlie Clark – Jruv Jan 10 '17 at 07:52

What causes "UserWarning: Discarded range with reserved name" - openpyxl

3 Answers3

Linked