Python处理Excel需求求助：匹配ID并在Sheet2标记True

Hey there! Let's tackle this Excel processing task step by step. I'll show you two practical approaches using Python libraries that are perfect for this kind of work—one that's quick and high-level, and another for more granular control over the Excel file.

Pandas is great for bulk data manipulation, which makes this task straightforward. First, install the required libraries:

pip install pandas openpyxl

Here's the code with explanations:

import pandas as pd

# 读取Excel文件，加载两个工作表
excel_file = pd.ExcelFile("your_excel_file.xlsx")
sheet1_df = excel_file.parse("Sheet1")
sheet2_df = excel_file.parse("Sheet2")

# 配置：根据你的实际列名调整这里的字段
# 假设Sheet1中，ID列名为"ID"，要查找值x的列是"C"
target_x = "your_target_value_here"  # 替换成你要找的x
target_ids = sheet1_df[sheet1_df["C"] == target_x]["ID"].tolist()

# 在Sheet2中匹配ID，将对应行的C列标记为True
sheet2_df.loc[sheet2_df["ID"].isin(target_ids), "C"] = True

# 将修改后的内容写回Excel（会创建新文件，避免覆盖原数据）
with pd.ExcelWriter("updated_excel_file.xlsx", engine="openpyxl", mode="w") as writer:
    sheet1_df.to_excel(writer, sheet_name="Sheet1", index=False)
    sheet2_df.to_excel(writer, sheet_name="Sheet2", index=False)

关键说明： #

• Replace "your_excel_file.xlsx" and "updated_excel_file.xlsx" with your actual file paths.
• If your ID column or target column has different names (e.g., ID is in column B instead of A), just swap out the column names in the code (like sheet1_df["B"] instead of sheet1_df["ID"]).
• Make sure the data type of target_x matches the values in Sheet1's C column (e.g., if the column has numbers, don't pass a string for target_x).

If you need to work directly with the Excel file's structure (like preserving formatting), OpenPyXL is a good choice. Install it first:

pip install openpyxl

Here's the code:

from openpyxl import load_workbook

# 打开Excel文件（支持读写）
wb = load_workbook("your_excel_file.xlsx")
sheet1 = wb["Sheet1"]
sheet2 = wb["Sheet2"]

target_x = "your_target_value_here"  # 替换成你要找的x
target_ids = []

# 遍历Sheet1，收集匹配x的ID（假设ID在A列，x在C列，从第2行开始跳过表头）
for row in sheet1.iter_rows(min_row=2, values_only=True):
    # 列索引从0开始，C列对应索引2，A列对应索引0
    if row[2] == target_x:
        target_ids.append(row[0])

# 遍历Sheet2，找到匹配ID的行，将C列标记为True
for row in sheet2.iter_rows(min_row=2):  # 不使用values_only，因为要修改单元格
    # 假设ID在Sheet2的A列（索引0），C列是索引2
    if row[0].value in target_ids:
        row[2].value = True

# 保存修改后的文件（建议先备份原文件！）
wb.save("updated_excel_file.xlsx")

关键说明： #

• Adjust the column indexes if your ID or target columns aren't in A/C. For example, if ID is in column B, use row[1].value instead of row[0].value.
• If your Excel file doesn't have a header row, set min_row=1 instead of min_row=2.

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