openpyxl图表错误栏样式 [英] openpyxl chart error bar styles

问题描述

我被分配了一个简单任务，将一系列数据列收集到结果工作簿中。结果工作簿包含必要的公式和图表来分析结果。数据是由我在python中编写的一系列excel工作簿中生成的图像分析应用程序。

现在的问题是openpyxl删除任何现有的图表在excel工作簿。我花了一天想出如何使用openpyxl图表，其中有一些文档和图表错误，没有任何例子。

使用win32com扩展将允许直接与Excel进行交互，但显然不会工作，除非在Windows和如果安装Excel。可能不是这样。也试图找出如何与Excel的交互方式可能需要一段时间，而不是从VBA宏。

..

为了设置图表样式，有一个比较好的方法，工作方式如下：

  valuewidth = openpyxl.utils .units.points_to_pixels（2.25）
 valuewidth = openpyxl.utils.units.pixels_to_EMU（valueewidth）
 redcolor = openpyxl.drawing.colors.ColorChoice（prstClr =red）
 bluecolor = openpyxl .drawing.colors.ColorChoice（prstClr =blue）
 
 xvalues = openpyxl.chart.Reference（exceldata.sheetrep，min_col = exceldata.seriesx ['column']，min_row = exceldata.seriesx [ 'row']，max_row = exceldata.seriesx ['row'] + exceldata.seriesx ['rows']）
 values = openpyxl.chart.Reference（exceldata.sheetrep，min_col = exceldata.seriesy ['column' ]，min_row = exceldata.seriesy ['row']，max_row = exceldata.seriesy ['row'] + exceldata.seriesy ['rows']）
 series = openpyxl.chart.Series（values，xvalues，title_from_data = True）
 series.graphicalProperties.line.width = valuewidth 
 series.graphicalProperties.line.solidFill = bluecolor 
 chart.series.append（series）
  pre>  errorwidth = openpyxl.utils.units.points_to_pixels（1）
 errorwidth = openpyxl.utils.units.pixels_to_EMU（errorwidth）
 errorlinetype = openpyxl.drawing.line .LineProperties（w = errorwidth，solidFill = redcolor）
 errorline = openpyxl.chart.shapes.GraphicalProperties（ln = errorlinetype）
 values = openpyxl.chart.Reference（exceldata.sheetrep，min_col = exceldata.lowerlim ['column']，min_row = exceldata.lowerlim ['row']）
 xvalues = openpyxl.chart.Reference（exceldata.sheetrep，min_col = exceldata.lower ['column']，min_row = exceldata.lower [ 'row']）
 chart.title =LL
 errorbar = openpyxl.chart.error_bar.ErrorBars（errBarType =minus，errValType =percentage，val = 100.0，spPr = errorline） 
 errorbar.NumDataRef =LL
 series = openpyxl.chart.Series（values，xvalues，title = None，title_from_data = False）
 series.errBars = errorbar 
图表。 series.append（series）
  



现在我想知道是否有一种方法来跳过构建spPr对象首先定义用于创建GraphicalProperties对象的LineProperties，然后在实例化期间将它们提供给ErrorBars方法。

NumDataRef似乎没有做任何事情。它没有记录。

EDIT 对NumDataRef的调用在任何情况下都可能是错误的。 plus和minus参数期望NumDataSource，其中NumDataRef是其中的一部分。正确的语法可能再次是两个步骤的过程，第一个创建

  foo = openpyxl.chart.data_source.NumRef =bar）
  

，然后按

  minus = openpyxl.chart.data_source.NumDataSource（numRef =foo）
  

我不知道numRef实际上是指什么，如果它是一个单元格，系列名称或其他东西。
如果任何人可以贡献plus，minus和val参数，这将是有帮助的。

解决方案

看起来 NumVal ， NumData 和 NumDataSource 需要从头创建 ErrorBars ：

 ＃和Y 
 xvalues =参考（< worksheet>，min_col =< xcol> ;, min_row =< min>，max_row =< max>）
 yvalues = =< ycol>，min_row =< min>，max_row =< max>）
 plus = [<正错误值列表>] 
 minus = [< for minus>] 
 
＃将错误值列表转换为ErrorBars对象
 errorbars = list2errorbars（plus，minus，errDir ='y'）
 
＃创建系列数据和设置错误栏
 series = SeriesFactory（yvalues，xvalues，title =y direction error）
 series.errBars = errorbars 
 
 def list2errorbars（plus，minus，errDir ='y'，errValType ='cust'）：
从错误值列表中返回ErrorBar
 
＃转换到NumVal列表
 numvals_plus = [NumVal无，v = x）for i，x in enumerate（plus）] 
 numvals_minus = [NumVal（i，None，v = x）for i，x in enumerate（minus）] 
 $ b b＃转换为NumData 
 nd_plus = NumData（pt = numvals_plus）
 nd_minus = NumData（pt = numvals_minus）
 
＃转换为NumDataSource 
 nds_plus = NumDataSource = nd_plus）
 nds_minus = NumDataSource（numLit = nd_minus）
 
 return ErrorBars（plus = nds_plus，minus = nds_minus，errDir = errDir，errValType = errValType）
  

完整示例代码： https://github.com/uskysd/openpyxl-errorbar

因为我也找不到好的文档，我从源代码中找出来。让我知道如果有任何更简单的方法来做到这一点。

I've been assigned a "simple" task of collecting series of data columns to a results workbook. The results workbook contains necessary formulas and charts to analyze the results. The data is generated by an image analysis application I've written in python as series of excel workbooks.

Now the problem is that openpyxl drops any existing charts in excel workbooks. I've spent the day figuring out how to use openpyxl charts for which there is some documentation and chart errorbars for which there are no examples whatsoever. There is the source which has terse comments..

Using win32com extension would allow interacting with Excel directly but that obviously won't work except in windows and if Excel is installed. This may not be the case. Also trying to figure out how interact with Excel that way might take a while instead of doing it from a VBA macro.

..

For setting chart styles there is relatively nice method that works somewhat like this:

valuewidth=openpyxl.utils.units.points_to_pixels(2.25)
valuewidth=openpyxl.utils.units.pixels_to_EMU(valuewidth)
redcolor=openpyxl.drawing.colors.ColorChoice(prstClr="red")
bluecolor=openpyxl.drawing.colors.ColorChoice(prstClr="blue")

xvalues = openpyxl.chart.Reference(exceldata.sheetrep, min_col=exceldata.seriesx['column'], min_row=exceldata.seriesx['row'], max_row=exceldata.seriesx['row']+exceldata.seriesx['rows'])
values = openpyxl.chart.Reference(exceldata.sheetrep, min_col=exceldata.seriesy['column'], min_row=exceldata.seriesy['row'], max_row=exceldata.seriesy['row']+exceldata.seriesy['rows'])
series = openpyxl.chart.Series(values, xvalues, title_from_data=True)
series.graphicalProperties.line.width=valuewidth
series.graphicalProperties.line.solidFill=bluecolor
chart.series.append(series)

Now to work with error bars, they have to be defined and created like this:

errorwidth=openpyxl.utils.units.points_to_pixels(1)
errorwidth=openpyxl.utils.units.pixels_to_EMU(errorwidth)
errorlinetype=openpyxl.drawing.line.LineProperties(w=errorwidth, solidFill=redcolor)
errorline=openpyxl.chart.shapes.GraphicalProperties(ln=errorlinetype)
values = openpyxl.chart.Reference(exceldata.sheetrep, min_col=exceldata.lowerlim['column'], min_row=exceldata.lowerlim['row'])
xvalues = openpyxl.chart.Reference(exceldata.sheetrep, min_col=exceldata.lower['column'], min_row=exceldata.lower['row'])
chart.title="LL"
errorbar=openpyxl.chart.error_bar.ErrorBars(errBarType="minus",errValType="percentage",val=100.0, spPr=errorline)
errorbar.NumDataRef="LL"
series = openpyxl.chart.Series(values, xvalues, title=None, title_from_data=False)
series.errBars=errorbar
chart.series.append(series)

Now I'm wondering if there's a way to skip building that spPr object by first defining those LineProperties used to create GraphicalProperties object which is then fed to the ErrorBars method during instantiation.

That NumDataRef does not seem to do anything. It's not documented.

EDIT That call to NumDataRef is probably wrong in any case. plus and minus parameters expect NumDataSource of which NumDataRef is part of. Proper syntax would be likely again a two-step process of 1st creating the

foo = openpyxl.chart.data_source.NumRef(f="bar") 

and then follow up by

minus = openpyxl.chart.data_source.NumDataSource(numRef="foo")

I'm not sure what the numRef actually refers to, if it's a cell, series name or something else. If anyone can contribute what the "plus", "minus" and "val" parameters do, it'd be helpful.

解决方案

It seems NumVal, NumData and NumDataSource are needed to create ErrorBars from scratch:

# References for X and Y
xvalues = Reference(<worksheet>, min_col=<xcol>, min_row=<min>, max_row=<max>)
yvalues = Reference(<worksheet>, min_col=<ycol>, min_row=<min>, max_row=<max>) 
plus = [<list of error values for plus>]
minus = [<list of error values for minus>]

# Convert lists of error values to ErrorBars object
errorbars = list2errorbars(plus, minus, errDir='y')

# Create series data and set error bar
series = SeriesFactory(yvalues, xvalues, title="y direction error")
series.errBars = errorbars

def list2errorbars(plus, minus, errDir='y', errValType='cust'):
    "Returns ErrorBar from lists of error values"

    #Convert to list of NumVal
    numvals_plus = [NumVal(i, None, v=x) for i,x in enumerate(plus)]
    numvals_minus = [NumVal(i, None, v=x) for i,x in enumerate(minus)]

    # Convert to NumData
    nd_plus = NumData(pt=numvals_plus)
    nd_minus = NumData(pt=numvals_minus)

    # Convert to NumDataSource
    nds_plus = NumDataSource(numLit=nd_plus)
    nds_minus = NumDataSource(numLit=nd_minus)

    return ErrorBars(plus=nds_plus, minus=nds_minus, errDir=errDir, errValType=errValType)

Full example code: https://github.com/uskysd/openpyxl-errorbar

Since I couldn't find good documentation either, I figured out from source code. Let me know if there are any easier ways to do this.

这篇关于openpyxl图表错误栏样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！