如何使用Python的Openpyxl为饼图元素添加百分比？

我通过Python的Openpyxl库在Excel文件中创建了一个饼图，希望为饼图的每个元素添加百分比标识。以下是我创建该饼图的代码片段：

from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference

imd=30
lid=18.5
cad=10
wad=12
rud=16.5
sad=0
ht=12
othd=40


wb = Workbook()  
sheet = wb.active
sheet.title="Data"

fi,ft=[],[]
sheet['C11'] = imd
sheet['C12'] = lid
sheet['C13'] = cad
sheet['C14'] = wad
sheet['C15'] = rud
sheet['C16'] = sad
sheet['C17'] = ht
sheet['C18'] = othd


sheet2 = wb.create_sheet()
sheet2.title='Charts'

pie=PieChart()

labels = Reference(sheet, min_col=1, min_row=11, max_row=18)
data = Reference(sheet, min_col=3, min_row=10, max_row=18)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pie Chart"

sheet2.add_chart(pie, 'A1')

wb.save("temp.xlsx")

请问是否有实现该需求的方法？

解决方案 #

可以通过Openpyxl的DataLabelList类为饼图添加百分比数据标签，具体步骤如下：

1. 导入DataLabelList类
2. 为饼图创建数据标签实例，并开启百分比显示

修改后的完整代码如下：

from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
from openpyxl.chart.label import DataLabelList  # 新增导入

imd=30
lid=18.5
cad=10
wad=12
rud=16.5
sad=0
ht=12
othd=40


wb = Workbook()  
sheet = wb.active
sheet.title="Data"

# 补充分类标签（原代码未设置A列内容，补上后饼图会显示对应分类名称）
sheet['A11'] = "IMD"
sheet['A12'] = "LID"
sheet['A13'] = "CAD"
sheet['A14'] = "WAD"
sheet['A15'] = "RUD"
sheet['A16'] = "SAD"
sheet['A17'] = "HT"
sheet['A18'] = "OTHD"

sheet['C11'] = imd
sheet['C12'] = lid
sheet['C13'] = cad
sheet['C14'] = wad
sheet['C15'] = rud
sheet['C16'] = sad
sheet['C17'] = ht
sheet['C18'] = othd


sheet2 = wb.create_sheet()
sheet2.title='Charts'

pie=PieChart()

labels = Reference(sheet, min_col=1, min_row=11, max_row=18)
data = Reference(sheet, min_col=3, min_row=10, max_row=18)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pie Chart"

# 添加百分比数据标签
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
# 可选：将标签放在饼图外侧，提升可读性
pie.dataLabels.position = "outsideEnd"

sheet2.add_chart(pie, 'A1')

wb.save("temp.xlsx")

说明 #

• showPercent=True：开启百分比显示，Excel会自动计算每个切片的占比并渲染
• position="outsideEnd"：可选配置，标签位置支持center（中心）、insideEnd（切片内侧）等多种选项
• 补充A列分类标签后，饼图会同时显示分类名称与百分比，效果更完整

内容的提问来源于stack exchange，提问作者NKK