workbook containing worksheet with space in name and print titles fails to open

Issue #690 resolved
Jacob C
created an issue

Attempting to open a workbook that contains any worksheets with a space in the name and a print title will result in an error as below: (Python 3.5, Openpyxl 2.4)

Traceback (most recent call last):
  File "C:/Users/user/pythonProjects/testproject/testpy/tester.py", line 5, in <module>
    EngineerMetrics = ox.load_workbook("book1.xlsx", data_only=True, read_only=True)
  File "C:\Users\user\AppData\Roaming\Python\Python35\site-packages\openpyxl\reader\excel.py", line 245, in load_workbook
    parser.assign_names()
  File "C:\Users\user\AppData\Roaming\Python\Python35\site-packages\openpyxl\packaging\workbook.py", line 79, in assign_names
    rows, cols = _unpack_print_titles(defn)
  File "C:\Users\user\AppData\Roaming\Python\Python35\site-packages\openpyxl\workbook\defined_name.py", line 48, in _unpack_print_titles
    return m.group('rows'), m.group('cols')
AttributeError: 'NoneType' object has no attribute 'group'

It took a little bit to isolate, attached is a workbook that will demonstrate the issue.

With a workbook containing a "Sheet 1"

def _unpack_print_titles(defn):
    """
    Extract rows and or columns from print titles so that they can be
    assigned to a worksheet
    """
    m = TITLES_REGEX.match(defn.value)    # defn.value = '\\'Sheet 1\\'!$A:$A,\\'Sheet 1\\'!$1:$1'
    return m.group('rows'), m.group('cols')

With a workbook containing Sheet1

def _unpack_print_titles(defn):
    """
    Extract rows and or columns from print titles so that they can be
    assigned to a worksheet
    """
    m = TITLES_REGEX.match(defn.value)   # defn.value = 'Sheet1!$A:$A,Sheet1!$1:$1'
    return m.group('rows'), m.group('cols')

Comments (12)

  1. Charlie Clark

    Thanks for the report and the sample file. Looks like we might want remove the escaping from and titles before we extract them (Excel needs the escaping but conceals it). Or, I could just disable this feature! ;-)

    I think we do have a regex that can find the titles more reliably.

  2. Charlie Clark

    Your post yesterday actually referred to the print area and would be a separate issue. The problem will only be resolved for print titles if you're working with a checkout.

  3. Jess

    I have the same error, but it is not because of a space in a title. I created fake excel sheets to test that was not the issue. Can you please advise, @Charlie Clark if you are recommending to checkout your branch for the fix?

  4. Ranjith

    I am not sure I understand this correctly. I had opened the issue #709, which was marked as a duplicate of this issue. It has been fixed in branch 2.4 and I have the version 2.4.0 installed and this bug definitely exists in that branch.

    You are recommendation is to use a checkout of that branch, but I have deployment scripts written and I would really like to use pip instead.

    Any idea when this fix would go on to the version at pip?

  5. John Sivak

    Hi Charlie,

    I didn't see a fix for the Print Titles issue in the default branch, so I forked and submitted a pull request #150 with my hack-fix; I don't like how I handled the second SHEET_TITLE reference so I'm willing to work on it if you have a suggestion.

  6. Log in to comment