python开发excel插件教程-利用 Python 插件 xlwings 读写 Excel

程序比较简单，直接上程序。 # -*- coding: utf-8 -*- import xlwings as xw import pandas as pd from datetime import datetime # 统计时间, 只有时间要改 START_TIME = "2018-07-01 00:00:00" END_TIME = "2018-07-31 23:59:00" START_ROW = 2 # 处理Excel文件开始行 END_ROW = 200 # 处理Excel结束行 # 天数 * 每天工作时间 * 分钟 WORK_TIME = 30 * 22 * 60 # 关键设备清单 key_machine = ["609", "610", "621", "622", "623", "624", "627", "628", "636", "638", "667", "670", "675", "689"] persons = ["张三", "李四", "王五"] app = xw.App(visible=True, add_book=False) wb_source = app.books.open("downTimeData.xls") # 打开Excel文件 downTimeData.xls sheet = wb_source.sheets[0] # 选择第0个表单 # 需每月修改时间 start_datetime = datetime.strptime(START_TIME, "%Y-%m-%d %H:%M:%S") # 把开始统计时间转换为DateTime end_datetime = datetime.strptime(END_TIME, "%Y-%m-%d %H:%M:%S") # 把结束统计时间转换为DateTime result = [] for row in range(START_ROW, END_ROW): row_content = [] row_str = str(row) time_str = sheet.range("C" + row_str).value.strip() create_datetime = datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S") if start_datetime <= create_datetime <= end_datetime: machine = sheet.range("A" + row_str).value machine_number = machine[-4:-1] if machine_number in key_machine: if sheet.range("G" + row_str).value.strip() in persons: row_content.append(machine_number) row_content.append(create_datetime) response_time_str = sheet.range("D" + row_str).value complete_time_str = sheet.range("E" + row_str).value row_content.append(response_time_str + complete_time_str) solution_str = sheet.range("H" + row_str).value.strip() row_content.append(solution_str) comments = sheet.range("I" + row_str).value.strip() row_content.append(comments) result.append(row_content) # count the times and downtime on the same machine and put it in dictionary # 统计每台设备的停机次数 dict_result = {} for _, [name, _, downtime, _, _] in enumerate(result): if name in dict_result: dict_result[name] = (dict_result[name][0] + 1, dict_result[name][1] + downtime) else: dict_result[name] = (1, downtime) # fill the result and write it on excel target_name = START_TIME[5:7] wb_target = app.books.open("analysis2018.xlsx") # 打开Excel文件，把结果写入 index = 3 for key in key_machine: if key not in dict_result: wb_target.sheets[target_name].range("B" + str(index)).value = 0 wb_target.sheets[target_name].range("C" + str(index)).value = 0 wb_target.sheets[target_name].range("D" + str(index)).value = WORK_TIME wb_target.sheets[target_name].range("E" + str(index)).value = 0 wb_target.sheets[target_name].range("F" + str(index)).value = 0 else: wb_target.sheets[target_name].range( "B" + str(index)).value = dict_result[key][0] wb_target.sheets[target_name].range( "C" + str(index)).value = dict_result[key][1] wb_target.sheets[target_name].range( "D" + str(index)).value = (WORK_TIME - dict_result[key][1]) / dict_result[key][0] wb_target.sheets[target_name].range( "E" + str(index)).value = dict_result[key][1] / dict_result[key][0] wb_target.sheets[target_name].range( "F" + str(index)).value = dict_result[key][1] / WORK_TIME index += 1 # write the comment and solution on excel result.sort() # 故障信息排序，用于最后输出 df = pd.DataFrame(result, columns=["编号", "故障时间", "停机时间", "解决方案", "备注"]) wb_target.sheets[target_name].range("H2").value = df wb_target.sheets[target_name].autofit("c")