检测系统中的英文字体：
import matplotlib.font_manager as fm# 列出系统中所有支持中文的字体
fonts = fm.findSystemFonts(fontpaths=None, fontext='ttf')
chinese_fonts = []
for font in fonts:try:font_prop = fm.FontProperties(fname=font)font_name = font_prop.get_name()# 筛选中文字体（包含常见中文字体关键词）if any(key in font.lower() for key in ['hei', 'song', 'kai', 'yahei', 'ming', 'li']):chinese_fonts.append((font_name, font))except:continue# 打印可用中文字体
print("系统可用中文字体列表：")
for name, path in chinese_fonts[:10]: # 只显示前10个print(f"字体名称：{name}，路径：{path}")
# encoding: utf-8
# 版权所有 2026 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：python.exe -m pip install --upgrade pip -i https://pypi.tuna.tsinghua.edu.cn/simple
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2024.3.6 python 3.11
# os : windows 10
# database : mysql 9.0 sql server 2019, postgreSQL 17.0 Oracle 21c Neo4j
# Datetime : 2026/1/21 21:25
# User : geovindu pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple pip install matplotlib -i https://pypi.tuna.tsinghua.edu.cn/simple pip3 install numpy -i https://pypi.tuna.tsinghua.edu.cn/simple
# Product : PyCharm
# Project : PyExceport
# File : Main.py
'''
pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install xlwt -i https://pypi.tuna.tsinghua.edu.cn/simple
python.exe -m pip install --upgrade pip -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install matplotlib pandas
pip install xlwt -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install xlsxwriter -i https://pypi.tuna.tsinghua.edu.cn/simple
'''
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
import os
from typing import Dict
import warningswarnings.filterwarnings('ignore')# 修复中文字体问题（核心修改部分）
def setup_chinese_font():"""配置matplotlib中文字体"""try:# 优先尝试微软雅黑（Windows默认）plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'DejaVu Sans']plt.rcParams['axes.unicode_minus'] = Falseplt.rcParams['font.family'] = 'sans-serif'print("中文字体配置成功（使用微软雅黑）")except:# 备选方案：自动查找系统中文字体font_paths = fm.findSystemFonts(fontext='ttf')chinese_fonts = [f for f in font_paths if any(c in f.lower() for c in ['hei', 'yahei', 'song'])]if chinese_fonts:font_prop = fm.FontProperties(fname=chinese_fonts[0])plt.rcParams['font.sans-serif'] = [font_prop.get_name(), 'DejaVu Sans']plt.rcParams['axes.unicode_minus'] = Falseprint(f"中文字体配置成功（使用系统字体：{font_prop.get_name()}）")else:print("警告：未找到中文字体，图表中文可能显示异常")# 初始化字体
setup_chinese_font()def check_dependencies() -> bool:"""检查必要的依赖是否安装"""required_packages = ['openpyxl', 'matplotlib']missing_packages = []for pkg in required_packages:try:__import__(pkg)except ImportError:missing_packages.append(pkg)if missing_packages:print(f"错误：缺少必要的依赖包：{', '.join(missing_packages)}")print(f"请执行安装命令：pip install {' '.join(missing_packages)}")return Falsereturn Truedef compare_two_sheets(excel_path: str,sheet1_name: str = 'Sheet1',sheet2_name: str = 'Sheet2',key_column: str = '员工号',output_excel: str = '人员比对详细报告.xlsx',output_image: str = '人员比对结果图表.png'
) -> Dict:"""比对两个Excel工作表中的人员数据:param excel_path: Excel文件路径:param sheet1_name: 第一个工作表名称:param sheet2_name: 第二个工作表名称:param key_column: 用于比对的关键字段（如员工号、身份证号）:param output_excel: 输出报告的Excel路径:param output_image: 输出图表的路径:return: 比对结果字典"""# 先检查依赖if not check_dependencies():raise ImportError("依赖检查失败，请先安装缺失的包")# 检查文件是否存在if not os.path.exists(excel_path):raise FileNotFoundError(f"Excel文件不存在：{excel_path}")try:# 读取两个工作表df1 = pd.read_excel(excel_path, sheet_name=sheet1_name)df2 = pd.read_excel(excel_path, sheet_name=sheet2_name)# 检查关键字段是否存在if key_column not in df1.columns:raise ValueError(f"Sheet1中缺少关键字段：{key_column}")if key_column not in df2.columns:raise ValueError(f"Sheet2中缺少关键字段：{key_column}")# 去除空值和重复值df1_clean = df1.dropna(subset=[key_column]).drop_duplicates(subset=[key_column])df2_clean = df2.dropna(subset=[key_column]).drop_duplicates(subset=[key_column])# 获取两个表的关键字段集合set1 = set(df1_clean[key_column].astype(str))set2 = set(df2_clean[key_column].astype(str))# 计算交集、差集common = set1 & set2 # 两个表都有的only_in_sheet1 = set1 - set2 # 仅Sheet1有的only_in_sheet2 = set2 - set1 # 仅Sheet2有的# 筛选对应的数据df_common = df1_clean[df1_clean[key_column].astype(str).isin(common)]df_only1 = df1_clean[df1_clean[key_column].astype(str).isin(only_in_sheet1)]df_only2 = df2_clean[df2_clean[key_column].astype(str).isin(only_in_sheet2)]# 生成统计结果result = {'total_sheet1': len(df1_clean),'total_sheet2': len(df2_clean),'common_count': len(common),'only_sheet1_count': len(only_in_sheet1),'only_sheet2_count': len(only_in_sheet2),'common_data': df_common,'only_sheet1_data': df_only1,'only_sheet2_data': df_only2}# 生成Excel报告with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:# 汇总表summary_df = pd.DataFrame({'项目': ['Sheet1总人数', 'Sheet2总人数', '两个表都有', '仅Sheet1有', '仅Sheet2有'],'数量': [result['total_sheet1'],result['total_sheet2'],result['common_count'],result['only_sheet1_count'],result['only_sheet2_count']]})summary_df.to_excel(writer, sheet_name='比对汇总', index=False)# 各分类数据df_only1.to_excel(writer, sheet_name='仅在Sheet1', index=False)df_only2.to_excel(writer, sheet_name='仅在Sheet2', index=False)df_common.to_excel(writer, sheet_name='两个表都有', index=False)# 生成可视化图表fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 6))# 饼图：人员分布比例pie_labels = ['仅Sheet1', '仅Sheet2', '两个表都有']pie_sizes = [len(only_in_sheet1), len(only_in_sheet2), len(common)]ax1.pie(pie_sizes, labels=pie_labels, autopct='%1.1f%%', startangle=90)ax1.set_title('人员分布比例')# 柱状图：数量对比bar_x = ['总人数', '独有人员', '共有人员']bar_sheet1 = [result['total_sheet1'],result['only_sheet1_count'],result['common_count']]bar_sheet2 = [result['total_sheet2'],result['only_sheet2_count'],result['common_count']]x = range(len(bar_x))width = 0.35ax2.bar([i - width / 2 for i in x], bar_sheet1, width, label='Sheet1')ax2.bar([i + width / 2 for i in x], bar_sheet2, width, label='Sheet2')ax2.set_xlabel('人员类型')ax2.set_ylabel('人数')ax2.set_title('两个表人员数量对比')ax2.set_xticks(x)ax2.set_xticklabels(bar_x)ax2.legend()ax2.grid(axis='y', alpha=0.3)plt.tight_layout()plt.savefig(output_image, dpi=300, bbox_inches='tight')plt.close()print(f"比对完成！")print(f"- 报告已保存至：{output_excel}")print(f"- 图表已保存至：{output_image}")print(f"- 仅Sheet1有 {result['only_sheet1_count']} 人，仅Sheet2有 {result['only_sheet2_count']} 人")return resultexcept Exception as e:print(f"比对过程中出现错误: {str(e)}")raise# 调用示例
if __name__ == "__main__":try:result = compare_two_sheets(excel_path='人员比对.xlsx', # 替换为你的Excel文件路径sheet1_name='Sheet1',sheet2_name='Sheet2',key_column='员工号',output_excel='人员比对详细报告.xlsx',output_image='人员比对结果图表.png')except Exception as e:print(f"执行失败：{e}")
