如何用openpyxl实现图表工作表的行列切换（批量场景）

我有一个包含数据的大型Excel工作表，数据结构处于转置状态：

直接绘制这些数据时，图表会生成5个系列，每个系列仅包含2个值；但我需要的是2个Series，每个包含5个数据点（用于展示直方图）——这对应Excel中的*"Switch Row/Column"（切换行/列）*按钮。由于图表工作表数量过多，无法手动逐个操作，请问如何用Python实现这一效果？

我编写了简化版代码（完整代码需使用MultiIndex，示例中仅作演示）：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference
   
excel_file = 'test.xlsx'
# create and write data
excel_tests_data = [[0, 1, 5, 2, 1, 0],
                    [0, 0, 1, 7, 2, 0]]
df = pd.DataFrame(excel_tests_data)
df.columns=pd.MultiIndex.from_tuples([('bin '+str(i), ) for i in range(6)])
df.to_excel(excel_file, startrow=0, sheet_name='test') 
# open workbook
workbook = load_workbook(excel_file)
worksheet = workbook.worksheets[0]
# create chartsheet
chartsheet = workbook.create_chartsheet()
chartsheet.title = "Testsheet"
chart = LineChart()
data = Reference(worksheet, 
                    min_col = 2,
                    min_row = 3,
                    max_row = 4,
                    max_col = 7)
chart.title = "test"
chart.add_data(data)
chartsheet.add_chart(chart)
# save and close workbook
workbook.save(excel_file)
workbook.close()

实现方法 #

在openpyxl的chart.add_data()方法中，设置transpose=True参数即可实现行/列切换，效果完全等同于Excel的"Switch Row/Column"按钮。同时可以补充配置系列名称和X轴标签，让图表逻辑更清晰。

修改后的完整代码

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference
   
excel_file = 'test.xlsx'
# create and write data
excel_tests_data = [[0, 1, 5, 2, 1, 0],
                    [0, 0, 1, 7, 2, 0]]
df = pd.DataFrame(excel_tests_data)
df.columns=pd.MultiIndex.from_tuples([('bin '+str(i), ) for i in range(6)])
# 添加行索引，对应原表格的"直方图"列
df.index = ['test 1', 'test 2']
df.to_excel(excel_file, startrow=0, sheet_name='test') 
# open workbook
workbook = load_workbook(excel_file)
worksheet = workbook.worksheets[0]
# create chartsheet
chartsheet = workbook.create_chartsheet()
chartsheet.title = "Testsheet"
chart = LineChart()
# 调整数据引用范围，包含bin列的表头
data = Reference(worksheet, 
                    min_col = 2,
                    min_row = 2,
                    max_row = 4,
                    max_col = 7)
# 设置X轴分类标签为行索引（test 1、test 2）
cats = Reference(worksheet, min_col=1, min_row=3, max_row=4)
chart.set_categories(cats)
chart.title = "test"
# 启用行/列转置，并从数据中读取系列名称
chart.add_data(data, titles_from_data=True, transpose=True)
chartsheet.add_chart(chart)
# save and close workbook
workbook.save(excel_file)
workbook.close()

关键说明 #

1. transpose=True：核心参数，直接将数据的行和列转置，把原本按列生成的系列改为按行生成，完美匹配需求。
2. titles_from_data=True：自动读取bin列的表头作为系列名称，无需手动设置。
3. chart.set_categories()：指定X轴的分类标签为行索引，让图表的逻辑展示更直观。

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