Python 3.11中openpyxl无法正确应用数字格式与样式的问题求助

问题原因 #

1. Workbook对象未关联：你加载本地Excel文件得到workbook对象，但创建pd.ExcelWriter时未将该对象传入，导致writer重新打开独立的文件句柄。你对workbook的样式修改、冻结窗格操作并未同步到writer处理的文件中，最终这些修改不会被保存。
2. 数字格式代码匹配错误：builtin_format_code(4)对应Excel的0.0格式，仅保留1位小数，和你预期的多位数小数显示不符。
3. 样式设置时机错误：即使对象关联成功，若在pandas写入数据前设置表头样式，写入操作会覆盖表头单元格的原有样式，导致样式不生效。
4. 数据行范围错误：原代码中range(2, num_rows + 1)会漏掉最后一行数据的格式设置，因为数据从第2行开始，到num_rows+1行结束（num_rows是数据总行数）。

解决方法 #

1. 创建pd.ExcelWriter时，通过book参数传入已加载的workbook对象，确保所有操作在同一个文件实例上进行。
2. 更换符合需求的数字格式代码：比如builtin_format_code(2)对应0.00（保留2位小数），builtin_format_code(3)对应0.000（保留3位小数），可根据需求选择。
3. 调整操作顺序：先完成pandas的数据写入，再进行样式设置、冻结窗格等操作，避免写入操作覆盖样式。
4. 修正数据行循环范围，确保所有数据行都被设置格式。

修正后的代码 #

import pandas as pd
import seaborn as sns
import shutil
from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.styles.numbers import builtin_format_code

iris = sns.load_dataset('iris')
iris = iris.rename(columns={'sepal_length': 'Sepal Length',
                            'sepal_width': 'Sepal Width',
                            'petal_length': 'Petal Length',
                            'petal_width': 'Petal Width',
                            'species': 'Species'})

main_path = rf"C:\Users\HaleighOeser\OneDrive - kyndryl\Documents\Misc_Projects\iris_template.xlsx"
new_path = rf"C:\Users\HaleighOeser\OneDrive - kyndryl\Documents\Misc_Projects\iris_modified.xlsx"
shutil.copy(main_path, new_path)
print('New File Created')

# 加载模板文件
workbook = load_workbook(new_path)
# 创建writer时传入已加载的workbook，确保对象统一
writer = pd.ExcelWriter(new_path, engine='openpyxl', mode='a', if_sheet_exists='overlay', book=workbook)
# 写入数据
iris.to_excel(writer, index=False, sheet_name='Data')
# 获取写入后的sheet对象
sheet = writer.book['Data']
num_rows = len(iris)

# 设置数值列格式（这里用builtin_format_code(2)对应2位小数，可按需修改）
for c in ['A', 'B', 'C', 'D']:
    for r in range(2, num_rows + 2):
        sheet[f'{c}{r}'].number_format = builtin_format_code(2)

# 设置表头样式（写入数据后再设置，避免被覆盖）
for c in ['A', 'B', 'C', 'D', 'E']:
    sheet[f'{c}1'].alignment = Alignment(vertical='center', wrapText=True)

# 冻结窗格
sheet.freeze_panes = sheet['A2']

# 保存并关闭
writer.save()
writer.close()

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