openpyxl学习笔记2 ---- 绘制图表

刚学习绘制图表，做下笔记，方便后续查阅。

import openpyxl
from openpyxl.chart import (
    Reference,          #表格里的数据
    Series,             #三维
    PieChart,
    BarChart,
    BubbleChart
)

wb = openpyxl.Workbook()

饼图

#Pie chart
ws = wb.active
ws.title = 'PieChart'
data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]
for row in data:
    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, 'A15')

直方图

#Bar chart
ws = wb.create_sheet('BarChart')
rows = [
    ['Number', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]

for row in rows:
    ws.append(row)
chart1 = BarChart()
chart1.type = 'col'     #bar:水平图；col：垂直图
chart1.style = 10       #设置图表颜色
chart1.title = 'Bar Chart'
chart1.y_axis.title = 'Sample Length(mm)'
chart1.x_axis.title = 'Test Number'

cats = Reference(ws, min_col=1, min_row=2, max_row=7)
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
ws.add_chart(chart1, anchor='A10')

泡泡图

#Bubble chart
ws = wb.create_sheet('Bubble chart')
rows = [
    ("Number of Products", "Sales in USD", "Market share"),
    (14, 12200, 15),
    (20, 60000, 33),
    (18, 24400, 10),
    (22, 32000, 42),
    (),
    (12, 8200, 18),
    (15, 50000, 30),
    (19, 22400, 15),
    (25, 25000, 50),
]

for row in rows:
    ws.append(row)
chart2 = BubbleChart()
chart2.style = 18

#add the first series of data
xVal = Reference(ws,min_col=1, min_row=2, max_row=5)
yVal = 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=yVal, xvalues=xVal, zvalues=size, title = '2013')
chart2.series.append(series)

#add the second series of data
xVal = Reference(ws,min_col=1, min_row=7, max_row=10)
yVal = 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=yVal, xvalues=xVal, zvalues=size, title= '2014')
chart2.series.append(series)

ws.add_chart(chart2,'E1')

wb.save('chart.xlsx')

ps：既没有心得也没有疑问 ，估计要到实操时才会有体会了。