如何使用OpenPyXL格式化趋势线方程中的数值？

OpenPyXL的Trendline类目前没有直接提供设置方程和R²数字格式的API——这部分格式控制属于OOXML规范的图表高级属性，尚未被OpenPyXL完全封装。下面提供两种可行的解决方法：

方法一：手动计算参数并添加自定义文本框（推荐，可控性强） #

跳过OpenPyXL自动生成趋势线文本的逻辑，用科学计算库手动拟合参数和R²值，再将格式化后的文本作为自定义文本框添加到图表中，完全控制数字格式。

修改后的代码示例 #

import openpyxl
from scipy.optimize import curve_fit
import numpy as np

# 定义指数拟合函数
def exponential_func(x, a, b):
    return a * np.exp(b * x)

def draw_cal_plot(sheet, xvalues, yvalues, title, x_axis_title, y_axis_title, chart_location):
    # 初始化散点图
    chart = openpyxl.chart.ScatterChart()
    chart.title = title
    chart.style = 13
    chart.x_axis.title = x_axis_title
    chart.y_axis.title = y_axis_title
    
    # 转换数据为numpy数组用于拟合（跳过表头）
    x_data = np.array([cell.value for cell in xvalues[1:]])
    y_data = np.array([cell.value for cell in yvalues[1:]])
    
    # 执行指数拟合，获取参数
    popt, _ = curve_fit(exponential_func, x_data, y_data)
    a, b = popt
    
    # 计算R²值
    y_pred = exponential_func(x_data, a, b)
    ss_res = np.sum((y_data - y_pred) ** 2)
    ss_tot = np.sum((y_data - np.mean(y_data)) ** 2)
    r_squared = 1 - (ss_res / ss_tot)
    
    # 按需求格式化文本
    eq_text = f"y = {a:.4g}e^({b:.9e})"  # 第一项4位有效数字，指数项9位科学计数法
    r2_text = f"R² = {r_squared:.6g}"    # R²保留6位有效数字
    
    # 配置数据系列与趋势线（关闭自动生成的方程和R²）
    series = openpyxl.chart.Series(yvalues, xvalues, title_from_data=False)
    series.marker.symbol = "circle"
    series.marker.graphicalProperties.solidFill = "0000FF"
    series.graphicalProperties.line.noFill = True
    
    series.trendline = openpyxl.chart.trendline.Trendline()
    series.trendline.trendlineType = "exp"
    series.trendline.graphicalProperties.line.solidFill = "000000"
    series.trendline.graphicalProperties.line.width = 1
    series.trendline.dispEq = False
    series.trendline.dispRSqr = False
    
    chart.series.append(series)
    sheet.add_chart(chart, chart_location)
    
    # 添加自定义文本框到图表（坐标单位为EMU，1英寸=914400 EMU，可按需调整）
    plot_area = chart.plotArea
    
    # 添加趋势线方程文本框
    eq_tx = openpyxl.chart.text.RichText(eq_text)
    eq_textbox = openpyxl.chart.shape.Shape(
        tx=eq_tx, x=plot_area.x + 20000, y=plot_area.y + 10000,
        width=150000, height=20000
    )
    chart.shapes.append(eq_textbox)
    
    # 添加R²文本框
    r2_tx = openpyxl.chart.text.RichText(r2_text)
    r2_textbox = openpyxl.chart.shape.Shape(
        tx=r2_tx, x=plot_area.x + 20000, y=plot_area.y + 35000,
        width=150000, height=20000
    )
    chart.shapes.append(r2_textbox)

说明 #

• 用scipy.curve_fit完成指数拟合，直接获取趋势线参数和R²值
• 利用Python格式化字符串语法精准控制有效数字：:.4g保留4位有效数字，:.9e强制科学计数法并保留9位，:.6g保留6位有效数字
• 自定义文本框的位置可通过x/y参数灵活调整

方法二：直接修改Excel的OOXML内容（无需额外依赖） #

如果不想引入科学计算库，可以先让OpenPyXL生成带趋势线的图表，再直接修改Excel文件的XML内容，添加数字格式设置。

代码示例 #

import openpyxl
import zipfile
from xml.etree import ElementTree as ET

# 保留你原有的绘图函数
def draw_cal_plot(sheet, xvalues, yvalues, title, x_axis_title, y_axis_title, chart_location):
    chart = openpyxl.chart.ScatterChart()
    chart.title = title
    chart.style = 13
    chart.x_axis.title = x_axis_title
    chart.y_axis.title = y_axis_title
    series = openpyxl.chart.Series(yvalues, xvalues, title_from_data=False)
    series.marker.symbol = "circle"
    series.marker.graphicalProperties.solidFill = "0000FF"
    series.graphicalProperties.line.noFill = True

    series.trendline = openpyxl.chart.trendline.Trendline()
    series.trendline.trendlineType = "exp"
    series.trendline.graphicalProperties.line.solidFill = "000000"
    series.trendline.graphicalProperties.line.width = 1
    series.trendline.dispEq = True
    series.trendline.dispRSqr = True
    chart.series.append(series)
    sheet.add_chart(chart, chart_location)

# 生成初始Excel文件
wb = openpyxl.Workbook()
ws = wb.active
# 填充测试数据（替换为你的实际数据）
xvalues = ws["A1:A5"]
yvalues = ws["B1:B5"]
for i in range(1,6):
    ws[f"A{i}"] = i
    ws[f"B{i}"] = np.exp(0.2*i)
draw_cal_plot(ws, xvalues, yvalues, "Test Chart", "X", "Y", "D1")
temp_file = "temp_chart.xlsx"
wb.save(temp_file)

# 修改图表XML添加格式设置
namespace = {"c": "http://schemas.openxmlformats.org/drawingml/2006/chart"}
with zipfile.ZipFile(temp_file, 'r') as zf:
    # 读取图表XML（默认路径为xl/charts/chart1.xml，若多个图表需调整）
    chart_xml = zf.read("xl/charts/chart1.xml").decode('utf-8')
    root = ET.fromstring(chart_xml)
    
    # 遍历所有趋势线，添加数字格式
    for tl in root.findall(".//c:trendline", namespace):
        # 设置趋势线方程的格式
        disp_eq = tl.find("c:dispEq", namespace)
        if disp_eq:
            num_fmt = ET.SubElement(disp_eq, "{http://schemas.openxmlformats.org/drawingml/2006/chart}numFmt")
            num_fmt.set("formatCode", "0.0000e+000")  # 对应4位有效数字的科学计数法
            num_fmt.set("sourceLinked", "0")
        # 设置R²的格式
        disp_r_sqr = tl.find("c:dispRSqr", namespace)
        if disp_r_sqr:
            num_fmt_r2 = ET.SubElement(disp_r_sqr, "{http://schemas.openxmlformats.org/drawingml/2006/chart}numFmt")
            num_fmt_r2.set("formatCode", "0.000000")  # 对应6位有效数字（R²在0-1区间）
            num_fmt_r2.set("sourceLinked", "0")
    
    # 重新打包Excel文件
    modified_xml = ET.tostring(root, encoding='utf-8').decode('utf-8')
    with zipfile.ZipFile("formatted_chart.xlsx", 'w') as new_zf:
        for item in zf.infolist():
            if item.filename == "xl/charts/chart1.xml":
                new_zf.writestr(item, modified_xml)
            else:
                new_zf.writestr(item, zf.read(item.filename))

说明 #

• 利用Excel是ZIP压缩包的特性，直接修改图表XML中的<c:numFmt>元素，自定义数字格式码
• 格式码可按需调整：比如0.0000e+000对应4位小数的科学计数法，0.000000对应6位小数（适配R²的0-1范围）
• 注意XML命名空间的正确使用，否则无法定位到目标元素

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