Iterate with loop through excel sheet and store values in variables using Python

user_10159144 0
Viewed 785

I was trying to read dats from excel sheet using python, due to some constraints i'm not able to use external libraries. I'm doing it with from Microsoft.Office.Interop import Excel. Now I'm able to read the datas from sheet by defining them explicitly. like,

x1=ws.Rows[3].Value2[0,0]
y1=ws.Rows[3].Value2[0,1]
z1=ws.Rows[3].value2[0,2]

x2=ws.Rows[4].value2[0,0]
y2=ws.Rows[4].value2[0,1]
z2=ws.Rows[4].value2[0,2]

x3=ws.Rows[5].value2[0,0]
y3=ws.Rows[5].value2[0,1]
z3=ws.Rows[5].value2[0,2]

x4=ws.Rows[6].value2[0,0]
y4=ws.Rows[6].value2[0,1]
z4=ws.Rows[6].value2[0,2]

But I want to loop it. How to loop it? at the end i need values in variables.

while-loop for-loop excel python loops
3 Answers

Assuming that you are reading a fixed number of 3 values for each row, I would recommend to use 3 lists for x, y, and z as a data structure:

x = []
y = []
z = []
for row in ws.Rows[3:]:
    x.append(row.Value2[0,0])
    y.append(row.Value2[0,1])
    z.append(row.Value2[0,2])

Then you can access your values via x[0], z[4] etc.

If the number of columns is also variable, you should use a 2D array and two nested for loops for assigning the values.

user_120565640

If you really want to declare the variables in the described way you could use, here you will get the 5 first x,y,z positions from your excel:

for i in range(5):
    globals()['x{0}'.format(i)] = ws.Rows[i + 2].Value2[0,0]
    globals()['y{0}'.format(i)] = ws.Rows[i + 2].Value2[0,1]
    globals()['z{0}'.format(i)] = ws.Rows[i + 2].Value2[0,2]

I however would recommend storing the points in a list of dictionary like this:

positions = list()
for i in range(5):
    positions.append({
        'x': ws.Rows[i + 2].Value2[0,0]
        'y': ws.Rows[i + 2].Value2[0,1]
        'z': ws.Rows[i + 2].Value2[0,2]
    })
user_59252570 
led = []
for i in range(5):
    globals()['x' + 'i'] = ws.Rows[i + 2].Value2[0,0]
    globals()['y' + 'i'] = ws.Rows[i + 2].Value2[0,1]
    globals()['z' + 'i'] = ws.Rows[i + 2].Value2[0,2]
    led.append(globals()['x' + 'i'])
    led.append(globals()['y' + 'i'])
    led.append(globals()['z' + 'i'])

It worked for me,as per my requirements.

user_101591440
Related