import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import (
    Reference,
    BarChart,
    PieChart,
    LineChart,
    AreaChart,
    ScatterChart,
    RadarChart,
    Series
)

# 1. 读取Excel数据
file_path = 'data.xlsx'
df = pd.read_excel(file_path)

# 2. 创建Excel工作簿并写入数据
wb = Workbook()
ws = wb.active

# 将DataFrame数据写入工作表
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 3. 生成柱状图（Sales数据）
bar_chart = BarChart()
data = Reference(ws, min_col=2, min_row=2, max_row=len(df) + 1)  # Sales列数据
categories = Reference(ws, min_col=1, min_row=2, max_row=len(df) + 1)  # Month列作为类别
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)
ws.add_chart(bar_chart, "E2")  # 插入图表到E2单元格

# 4. 生成饼图（Market Share数据）
pie_chart = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=len(df) + 1)  # Month列作为标签
data = Reference(ws, min_col=4, min_row=1, max_row=len(df) + 1)  # Market Share列数据
pie_chart.add_data(data, titles_from_data=True)
pie_chart.set_categories(labels)
ws.add_chart(pie_chart, "J2")  # 插入图表到J2单元格

# 5. 生成折线图（Profit数据）
line_chart = LineChart()
data = Reference(ws, min_col=3, min_row=2, max_row=len(df) + 1)  # Profit列数据
line_chart.add_data(data, titles_from_data=True)
line_chart.set_categories(categories)
ws.add_chart(line_chart, "M2")  # 插入图表到M2单元格

# 6. 生成面积图（Sales数据）
area_chart = AreaChart()
area_chart.add_data(data, titles_from_data=True)
area_chart.set_categories(categories)
ws.add_chart(area_chart, "R2")  # 插入图表到R2单元格

# 7. 生成散点图（Sales vs. Profit）
scatter_chart = ScatterChart()
xvalues = Reference(ws, min_col=2, min_row=2, max_row=len(df) + 1)  # Sales列数据
yvalues = Reference(ws, min_col=3, min_row=2, max_row=len(df) + 1)  # Profit列数据
series = Series(yvalues, xvalues, title_from_data=True)
scatter_chart.series.append(series)
ws.add_chart(scatter_chart, "V2")  # 插入图表到V2单元格

# 8. 生成雷达图（Market Share数据）
radar_chart = RadarChart()
data_radar = Reference(ws, min_col=4, min_row=2, max_row=len(df) + 1)  # Market Share列数据
radar_chart.add_data(data_radar, titles_from_data=True)
radar_chart.set_categories(labels)
ws.add_chart(radar_chart, "Z2")  # 插入图表到Z2单元格

# 9. 生成组合图（Sales和Profit）
combo_chart = BarChart()
data_sales = Reference(ws, min_col=2, min_row=2, max_row=len(df) + 1)
combo_chart.add_data(data_sales, titles_from_data=True)
combo_chart.set_categories(categories)

line_chart2 = LineChart()
data_profit = Reference(ws, min_col=3, min_row=2, max_row=len(df) + 1)
line_chart2.add_data(data_profit, titles_from_data=True)
line_chart2.set_categories(categories)

combo_chart += line_chart2
ws.add_chart(combo_chart, "AE2")  # 插入组合图表到AE2单元格

# 10. 保存Excel文件
output_file_path = 'sample_data_with_charts.xlsx'
wb.save(output_file_path)
print(f"Excel文件已保存为 {output_file_path}，图表生成完毕！")