如何创建Excel文件并在每次运行代码时添加新数据行更新文件

Hey there! Switching from a text file to an Excel file for appending data is a great move—Excel makes it way easier to analyze and reuse the data later. Let's walk through two solid approaches to do this, depending on your needs.

Method 1: Using Pandas (Recommended for Data Analysis) #

Pandas is perfect here because it handles tabular data seamlessly, and makes reading/writing Excel files a breeze. First, install the required packages:

pip install pandas openpyxl

Here's the code that replicates your text file logic, but for Excel:

import pandas as pd
import os

# Define your Excel file path
excel_file = "OUTPUT.xlsx"

# Prepare your new data (replace these variables with your actual values)
new_data = {
    "M": [M],
    "D": [D],
    "pr": [pr(M, 1)],
    "rpr": [epr(M)],
    "TRT": [TRT(bd)],
    "center": [center]
}
# Convert to a pandas DataFrame
df_new = pd.DataFrame(new_data)

# Check if the Excel file exists
if not os.path.exists(excel_file):
    # File doesn't exist yet: write headers + data
    df_new.to_excel(excel_file, index=False, engine="openpyxl")
else:
    # File exists: append data without rewriting headers
    with pd.ExcelWriter(
        excel_file,
        mode="a",
        engine="openpyxl",
        if_sheet_exists="overlay"
    ) as writer:
        # Get the next empty row in the existing sheet
        next_row = writer.sheets["Sheet1"].max_row
        df_new.to_excel(writer, index=False, header=False, startrow=next_row)

How this matches your original text file code: #

• The os.path.exists(excel_file) check replaces your fd.tell() == 0 logic (to write headers only once)
• The DataFrame columns match your text file headers (M, D, pr, etc.)
• Each run appends a new row of data, just like your text file code

Later, when you need to read the data, it's as simple as:

df = pd.read_excel("OUTPUT.xlsx")
# Now you can easily filter, sort, or process the data

Method 2: Using OpenPyXL (Lightweight, No Pandas Needed) #

If you don't want to use Pandas, OpenPyXL is a lightweight library that lets you interact directly with Excel files. First install it:

pip install openpyxl

Here's the equivalent code:

from openpyxl import Workbook, load_workbook
import os

excel_file = "OUTPUT.xlsx"

# Prepare your new data row (replace with your actual values)
new_row = [M, D, pr(M, 1), epr(M), TRT(bd), center]

if not os.path.exists(excel_file):
    # Create a new workbook and add headers
    wb = Workbook()
    ws = wb.active
    # Write headers (matches your text file's header line)
    ws.append(["M", "D", "pr", "rpr", "TRT", "center"])
    # Append the new data row
    ws.append(new_row)
else:
    # Load the existing workbook and append the new row
    wb = load_workbook(excel_file)
    ws = wb.active
    ws.append(new_row)

# Save the workbook
wb.save(excel_file)

This approach is more low-level, but it's great if you only need basic append functionality without the extra features of Pandas.

Either method will let you append new data every time you run your code, and make it easy to read and reuse the data in other scripts later.

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