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?

Answer 1

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.

Answer 2

If you want to ignore it:

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

Answer 3

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")