在openpyxl中格式化图表数据标签 [英] Formatting chart data labels in openpyxl

问题描述

我正在使用Python 3.6.3用openpyxl(2.4.9)编写一些excel表.在图表数据上获取数据标签并不明显，但是当我尝试格式化所述数据标签时，事情开始变得很糟糕.我想要做的是改变他们的位置，也改变他们的旋转.有人有想法吗?我对python有点新鲜，所以任何建议都很棒. 这是我尝试过的:

I'm using Python 3.6.3 to write some excel sheets with openpyxl (2.4.9). Getting data labels on charted data wasn't obvious, but things start to go badly when I try to format said data labels. What I want to be able to do is change their position and also change their rotation. Anyone any ideas? I'm a bit fresh to python, so any advice would be great. Here's what I've tried:

from openpyxl.chart import LineChart, Reference, Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import RichTextProperties

#this is the only way I found to set text properties which didnt give an error
vertical = RichTextProperties(rot = 270)
labelvertical = RichText(bodyPr = vertical)


chart1 = LineChart()

# setup and append the first series
values = Reference(dprsheet, min_col=5, min_row=4, max_row=34)
series = Series(values, title="Series 1")
chart1.append(series)

# setup and append the second series
values = Reference(dprsheet, min_col=8, min_row=4, max_row=34)
series = Series(values, title="Series 2")
chart1.append(series)

dates = Reference(dprsheet, min_col=2, min_row=4, max_row=34)

chart1.set_categories(dates)

s1 = chart1.series[0]
s1.graphicalProperties.line.solidFill = 'FF0000'
s1.graphicalProperties.line.width = 25000
s1.dLbls = DataLabelList() 
s1.dLbls.showVal = True

## s1.dLbls.dLblPos = 't'
## if ^this^ line isn't commented then ALL images in the sheet are removed by excel upon opening
## s1.dLbls.txPr = labelvertical
## if ^this^ line isn't commented then ALL images in the sheet are removed by excel upon opening
s2 = chart1.series[1]
s2.graphicalProperties.line.solidFill = '000000'
s2.graphicalProperties.line.width = 25000

essheet.add_chart(chart1, 'B35')

推荐答案

最后，我通过尝试找出如何更改轴标签来达到目标​​……这应该可以扩展到任何更改(使用任一段落字体等的属性，或对齐方式的主体属性等).

I got there in the end, via trying to work out how to change the axis labels... this should be extendable to any changes (using either the paragraph properties for fonts etc, or body properties for alignment etc).

from openpyxl.chart import LineChart, Reference, Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
#additional imports needed for the solution:
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties

chart1 = LineChart()
# setup and append the first series
values = Reference(dprsheet, min_col=5, min_row=4, max_row=34)
series = Series(values, title="Series 1")
chart1.append(series)
# setup and append the second series
values = Reference(dprsheet, min_col=8, min_row=4, max_row=34)
series = Series(values, title="Series 2")
chart1.append(series)

dates = Reference(dprsheet, min_col=2, min_row=4, max_row=34)
chart1.set_categories(dates)

#create label styling
axis = CharacterProperties(sz=800)
rot = openpyxl.drawing.text.RichTextProperties(vert='vert270')

#set axis label styles
chart1.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)], bodyPr=rot)
chart1.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])

#set data labels and styles
s1 = chart1.series[0]
s1.dLbls = DataLabelList() 
s1.dLbls.showVal = True
s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)], bodyPr=rot)

请注意，chart.dataLabels.dLblPos仍然不起作用(txPr可以修改，但不能定位).显而易见的解决方法是分别设置每个系列的位置.

Note that chart.dataLabels.dLblPos still does not work (txPr can be amended but not position). The obvious work around requires setting position on each series individually.

这篇关于在openpyxl中格式化图表数据标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！