如何用Python按行拆分Excel数据并分档导出至新文件

Got it, let's solve this Excel splitting problem efficiently with Python. We'll use pandas for smooth data manipulation and openpyxl to handle writing back to the original Excel file (since pandas needs this engine for .xlsx write operations).

Step 1: Install Required Libraries #

First, make sure you have the necessary packages installed. Run this command in your terminal:

pip install pandas openpyxl

Step 2: Full Python Code #

Here's the complete script that does exactly what you need—extracts 200 rows at a time, saves them to new files, and removes those rows from the original file until all data is processed:

import pandas as pd

# Replace with your actual original file path
original_file = "your_data.xlsx"
chunk_size = 200
chunk_counter = 1

while True:
    # Read the latest state of the original Excel file
    df = pd.read_excel(original_file, engine="openpyxl")
    
    # Exit loop if no data is left
    if len(df) == 0:
        print("All data has been split successfully!")
        break
    
    # Grab the first chunk of rows
    chunk_df = df.iloc[:chunk_size]
    # Save chunk to a new numbered file
    chunk_filename = f"split_chunk_{chunk_counter}.xlsx"
    chunk_df.to_excel(chunk_filename, index=False, engine="openpyxl")
    print(f"Saved {len(chunk_df)} rows to {chunk_filename}")
    
    # Keep only the unprocessed rows
    remaining_df = df.iloc[chunk_size:]
    # Overwrite original file with remaining rows (removes processed ones)
    remaining_df.to_excel(original_file, index=False, engine="openpyxl")
    
    chunk_counter += 1

Key Details Explained #

• Loop Behavior: The loop runs continuously, reading the original file's current state each time—so it always works with the latest remaining rows.
• Edge Case Handling: If the final chunk has fewer than 200 rows (like the 5th chunk in your 1000-row file), the script will still save it without extra checks.
• Original File Update: After extracting a chunk, we write the remaining rows back to the original file, effectively removing the processed data.
• File Naming: Chunks are saved as split_chunk_1.xlsx, split_chunk_2.xlsx, etc., so you can easily track their order.

Quick Notes #

• Don't forget to replace "your_data.xlsx" with the actual path to your Excel file.
• Always back up your original file before running the script—while it's designed to be safe, backups are a smart precaution for any data-modifying task!

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