python3 用xlwings模块for循环遍历读取excel表中的每个数据

各位大神你们好,是这样的之前代码是导入的python的xlrd的模块,用for循环遍历excel表中的数据并赋值,现在想用xlwings来实现,尝试了各种方法没有成功,请教一下大家看如何实现。
原来用xlrd的时的脚本如下:

import xlrd
import paramiko
import time
import os

workbook = xlrd.open_workbook(r"./devices.xlsx")
sheet = workbook.sheet_by_index(0)

for index in range(1, sheet.nrows):
    hostname = sheet.row(index)[0].value
    ipaddr = sheet.row(index)[1].value
    port = sheet.row(index)[2].value
    username = sheet.row(index)[3].value
    password = sheet.row(index)[4].value
    device_type = sheet.row(index)[5].value
    devices = {
        'hostname': hostname,
        'ip': ipaddr,
        'port': int(port),
        'username': username,
        'password': password,
        'device_type': device_type
    }
    print(devices)

excel表的内容如下:

上述脚本的for循环中sheet.row(index)[0].value这个方法在xlwings中没有了,那么xlwings中有什么类似的方法动态的取excel表中的数据呢?

Jason990420
最佳答案

Try this

import xlwings as xw

app = xw.App(visible=True, add_book=False)
app.display_alerts = False
app.screen_updating = False

wb = app.books.open(r"./devices.xlsx")
ws = wb.sheets[0]
rows, cols = ws.used_range.shape
headings = ws[0, :cols].value

for row in range(1, rows):
    devices = {}
    for col in range(cols):
        if col == 2:
            devices[headings[col]] = int(ws[row, col].value)
        else:
            devices[headings[col]] = ws[row, col].value
    print(devices)

wb.close()
app.quit()
1周前 评论
HeMaoYuan (楼主) 1周前
讨论数量: 2
Jason990420

Try this

import xlwings as xw

app = xw.App(visible=True, add_book=False)
app.display_alerts = False
app.screen_updating = False

wb = app.books.open(r"./devices.xlsx")
ws = wb.sheets[0]
rows, cols = ws.used_range.shape
headings = ws[0, :cols].value

for row in range(1, rows):
    devices = {}
    for col in range(cols):
        if col == 2:
            devices[headings[col]] = int(ws[row, col].value)
        else:
            devices[headings[col]] = ws[row, col].value
    print(devices)

wb.close()
app.quit()
1周前 评论
HeMaoYuan (楼主) 1周前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!