Openpyxl How to get row from worksheet by index
Solution 1
I finally found the answer in the documentation:
first_row = worksheet[1]
# worksheet[row_index_from_1]
This worked for me.
Solution 2
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
Related videos on Youtube
Damilola Boiyelove
Software Engineer On Web I play with Python/Django/Flask On Mobile, I roll with Flutter/Java/Kotlin On Windows, VB.net is my jam Company: boiyelove.com Personal: damilola.xyz
Updated on November 09, 2020Comments
-
Damilola Boiyelove over 3 years
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
-
Charlie Clark over 7 yearsThis is covered in the documentation.
-
-
Charlie Clark over 7 yearsYes, 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 usingws.iter_rows()
and, where possible,ws.iter_cols()
. -
Ben Liyanage over 6 yearsThis is now worksheet.rows[1]. Going to update the answer.
-
Ben Liyanage over 6 yearsThis is not a great answer. It's better to just get the row directly, rather then explicitly converting the iterator to a list.
-
Manuel Alvarez over 6 yearsSure 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.
-
ebt about 6 years@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.
-
Ben Liyanage about 6 yearsYup, I fixed the other answer. =D Happy bug hunting guys!
-
Erdem KAYA about 6 yearsI 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.
-
ebt about 6 years@ErdemKAYA I would suggest using the enumerate method. docs.python.org/3/library/functions.html#enumerate you can use an if statement to identify which row to take action on.
-
hsc almost 6 years@CharlieClark after the
rows
variable is a generator, do we still have a way to access row by index now? -
Mark Dickinson over 5 years@BenLiyanage: I've rolled back your edit. I suspect you were using an older version of
openpyxl
.worksheet.rows
is a generator, soworksheet.rows[1]
won't work. -
Ben Liyanage over 5 yearsIf 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.
-
Flying Thunder almost 3 yearsthis no longer works in the newest version