openpyxl 无法理解我的 2 个子标题

您可以将每一行添加为单独的系列。
在添加初始数据行 m1 后，将 m2 至 m10 的每一行添加为系列。
包含两级标题的类别取自第 1 行和第 2 行。然后为 X 轴启用“多级类别标签”。

下面的代码示例假设您的数据包含在 A1:G12 范围内，

代码示例一

from openpyxl.chart import (
    BarChart,
    Reference,
    Series
)
from openpyxl import load_workbook
from openpyxl.chart.layout import Layout, ManualLayout

wb = load_workbook("book1.xlsx")
ws = wb.active

### Chart Type
barChart = BarChart()

### Initial data Series m1 B3:G3
data = Reference(ws, min_col=1, min_row=3, max_row=3, max_col=7)
barChart.add_data(data, titles_from_data=True, from_rows=True)

### Data Categories (B1:G2), 2 sub header
categories = Reference(ws, min_col=2, max_col=7, min_row=1, max_row=2)
### Add Categories to Chart
barChart.set_categories(categories)
### Set X Axis multilevel label to enabled
barChart.x_axis.noMultiLvlLbl = False

### Add additional Series m2 - m10
### Create a Series for each row from m2 (row 4) to m10 (row 12)
for i in range(4, 13):  # 13 is ws.max_row+1
    data = Reference(ws, min_col=1, min_row=i, max_col=7, max_row=i)
    ### Use the header in Column A as the title
    series = Series(data, title_from_data=True)
    ### Add the created Series to the chart
    barChart.append(series)


### Chart Settings
barChart.type = "col"
barChart.grouping = "clustered"
barChart.title = "XYZ"
barChart.y_axis.title = "service"
barChart.x_axis.title = "month"

# barChart.style = 5  # Use Chart Style required. This example leaves as default
barChart.width = 20
barChart.height = 10

### Additional Chart Settings
### Set the 'Series Overlap', gap between columns 
barChart.overlap = -10  # -10 is -10% 

# Enable Axis for display
barChart.x_axis.delete = False
barChart.y_axis.delete = False

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

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

### Add Chart to Sheet
ws.add_chart(barChart, "K8")

### Save workbook
wb.save('test.xlsx')

代码示例 II
_{将所有系列添加为一个数据引用}

from openpyxl.chart import (
    BarChart,
    Reference
)
from openpyxl import load_workbook
from openpyxl.chart.layout import Layout, ManualLayout

wb = load_workbook("book1.xlsx")
ws = wb.active

### Chart Type
barChart = BarChart()

### Initial data Series' B3:G12
data = Reference(ws, min_col=1, min_row=3, max_row=12, max_col=7)
barChart.add_data(data, titles_from_data=True, from_rows=True)

### Data Categories (B1:G2), 2 sub header
categories = Reference(ws, min_col=2, max_col=7, min_row=1, max_row=2)
### Add Categories to Chart
barChart.set_categories(categories)
### Set X Axis multilevel label to enabled
barChart.x_axis.noMultiLvlLbl = False

### Chart Settings
barChart.type = "col"
barChart.grouping = "clustered"
barChart.title = "XYZ"
barChart.y_axis.title = "service"
barChart.x_axis.title = "month"

# barChart.style = 5  # Use Chart Style required. This example leaves as default
barChart.width = 20
barChart.height = 10

### Additional Chart Settings
### Set the 'Series Overlap', gap between columns
barChart.overlap = -10  # -10 is -10%

# Enable Axis for display
barChart.x_axis.delete = False
barChart.y_axis.delete = False

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

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

### Add Chart to Sheet
ws.add_chart(barChart, "K8")

### Save workbook
wb.save('test.xlsx')

以下是预期生成的图表。