使用python将图表与来自多个工作表的数据添加到excel中

pip install openpyxl
pip install matplotlib

import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import matplotlib.pyplot as plt

wb = Workbook()
ws = wb.active

# 假设有两个工作表sheet1和sheet2
data_sheet1 = [
    ['Name', 'Age', 'Gender'],
    ['John', 25, 'Male'],
    ['Alice', 30, 'Female'],
    ['Bob', 35, 'Male']
]

data_sheet2 = [
    ['Country', 'Population'],
    ['China', 1439323776],
    ['India', 1380004385],
    ['USA', 331002651]
]

# 将数据添加到工作表sheet1
for row in data_sheet1:
    ws.append(row)

# 将数据添加到工作表sheet2
ws2 = wb.create_sheet(title='Sheet2')
for row in data_sheet2:
    ws2.append(row)

# 创建一个柱状图
fig, ax = plt.subplots()
x = ['John', 'Alice', 'Bob']
y = [25, 30, 35]
ax.bar(x, y)

# 将图表保存为图片
chart_filename = 'chart.png'
plt.savefig(chart_filename)

# 将图片添加到Excel中
img = openpyxl.drawing.image.Image(chart_filename)
img.anchor = 'E2'  # 图表将被添加到单元格E2的位置
ws.add_image(img)

# 删除保存的图表图片
import os
os.remove(chart_filename)

excel_filename = 'data.xlsx'
wb.save(excel_filename)