|
|
import pandas as pd
|
|
|
import pymysql
|
|
|
from datetime import date
|
|
|
import os
|
|
|
import openpyxl
|
|
|
from openpyxl.styles import PatternFill
|
|
|
from openpyxl.styles import Font
|
|
|
# 建立与MySQL数据库的连接
|
|
|
conn = pymysql.connect(
|
|
|
host='192.168.10.28',
|
|
|
user='root',
|
|
|
port=3307,
|
|
|
password='123456',
|
|
|
database='zentao'
|
|
|
)
|
|
|
today = date.today()
|
|
|
#打开游标
|
|
|
cursor = conn.cursor()
|
|
|
query = "SELECT account FROM zt_user"
|
|
|
cursor.execute(query)
|
|
|
account_result = cursor.fetchall()
|
|
|
account_of_list = []
|
|
|
for row in account_result:
|
|
|
account_of_list.append(row)
|
|
|
account_list = [item[0] for item in account_of_list]
|
|
|
account_list.pop(0)
|
|
|
#print(account_list)
|
|
|
for accounts in account_list:
|
|
|
query_effort = "SELECT e.account,u.realname,e.consumed,e.work,t.name,t.desc from zt_effort e , zt_user u ,zt_task t where e.DATE = current_date AND e.account = u.account AND e.objectID = t.id;"
|
|
|
cursor.execute(query_effort)
|
|
|
df = pd.read_sql(query_effort, con=conn)
|
|
|
|
|
|
# 关闭数据库连接
|
|
|
conn.close()
|
|
|
excel_file = '{}_zentao.xlsx'.format(today) # 指定要输出的Excel文件名
|
|
|
df.to_excel(excel_file, index=False) # index=False 表示不包含行号
|
|
|
excel_file_path = excel_file
|
|
|
df = pd.read_excel(excel_file_path)
|
|
|
#将相同的name排在相邻行
|
|
|
df_sorted = df.sort_values(by='account')
|
|
|
df_sorted = df_sorted.groupby('account').apply(lambda x: x.reset_index(drop=True))
|
|
|
df_sorted.reset_index(drop=True, inplace=True)
|
|
|
output_excel_path = '{}工时.xlsx'.format(today) # 替换为你想保存的Excel文件路径
|
|
|
df_sorted.to_excel(output_excel_path, index=False)
|
|
|
os.remove(excel_file)
|
|
|
print(f'Processed data saved to: {output_excel_path}')
|
|
|
excel_file_new = output_excel_path # 替换为你的Excel文件路径
|
|
|
df = pd.read_excel(excel_file_new)
|
|
|
# 将新数据添加到DataFrame
|
|
|
df.rename(columns={'realname': '员工姓名','name': '任务名称','desc':'任务描述','work':'工时对应的工作内容','account':'员工账号','consumed':'消耗工时'}, inplace=True)
|
|
|
pd.set_option("display.max_colwidth",1000)
|
|
|
# 保存更新后的数据到同一Excel文件
|
|
|
df.to_excel(excel_file_new, sheet_name='员工工时详情',index=False)
|
|
|
#sheet2工时操作
|
|
|
df_grouped = df.groupby('员工姓名')['消耗工时'].sum().reset_index()
|
|
|
df = pd.read_excel(excel_file_new,header=None)
|
|
|
# 对原始数据进行处理，并将结果保存到result变量中
|
|
|
# 创建一个ExcelWriter对象
|
|
|
writer = pd.ExcelWriter(excel_file_new)
|
|
|
# 将原始数据写入到Excel文件中
|
|
|
df.to_excel(writer, sheet_name='员工工时详情', index=False,header=False)
|
|
|
# 将处理后的结果写入到新的sheet页中
|
|
|
df_grouped.to_excel(writer, sheet_name='员工今日总工时', index=False,header=False)
|
|
|
# 关闭ExcelWriter对象
|
|
|
writer._save()
|
|
|
|
|
|
# 打开Excel文件
|
|
|
workbook = openpyxl.load_workbook(excel_file_new)
|
|
|
|
|
|
# 总工时新开一个sheet页面
|
|
|
sheet = workbook['员工今日总工时']
|
|
|
sheet.insert_rows(1)
|
|
|
data_to_insert = ["员工姓名", "员工今日总工时"] # 替换为你要插入的实际数据
|
|
|
for col_num, value in enumerate(data_to_insert, start=1):
|
|
|
cell = sheet.cell(row=1, column=col_num, value=value)
|
|
|
# 加粗字体
|
|
|
cell.font = Font(bold=True)
|
|
|
sheet_1 = workbook['员工工时详情']
|
|
|
first_row = sheet_1[1]
|
|
|
for cell in first_row:
|
|
|
cell.font = Font(bold=True)
|
|
|
# 自动调整列宽以适应内容
|
|
|
for column in sheet_1.columns:
|
|
|
max_length = 0
|
|
|
column = [cell for cell in column]
|
|
|
for cell in column:
|
|
|
try:
|
|
|
if len(str(cell.value)) > max_length:
|
|
|
max_length = len(cell.value)
|
|
|
except:
|
|
|
pass
|
|
|
adjusted_width = (max_length + 2)
|
|
|
sheet_1.column_dimensions[column[0].column_letter].width = adjusted_width
|
|
|
# 创建一个红色填充样式
|
|
|
red_fill = PatternFill(start_color='FFFF0000',
|
|
|
end_color='FFFF0000',
|
|
|
fill_type='solid')
|
|
|
# 创建一个黄色填充样式
|
|
|
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
|
|
|
# 遍历第二列的每一行
|
|
|
for row in sheet.iter_rows(min_row=2, min_col=2, max_col=2):
|
|
|
cell = row[0]
|
|
|
if cell.value is not None and cell.value < 8:
|
|
|
cell.fill = red_fill
|
|
|
if cell.value is not None and cell.value > 8:
|
|
|
cell.fill = yellow_fill
|
|
|
|
|
|
# 保存修改后的Excel文件
|
|
|
workbook.save(excel_file_new)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|