使用openpyxl匹配列写入现有Excel无数据的原因及高效优化方案

问题背景 #

尝试通过匹配列将DataFrame数据写入现有Excel文件时，最初的代码无法成功写入；改用嵌套循环后数据可以写入，但三层循环导致效率极低，需要更高效的解决方案。

原失败代码 #

import warnings

warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

file = f"{data_folder}\DIS_Ultimo_RWS_Decompositie4.xlsx"
book = load_workbook(file, data_only=False)
writer = pd.ExcelWriter(file, engine='openpyxl')
writer.workbook = book

bouwdelen = book["Bouwdelen"]
data = bouwdelen.values

# Skip one row (second row in Excel sheet is the actual header)
next(data)

# Get the existing columns in the sheet and their corresponding indices
columns = [c for c in next(data)[0:] if c is not None]
# Set the column names as keys and indices as values
columns_dict = {c: i+1 for i, c in enumerate(columns)}

# Change order of the dataframe columns according to column order of the Excel file
def reorder_columns(df, column_order):
    try:
        # Get the list of columns that exist in both the DataFrame and the column_order list
        existing_columns = list(filter(lambda col: col in df.columns, column_order))

        # Reindex the DataFrame using the desired column order
        df = df.reindex(columns=existing_columns)

        return df
    except Exception as e:
        print(f"An error occurred: {e}")
        return None

data_verwijderen = reorder_columns(data_verwijderen, columns)    

# Insert the rows of the dataframe to the target columns in the Excel file

for column_source, col_idx in columns_dict.items():
    if column_source in data_verwijderen.columns:
        for row_idx, row_data in enumerate(data_verwijderen[column_source].values, start=3):
            bouwdelen.cell(row=row_idx, column=col_idx, value=row_data)
                    
book.save(file)

低效但成功的嵌套循环代码 #

for column_source, col_idx in columns_dict.items():
    for column_df in data_verwijderen.columns[0:]:
        if column_source == column_df:
            for row_idx, row_data in enumerate(data_verwijderen[column_df].values, start=3):
                bouwdelen.cell(row=row_idx, column=col_idx, value=row_data)  
        else:
            continue  

原代码失败原因 #

原代码的核心问题在于表头获取逻辑错误：

• data = bouwdelen.values返回的是迭代器，调用next(data)会消耗掉一行数据；
• 随后再次调用next(data)获取列名时，实际取的是Excel的第三行数据，而非预期的第二行表头；
• 这导致columns_dict的键不是正确的表头名称，后续列匹配失败，数据无法写入。

高效解决方案 #

方案一：修正表头逻辑+优化循环 #

先修正表头获取方式，再通过减少循环层级提升效率：

import warnings
import pandas as pd
from openpyxl import load_workbook

warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

file = f"{data_folder}\\DIS_Ultimo_RWS_Decompositie4.xlsx"
book = load_workbook(file, data_only=False)
bouwdelen = book["Bouwdelen"]

# 正确获取第二行的表头（Excel行索引从1开始）
header_row = bouwdelen[2]
columns = [cell.value for cell in header_row if cell.value is not None]
columns_dict = {c: i+1 for i, c in enumerate(columns)}

# 重排DataFrame列
def reorder_columns(df, column_order):
    try:
        existing_columns = [col for col in column_order if col in df.columns]
        return df.reindex(columns=existing_columns)
    except Exception as e:
        print(f"错误发生: {e}")
        return None

data_verwijderen = reorder_columns(data_verwijderen, columns)    

if data_verwijderen is not None:
    start_row = 3
    # 仅遍历DataFrame中存在的列，避免多余循环
    for col_name in data_verwijderen.columns:
        if col_name in columns_dict:
            col_idx = columns_dict[col_name]
            col_data = data_verwijderen[col_name].tolist()
            # 逐行写入该列数据
            for row_offset, value in enumerate(col_data, start=start_row):
                bouwdelen.cell(row=row_offset, column=col_idx, value=value)

book.save(file)

方案二：利用Pandas批量写入（推荐） #

直接使用pandas.ExcelWriter的批量写入能力，这是效率最高的方式，内部做了底层优化：

import warnings
import pandas as pd
from openpyxl import load_workbook

warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

file = f"{data_folder}\\DIS_Ultimo_RWS_Decompositie4.xlsx"
book = load_workbook(file, data_only=False)
writer = pd.ExcelWriter(file, engine='openpyxl')
writer.workbook = book

bouwdelen = book["Bouwdelen"]
# 获取第二行的表头
header_row = bouwdelen[2]
columns = [cell.value for cell in header_row if cell.value is not None]

# 筛选出DataFrame与Excel共有的列，保持Excel的列顺序
existing_columns = [col for col in columns if col in data_verwijderen.columns]
data_to_write = data_verwijderen[existing_columns]

# 从第3行开始写入（startrow=2对应Excel第3行），不写入表头（Excel已有）
data_to_write.to_excel(writer, sheet_name="Bouwdelen", startrow=2, header=False, index=False)

writer.save()
writer.close()

优势：Pandas的to_excel方法会批量处理数据写入，避免逐单元格操作的性能开销，数据量越大，效率提升越明显。

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