3. opnepyxl 制作excel饼状图

1. 准备数据

这次模拟的数据是4家店铺的利润

from openpyxl import Workbook
from openpyxl.chart import (
    PieChart,
    ProjectedPieChart,
    Reference
)
from openpyxl.chart.series import DataPoint


wb = Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "饼状图"

data = [
    ('1店', 120),
    ('2店', 100),
    ('3店', 50),
    ('4店', 30)
]

sheet['A1'] = '店铺'
sheet['B1'] = '利润'

for row in data:
    sheet.append(row)

wb.save("./data/饼状图.xlsx")

excel 里的数据截图

2. 制作饼图

for row in data:
    sheet.append(row)

pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=5)      # 确定label的范围
data = Reference(sheet, min_col=2, min_row=1, max_row=5)        # 确定数据的范围
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)                  # 设置label
pie.title = "利润"
sheet.add_chart(pie, "A8")                  # 在A8 位置添加

wb.save("./data/饼状图.xlsx")

效果图

2.1 制作出分离效果

for row in data:
    sheet.append(row)

pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=5)      # 确定label的范围
data = Reference(sheet, min_col=2, min_row=1, max_row=5)        # 确定数据的范围
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)                  # 设置label
pie.title = "利润"

# 让第一块分离出来
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

sheet.add_chart(pie, "A8")                  # 在A8 位置添加

wb.save("./data/饼状图.xlsx")

2.2 显示标签，百分比，具体数值

from openpyxl import Workbook
from openpyxl.chart import (
    PieChart,
    ProjectedPieChart,
    Reference
)
from openpyxl.chart.series import DataPoint
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties


wb = Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "饼状图"

data = [
    ('1店', 120),
    ('2店', 100),
    ('3店', 50),
    ('4店', 30)
]

sheet['A1'] = '店铺'
sheet['B1'] = '利润'

for row in data:
    sheet.append(row)

pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=5)      # 确定label的范围
data = Reference(sheet, min_col=2, min_row=1, max_row=5)        # 确定数据的范围
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)                  # 设置分类
pie.title = "利润"

s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True     # 标签显示
s1.dLbls.showVal = True     # 数量显示
s1.dLbls.showPercent = True     # 百分比显示
axis = CharacterProperties(sz=1800)     # 图表中字体大小 *100
s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])


sheet.add_chart(pie, "A8")                  # 在A8 位置添加

wb.save("./data/饼状图.xlsx")

最终效果

3. 投影饼图

某些情况下，有些饼块由于太小，无法清晰的显示，这种情况下可以使用投影饼图

from openpyxl import Workbook
from openpyxl.chart import (
    PieChart,
    ProjectedPieChart,
    Reference
)
from openpyxl.chart.series import DataPoint


wb = Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "饼状图"

data = [
    ('1店', 120),
    ('2店', 100),
    ('3店', 0.5),
    ('4店', 0.4)
]

sheet['A1'] = '店铺'
sheet['B1'] = '利润'

for row in data:
    sheet.append(row)

projected_pie = ProjectedPieChart()
projected_pie.type = "pie"          # 投影后还是用饼状图
projected_pie.splitType = "val"     # 根据值切分
labels = Reference(sheet, min_col=1, min_row=2, max_row=5)
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)

sheet.add_chart(projected_pie, "A10")

from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar"              # 投影后用柱状图
projected_bar.splitType = 'pos'         # 根据位置切分

sheet.add_chart(projected_bar, "A27")

wb.save("./data/饼状图.xlsx")

效果图

投影后仍然采用pie的效果还是挺不错的。

4. 3D饼状图

from openpyxl import Workbook

from openpyxl.chart import (
    PieChart3D,
    Reference
)
wb = Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "饼状图"

data = [
    ('1店', 120),
    ('2店', 100),
    ('3店', 50),
    ('4店', 30)
]

sheet['A1'] = '店铺'
sheet['B1'] = '利润'

for row in data:
    sheet.append(row)

pie = PieChart3D()
labels = Reference(sheet, min_col=1, min_row=2, max_row=5)
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "3D饼状图"

sheet.add_chart(pie, "A8")                  # 在A8 位置添加

wb.save("./data/饼状图.xlsx")

最终效果图