Openpyxl单系列折线图自动分配分类颜色的问题及解决方法

我用openpyxl编写了一个基于工作表数据生成折线图的函数，其中secondary参数用于控制是否启用次坐标轴。当图表包含两个及以上数据系列时显示正常，但仅绘制单个系列时，折线的每个分段会被自动分配不同颜色，效果如下：

问题代码 #

import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, NamedStyle
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.utils import get_column_letter
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.axis import ChartLines, TextAxis
from openpyxl.drawing.line import LineProperties
from openpyxl.drawing.text import ParagraphProperties, CharacterProperties
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl.chart.shapes import GraphicalProperties


def produce_line_chart(ws_data, charts_sheet, series_list, secondary=False):
    """
    Creates a customized line chart based on data from a worksheet and prepares it for placement on a charts sheet.

    Args:
        ws_data (Worksheet): The worksheet containing the data to be charted.
        charts_sheet (Worksheet): The worksheet where the chart will be placed.
        series_list (list): A list of strings where the first item is the chart title and subsequent items are the series names to be charted.
        secondary (bool): If True, disables major gridlines on both axes. Defaults to False.

    Returns:
        tuple: A tuple containing the created chart object and the suggested position for placing the chart on the charts sheet.
    """

    # Create a line chart
    chart = LineChart()

    # Set the title of the chart to the first item in series_list
    chart.title = series_list[0]

    # Increase the chart title's font size to 20pt
    chart.title.tx.rich.paragraphs[0].pPr = ParagraphProperties(
        defRPr=CharacterProperties(sz=2000)  # Font size 20pt
    )

    # Configure the appearance of the axes
    chart.x_axis = TextAxis(tickMarkSkip=4)
    chart.x_axis.spPr = GraphicalProperties(ln=LineProperties(solidFill="D9D9D9"))
    chart.y_axis.spPr = GraphicalProperties(ln=LineProperties(solidFill="D9D9D9"))

    # Add or remove major gridlines based on the 'secondary' flag
    if secondary:
        chart.x_axis.majorGridlines = None
        chart.y_axis.majorGridlines = None
    else:
        chart.x_axis.majorGridlines = ChartLines()
        chart.x_axis.majorGridlines.spPr = GraphicalProperties(
            ln=LineProperties(solidFill="D9D9D9")
        )
        chart.y_axis.majorGridlines = ChartLines()
        chart.y_axis.majorGridlines.spPr = GraphicalProperties(
            ln=LineProperties(solidFill="D9D9D9")
        )

    # Set tick marks on the axes
    chart.x_axis.majorTickMark = "out"
    chart.y_axis.majorTickMark = "cross"

    # Ensure axes are displayed
    chart.x_axis.delete = False
    chart.y_axis.delete = False

    # Set the legend position at the bottom of the chart
    chart.legend.position = "b"

    # Adjust the layout to ensure enough space for the legend
    chart.layout = Layout(
        manualLayout=ManualLayout(
            x=0,  # x position of the plot area
            y=0,  # y position of the plot area
            h=0.85,  # height of the plot area
            w=0.9,  # width of the plot area
        )
    )

    # Scale the chart by adjusting its height and width
    chart.height = chart.height * 1.71
    chart.width = chart.width * 2.03

    # Get the first row of the worksheet data to identify the series columns
    first_row = next(ws_data.iter_rows(min_row=1, max_row=1, values_only=True), [])

    # Loop through each series name in series_list (excluding the title)
    num_rows = ws_data.max_row
    for series in series_list[1:]:
        for idx, cell_value in enumerate(first_row, start=1):
            if cell_value == series:
                col = idx
                break
        # Create a reference to the data for the series and add it to the chart
        series_to_chart = Reference(
            ws_data, min_col=col, min_row=1, max_col=col, max_row=num_rows
        )
        chart.add_data(series_to_chart, titles_from_data=True)
        # Ensure the series line is not smoothed
        chart.series[-1].smooth = False

    # Set the categories (x-axis labels) based on the first column of data (excluding the header)
    categories = Reference(ws_data, min_col=1, min_row=2, max_row=ws_data.max_row)
    chart.set_categories(categories)

    # Determine the next available position for the chart on the charts sheet
    chart_row = 1  # Start in the first row
    for existing_chart in charts_sheet._charts:
        existing_chart_row = int("".join(filter(str.isdigit, existing_chart.anchor)))
        # Convert the chart height from cm to Excel cell height and add some space
        chart_row = max(chart_row, existing_chart_row + int(chart.height * 1.86) + 4)

    # Determine the position to add the chart on the charts sheet
    chart_position = f"A{chart_row}"

    return chart, chart_position

原因分析 #

问题出在openpyxl的LineChart默认启用了varyColors属性（值为True）。这个属性的逻辑是：

• 当图表包含多个系列时，为每个系列分配不同颜色，符合正常需求
• 当只有单个系列时，会自动给系列内的每个数据点/线段分配不同颜色，导致单系列折线出现“彩虹色”效果

修复方案 #

方案1：禁用逐点变色

在创建图表后，手动将varyColors设置为False，强制单系列使用统一颜色：

# 创建折线图
chart = LineChart()
# 添加这一行，禁用逐点变色
chart.varyColors = False

方案2：自定义系列颜色（可选）

如果需要指定单系列的具体颜色，可以在添加系列后进一步设置：

# 在循环添加系列的代码之后
if len(chart.series) == 1:
    series = chart.series[0]
    # 设置线条为红色，可替换为其他十六进制颜色码
    series.graphicalProperties.line.solidFill = "FF0000"

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