Python实现将Tab分隔TXT坐标数据追加至Excel已有数据下方

Why Your Current Code Overwrites Data #

Your existing code works great for creating a new Excel file, but pd.to_excel() defaults to replacing the entire file every time you run it. But don't worry—this is totally fixable with a few small tweaks to your pandas workflow!

Step 1: Install Required Dependency #

First, we’ll use the openpyxl engine to handle Excel file appending. If you don’t have it installed yet, run this in your terminal:

pip install openpyxl

Step 2: Full Reusable Code #

Here’s a modified script that handles both first-time file creation and subsequent appends. I’ll break down each part so you follow along easily:

import pandas as pd
import os

def append_coords_to_excel(txt_file_path, excel_file_path='Test.xlsx'):
    # 1. Read your tab-separated TXT file (same as your original code)
    new_coords = pd.read_table(txt_file_path)
    
    # 2. Check if the Excel file already exists
    if os.path.exists(excel_file_path):
        # Get the number of rows already in the Excel file to find the next empty row
        existing_data = pd.read_excel(excel_file_path)
        next_empty_row = len(existing_data)
        
        # Open the Excel file in append mode
        with pd.ExcelWriter(
            excel_file_path,
            mode='a',
            engine='openpyxl',
            if_sheet_exists='overlay'  # Ensures we add to your existing Sheet1, not create a new one
        ) as writer:
            # Write new data starting at the first empty row, skip header (since it's already there)
            new_coords.to_excel(
                writer,
                sheet_name='Sheet1',
                startrow=next_empty_row,
                header=False,
                index=False  # Don't add pandas' default index column to keep your data clean
            )
    else:
        # If the file doesn't exist, create it and write the full data (including headers)
        new_coords.to_excel(
            excel_file_path,
            sheet_name='Sheet1',
            index=False
        )

# Example usage:
# First run (creates Test.xlsx with your initial coordinates):
append_coords_to_excel('Coordinates.txt')
# Later runs (appends new coordinate files to the same Excel):
append_coords_to_excel('Coordinates2.txt')
append_coords_to_excel('Coordinates3.txt')

Key Explanations for Beginners #

• os.path.exists(): Checks if your Excel file already exists so we know whether to create it from scratch or add to it.
• mode='a': Tells pandas to "append" to the existing file instead of overwriting it.
• if_sheet_exists='overlay': Makes sure we add data to your existing Sheet1 instead of creating a duplicate sheet with the same name.
• startrow=next_empty_row: Ensures new data starts right below the last row of existing data (no empty gaps!).
• header=False: Skips writing the column headers again (since they’re already in the Excel file from your first run).
• index=False: Prevents pandas from adding an extra column of row numbers to your Excel sheet (keeps your data matching the TXT format exactly).

Testing the Code #

1. Run the script with your first Coordinates.txt—it will create Test.xlsx with your 4 rows (K1-K4) and the Name X Y Z headers.
2. Create another TXT file (e.g., Coordinates2.txt) with new coordinates, run the script again, and you’ll see the new data added cleanly below the original rows in Test.xlsx.

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