python - 使用 openpyxl 从 xlwt 将 xls 更改为 xlsx
问题描述
我一直在使用 xlwt 来自动化报告。我必须添加一个 COUNTIFS 公式,不幸的是它不适用于 xls 中的 xlwt,所以我必须使用 openpyxl 重新创建整个 python 脚本。我遇到的问题是,被提取的数据来自一个休息 API,并且某些值用前面的“传感器”标识。下面的代码显示了我正在尝试使用 open pyxl 的内容,注释行是我在 xlwt 中使用的内容。如果有人能告诉我如何让 openpyxl 做 xlwt 所做的事情,那就太棒了。
book = Workbook("")
sheet = book.active
sheet["A1"] = "id"
#worksheet.write(0, column_number, 'id')
sheet['B1'] = "hostname"
#worksheet.write(0, column_number, 'hostname')
sheet['C1'] = "os"
#worksheet.write(0, column_number, 'os')
sheet['D1'] = "ip_address"
#worksheet.write(0, column_number, 'ip_address')
sheet['E1'] = "last_checkin_time"
#worksheet.write(0, column_number, 'last_checkin_time')
sheet['F1'] = "days_offline"
#worksheet.write(0, column_number, 'days_offline')
sheet['G1'] = "console"
#worksheet.write(0, column_number, 'console')
cb = CbResponseAPI()
sensors = list(cb.select(Sensor))
row = 1
for sensor in sensors:
#print sensor
if sensor.uninstall == False and (sensor.uninstalled == False or sensor.uninstalled == None):
last_checkin_time = sensor.last_checkin_time.strftime('%m/%d/%Y')
p = datetime.now().strftime('%m/%d/%Y')
d = datetime_object = datetime.strptime(last_checkin_time, '%m/%d/%Y')
q = datetime_object = datetime.strptime(p, '%m/%d/%Y')
delta = (q - d).days
cell = sheet.cell(row=2, column=1)
cell.value = "sensor.id"
cell = sheet.cell(row=2, column=2)
cell.value = "sensor.hostname"
#worksheet.write(row, 0, sensor.id)
#worksheet.write(row, 1, sensor.computer_name)
if "Windo 7" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo 7"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo Server 2008" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo Server 2008"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo Server 2012" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo Server 2012"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo XP" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo XP"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Mac OSX" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Mac OSX"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo Server 2003" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo Server 2003"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo 10" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo 10"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo 8" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo 8"
#worksheet.write(row, 2, sensor.os_environment_display_string)
elif "Windo Server 2016" in sensor.os_environment_display_string:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
#sensor.os_environment_display_string = "Windo Server 2016"
#worksheet.write(row, 2, sensor.os_environment_display_string)
else:
cell = sheet.cell(row=2, column=3)
cell.value = "sensor.os_environment_display_string"
cell = sheet.cell(row=2, column=4)
cell.value = "sensor.network_adapters"
cell = sheet.cell(row=2, column=5)
cell.value = "sensor.last_checkin_time"
cell = sheet.cell(row=2, column=6)
#worksheet.write(row, 2, sensor.os_environment_display_string)
#worksheet.write(row, 3, sensor.network_adapters)
# worksheet.write(row, 4, last_checkin_time)
# worksheet.write(row, 5, delta)
# worksheet.write(row, 6, "DELI")
row+=1
print("-> DELI-RESPONSE - Done exporting! <-")
book.save("sensor_export.xlsx")
解决方案
好的。我在这方面很业余,但这里有几件事我看到了。
1. 在你的 for 循环中,行 += 1 缩进了 4 个空格。它在 if 循环内,而不是 for 循环内。或者更具体地说,它位于底部的 else 循环内。2.你有一个变量“行”。但是在 openpyxl 中,“行”是单元格的属性。所以它是一个功能词。我不确定您是否可以通过使用“行”作为变量和属性来使其工作。我会将您的变量更改为“r”。然后在 if 和 elif 中,你可以有:cell = sheet.cell(row=r, column=3)。
正如现在的代码一样,您告诉 openpyxl 在每个实例中 row = 2。
祝你好运。
推荐阅读
- tomcat - OpenGrok 索引器无法执行
- python - 为什么monkeypatch不能识别构造函数中的赋值?
- c++ - 确认对函数指针的理解
- apache-flink - 任务失败时 Flink Yarn 无限重启
- user-interface - 如何修复 ui actionbar nativescript 错误
- java - 在spring-boot中运行项目restfulapi时出错
- c++ - 如何定义全局函数指针并分配给特定地址
- java - 使用 ... extend Thread 在线程内创建方法
- html - 非登录状态时不会出现来自静态文件夹的图像 [Spring MVC & Thymeleaf]
- matrix - 从基本矩阵解释相机矩阵?