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

Question

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.

You might want to look at the concept of nested loops to solve this problem. By the way, the first two rows you have Value2 and after that you have value2. This may cause an error depending on what you are doing. — ashishv
– ashishv, Commented Jun 30, 2020 at 15:49

Gerd · Accepted Answer · 2020-07-10 11:36:00Z

4

+25

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.

edited Jul 10, 2020 at 11:36

answered Jul 10, 2020 at 10:45

Gerd

2,8832 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jop Knoppers · Accepted Answer · 2020-07-16 06:43:32Z

3

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]
    })

edited Jul 16, 2020 at 6:43

answered Jul 7, 2020 at 7:13

Jop Knoppers

7221 gold badge10 silver badges25 bronze badges

Comments

Tamil Selvan S · Accepted Answer · 2020-07-22 15:36:43Z

0

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.

edited Jul 22, 2020 at 15:36

answered Jul 16, 2020 at 4:12

Tamil Selvan S

72612 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related