循环环境下向XLSX追加数据不覆盖的openpyxl代码问题求助

It looks like your code has a few small but critical issues preventing it from appending rows correctly without overwriting existing content. Let's walk through what's wrong and fix it step by step:

Key Problems in Your Code #

1. Undefined Variable Error: You declared headers_column but tried to use headers_row in ws.append(headers_row)—this will throw a NameError when the file doesn't exist.
2. No Action When File Exists: The try block loads the existing workbook but doesn't actually append the new row or save your changes.
3. Invalid Save/Close Syntax: wb.save.close() is incorrect. The save() method takes the filename as an argument, and there's no close() method chained to its result.
4. Redundant Filename Definition: You redefine filename inside the loop every time, which is unnecessary.

Corrected Code #

Here's the fixed version that will properly append rows in your loop without overwriting data:

from openpyxl import Workbook
from openpyxl import load_workbook

t = 0
filename = "myfile.xlsx"  # Move filename outside the loop for efficiency

while t <= 3:
    d1 = 'ID:001'
    d2 = 'ID:002'
    d3 = 'ID:003'
    new_row = [d1, d2, d3]
    
    try:
        # Load existing workbook
        wb = load_workbook(filename)
        ws = wb.worksheets[0]
        # Append the new row to the existing sheet
        ws.append(new_row)
        # Save the updated workbook
        wb.save(filename)
        # Close the workbook to avoid corruption
        wb.close()
    except FileNotFoundError:
        # Create new workbook if file doesn't exist
        wb = Workbook()
        ws = wb.active
        # Fix header variable name to match usage
        headers_row = ['Header 1', 'Header 2', 'Header 3']
        ws.append(headers_row)
        ws.append(new_row)
        wb.save(filename)
        wb.close()
    
    t += 1

Breakdown of Fixes #

• Moved Filename: We moved filename outside the loop since it doesn't change each iteration—this makes the code cleaner and more efficient.
• Fixed Header Variable: Renamed headers_column to headers_row to match the variable used in ws.append(), eliminating the NameError.
• Added Append/Save in Try Block: When the file already exists, we now append the new row and save the workbook to preserve changes.
• Validated Save/Close: Replaced the invalid wb.save.close() with separate wb.save(filename) and wb.close() calls to properly handle the workbook lifecycle.

Bonus: Robust Context Manager Version #

For even more reliable workbook handling, use with statements (context managers) which automatically close the workbook for you, reducing the risk of file corruption:

from openpyxl import Workbook
from openpyxl import load_workbook

t = 0
filename = "myfile.xlsx"

while t <= 3:
    new_row = ['ID:001', 'ID:002', 'ID:003']
    
    try:
        with load_workbook(filename) as wb:
            ws = wb.worksheets[0]
            ws.append(new_row)
            wb.save(filename)
    except FileNotFoundError:
        with Workbook() as wb:
            ws = wb.active
            ws.append(['Header 1', 'Header 2', 'Header 3'])
            ws.append(new_row)
            wb.save(filename)
    
    t += 1

This version is more concise and ensures proper resource management without manual close() calls.

内容的提问来源于stack exchange，提问作者陳韋仲