How do I put multiple excel rows into one big list with sublists?

Question

How do you take multiple excel rows and put them into one big list with sublists for each excel row? I've used openpyxl to extract the data from a .xlsx file and then used .iter_rows to get all the needed rows' values like so:

from openpyxl import load_workbook

workbook = load_workbook(filename=r"C:\Users\file.xlsx")
sheet = workbook.active

for row in sheet.iter_rows(min_row=2,
                           max_row=21,
                           min_col=1,
                           max_col=4,
                           values_only=True):
    print(row)
    listtest = []
    for cell in row:
        listtest.append(cell)

print(listtest)

I tried using this method, but it only makes the last line into a list, giving me an output like this:

(1, 1, 12, 4)

(2, 1, 8, 3)

...

...

(20, 101, 3, 11)

[20, 101, 3, 11]

I'm trying to get an output like this:

[['1', '1', '12', '4'], ['2', '1', '8', '3'], ... , ['20', '101', '3', '11']]

Would make my day if someone could budge me in the right direction here :) Thanks.

Answer 1

That's because you're overwriting listtest in each iteration of your loop. To stick to your original code (with a slight modification), try:

output = []
for row in sheet.iter_rows(min_row=2,
                           max_row=21,
                           min_col=1,
                           max_col=4,
                           values_only=True):
    listtest = []
    for cell in row:
        listtest.append(cell)
    output.append(listtest)