NamedRangeException: Invalid named range string: "0"

Issue #168 resolved
Jared Thompson
created an issue

ISSUE: When using a dynamic named range (for instance with a chart) the above error occurs.

DESCRIPTION: Example, I have in Name Manager a named range called 'pieD' and it refers to '=OFFSET(rep!$AK$1,0,0,COUNT(rep!$AK$1),1)'

This will count items in a range and return it so my Data range for a chart can be dynamic. This has always worked fine in excel, but when I try to import this workbook into openpyxl it throws the error.

NamedRangeException                       Traceback (most recent call last)
<ipython-input-1-280326684a66> in <module>()
     44     shutil.copyfile('basic_trial.xlsx',workbook_iter_name)
     45     #load the workbook into memory
---> 46     primary = openpyxl.load_workbook(workbook_iter_name)
     47     cur = conn.cursor()
     48     cur.execute("""SELECT distinct rep_name_title_ from source_table where location_name__cor_ = %s limit 3""",(distinct_stores[0][each],))  #USING LIMIT HERE !!!!!!!!!!!!!

/usr/local/lib/python2.7/dist-packages/openpyxl/reader/excel.pyc in load_workbook(filename, use_iterators)
    114     try:
--> 115         _load_workbook(wb, archive, filename, use_iterators)
    116     except KeyError:
    117         e = exc_info()[1]

/usr/local/lib/python2.7/dist-packages/openpyxl/reader/excel.pyc in _load_workbook(wb, archive, filename, use_iterators)
    158         wb.add_sheet(new_ws, index=i)
--> 160     wb._named_ranges = read_named_ranges(, wb)

/usr/local/lib/python2.7/dist-packages/openpyxl/reader/workbook.pyc in read_named_ranges(xml_source, workbook)
    120             if valid:
    121                 if refers_to_range(name_node.text):
--> 122                     destinations = split_named_range(name_node.text)
    124                     new_destinations = []

/usr/local/lib/python2.7/dist-packages/openpyxl/namedrange.pyc in split_named_range(range_string)
     77         match = NAMED_RANGE_RE.match(range_string)
     78         if not match:
---> 79             raise NamedRangeException('Invalid named range string: "%s"' % range_string)
     80         else:
     81             match = match.groupdict()

NamedRangeException: Invalid named range string: "0"

Comments (8)

  1. Jared Thompson reporter

    I have attached a simple example of a workbook throwing the noted error upon import with openpyxl.

    The data for the chart is in columns M:N

    You can add/remove data in that column (going from bottom to top) and the chart will dynamically change to adjust.

    The only exception is to make sure something is always in M1 and N1 else it will throw an error dialog box, but once data is entered in it will resume proper functioning.

  2. Jared Thompson reporter

    I unzipped the workbook and here is the <definedNames> section in the workbook.xml file:

    <definedNames><definedName name="pieD">OFFSET(rep!$AK$1,0,0,COUNT(rep!$AK$1),1)</definedName><definedName name="pieN">OFFSET(rep!$AJ$1,0,0,COUNT(rep!$AK$1),1)</definedName></definedNames>

  3. Log in to comment