Performing manual ping tests across a large list of IP addresses is impractical. A Python script can automate this by leveraging the openpyxl and pythonping lirbaries to test connectivity and log results directly into an Excel workbook.

• openpyxl: Enables reading from and writing to .xlsx files, supporting cell-level operations and worksheet modifications.
• pythonping: Sends ICMP echo requests to target hosts and returns metrics such as round-trip time (RTT) and packet loss.

Setup

Instal the required packages:

pip install openpyxl pythonping

Implementation

#!/usr/bin/env python3

import openpyxl
from pythonping import ping

def test_host_and_log(ip_addr, row_idx, worksheet):
    try:
        response = ping(ip_addr, count=2)
        
        if response.success():
            avg_rtt = round(response.rtt_avg_ms, 3)
            max_rtt = round(response.rtt_max_ms, 3)
            min_rtt = round(response.rtt_min_ms, 3)
            loss_pct = round(response.packet_loss * 100)

            worksheet.cell(row=row_idx, column=4, value=avg_rtt)
            worksheet.cell(row=row_idx, column=5, value=max_rtt)
            worksheet.cell(row=row_idx, column=6, value=min_rtt)
            worksheet.cell(row=row_idx, column=7, value=loss_pct)

            print(f"Recorded {ip_addr} (Row {row_idx}) – Avg: {avg_rtt} ms, Max: {max_rtt} ms, Min: {min_rtt} ms, Loss: {loss_pct}%")
        else:
            print(f"No response from {ip_addr}")
    except Exception as err:
        print(f"Ping failed for {ip_addr}: {err}")

def main():
    workbook = openpyxl.load_workbook("test1.xlsx")
    sheet = workbook.active

    # Write result headers
    headers = ["Avg Delay (ms)", "Max Delay (ms)", "Min Delay (ms)", "Packet Loss (%)"]
    for idx, title in enumerate(headers, start=4):
        sheet.cell(row=1, column=idx, value=title)

    # Process each IP in column A starting from row 2
    for current_row in range(2, sheet.max_row + 1):
        target_ip = sheet.cell(row=current_row, column=1).value
        if target_ip:
            print(f"Testing {target_ip}...")
            test_host_and_log(target_ip, current_row, sheet)

    workbook.save("test1.xlsx")

if __name__ == "__main__":
    main()

Usage

Prepare a Excel file named test1.xlsx with IP addresses listed in column A (starting at row 2). Run the script using:

python3 ping.py

The script populates columns D through G with average, maximum, and minimum RTT in milliseconds, along with packet loss percentage. Results are saved back to the same file.