CODEWITHSUNDEEP
pythonexcelopenpyxl
cheese
cheese

Reputation: 13

Is there a way to convert the output of an iteration in Openpyxl to a coordinate

When iterating through a range of cells in Openpyxl,

for x in ws.iter_cols(1, 32, 1, 24):

or

for x in ws['A1':'T1']:

it outputs a string like this when I print it out:

print(x)

>>> <Cell 'Worksheet 1'.A1>

Is there a way to get a coordinate instead ? Either 'A1' or (1, 1).

I am quite new to this so I am sorry if i missed something

Upvotes: 1

Views: 595

Answers (1)

Mike67
Mike67

Reputation: 11342

In Openpyxl, worksheet['A1':'C3'] returns a tuple of rows and columns:

((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), 
 (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), 
 (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))

To access the first cell, use worksheet['A1':'C3'][0][0], which returns a cell object.

To get the range string (ie 'A1') of the cell, use the coordinate attribute.

worksheet['A1':'C3'][0][0].coordinate   # A1

To get the row and column, use the related attributes

(worksheet['A1':'C3'][0][0].row, worksheet['A1':'C3'][0][0].column)  # (1,1)

For iterating through a range, you can loop over the tuple values

rng = worksheet['A1':'C3']
for r in rng:
   for x in r:
      print(x.coordinate, x.row, x.column)

Output

A1 1 1
B1 1 2
C1 1 3
A2 2 1
B2 2 2
C2 2 3
A3 3 1
B3 3 2
C3 3 3

You can also use iter_rows to iterate over the cells. The following code produces the same output as above

for c in worksheet.iter_rows(min_row=1, max_col=3, max_row=3):  # A1 - C3
   for x in c:
       print(x.coordinate, x.row, x.column)

Upvotes: 2

Related Questions