import osimport pandas as pdimport matplotlib.pyplot as pltimport glob# 设置中文字体plt.rcParams['font.sans-serif'] = ['SimHei', 'Microsoft YaHei', 'DejaVu Sans']plt.rcParams['axes.unicode_minus'] = Falsedef merge_excel_and_chart(folder_path, x_column=None, y_column=None, chart_type='bar', output_file="合并结果.xlsx", chart_name="图表.png"): """ 合并文件夹中所有Excel文件，并生成图表 """ excel_files = glob.glob(os.path.join(folder_path, '*.xlsx')) + glob.glob(os.path.join(folder_path, '*.xls')) if not excel_files: print("❌ 未找到Excel文件") return print(f"📂 找到 {len(excel_files)} 个Excel文件") all_data = [] for file in excel_files: try: df = pd.read_excel(file) df['来源文件'] = os.path.basename(file) all_data.append(df) print(f" ✅ {os.path.basename(file)} ({len(df)} 行)") except Exception as e: print(f" ❌ {os.path.basename(file)} 读取失败: {str(e)[:30]}") if not all_data: print("❌ 没有成功读取任何文件") return merged_df = pd.concat(all_data, ignore_index=True) output_path = os.path.join(folder_path, output_file) merged_df.to_excel(output_path, index=False) print(f"\n✅ 合并完成！共 {len(merged_df)} 行") print(f"📁 合并结果: {output_path}") # 生成图表 if x_column and y_column and x_column in merged_df.columns and y_column in merged_df.columns: try: chart_data = merged_df.groupby(x_column)[y_column].sum().reset_index() plt.figure(figsize=(12, 6)) if chart_type == 'bar': plt.bar(chart_data[x_column], chart_data[y_column], color='steelblue') plt.title(f'{y_column} 按 {x_column} 汇总（柱状图）', fontsize=14) elif chart_type == 'line': plt.plot(chart_data[x_column], chart_data[y_column], marker='o', linewidth=2, color='coral') plt.title(f'{y_column} 按 {x_column} 汇总（折线图）', fontsize=14) elif chart_type == 'pie': plt.pie(chart_data[y_column], labels=chart_data[x_column], autopct='%1.1f%%') plt.title(f'{y_column} 按 {x_column} 汇总（饼图）', fontsize=14) else: plt.bar(chart_data[x_column], chart_data[y_column], color='steelblue') plt.title(f'{y_column} 按 {x_column} 汇总', fontsize=14) plt.xlabel(x_column, fontsize=12) plt.ylabel(y_column, fontsize=12) plt.xticks(rotation=45) plt.tight_layout() chart_path = os.path.join(folder_path, chart_name) plt.savefig(chart_path, dpi=150, bbox_inches='tight') print(f"📊 图表已保存: {chart_path}") plt.close() except Exception as e: print(f"⚠️ 生成图表失败: {e}") else: print("\n💡 提示: 指定x_column和y_column可自动生成图表")def merge_by_keyword(folder_path, keyword, x_column=None, y_column=None, chart_type='bar', output_file="按关键词合并.xlsx"): """只合并文件名包含关键词的Excel文件""" excel_files = glob.glob(os.path.join(folder_path, '*.xlsx')) + glob.glob(os.path.join(folder_path, '*.xls')) excel_files = [f for f in excel_files if keyword.lower() in os.path.basename(f).lower()] if not excel_files: print(f"❌ 未找到文件名包含「{keyword}」的Excel文件") return print(f"📂 找到 {len(excel_files)} 个匹配的Excel文件") all_data = [] for file in excel_files: try: df = pd.read_excel(file) df['来源文件'] = os.path.basename(file) all_data.append(df) print(f" ✅ {os.path.basename(file)} ({len(df)} 行)") except Exception as e: print(f" ❌ {os.path.basename(file)} 读取失败") if not all_data: print("❌ 没有成功读取任何文件") return merged_df = pd.concat(all_data, ignore_index=True) output_path = os.path.join(folder_path, output_file) merged_df.to_excel(output_path, index=False) print(f"\n✅ 合并完成！共 {len(merged_df)} 行") print(f"📁 保存至: {output_path}") if x_column and y_column and x_column in merged_df.columns and y_column in merged_df.columns: try: chart_data = merged_df.groupby(x_column)[y_column].sum().reset_index() plt.figure(figsize=(12, 6)) if chart_type == 'bar': plt.bar(chart_data[x_column], chart_data[y_column], color='coral') plt.title(f'{y_column} 按 {x_column} 汇总（柱状图）', fontsize=14) elif chart_type == 'line': plt.plot(chart_data[x_column], chart_data[y_column], marker='o', linewidth=2, color='coral') plt.title(f'{y_column} 按 {x_column} 汇总（折线图）', fontsize=14) elif chart_type == 'pie': plt.pie(chart_data[y_column], labels=chart_data[x_column], autopct='%1.1f%%') plt.title(f'{y_column} 按 {x_column} 汇总（饼图）', fontsize=14) else: plt.bar(chart_data[x_column], chart_data[y_column], color='coral') plt.title(f'{y_column} 按 {x_column} 汇总', fontsize=14) plt.xlabel(x_column, fontsize=12) plt.ylabel(y_column, fontsize=12) plt.xticks(rotation=45) plt.tight_layout() chart_path = os.path.join(folder_path, f"图表_{keyword}.png") plt.savefig(chart_path, dpi=150, bbox_inches='tight') print(f"📊 图表已保存: {chart_path}") plt.close() except Exception as e: print(f"⚠️ 生成图表失败: {e}")if __name__ == "__main__": print("=" * 50) print("📊 批量合并Excel并生成图表") print("=" * 50) print() path = input("请输入Excel文件夹路径: ").strip() if path.startswith('"') and path.endswith('"'): path = path[1:-1] path = path.replace('\\', '/') if not os.path.exists(path): print("❌ 路径不存在") input("按回车键退出...") exit() print("\n1. 合并所有Excel并生成图表") print("2. 按关键词筛选后合并") choice = input("请选择（1或2，默认1）: ").strip() or "1" x_col = input("\n请输入作为X轴的列名（如 产品名称，直接回车跳过图表）: ").strip() y_col = input("请输入作为Y轴的列名（如 销售额，直接回车跳过图表）: ").strip() if x_col and y_col: print("\n图表类型: bar(柱状图), line(折线图), pie(饼图)") chart_type = input("请选择（默认bar）: ").strip() or "bar" else: chart_type = 'bar' if choice == "2": keyword = input("\n请输入关键词（如 销售）: ").strip() if not keyword: print("❌ 关键词不能为空") input("按回车键退出...") exit() merge_by_keyword(path, keyword, x_col if x_col else None, y_col if y_col else None, chart_type) else: merge_excel_and_chart(path, x_col if x_col else None, y_col if y_col else None, chart_type) input("\n按回车键退出...")
10个月宝宝每天需要喝多少奶粉？
