0

I need to iterate over a disjoint range that has 'A1', 'O1', 'R1:S1' structure. There are many workbooks, and there are many rows to iterate in respective sheet of each workbook (say, rows 21, 24, 31:35, 40 of 'Sheet1'). Is there a simple way to iterate over it with openpyxl, or other library?

At the moment I use the following for each row, which is unacceptable.

wb = openpyxl.load_workbook(filename)

sheet = wb['Sheet1']
for cellObj in sheet['A1:S2']:
    for cell in cellObj:
        pass

I've failed to find a tip in the documentation. May be some other library or different approach is applicable in such cases?

Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
garej
  • 508
  • 1
  • 8
  • 24

1 Answers1

1

I don't believe there is something built-in the library for it, but you might do something like:

wb = openpyxl.load_workbook(filename)
sheet = wb['Sheet1']

rows = [21, 24] + list(range(31, 36) + [40]
cols = ["A", "O", "R", "S"]

for row in rows:
    for col in cols:
        cell = sheet[f"{col}{row}"]

In this case R and S are consecutive so there is not much problem. If you had bigger ranges that will be tedious to write manually, check out how to create a character range and use it the same way as in the rows.

As noted by stovfl, the string formatting might be more expensive. As we have the row and col of the cells, we can use the Worksheet.cell method with a simple conversion:

from openpyxl.utils.cell import column_index_from_string

...
cols = ["A", "O", "R", "S"]
cols = [column_index_from_string(col) for col in cols]
...
        cell = sheet.cell(row=row, col=col)
  • Of course if you would use integer notation in the first place for the cols, no conversion will be needed and the access would be as the last row of code.
Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
  • Don't use `sheet["{col}{row}"]` notation, it's slow. You allready have `row`,`col` access the cell direct. – stovfl Jan 22 '20 at 21:03
  • Thanks @stovfl indeed that might be better apart from a small conversion (as the `cell` method takes integers – Tomerikoo Jan 22 '20 at 21:16
  • 1
    @garej not sure I understand what you mean – Tomerikoo Jan 22 '20 at 21:24
  • @Tomerikoo, I mean, in notation similar to the following wrong expression: `sheet['A1, R1:S1']` – garej Jan 22 '20 at 21:27
  • @garej not that i'm aware of or able to find in the docs. The most is continuos range spreading over multiple rows/cols – Tomerikoo Jan 22 '20 at 21:31
  • 2
    @garej ***`sheet['A1, R1:S1']`***: You can define a function generator, e.g. `def disjoint_range('A1, R1:S1'):` which yields column index according to the given ranges. But you can't use this with `sheet[...]`. – stovfl Jan 22 '20 at 21:37