如何用Openpyxl或Pandas实现Excel单元格条件格式：小于1显百分比，大于1显数字？

需求：Excel单元格数值小于1时显示百分比格式，大于1时显示常规数字格式。尝试以下代码未达到预期效果：

# Openpyxl 尝试代码
worksheet.conditional_formatting.add(cell, CellIsRule(operator='lessThan', formula=['1'], stopIfTrue=True, format='0.0%'))

# Pandas 尝试代码
cell = pd.DataFrame(worksheet.conditional_format(cell,{'format':'0.00%'}))

一、Openpyxl 正确实现方式 #

Openpyxl的CellIsRule不支持直接传入格式字符串作为format参数，需要使用内置的数字格式常量或自定义样式：

1. 导入依赖模块

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import numbers

2. 准备测试数据

wb = Workbook()
ws = wb.active

# 填充测试值
ws['A1'] = 0.5
ws['A2'] = 2.3
ws['A3'] = 0.85
ws['A4'] = 5

3. 添加条件格式规则

# 规则1：数值小于1时显示1位小数百分比
percent_rule = CellIsRule(
    operator='lessThan',
    formula=['1'],
    stopIfTrue=True,
    format=numbers.FORMAT_PERCENTAGE_1_DECIMAL
)
ws.conditional_formatting.add('A1:A4', percent_rule)

# 规则2：数值大于等于1时显示2位小数数字
number_rule = CellIsRule(
    operator='greaterThanOrEqual',
    formula=['1'],
    format=numbers.FORMAT_NUMBER_2_DECIMALS
)
ws.conditional_formatting.add('A1:A4', number_rule)

4. 保存文件

wb.save('conditional_format.xlsx')

二、Pandas 正确实现方式 #

可以通过Styler对象的自定义格式函数或原生条件格式方法实现：

方法1：自定义格式函数 #

import pandas as pd

# 测试数据
data = {'values': [0.5, 2.3, 0.85, 5]}
df = pd.DataFrame(data)

# 定义格式逻辑
def format_value(val):
    if val < 1:
        return '{:.1%}'.format(val)
    else:
        return '{:.2f}'.format(val)

# 应用格式并导出
styled_df = df.style.applymap(format_value, subset=['values'])
styled_df.to_excel('pandas_format.xlsx', engine='openpyxl', index=False)

方法2：使用conditional_format方法（Pandas 1.3.0+） #

import pandas as pd

data = {'values': [0.5, 2.3, 0.85, 5]}
df = pd.DataFrame(data)

styled_df = df.style.conditional_format(
    subset=['values'],
    cond=[
        {'if': 'value < 1', 'format': '{:.1%}'},
        {'if': 'value >= 1', 'format': '{:.2f}'}
    ]
)

styled_df.to_excel('pandas_conditional_format.xlsx', engine='openpyxl', index=False)

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