使用Python Pandas写入Excel后文件损坏问题求助

你的代码导致Excel文件损坏的核心问题有两个：

1. 未完成文件写入流程：在循环的每一行都创建ExcelWriter实例，但从未调用save()或close()方法完成文件写入，文件始终处于未收尾的异常状态。
2. 循环内频繁写入的逻辑缺陷：每次修改一行就重复写入整个DataFrame，不仅效率极低，还会因文件流反复被打开却未正确关闭，导致文件结构损坏。

另外代码里存在一个列名错误：原表格的列是gamma，但你写的是gammaUnsat = row['gammaUnsat']，这会触发KeyError，需要修正为gammaUnsat = row['gamma']。

如果必须坚持每计算完一行就立即写入文件，使用with语句自动管理文件流，确保每次写入后正确关闭文件：

import pandas as pd
import os

file_name = "1234.xlsx"
file_path = os.path.join(os.getcwd(), file_name)

df = pd.read_excel(file_path)

for index, row in df.iterrows():
    # 修正列名错误
    gammaUnsat = row['gamma']
    gammaSat = row['gammasat']
    E50ref = row['E50ref']
    EoedRef = row['EoedRef']
    EurRef = row['EurRef']
    c = row['c']
    phi = row['Φ']
    EA = row['EA']

    # 替换为你的实际计算逻辑
    sf_i = (EA / 1000) + phi  # 示例计算，仅作演示

    # 更新当前行的f.s列
    df.loc[index, "f.s"] = sf_i

    # with语句会自动调用save和close，确保文件写入完成
    with pd.ExcelWriter(file_path, engine="xlsxwriter") as writer:
        df.to_excel(writer, "Sheet1", index=False)

实际上没必要逐行写入，先完成所有行的计算，最后一次性写入文件，既高效又能避免文件损坏：

import pandas as pd
import os

file_name = "1234.xlsx"
file_path = os.path.join(os.getcwd(), file_name)

df = pd.read_excel(file_path)

for index, row in df.iterrows():
    gammaUnsat = row['gamma']
    gammaSat = row['gammasat']
    E50ref = row['E50ref']
    EoedRef = row['EoedRef']
    EurRef = row['EurRef']
    c = row['c']
    phi = row['Φ']
    EA = row['EA']

    # 替换为实际计算逻辑
    sf_i = (EA / 1000) + phi

    df.loc[index, "f.s"] = sf_i

# 所有计算完成后一次性写入文件
with pd.ExcelWriter(file_path, engine="xlsxwriter") as writer:
    df.to_excel(writer, "Sheet1", index=False)

如果原Excel文件包含复杂格式（如单元格样式、内置公式），xlsxwriter会覆盖这些格式，仅保留数据。若需保留原格式，可使用openpyxl作为引擎：

# 需要先安装openpyxl：pip install openpyxl
from openpyxl import load_workbook
import pandas as pd
import os

file_name = "1234.xlsx"
file_path = os.path.join(os.getcwd(), file_name)

df = pd.read_excel(file_path)
# 完成所有行的计算逻辑（同上述代码）

# 加载原文件保留格式
book = load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

df.to_excel(writer, "Sheet1", index=False)
writer.save()

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