read_only cell row & column attributes are sometimes None

Issue #601 closed
Eric G
created an issue

Apologies if this is a duplicate, I didn't see any open issues related to it.

(Also, although it doesn't matter for my case, read-only cell column values are numeric, vs. regular cell column values are strings.)

Read-only cells:

import openpyxl
wb_ro = openpyxl.load_workbook('./scratch/test.xlsx', read_only=True)
ws_ro = wb_ro.get_sheet_by_name('Sheet1')
for row_ro in ws_ro.rows:
   print [(c.row,c.column) for c in row_ro]

Results:

[(None, None), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (
1, 10), (1, 11), (1, 12), (1, 13), (None, None), (None, None)]
[(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8), (2, 9), (2, 10)
, (2, 11), (2, 12), (2, 13), (2, 14), (2, 15)]
[(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6), (3, 7), (3, 8), (3, 9), (None,
None), (None, None), (None, None), (None, None), (None, None), (None, None)]
[(None, None), (4, 2), (4, 3), (4, 4), (4, 5), (4, 6), (4, 7), (4, 8), (4, 9), (
4, 10), (4, 11), (4, 12), (4, 13), (None, None), (None, None)]
[(None, None), (5, 2), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7), (5, 8), (5, 9), (
5, 10), (5, 11), (5, 12), (5, 13), (None, None), (None, None)]

Non read-only cells:

import openpyxl
wb = openpyxl.load_workbook('./scratch/test.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
for row in ws.rows:
   print [(c.row,c.column) for c in row]

Results:

[(1, 'A'), (1, 'B'), (1, 'C'), (1, 'D'), (1, 'E'), (1, 'F'), (1, 'G'), (1, 'H'),
 (1, 'I'), (1, 'J'), (1, 'K'), (1, 'L'), (1, 'M'), (1, 'N'), (1, 'O')]
[(2, 'A'), (2, 'B'), (2, 'C'), (2, 'D'), (2, 'E'), (2, 'F'), (2, 'G'), (2, 'H'),
 (2, 'I'), (2, 'J'), (2, 'K'), (2, 'L'), (2, 'M'), (2, 'N'), (2, 'O')]
[(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'), (3, 'E'), (3, 'F'), (3, 'G'), (3, 'H'),
 (3, 'I'), (3, 'J'), (3, 'K'), (3, 'L'), (3, 'M'), (3, 'N'), (3, 'O')]
[(4, 'A'), (4, 'B'), (4, 'C'), (4, 'D'), (4, 'E'), (4, 'F'), (4, 'G'), (4, 'H'),
 (4, 'I'), (4, 'J'), (4, 'K'), (4, 'L'), (4, 'M'), (4, 'N'), (4, 'O')]
[(5, 'A'), (5, 'B'), (5, 'C'), (5, 'D'), (5, 'E'), (5, 'F'), (5, 'G'), (5, 'H'),
 (5, 'I'), (5, 'J'), (5, 'K'), (5, 'L'), (5, 'M'), (5, 'N'), (5, 'O')]

Comments (8)

  1. CharlieC

    What you're seeing is the intended behaviour. openpyxl is creating cells to fill the "holes" in the sheet where there are no cells so that each row is consistently sized.

  2. Eric G reporter

    OK, I understand now. It's a bit unexpected -- I can understand value being none but row and column? But if these are 'virtual' cells I can see it might be a pain to implement.

  3. CharlieC

    Yes, they're only filler cells: Excel allows rows to be "ragged", but this could be a problem for client code expecting anything of particular length: for r in ws.rows: c = row[4]…

    It would be possible to add coordinates but I don't see that anything could be gained by this.

  4. Eric G reporter

    It just means if you want to keep track of which row you're on, you have to do it externally, or do something like this:

    for r in ws.rows:
      rownum = next(c.row for c in r if not c.row is None)
      #  but then what about blank lines, etc.
    
  5. Log in to comment