openpyxl.shared.exc.NamedRangeException: Invalid named range string: "0"

Issue #292 duplicate
Anonymous created an issue

I get this error when trying to open a particular xlsx file. Others have worked, but this one gives me this error. This is the traceback:

Traceback (most recent call last): File "C:\Users\uidg8525\workspace\XML_Builder\xmlbuilder.py", line 11, in <module> wb = load_workbook(filename+'.xlsx', data_only=True) File "C:\Python27\myLibraries\ericgazoni-openpyxl-e3bec545653f\openpyxl\reader\excel.py", line 136, in load_workbook _load_workbook(wb, archive, filename, use_iterators, keep_vba) File "C:\Python27\myLibraries\ericgazoni-openpyxl-e3bec545653f\openpyxl\reader\excel.py", line 212, in _load_workbook wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb) File "C:\Python27\myLibraries\ericgazoni-openpyxl-e3bec545653f\openpyxl\reader\workbook.py", line 117, in read_named_ranges destinations = split_named_range(node_text) File "C:\Python27\myLibraries\ericgazoni-openpyxl-e3bec545653f\openpyxl\namedrange.py", line 76, in split_named_range raise NamedRangeException('Invalid named range string: "%s"' % range_string) openpyxl.shared.exc.NamedRangeException: Invalid named range string: "0"

And this is the piece of code that I use to open the file:

from openpyxl import load_workbook import os

filename = raw_input("\nEnter filename: ") filename = os.path.splitext(filename)[0]

wb = load_workbook(filename+'.xlsx', use_iterators = True, data_only=True) ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])

Comments (12)

  1. Ștefan Urziceanu

    What I attached is the XLSX document. I deleted all sheets but the first, because it is a company report. Nevertheless, the error persists with the document in this form. Let me know if you need additional info.

  2. Charlie Clark

    The problem is caused by the NamedRange

    OFFSET([2]Dropdown!$A$2,0,0,COUNTA([2]Dropdown!$A$1:$A$50),1)
    

    For which we simply don't have the mechanics to work with.

    Possible solutions:

    • someone provides the code via a pull request for this and similar ranges
    • such ranges are (silently) skipped when reading files

    The second proposal code be included in 1.9.

  3. Charlie Clark

    As usual, Microsoft's documentation on this kind of range is useless. But it looks like the use case is limited and, therefore, we might be able to handle it at some point.

    It seems there is something called a dynamic range. Assuming this is only way to set a range programmatically then it could be handled but will probably require a new (immutable?) range type.

  4. Charlie Clark

    I hope to have something in 1.9 that can at least skip these kind of ranges with a warning.

    If it were only a case of OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) then it would probably be quite easy to parse that. However, you've also got the following monster in there:

    <definedName name="Process" localSheetId="9">OFFSET(INDIRECT(ADDRESS(MATCH(INDEX(val1cell,ROW()-2,1),Area,0)+1,2,,,"sheet3")),0,0,COUNTIF(Area,INDEX(val1cell,ROW()-2,1)),1)</definedName>
    

    And I don't see us adding the mechanics necessary to handle that any time soon.

    In the meantime hardcoding is the only way around the problem.

  5. Charlie Clark

    I've no idea. openpyxl was initially a port but was never kept in sync with PHPExcel - coding styles and customer requirements are too different for this. We do, however, know that the PHPExcel project spent a lot of time working on a formula engine so it is possible that it might work.

    For openpyxl I think the best we can expect in the short term is that you can open the files (but lose the ranges).

  6. Log in to comment