写入Excel时排序后的DataFrame未体现排序效果的问题

问题场景 #

使用Pandas结合openpyxl引擎读取Excel文件，按自定义规则["D1", "D2", "R1", "W"]对DataFrame排序后，控制台打印确认排序成功，但写入原文件后数据并未呈现预期的排序状态。相关代码如下：

# Open the output workbook using openpyxl
output_file_path = os.path.join(os.path.dirname(self.template_path), "test.xlsx")
workbook = load_workbook(output_file_path)

# Select the worksheet by name
worksheet = workbook['Sheet1']

# Load the output file into a DataFrame
df = pd.read_excel(output_file_path, engine='openpyxl')

# Load the output file into a DataFrame and sort it
sort_order = ["D1", "D2", "R1", "W"]
print(df.head())

df.sort_values(by=["SortCol"], ascending=True, inplace=True, key=lambda x: pd.Categorical(x, categories=sort_order))
print("Sort Order Result")
print(df)
# Overwrite sorted data in the existing worksheet
for row, record in df.iterrows():
    for col, value in record.items():
        if row+2 >= 1 and column_mappings[col] >= 1: # added check to ensure row and column values are greater than or equal to 1
            worksheet.cell(row=row+2, column=column_mappings[col]+1, value=value)

# Save the changes
workbook.save(output_file_path)

问题根源排查 #

1. 旧数据残留：手动循环写入时仅覆盖对应行单元格，原工作表中超出新数据行数的旧行未被清理，导致视觉上数据未排序。
2. 行/列映射错误：column_mappings对应关系有误会将数据写入错误列；行号计算row+2若与原数据起始行不匹配，会导致数据写入位置偏移。
3. 文件读取冲突：先通过load_workbook打开文件，再用pd.read_excel读取同一份文件，可能导致内存中workbook对象与DataFrame数据不同步。

解决方案 #

方案1：修复手动写入逻辑（保留原工作表格式） #

若需保留原Excel单元格样式，可调整写入逻辑，先清空旧数据再写入：

output_file_path = os.path.join(os.path.dirname(self.template_path), "test.xlsx")
workbook = load_workbook(output_file_path)
worksheet = workbook['Sheet1']

# 读取并排序数据
df = pd.read_excel(output_file_path, engine='openpyxl')
sort_order = ["D1", "D2", "R1", "W"]
df.sort_values(by=["SortCol"], ascending=True, inplace=True, key=lambda x: pd.Categorical(x, categories=sort_order))

# 清空表头后的所有旧数据行（假设表头在第1行）
max_row = worksheet.max_row
if max_row >= 2:
    worksheet.delete_rows(2, max_row - 1)

# 写入排序后的数据
for row_idx, record in df.iterrows():
    excel_row = row_idx + 2  # 数据从第2行开始写入
    for col_name, value in record.items():
        # 确认column_mappings是DataFrame列名到Excel列索引（0-based）的映射
        if col_name in column_mappings:
            excel_col = column_mappings[col_name] + 1  # 转成openpyxl的1-based列索引
            worksheet.cell(row=excel_row, column=excel_col, value=value)

workbook.save(output_file_path)

方案2：使用Pandas直接写入（简洁可靠） #

放弃手动循环，用Pandas内置方法直接替换工作表内容，避免映射错误：

import pandas as pd
import os

output_file_path = os.path.join(os.path.dirname(self.template_path), "test.xlsx")
sort_order = ["D1", "D2", "R1", "W"]

# 读取数据并排序
df = pd.read_excel(output_file_path, engine='openpyxl')
df.sort_values(by=["SortCol"], ascending=True, key=lambda x: pd.Categorical(x, categories=sort_order), inplace=True)

# 写入Excel，替换原有工作表
with pd.ExcelWriter(output_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

• mode='a'：以追加模式打开文件
• if_sheet_exists='replace'：替换已存在的Sheet1
• index=False：避免将DataFrame的索引列写入Excel

验证要点 #

• 确认SortCol列存在于DataFrame中，且列值包含sort_order内的所有类别
• 检查column_mappings（方案1）是否准确对应DataFrame列与Excel列的位置
• 写入后重新打开Excel文件，确认数据行无旧数据残留

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