How to import entire sheet to list via openpyxl

Question

I have a problem with importing data from the spreadsheet. I'm trying create list containing all data. But only last row is added to list.

from openpyxl import load_workbook

path = "excel.xlsx"
l_data = load_workbook(path)
sheet = l_data.active
c_row = sheet.max_row
c_column = sheet.max_column
out_data=[]
dict={}
for a in range(1, c_row + 1):
    for b in range(1, c_column + 1):
        ob = sheet.cell(row=a, column=b)
        dict[b] = ob.value
    out_data.append(dict)

I need such output data: [ {1:"row1_call1",2:"row1_call2"},{1:"row2_call1",2:"row2_call2"} ]

N00b · Accepted Answer · 2019-02-21T11:59:10.843

0

Try that one:

from openpyxl import load_workbook

path = "excel.xlsx"
l_data = load_workbook(path)
sheet = l_data.active
c_row = sheet.max_row
c_column = sheet.max_column
out_data=[]
dict={}
for a in range(1, c_row + 1):
    for b in range(1, c_column + 1):
        ob = sheet.cell(row=a, column=b)
        dict[b] = ob.value

    out_data.append(str(dict))

print(out_data)

It changes type of dict values. So only this line changes from:

out_data.append(dict)

to:

out_data.append(str(dict))

Check that site if you want know more appending dict to list: python: Appending a dictionary to a list - I see a pointer like behavior

So this is also working:

out_data.append(dict.copy())

edited Feb 21 '19 at 11:59

answered Feb 21 '19 at 11:37

N00b

148
11

In that case, i will receive [ 'row1_call1','row2_call1']. And i need it [{1: 'row1_call1'},{1: 'row2_call1'}] – bobi Feb 21 '19 at 11:47
Edited that. check that one. – N00b Feb 21 '19 at 11:53
Yes, that's it :) Tnx4help – bobi Feb 21 '19 at 12:03
`dict` is a keyword so it's not a good idea to use it as a variable name – Charlie Clark Feb 22 '19 at 11:26

How to import entire sheet to list via openpyxl

1 Answers1

Linked