CODEWITHSUNDEEP
pythonopenpyxl
I.T. Guy
I.T. Guy

Reputation: 121

How to iterate over each cell in a column and perform an operation on each

I have an excel document that lists several IP addresses, I need to iterate over each cell in the column and perform a ping to determine if each IP is up or not. I then need the result of the ping function written to a text file.

So in the spreadsheet in column A starting at A1 will be:

10.10.10.10
20.20.20.20
30.30.30.30
40.40.40.40

The script should read each line, ping each IP address, and then write to text file "10.10.10.10. is up, 20.20.20.20 is down", ect

import os
import openpyxl
import time


wb = openpyxl.load_workbook('IPS.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
with open(time.strftime("%d-%m-%Y") + "-Decom.txt", "w") as s:
    for i in range(1, sheet.max_row):
        response = os.system("ping -n 2" + sheet[i].value )
        if response == 0:
            s.write(sheet[i].value, 'is up')
        else:
            s.write(sheet[i].value, 'is down')

c:\Python35\Scripts>python ping.py Traceback (most recent call last): File "ping.py", line 10, in response = os.system("ping -n 2" + sheet[i].value ) AttributeError: 'tuple' object has no attribute 'value'

Upvotes: 1

Views: 2688

Answers (1)

alecxe
alecxe

Reputation: 473853

When you do sheet[i].value, sheet[i] would return a tuple of cell values in a row.

There is, though, a simpler way to iterate over the cells of a specific column:

for cell in sheet['A']:        
    value = cell.value

    response = os.system("ping -n 2" + value)
    s.write(value, 'is up' if response == 0 else 'is down')

Upvotes: 1

Related Questions