python操作excel-----生成excel内图表

from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, Reference, LineChart

# 只能打开已经存在的表格，不能用该方法创建一个新的表格
workbook = load_workbook(filename='test.xlsx')

# 通过sheet名称获取表格 如果只有一张表，可以直接打开
sheet = workbook.active

# 插入图片
logo = Image('tou.jpg')
logo.height = 100
logo.width = 100
sheet.add_image(logo, 'C1')


# 插入柱状图
chart = BarChart()  # 新建一个柱状图
data = Reference(worksheet=sheet, min_row=1, max_row=3, min_col=2, max_col=4)  # 设定数据引用范围
categories = Reference(sheet, min_row=1, max_row=3, min_col=2, max_col=4)  # 设定X轴项目名称
chart.add_data(data, titles_from_data=True)  # 给柱状图添加数据 数据源中有标题
chart.set_categories(categories)  # 设定X轴类目
sheet.add_chart(chart, 'E2')


# 插入条形图
chart = LineChart()  # 新建一个柱状图
data = Reference(worksheet=sheet, min_row=1, max_row=3, min_col=2, max_col=4)  # 设定数据引用范围
categories = Reference(sheet, min_row=1, max_row=3, min_col=2, max_col=4)  # 设定X轴项目名称
chart.add_data(data,from_rows=True, titles_from_data=True)  # 给柱状图添加数据 数据源中有标题
chart.set_categories(categories)  # 设定X轴类目
sheet.add_chart(chart, 'E2')


# 修改后记得保存
workbook.save(filename='test.xlsx')