Python绘制Excel图表

今天讲解下如何使用Python绘制各种Excel图表,下面我们以绘制饼状图、柱状图、水平图、泡泡图为例来说明。

import openpyxl
from openpyxl.chart import Reference, Series, PieChart, BarChart, BubbleChart

绘制饼图


wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'pieChart'
rows = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40]
]
# for循环写入Excel
for row in rows:
ws.append(row)

# 创建一个饼图对象
pie = PieChart()
# 定义标签和数据范围
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=2, max_row=5)

# 添加数据和标签
pie.add_data(data)
pie.set_categories(labels)

# 设置饼图标题
pie.title = 'Pies sold by category'

# 设置饼图的位置
ws.add_chart(pie, 'C1')

绘制柱形图


ws = wb.create_sheet('columnChart')
rows = [
('Number', 'Batch1', 'Batch2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30)
]
for row in rows:
ws.append(row)
# 创建一个柱形图对象
columnChart = BarChart()
columnChart.type = 'col'
columnChart.style = 10 # 这种风格,色彩对比很鲜明
columnChart.title = 'batchColumnChart'

# 定义横纵坐标的标题
columnChart.x_axis.title = 'Test number'
columnChart.y_axis.title = 'Sample length(mm)'

# 定义category和data范围
categories = Reference(ws, min_col=1, min_row=2, max_row=7)
data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=7)

# 添加category和data
columnChart.set_categories(categories)
columnChart.add_data(data)

# 设置柱形图的位置
ws.add_chart(columnChart, 'D1')

绘制水平图


ws = wb.create_sheet('barChart')
rows = [
('Number', 'Batch1', 'Batch2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30)
]
for row in rows:
ws.append(row)
# 创建一个水平图对象
barChart = BarChart()
barChart.type = 'bar'
barChart.style = 10 # 这种风格,色彩对比很鲜明
barChart.title = 'batchBarChart'

# 定义横纵坐标的标题
barChart.y_axis.title = 'Sample length(mm)'
barChart.x_axis.title = 'Test number'
# 注意:对于柱形图变成水平图,x和y轴的标题不用改变。

# 定义category和data范围
categories = Reference(ws, min_col=1, min_row=2, max_row=7)
data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=7)

# 添加category和data
barChart.set_categories(categories)
barChart.add_data(data)

# 设置水平图的位置
ws.add_chart(barChart, 'D1')

绘制泡泡图


ws = wb.create_sheet('bubbleChart')
rows = [
('Number of products', 'Sales in USA', 'Market share'),
(14, 12200, 15),
(20, 60000, 33),
(18, 24400, 10),
(22, 32000, 42),
(),
(12, 8200, 18),
(15, 50000, 30),
(19, 24400, 15),
(25, 25000, 50)
]
for row in rows:
ws.append(row)
# 创建一个泡泡图对象
bubbleChart = BubbleChart()
bubbleChart.style = 18 # 这种风格,色彩对比很鲜明
bubbleChart.title = 'bubbleChart'

# 添加第一组数据
xValues = Reference(ws, min_col=1, min_row=2, max_row=5)
yValues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yValues, xvalues=xValues, zvalues=size, title=2013)
bubbleChart.series.append(series)

# 添加第二组数据
xValues = Reference(ws, min_col=1, min_row=7, max_row=10)
yValues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yValues, xvalues=xValues, zvalues=size, title=2014)
bubbleChart.series.append(series)

# 设置泡泡图的位置
ws.add_chart(bubbleChart, 'D1')

# 保存工作簿
wb.save('charts.xlsx')

运行程序结果如下:
Python绘制Excel图表

 Python绘制Excel图表

  Python绘制Excel图表

  Python绘制Excel图表

   最后,觉得有用的朋友麻烦点赞,推荐一下,更多精彩内容持续更新中!也欢迎大家评论区留言、交流,共同学习进步。

 


 

 

 






上一篇:pdf reference官方指南之-语法基础和文件结构


下一篇:JVM-再谈引用