使用openpyxl Series触发TypeError，求助原因及解决办法

问题描述 #

使用openpyxl的Series组件时触发TypeError，报错信息为TypeError: expected <class 'int'>。现有代码接收DataFrame字典，生成过滤后的DataFrame，已确认Excel数据写入正常、引用范围正确，请求排查原因。

报错信息 #

TypeError: expected <class 'int'>

相关代码 #

def create_chart(workbook, dfs, sheet_name, key, y_label):
    """Generate chart from the dataframe(s)."""

    # create the chart
    chart = openpyxl.chart.ScatterChart()
    chart.title = sheet_name
    chart.x_axis.title = "X values"
    chart.y_axis.title = y_label

    start_row = 1
    start_col = 1
    workbook.save("example.xlsx")

    for material, df in filtered_dfs.items():
        x_values = Reference(ws, min_col=start_col, min_row=start_row+2,
                                      max_row=start_row+1+len(df))
        y_values = Reference(ws, min_col=start_col+1, min_row=start_row + 2,
                                      max_row=start_row + 1 + len(df))
        new_series = series.Series(y_values, x_values)

        workbook.save("example.xlsx")
        start_col += len(df.columns) + 1

    ws.add_chart(chart, "H6")


def write_filtered_dfs_to_excel_sheet(filtered_dfs, ws):
    """Write specified dataframe to column and separate."""

    # write graph to sheet
    start_row = 1
    start_col = 1

    for material, df in filtered_dfs.items():
        # write header for each material
        ws.cell(row=start_row, column=start_col, value=material)

        # write column headers
        for i, col in enumerate(df.columns):
            ws.cell(row=start_row + 1, column=start_col + i, value=col)

        # going through each row, column in df and appending to excel
        for row_idx, row in df.iterrows():
            for col_idx, value in enumerate(row):
                ws.cell(row=start_row + 2 + row_idx, column=start_col + col_idx, value=float(value))

        start_col += len(df.columns) + 1

示例DataFrame #

df = pd.DataFrame({
    'X': [0.0000, 3.5714, 7.1429, 10.7143, 14.2857, 17.8571, 21.4286, 25.0000],
    'Y': [0.00000000, 0.14285714, 0.28571429, 0.42857143, 0.57142857, 0.71428571, 0.85714286, 1.00000000]
})

问题分析与修复方案 #

1. 未定义ws变量 #

create_chart函数中直接使用ws但未初始化，需要从传入的workbook中获取目标工作表：

# 在函数开头添加
ws = workbook[sheet_name]

2. 循环变量名不匹配 #

函数参数为dfs，但循环遍历的是未定义的filtered_dfs，修正为：

for material, df in dfs.items():

3. Series未添加到图表 #

创建new_series后，未将其添加到chart对象中，导致图表无数据，添加以下代码：

new_series = series.Series(y_values, x_values)
chart.append(new_series)  # 新增该行

4. 导入路径问题 #

确保Series的导入正确，若使用series.Series，需确认导入语句为：

from openpyxl.chart import Series as series
# 或直接使用
from openpyxl.chart import Series
new_series = Series(y_values, x_values)

5. 验证引用范围 #

虽然你确认引用范围正确，但可再次核对：

• min_row=start_row+2对应数据起始行（跳过材料标题和列标题）
• max_row=start_row+1+len(df)计算正确，因为数据行数为len(df)，起始行+行数-1=结束行

修复后完整的create_chart函数示例：

def create_chart(workbook, dfs, sheet_name, key, y_label):
    """Generate chart from the dataframe(s)."""
    # 获取工作表
    ws = workbook[sheet_name]
    
    # create the chart
    chart = openpyxl.chart.ScatterChart()
    chart.title = sheet_name
    chart.x_axis.title = "X values"
    chart.y_axis.title = y_label

    start_row = 1
    start_col = 1

    for material, df in dfs.items():
        x_values = Reference(ws, min_col=start_col, min_row=start_row+2,
                                      max_row=start_row+1+len(df))
        y_values = Reference(ws, min_col=start_col+1, min_row=start_row + 2,
                                      max_row=start_row + 1 + len(df))
        # 确保Series导入正确
        new_series = Series(y_values, x_values)
        # 将系列添加到图表
        chart.append(new_series)

        start_col += len(df.columns) + 1

    ws.add_chart(chart, "H6")
    workbook.save("example.xlsx")

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