使用XlsxWriter配置多值、多类别轴绘制DataFrame折线图及轴错位问题解决

• 违反xlsxwriter系列绑定规则：add_series()的values参数仅支持单个连续单元格区域对应单条独立曲线，传入逗号拼接的多列范围会被识别为非法引用，最终仅取第一个范围生成单条曲线，无法实现多系列效果。
• 单元格引用路径书写错误：原代码中类别范围漏写了工作表名与单元格区间之间的!符号，会导致引用逻辑失效，出现X轴类别错位显示在图表顶部的问题。
• 多索引结构适配不足：多索引DataFrame导出时会生成多层表头，硬编码固定行号极易出现范围错位，需要动态计算数据边界。

实现逻辑 #

1. 先通过列名定位三类需要作为类别轴的列（Device_ID、Temp、supply），生成统一的多列联合类别范围，所有数据系列复用这个类别配置，不需要每个系列单独设置一次
2. 逐个对需要绘图的数值列（黄色、蓝色标注列，排除0.8、1.6无关列）单独调用add_series()，每一列对应一条独立折线
3. 借助xlsxwriter的内置工具函数动态计算单元格范围，适配多索引的多层表头结构，避免固定行号带来的错位问题

参考可运行代码 #

import pandas as pd
from xlsxwriter.utility import xl_rowcol_to_cell

# 初始化Excel写入器
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
multi_index.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 适配多索引结构动态计算数据边界
index_levels = multi_index.index.nlevels  # 多索引的表头占用行数
total_data_rows = multi_index.shape[0]  # 实际数据总行数
data_start_row = index_levels + 1  # 实际数据起始行（Excel行号从1开始计数）
data_end_row = index_levels + total_data_rows

# 1. 生成类别轴范围：Device_ID、Temp、supply 三列联合作为分类依据
# 可根据实际导出后的列位置调整起止列索引
cat_col_start = 0
cat_col_end = 2
categories_range = (
    f'=Sheet1!'
    f'{xl_rowcol_to_cell(data_start_row, cat_col_start)}:'
    f'{xl_rowcol_to_cell(data_end_row, cat_col_end)}'
)

# 2. 定义需要绘制的数值列（黄色、蓝色标注列，排除0.8、1.6无关列）
# 可根据实际列位置调整列索引
value_col_config = [
    {'col_idx': 3, 'series_name': '黄色标注值'},
    {'col_idx': 4, 'series_name': '蓝色标注值'}
]

# 创建折线图对象
chart = workbook.add_chart({'type': 'line'})

# 循环注册每个独立数据系列
for config in value_col_config:
    values_range = (
        f'=Sheet1!'
        f'{xl_rowcol_to_cell(data_start_row, config["col_idx"])}:'
        f'{xl_rowcol_to_cell(data_end_row, config["col_idx"])}'
    )
    chart.add_series({
        'name': config['series_name'],
        'categories': categories_range,
        'values': values_range,
    })

# 配置图表基础属性
chart.set_title({'name': '多维度参数变化折线图'})
chart.set_x_axis({'name': '分类维度（Device_ID/Temp/supply）'})
chart.set_y_axis({'name': '数值'})

# 插入图表到指定位置
worksheet.insert_chart('F2', chart, {'x_scale': 1.2, 'y_scale': 1.1})

# 关闭写入器保存文件
writer.close()

关键修正要点：

• 每一条折线对应一次独立的add_series()调用，不要把多列范围拼接进单个values参数
• 所有工作表单元格引用都必须在表名与区间之间补充!符号，避免路径识别错误
• 多索引导出场景下不要硬编码行号，通过索引层级动态计算范围边界，从根源上避免类别轴错位

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