pandas统计
import re
import openpyxl
import pandas as pd
"""
这个python文件是为了生成当天巡检的工作量统计情况。
"""
current_time = input("请输入当前时间(格式为YYYY/MM/DD):")
file_path = r'C:\Users\Administrator.DESKTOP-D6NNI6Q\Desktop\巡检工作\健康巡检异常项分析统计追踪表.xlsx'
sheets_to_read = [
'Sheet1',
'Sheet2',
'Sheet3',
'Sheet4',
'Sheet5',
'Sheet6',
'Sheet7',
'Sheet8',
'Sheet9',
'Sheet10'
]
all_sheets = pd.read_excel(file_path, sheet_name=sheets_to_read)
result_list = []
for sheet_name, sheet_data in all_sheets.items():
analysis_time_count = sheet_data[sheet_data['分析时间'] == current_time].shape[0]
submit_time_count = sheet_data[sheet_data['提单时间'] == current_time].shape[0]
archive_time_count = sheet_data[sheet_data['归档时间'] == current_time].shape[0]
close_submit_count = sheet_data[sheet_data['关闭提单'] == current_time].shape[0]
print(f"应用:{sheet_name}")
print(f"分析异常项个数:{analysis_time_count}")
print(f"提交缺陷单个数:{submit_time_count}")
print(f"归档异常单个数:{archive_time_count}")
print(f"关闭缺陷单个数:{close_submit_count}")
print("\n")
result_list.append({
'应用': sheet_name,
'分析异常项个数': analysis_time_count,
'提交缺陷单个数': submit_time_count,
'归档异常单个数': archive_time_count,
'关闭缺陷单个数': close_submit_count
})
result_df = pd.DataFrame(result_list)
result_df.loc['汇总'] = result_df.iloc[:, 1:].sum()
result_df = result_df.replace({pd.NA: '', pd.NaT: '', float('inf'): '', float('-inf'): ''})
result_df.iloc[-1, 0] = '汇总'
output_file_path = r'C:\Users\Administrator.DESKTOP-D6NNI6Q\Desktop\巡检工作量统计.xlsx'
result_df.to_excel(output_file_path, index=False)
workbook = openpyxl.load_workbook(output_file_path)
worksheet = workbook.active
for col in worksheet.columns:
max_length = max([
len(str(cell.value)) + 0.7 * len(re.findall(r'([\u4e00-\u9fa5])', str(cell.value)))
for cell in col
])
worksheet.column_dimensions[col[0].column_letter].width = (max_length + 2) * 1.2
workbook.save(output_file_path)
print(f"结果已保存到:{output_file_path}")