Make a lists of list with excel values and sort by sheets

I would like to make a lists of list with excel values. I have multiple excel sheets where there is always 1 same data,and i want to put the datas into lists. Here is an example:

In the Sheet1 these are the datas:

And at the Sheet2 these are the datas:

In my code i find the 1Data and after make a list to put the {}Data into that list, and i want to make a big list where the datas are sorted by the sheets

Example:

[[‘1Data’,’3Data’,’5Data1],[‘1Data’,’2Data’,’3Data’]

Unfortunately i got this output:

[[‘1Data’, ‘2Data’, ‘3Data’, ‘1Data’, ‘3Data’, ‘5Data’], [‘1Data’, ‘2Data’, ‘3Data’, ‘1Data’, ‘3Data’, ‘5Data’]]

Here is my code:

from openpyxl import load_workbook

wb = load_workbook('test01.xlsx',data_only=True)

small_lst = []
big_lst = []

for sheets in wb.sheetnames:
    ws = wb[sheets]
    for i in ws.rows:
        for asd in i:
            for qwe in range(10):
                if asd.value == '{}Data'.format(qwe):
                    small_lst.append(asd.value)
    big_lst.append(small_lst)

print(big_lst)

Answer

You aren’t resetting small_lst every time you are changing sheets. So you are just adding new sheet Data to the previous one.

Try moving the small_lst = [] inside the for sheets.

Advertisement

Answer