Openpyxl How to get row from worksheet by index

Question

Using Openpyxl and python3.5, I tried getting the first row from an excel worksheet using a subscript but I an error.

# after getting filename
# after loading worksheet
# to get the first row of the worksheet
first_row = worksheet.rows[0]

# I get 
Traceback (most recent call last):
      File "<pyshell#54>", line 1, in <module>
      first_row = phc_th_sheet.rows[1]
TypeError: 'generator' object is not subscriptable

In relation to getting the first row, I've also tried first_row = worksheet.(row=1) # and first_row = worksheet.rows[:1]

None worked. Any suggestions or is the feature not available in openpyxl? I've been to the documentation at https://openpyxl.readthedocs.io/en/default/ but I found nothing helpful enough to index and select rows

This is covered in the documentation. – Charlie Clark Nov 12 '16 at 13:40 — Charlie Clark, Nov 12 '16 at 13:40

score 63 · Accepted Answer · edited Nov 08 '18 at 15:22

63

I finally found the answer in the documentation:

first_row = worksheet[1]
# worksheet[row_index_from_1]

This worked for me.

edited Nov 08 '18 at 15:22

Mark Dickinson

29,088
9
83
120

answered Nov 12 '16 at 10:56

Damilola Boiyelove

1,119
1
9
18

2

Yes, we converted `ws.rows` into a generator so that behaviour across different implementations would be consistent and also to encourage this syntax for ad-hoc queries. Programmatic access is best done using `ws.iter_rows()` and, where possible, `ws.iter_cols()`. – Charlie Clark Nov 12 '16 at 13:39
4

This is now worksheet.rows[1]. Going to update the answer. – Ben Liyanage Sep 07 '17 at 18:44
3

@CharlieClark after the `rows` variable is a generator, do we still have a way to access row by index now? – hsc Aug 06 '18 at 03:17
1

@BenLiyanage: I've rolled back your edit. I suspect you were using an older version of `openpyxl`. `worksheet.rows` is a generator, so `worksheet.rows[1]` won't work. – Mark Dickinson Nov 08 '18 at 15:23
If version is important, can you please specify it in the answer mark? Thanks for keeping the answer fresh! I don’t have access to this codebase any more so I do not know what version I was using. – Ben Liyanage Nov 09 '18 at 18:12
4

this no longer works in the newest version – Flying Thunder May 28 '21 at 11:38

score 21 · Answer 2 · answered Nov 12 '16 at 10:16

21

The error TypeError: 'generator' object is not subscriptable. Means that you are trying to access by index a generator, which doesn't have one, because it creates the elements as you iterate through it.

You can solve it easily, cast it to a list to get the element you want:

first_row = list(worksheet.rows)[0]

or iterate thought the rows:

for row in worksheet.rows:
    foo(row)

This is because, even if both are iterables, lists and generators can behave quite differently, you can get it better explained here:

https://wiki.python.org/moin/Generators

https://docs.python.org/3/library/stdtypes.html#iterator-types

https://docs.python.org/3/library/stdtypes.html#sequence-types-list-tuple-range

answered Nov 12 '16 at 10:16

Manuel Alvarez

367
2
4

3

This is not a great answer. It's better to just get the row directly, rather then explicitly converting the iterator to a list. – Ben Liyanage Sep 07 '17 at 18:45
3

Sure it is. However, as I usually manipulate excel spreadsheets with pandas and I'm not familiar with the internals of openpyxl, just tried to give an explanation of the error and how to get a workaround. – Manuel Alvarez Sep 08 '17 at 06:51
@BenLiyanage the issue is you cant because the method is now a generator. the list method emulates accessing the row directly. Note that it pulls all the data into memory first. like DOM and StaX considerations, if you have a large dataset you'll want to iterate using the second example. – ebt Apr 25 '18 at 01:34
Yup, I fixed the other answer. =D Happy bug hunting guys! – Ben Liyanage May 03 '18 at 18:51
I understand @ebt's explanation generators, but I still can't get how one could access to the n_th row with the "second example" presented in the answer. – precise May 06 '18 at 11:32
@ErdemKAYA I would suggest using the enumerate method. https://docs.python.org/3/library/functions.html#enumerate you can use an if statement to identify which row to take action on. – ebt May 07 '18 at 20:11

Openpyxl How to get row from worksheet by index

2 Answers2

Linked