源码B
:import os
import logging
import re
import smtplib
from openpyxl import load_workbook, Workbook
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
from tkinter import Tk, Label, Button, filedialog, messagebox, Text, Scrollbar, END, VERTICAL, RIGHT, Y
from datetime import datetime
import shutil
# 日志配置
logging.basicConfig(
filename="excel_to_word.log",
level=logging.INFO,
format="%(asctime)s - %(levelname)s - %(message)s",
)
class ExcelWordApp
:
def __init__(self, root)
:
# 初始化主窗口
self.root = root
self.root.title("底层健康表提醒")
self.root.geometry("800x650")
# 标题
title_label = Label(root, text="底层健康表提醒", font=("Arial", 18))
title_label.pack(pady=10)
# 第一步：选择健康表文件
step1_label = Label(root, text="第一步：选择健康表文件", font=("Arial", 14), anchor="w")
step1_label.pack(fill="x", padx=20, pady=10)
self.select_health_excel_button = Button(
root, text="选择健康表文件", command=self.select_health_excel, width=30
)
self.select_health_excel_button.pack(pady=5)
# 第二步：选择问题清单文件
step2_label = Label(root, text="第二步：选择问题清单文件", font=("Arial", 14), anchor="w")
step2_label.pack(fill="x", padx=20, pady=10)
self.select_issue_excel_button = Button(
root, text="选择问题清单文件", command=self.select_issue_excel, width=30
)
self.select_issue_excel_button.pack(pady=5)
# 第三步：生成附件
step3_label = Label(root, text="第三步：生成附件", font=("Arial", 14), anchor="w")
step3_label.pack(fill="x", padx=20, pady=10)
self.generate_attachment_button = Button(
root, text="生成附件", command=self.generate_attachment, state="disabled", width=30
)
self.generate_attachment_button.pack(pady=5)
# 第四步：增加附件横展清单
step4_label = Label(root, text="第四步：增加附件横展清单", font=("Arial", 14), anchor="w")
step4_label.pack(fill="x", padx=20, pady=10)
self.add_horizontal_attachment_button = Button(
root, text="增加附件横展清单", command=self.add_horizontal_attachment, state="disabled", width=30
)
self.add_horizontal_attachment_button.pack(pady=5)
# 第五步：发送邮件
step5_label = Label(root, text="第五步：发送邮件", font=("Arial", 14), anchor="w")
step5_label.pack(fill="x", padx=20, pady=10)
self.send_email_button = Button(
root, text="发送邮件", command=self.send_email, state="disabled", width=30
)
self.send_email_button.pack(pady=5)
# 增加退出按钮
exit_button = Button(
root, text="退出程序", command=root.quit, width=30, bg="red", fg="white", font=("Arial", 12, "bold")
)
exit_button.pack(pady=20)
# 日志显示框
self.log_text = Text(root, wrap="word", state="disabled", height=18)
self.log_text.pack(pady=10, padx=10, fill="both", expand=True)
scrollbar = Scrollbar(self.log_text, orient=VERTICAL, command=self.log_text.yview)
self.log_text.configure(yscrollcommand=scrollbar.set)
scrollbar.pack(side=RIGHT, fill=Y)
# 初始化路径变量
self.health_excel_path = None
self.issue_excel_path = None
self.default_email = "baokun.dong@kostal.com" # 默认邮箱
self.output_dir = r"D
:\AEB 管理\4.健康表提醒\附件"
self.log_dir = r"D
:\AEB 管理\4.健康表提醒\log"
self.smtp_server = "cnshsmtp001.kostal.int"
self.sender_email = "底层JIRA处理提示no-reply@kostal.com"
# 确保输出目录存在
os.makedirs(self.output_dir, exist_ok=True)
os.makedirs(self.log_dir, exist_ok=True)
# 项目到邮箱的映射关系
self.project_email_map = {
"P08878"
: ["baokun.dong@kostal.com"],
}
# 日志方法
def log(self, message, level="info")
:
if level == "info"
:
logging.info(message)
elif level == "warning"
:
logging.warning(message)
elif level == "error"
:
logging.error(message)
self.log_text.configure(state="normal")
self.log_text.insert(END, message + "\n")
self.log_text.see(END)
self.log_text.configure(state="disabled")
def find_column_index(self, headers, target_column)
:
"""
在表头中查找指定
列名的索引。
:param headers
: 表头列表
:param target_column
: 要查找的
列名（字符串）
:return
: 找到的列索引（从1开始的整数）
"""
for idx, header in enumerate(headers)
:
if header and target_column.lower
() in header.strip
().lower
(): # 忽略大小写匹配
return idx + 1
raise ValueError(f"未找到列：{target_column}，请检查表头：{headers}")
def send_email_with_attachments(self, recipient_email, subject, body, attachment_paths)
:
try
:
# 日志记录邮件内容
self.log(
f"发送邮件：\n 收件人：{recipient_email}\n 主题：{subject}\n 正文：{body[
:100]}...\n 附件：{attachment_paths}")
from email.header import Header
from email.utils import formataddr
# 处理发件人名称的 UTF-8 编码
formatted_sender = formataddr((str(Header("底层JIRA处理提示", "utf-8")), self.sender_email))
# 创建邮件对象
msg = MIMEMultipart
()
msg["From"] = formatted_sender
msg["Subject"] = Header(subject, "utf-8")
if isinstance(recipient_email, list)
:
msg["To"] = ", ".join(recipient_email)
else
:
msg["To"] = recipient_email
# 添加正文
msg.attach(MIMEText(body, "html", _charset="utf-8"))
# 添加附件
for attachment_path in attachment_paths
:
if os.path.exists(attachment_path)
:
with open(attachment_path, "rb") as file
:
part = MIMEBase("application", "octet-stream")
part.set_payload(file.read
())
encoders.encode_base64(part)
# 处理文件名的 UTF-8 编码
filename = os.path.basename(attachment_path)
part.add_header(
"Content-Disposition",
"attachment",
filename=("utf-8", "", filename)
)
msg.attach(part)
self.log(f"附件已添加：{filename}")
else
:
self.log(f"附件文件不存在：{attachment_path}", level="warning")
# 发送邮件
with smtplib.SMTP(self.smtp_server, timeout=30) as server
:
server.sendmail(self.sender_email, recipient_email, msg.as_string
())
self.log(f"邮件发送成功：收件人 {recipient_email}")
except Exception as e
:
# 捕获具体出错位置
self.log(f"邮件发送失败 - 错误详情：{repr(e)}", level="error")
raise
# 检查
列名project key
def check_and_correct_column(self, file_path, old_column_name, new_column_name)
:
"""
检查 Excel 文件的表头，如果包含目标
列名则不做处理；
如果不包含目标
列名但有需要替换的
列名，则替换并保存为新文件，覆盖旧文件。
:param file_path
: Excel 文件路径
:param old_column_name
: 需要查找并修改的旧
列名，比如 "ProjectNum"
:param new_column_name
: 修改为的新
列名，比如 "Project key"
:return
: 如果处理成功或不需要处理，返回 True；否则引发异常
"""
try
:
wb = load_workbook(file_path)
sheet = wb.active
# 获取表头行内容
headers = [cell.value for cell in sheet[1]] # 第一行表头
if new_column_name in headers
:
# 如果目标
列名 (new_column_name) 已存在，则不需要处理
self.log(f"文件 {file_path} 已包含列：{new_column_name}，无需修改。")
return True
if old_column_name in headers
:
# 如果老的
列名存在，将其替换为新的
列名
col_index = headers.index(old_column_name) + 1 # 列索引从1开始
sheet.cell(
row=1, column=col_index, value=new_column_name) # 修改
列名
wb.save(file_path) # 保存更改
self.log(f"文件 {file_path} 中的
列名已从 {old_column_name} 修改为 {new_column_name}。")
return True
# 如果两者都不存在，则报错
raise ValueError(f"文件 {file_path} 缺少列：{new_column_name} 或 {old_column_name}。")
except Exception as e
:
self.log(f"检查和修改
列名时出错：{e}", level="error")
raise
# 第一步：选择健康表文件
def select_health_excel(self)
:
"""
处理健康表文件的选择逻辑。
"""
health_excel_file = filedialog.askopenfilename(
title="选择健康表文件", filetypes=[("Excel文件", "*.xlsx")]
)
if health_excel_file
:
try
:
# 调用类内部方法通过 self
self.check_and_correct_column(health_excel_file, "ProjectNum", "Project key")
# 设置路径
self.health_excel_path = health_excel_file
self.log(f"已成功选择健康表文件
: {health_excel_file}")
self.check_ready_to_generate
()
except Exception as e
:
self.log(f"健康表文件处理中发生错误：{e}", level="error")
messagebox.showerror("错误", f"健康表文件处理失败：{e}")
else
:
self.log("用户取消了健康表文件选择。")
# 第二步：选择问题清单文件
def select_issue_excel(self)
:
"""
处理问题清单文件的选择逻辑。
"""
issue_excel_file = filedialog.askopenfilename(
title="选择问题清单文件", filetypes=[("Excel文件", "*.xlsx")]
)
if issue_excel_file
:
try
:
# 调用类内部方法通过 self
self.check_and_correct_column(issue_excel_file, "ProjectNum", "Project key")
# 设置路径
self.issue_excel_path = issue_excel_file
self.log(f"已成功选择问题清单文件
: {issue_excel_file}")
self.check_ready_to_generate
()
except Exception as e
:
self.log(f"问题清单文件处理中发生错误：{e}", level="error")
messagebox.showerror("错误", f"问题清单文件处理失败：{e}")
else
:
self.log("用户取消了问题清单文件选择。")
# 检查是否准备好解锁下一步按钮
def check_ready_to_generate(self)
:
if self.health_excel_path and self.issue_excel_path
:
self.generate_attachment_button.config(state="normal")
self.send_email_button.config(state="normal")
self.add_horizontal_attachment_button.config(state="normal")
else
:
self.generate_attachment_button.config(state="disabled")
self.send_email_button.config(state="disabled")
self.add_horizontal_attachment_button.config(state="disabled")
# 第三步：生成附件
# 第三步：生成附件
def generate_attachment(self)
:
"""生成附件"""
try
:
self.log("清理旧附件...")
self.clear_output_dir
() # 清空旧附件
self.log("开始生成新附件...")
self.process_attachment_excel(self.issue_excel_path, self.output_dir)
self.log("附件生成完成！")
messagebox.showinfo("完成", "附件生成完成！")
except Exception as e
:
self.log(f"生成附件失败：{e}", level="error")
messagebox.showerror("错误", f"附件生成失败：{e}")
def clear_output_dir(self)
:
"""
清理附件输出目录中的所有内容。
这是为了避免
使用旧的附件文件误发送错误数据。
"""
try
:
for item in os.listdir(self.output_dir)
:
item_path = os.path.join(self.output_dir, item)
if os.path.isfile(item_path) or os.path.islink(item_path)
:
os.unlink(item_path) # 删除文件或软链接
elif os.path.isdir(item_path)
:
shutil.rmtree(item_path) # 递归删除文件夹
self.log("旧附件已全部清理。")
except Exception as e
:
self.log(f"清理输出目录失败：{e}", level="error")
raise
def process_attachment_excel(self, issue_excel_path, output_dir)
:
"""处理问题清单文件并生成项目附件"""
if not os.path.exists(issue_excel_path)
:
raise FileNotFoundError(f"问题清单文件不存在：{issue_excel_path}")
try
:
wb = load_workbook(issue_excel_path)
sheet = wb.active
headers = [cell.value for cell in sheet[1]] # 获取表头
# 确保必要列存在
try
:
project_key_idx = headers.index("Project key")
issue_type_idx = headers.index("Issue Type") # 用于筛选规则
update_over_7_idx = headers.index("update_over_7")
update_over_15_idx = headers.index("update_over_15")
horizontal_project_
number_idx = headers.index("横展项目号")
involvement_bsw_idx = headers.index("Involvement of BSW")
component_idx = headers.index("Component/s")
custom_field_idx = headers.index("Custom field (Custom_2)")
except ValueError as e
:
raise ValueError(f"问题清单文件缺少必要列，请检查表头内容：{e}")
# 初始化分组数据字典
grouped_data_pending = {} # 待处理 JIRA 数据
grouped_data_long_term = {} # 长期未流转 JIRA 数据
# 遍历数据并筛选
for
row in range(2, sheet.max_
row + 1)
:
try
:
project_key = sheet.cell(
row=
row, column=project_key_idx + 1).value
issue_type = sheet.cell(
row=
row, column=issue_type_idx + 1).value
update_over_7 = sheet.cell(
row=
row, column=update_over_7_idx + 1).value
update_over_15 = sheet.cell(
row=
row, column=update_over_15_idx + 1).value
horizontal_project_
number = sheet.cell(
row=
row, column=horizontal_project_
number_idx + 1
).value
involvement_value = sheet.cell(
row=
row, column=involvement_bsw_idx + 1).value
component_value = sheet.cell(
row=
row, column=component_idx + 1).value
custom_field_value = sheet.cell(
row=
row, column=custom_field_idx + 1).value
# -------------------------
# 1. "待处理 JIRA" 筛选逻辑
# -------------------------
if involvement_value == "Yes" and \
issue_type in ["KOCHI AE Issue", "Problem"] and \
(
(
(
not component_value or "Stack" not in component_value
) and custom_field_value not in ["非BSW问题", "Non-BSW"]
)
or (
custom_field_value is None
or custom_field_value.strip
() == ""
or not re.search(
"平台问题|开发设计问题|三方包问题|需求问题|非BSW问题|"
"Development Design Issue|Platform Issue|"
"Party Package Issue|Requirement Issue|Non-BSW Issue",
custom_field_value
)
)
)
:
if not project_key
:
continue # 跳过
无效的 Project Key
if project_key not in grouped_data_pending
:
grouped_data_pending[project_key] = []
row_data = [
sheet.cell(
row=
row, column=col_idx + 1).value
for col_idx in range(len(headers))
]
grouped_data_pending[project_key].append(
row_data)
# -------------------------
# 2. "长期未流转 JIRA" 筛选
# -------------------------
if (update_over_7 == 1) or (update_over_15 == 1)
:
if not project_key
:
continue # 跳过
无效的 Project Key
if project_key not in grouped_data_long_term
:
grouped_data_long_term[project_key] = []
row_data = [
sheet.cell(
row=
row, column=col_idx + 1).value
for col_idx in range(len(headers))
]
grouped_data_long_term[project_key].append(
row_data)
except Exception as e
:
self.log(f"解析问题清单第 {
row} 行时出错：{e}", level="error")
# -------------------------
# 生成每个项目的附件
# -------------------------
for project_key,
rows in grouped_data_pending.items
():
try
:
sanitized_key = self.sanitize_filename(project_key) # 清理非法文件名
folder_path = os.path.join(output_dir, sanitized_key)
# 如果文件夹已存在则先删除
if os.path.exists(folder_path)
:
shutil.rmtree(folder_path)
os.makedirs(folder_path) # 创建新文件夹
# 文件路径
file_path = os.path.join(folder_path, f"{sanitized_key}.xlsx")
# 创建新 Excel 工作簿
new_wb = Workbook
()
# 创建 "待处理 JIRA" Sheet
pending_ws = new_wb.active
pending_ws.title = "待处理JIRA"
pending_ws.append(headers) # 添加标题行
for
row in
rows
:
pending_ws.append(
row) # 写入数据
# 创建 "长期未流转 JIRA" Sheet
if project_key in grouped_data_long_term
:
long_term_ws = new_wb.create_sheet(title="长期未流转JIRA")
long_term_ws.append(headers)
for
row in grouped_data_long_term[project_key]
:
long_term_ws.append(
row)
new_wb.save(file_path) # 保存文件
self.log(f"成功生成附件：{file_path}")
except Exception as e
:
self.log(f"生成项目附件失败 - 项目：{project_key}，错误：{e}", level="error")
except Exception as e
:
raise Exception(f"问题清单处理失败：{e}")
def add_horizontal_attachment(self)
:
"""处理'增加附件横展清单'的逻辑"""
try
:
# 检查问题清单文件路径是否已经选择
if not self.issue_excel_path
:
raise ValueError("未选择问题清单文件！")
# 加载问题清单的 Excel 文件
wb = load_workbook(self.issue_excel_path)
sheet = wb.active
headers = [cell.value.strip
() if cell.value else "" for cell in sheet[1]]
# 检查是否包含所需的列
try
:
project_key_col_idx = self.find_column_index(headers, "Project key")
horizontal_project_col_idx = self.find_column_index(headers, "横展项目号")
status_col_idx = self.find_column_index(headers, "Status")
except ValueError as e
:
raise ValueError(f"问题清单文件缺少必要
列名：{e}")
# 分组数据存储
grouped_data = {}
# 遍历问题清单，根据条件筛选数据
for
row in range(2, sheet.max_
row + 1)
:
project_key = sheet.cell(
row=
row, column=project_key_col_idx).value
horizontal_project_
number = sheet.cell(
row=
row, column=horizontal_project_col_idx).value
status = sheet.cell(
row=
row, column=status_col_idx).value
# 筛选规则
if project_key == "KOCHI_PL" and horizontal_project_
number and status != "Done"
:
if horizontal_project_
number not in grouped_data
:
grouped_data[horizontal_project_
number] = []
row_data = [sheet.cell(
row=
row, column=col_idx + 1).value for col_idx in range(len(headers))]
grouped_data[horizontal_project_
number].append(
row_data)
# 遍历分组数据并生成 Excel 文件
for horizontal_project_
number,
rows in grouped_data.items
():
filename = f"{horizontal_project_
number}_Overdue_Horizontal_Issues.xlsx"
temp_file_path = os.path.join(self.output_dir, filename)
# 创建新的 Excel 工作簿
new_wb = Workbook
()
new_sheet = new_wb.active
new_sheet.title = "横展未完成清单"
new_sheet.append(headers) # 添加表头
for
row in
rows
:
new_sheet.append(
row) # 添加数据行
# 保存到临时路径
new_wb.save(temp_file_path)
self.log(f"生成文件
: {temp_file_path}")
# 搜索 D
:\AEB 管理\4.健康表提醒\附件 是否存在与横展项目号匹配的文件夹
target_folder = None
for folder in os.listdir(self.output_dir)
:
folder_path = os.path.join(self.output_dir, folder)
if os.path.isdir(folder_path) and horizontal_project_
number in folder
:
target_folder = folder_path
break
if target_folder
:
# 如果找到匹配的目标文件夹，将文件移动到目标文件夹
shutil.move(temp_file_path, os.path.join(target_folder, filename))
self.log(f"文件 {filename} 已移动到文件夹
: {target_folder}")
else
:
# 如果未找到目标文件夹，创建新的文件夹
new_folder_path = os.path.join(self.output_dir, horizontal_project_
number)
os.makedirs(new_folder_path, exist_ok=True)
shutil.move(temp_file_path, os.path.join(new_folder_path, filename))
self.log(f"文件 {filename} 已创建并放入新文件夹
: {new_folder_path}")
messagebox.showinfo("完成", "附件横展清单已生成并归档！")
except Exception as e
:
self.log(f"处理附件横展清单时出错：{e}", level="error")
messagebox.showerror("错误", f"生成附件横展清单失败：{e}")
def sanitize_filename(self, filename)
:
"""清理非法文件名并替换非法字符"""
if not filename or filename.strip
() == ""
:
return "unnamed"
return re.sub(r'[\\/
:*?"<>|]', "_", filename.strip
()) # 替换不允许的字符
def send_email_with_attachments(self, recipient_email, subject, body, attachment_paths)
:
try
:
self.log(
f"准备发送邮件：\n 收件人：{recipient_email}\n 主题：{subject}\n 正文(前100字符)：{body[
:100]}...\n 附件：{attachment_paths}")
# 邮件内容逻辑和附件编码...
except Exception as e
:
self.log(f"邮件发送失败：项目 -> {subject}，错误 -> {repr(e)}", level="error")
raise
# -----------------------------------
# 第四步：发送邮件
# -----------------------------------
def save_email_logs(self, email_logs)
:
"""保存邮件发送的日志到文件中"""
try
:
# 创建独一无二的日志文件
timestamp = datetime.now
().strftime("%Y%m%d_%H%M%S")
log_file_path = os.path.join(self.log_dir, f"邮件日志_{timestamp}.log")
# 写入日志文件
with open(log_file_path, "w", encoding="utf-8") as log_file
:
for project_name, status in email_logs
:
log_file.write(f"项目：{project_name}, 状态：{status}\n")
# 日志成功提示
self.log(f"邮件日志已保存到：{log_file_path}")
messagebox.showinfo("日志保存完成", f"邮件日志已成功保存到：{log_file_path}")
except Exception as e
:
# 捕获文件写入问题
self.log(f"保存邮件日志失败：{e}", level="error")
messagebox.showerror("错误", f"保存邮件日志失败，请检查：{e}")
def send_email(self)
:
"""发送邮件，并将对应 Project key 文件夹中的所有附件加到邮件中"""
email_logs = [] # 记录邮件发送成功或失败的日志
try
:
# 打开 SMTP 连接
server = smtplib.SMTP(self.smtp_server, timeout=30)
self.log("已连接 SMTP 服务器。")
except Exception as e
:
self.log(f"无法连接 SMTP 服务器：{e}", level="error")
return
# 遍历附件输出目录中的所有文件夹
for project_folder in os.listdir(self.output_dir)
:
folder_path = os.path.join(self.output_dir, project_folder)
# 确保只处理文件夹
if not os.path.isdir(folder_path)
:
continue
# 获取项目名称，并映射到收件人邮箱
sanitized_project_folder = project_folder.strip
() # 去掉前后空格
recipient_email = self.project_email_map.get(sanitized_project_folder)
# 若项目未映射到邮箱，则发送到默认邮箱
if recipient_email is None
:
self.log(f"项目 {sanitized_project_folder} 未定义收件人，邮箱设置为默认值：{self.default_email}",
level="warning")
recipient_email = [self.default_email]
# 确保收件人以列表形式存在
if isinstance(recipient_email, list)
:
smtp_recipient_list = recipient_email
else
:
smtp_recipient_list = [recipient_email]
# 准备邮件内容
subject = f"项目提醒：{sanitized_project_folder}"
body = self.generate_email_body(sanitized_project_folder)
# 搜索文件夹中的所有附件文件
attachments = []
for file_name in os.listdir(folder_path)
:
file_path = os.path.join(folder_path, file_name)
if os.path.isfile(file_path)
: # 确保只处理文件
attachments.append(file_path)
# 检查是否存在附件
if not attachments
:
self.log(f"文件夹 {folder_path} 中没有找到附件文件，跳过发送邮件。", level="warning")
email_logs.append((sanitized_project_folder, "附件缺失"))
continue
# 发送邮件
try
:
self.send_email_with_attachments(
smtp_recipient_list, subject, body, attachments
)
self.log(f"邮件发送成功 - 项目：{sanitized_project_folder}，收件人：{', '.join(smtp_recipient_list)}")
email_logs.append((sanitized_project_folder, "发送成功"))
except Exception as e
:
self.log(f"邮件发送失败 - 项目：{sanitized_project_folder}，错误：{e}", level="error")
email_logs.append((sanitized_project_folder, f"发送失败：{e}"))
# 关闭 SMTP 连接
server.quit
()
# 保存邮件日志
self.save_email_logs(email_logs)
def generate_email_body(self, project_key)
:
"""生成邮件正文内容"""
try
:
# 加载健康表
wb = load_workbook(self.health_excel_path)
sheet = wb.active
# 找到包含指定字段的表头
headers = [cell.value.strip
() if cell.value else "" for cell in sheet[1]]
try
:
# 动态查找用到的列索引
project_key_col_idx = self.find_column_index(headers, "Project key")
responsible_col_idx = self.find_column_index(headers, "Responsible")
health_status_col_idx = self.find_column_index(headers, "健康度")
total_col_idx = self.find_column_index(headers, "total")
involvement_col_idx = self.find_column_index(headers, "底层参与")
not_classified_col_idx = self.find_column_index(headers, "Stack_底层未分类")
not_analyzed_col_idx = self.find_column_index(headers, "底层未分析")
update_over_7_col_idx = self.find_column_index(headers, "update_over_7")
update_over_15_col_idx = self.find_column_index(headers, "update_over_15")
horizontal_col_idx = self.find_column_index(headers, "总横展数")
Overdue_Horizontal_col_idx = self.find_column_index(headers, "横展超期未完成数")
High_Risk_Prolonged_Unresolved_col_idx = self.find_column_index(headers, "高风险长时间未解决")
except ValueError as e
:
raise ValueError(f"健康表中未找到指定
列名，请检查表头内容：{e}")
# 初始化邮件正文的变量
responsible = "相关负责人"
health_status = "未知"
total = 0
bottom_involvement = 0
not_classified = 0
not_analyzed = 0
update_7 = 0
update_15 = 0
horizontal = 0
Overdue_Horizontal = 0
High_Risk = 0
# 遍历表格，找到与项目键匹配的行数据
for
row in range(2, sheet.max_
row + 1)
:
current_project_key = sheet.cell(
row=
row, column=project_key_col_idx).value
if current_project_key == project_key
: # 匹配项目键
responsible = sheet.cell(
row=
row, column=responsible_col_idx).value or "相关负责人"
health_status = sheet.cell(
row=
row, column=health_status_col_idx).value or "未知"
total = sheet.cell(
row=
row, column=total_col_idx).value or 0
bottom_involvement = sheet.cell(
row=
row, column=involvement_col_idx).value or 0
not_classified = sheet.cell(
row=
row, column=not_classified_col_idx).value or 0
not_analyzed = sheet.cell(
row=
row, column=not_analyzed_col_idx).value or 0
update_7 = sheet.cell(
row=
row, column=update_over_7_col_idx).value or 0
update_15 = sheet.cell(
row=
row, column=update_over_15_col_idx).value or 0
horizontal = sheet.cell(
row=
row, column=horizontal_col_idx).value or 0
Overdue_Horizontal = sheet.cell(
row=
row, column=Overdue_Horizontal_col_idx).value or 0
High_Risk = sheet.cell(
row=
row, column=High_Risk_Prolonged_Unresolved_col_idx).value or 0
break
# 数据格式化
total = int(total)
bottom_involvement = int(bottom_involvement)
horizontal = int(horizontal)
# 数值映射逻辑
def map_value(value)
:
"""将数值映射为实际值或'无需处理'"""
return value if value > 0 else "无需处理"
# 对第二部分的变量进行映射
not_classified = map_value(int(not_classified))
not_analyzed = map_value(int(not_analyzed))
update_7 = map_value(int(update_7))
update_15 = map_value(int(update_15))
Overdue_Horizontal = map_value(int(Overdue_Horizontal))
High_Risk = map_value(int(High_Risk))
# 将健康度转为百分比格式
if isinstance(health_status, (int, float))
:
health_status = f"{health_status * 100
:.2f}%"
except Exception as e
:
self.log(f"健康表数据提取失败：项目 {project_key}，错误：{e}", level="error")
return "<html><body><p>邮件正文生成失败。</p></body></html>"
# HTML 邮件正文模板
html = f"""
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>项目健康状态汇总</title>
<style>
body {{
font-family
: Arial, sans-serif;
font-size
: 14px;
color
: #333;
line-height
: 1.4;
margin
: 0;
padding
: 0;
background-color
: #ffffff; /* 背景保持为纯白 */
}}
.header {{
font-size
: 16px;
font-weight
: bold;
margin-bottom
: 5px;
color
: #0056b3;
}}
.section-title {{
font-size
: 14px;
font-weight
: bold;
color
: #ffffff;
background-color
: #0056b3;
padding
: 4px 8px;
border-radius
: 4px;
margin-top
: 15px;
margin-bottom
: 5px;
}}
.content-list {{
margin
: 0; /* 去除段落外的多余间距 */
padding-left
: 0; /* 去掉缩进 */
font-family
: monospace; /* 等宽字体 */
}}
.content-list p {{
margin
: 0; /* 去掉段落的上下边距 */
padding
: 3px 0; /* 更加紧凑的段落 */
white-space
: pre; /* 保留空格用于对齐 */
}}
.highlight {{
color
: #d9534f;
font-weight
: bold;
}}
.note {{
font-size
: 12px;
color
: #666;
margin-top
: 15px;
padding
: 8px;
background-color
: #f9f9f9;
border-left
: 3px solid #0056b3;
}}
</style>
</head>
<body>
<!-- 无空行紧接呈现 -->
<p class="header">Hello {responsible}</p>
<p>项目 <strong>{project_key}</strong> 的JIRA阶段健康度为<span class="highlight"><strong>{health_status}</strong></span>。具体汇总参考下列数据：</p>
<!-- 第一部分：总信息 -->
<div>
<p class="section-title">JIRA统计汇总（2025/05/01—{datetime.now
().strftime('%Y/%m/%d')}）：</p>
<div class="content-list">
<p>✔️ JIRA 总数： <strong>{total}</strong></p>
<p>✔️ 涉及底层工程师的任务： <strong>{bottom_involvement}</strong></p>
<p>✔️ 横展 JIRA 总数： <strong>{horizontal}</strong></p>
</div>
</div>
<!-- 第二部分：待处理详情 -->
<div>
<p class="section-title">待处理JIRA数汇总(详情见附件)：</p>
<div class="content-list">
<p>⚠️ 未分类底层问题（Component/s 未选）： <span class="highlight"><strong>{not_classified}</strong></span></p>
<p>⚠️ 未分析底层问题（Custom_2 未选）： <span class="highlight"><strong>{not_analyzed}</strong></span></p>
<p>⚠️ 超过 7 天未流转： <strong>{update_7}</strong></p>
<p>⚠️ 超过 15 天未流转： <strong>{update_15}</strong></p>
<p>⚠️ 高风险 JIRA 超期未处理： <span class="highlight"><strong>{High_Risk}</strong></span></p>
<p>⚠️ 横展超期未处理任务： <strong>{Overdue_Horizontal}</strong></p>
</div>
</div>
<!-- 结尾部分 -->
<p class="note">为了确保项目健康，请及时处理上述问题。如有任何疑问，请联系dong baokun。附件 1 提供了详细数据字段。</p>
</body>
</html>
"""
return html
# -----------------------------------
# 主程序入口
# -----------------------------------
def main
():
root = Tk
()
app = ExcelWordApp(root)
root.mainloop
()
if __name__ == "__main__"
:
main
()
本文探讨了在SQL查询中使用ROW_NUMBER()方法时出现列名无效错误的原因，并提供了修正方法，通过包一层查询来解决问题。适用于数据库管理和查询优化场景。
扫一扫
8112
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
