如何使用Python实现Excel工作表间的数据读取与写入？

No problem at all—this is a super common task, and we’ve got two solid approaches for you depending on your needs. Let’s dive right in!

Approach 1: Using pandas (Simplest & Fastest) #

Pandas is perfect for straightforward data transfer since it handles most of the heavy lifting automatically. First, make sure you have the required packages installed:

pip install pandas openpyxl

Here’s the code to read and write your sheets smoothly:

import pandas as pd

# Replace with your actual Excel file path
excel_file = "your_workbook.xlsx"

try:
    # Read data from "Original Sheet"
    df = pd.read_excel(excel_file, sheet_name="Original Sheet")
except FileNotFoundError:
    print("Oops! The specified Excel file doesn't exist.")
except ValueError:
    print("Error: 'Original Sheet' isn't present in the workbook.")
else:
    # Write data to "Copy Sheet" (overwrites if the sheet already exists)
    with pd.ExcelWriter(excel_file, mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
        df.to_excel(writer, sheet_name="Copy Sheet", index=False)
    print("Successfully copied data from 'Original Sheet' to 'Copy Sheet'!")

Quick Notes: #

• mode="a" appends the new sheet to your existing workbook instead of creating a new file.
• if_sheet_exists="replace" ensures old versions of "Copy Sheet" get overwritten—remove this if you want to avoid overwriting (but you’ll get an error if the sheet already exists).
• index=False prevents pandas from adding an extra index column to your copied sheet.

Approach 2: Using openpyxl (More Granular Control) #

If you need finer control (like preserving cell formatting or working with specific cell ranges), openpyxl is the way to go. Install it first:

pip install openpyxl

Here’s the code for this method:

from openpyxl import load_workbook

# Replace with your actual Excel file path
excel_file = "your_workbook.xlsx"

try:
    # Load the existing workbook
    wb = load_workbook(excel_file)
    
    # Access the original sheet
    original_sheet = wb["Original Sheet"]
    
    # Remove existing "Copy Sheet" if it exists, then create a new one
    if "Copy Sheet" in wb.sheetnames:
        wb.remove(wb["Copy Sheet"])
    copy_sheet = wb.create_sheet(title="Copy Sheet")
    
    # Copy every row and cell value from original to copy sheet
    for row in original_sheet.iter_rows(values_only=False):
        copy_sheet.append([cell.value for cell in row])
    
    # Save the changes to the workbook
    wb.save(excel_file)
    print("Data copied successfully!")
    
except FileNotFoundError:
    print("Oops! The file you specified doesn't exist.")
except KeyError:
    print("Error: 'Original Sheet' isn't in the workbook.")

Quick Notes: #

• This code copies cell values by default. If you need to copy formatting (like fonts, colors, or borders), you can extend it to copy cell styles using cell.style or custom copy functions.
• We explicitly remove existing "Copy Sheet" entries to avoid duplicate sheets in your workbook.

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