使用Python关联两个Excel数据表：实现单元格跳转查看对应数据

Got it, let's build this interactive tool step by step. We'll use pandas to handle Excel data (we'll simulate sample frames since you can't share your actual data) and tkinter—Python's built-in GUI library—to create a clickable table interface. This approach is lightweight and doesn't require fancy external tools.

Step 1: Install Required Packages #

First, make sure you have these dependencies set up:

pip install pandas numpy

(Note: tkinter is usually pre-installed with Python. If not, grab it via your system package manager—like sudo apt-get install python3-tk on Ubuntu.)

Step 2: Simulate Your Data Frames #

Since you can't share your actual data, let's create samples that match your example (A1 maps to NaN, A2 maps to New York):

import pandas as pd
import numpy as np

# First table: the one you'll click on
df_table1 = pd.DataFrame({
    "Column A": ["Click for NaN", "Click for New York"],
    "Column B": ["Another cell", "Yet another cell"]
})

# Second table: source of the values we'll display
df_table2 = pd.DataFrame({
    "Column A": [np.nan, "New York"],
    "Column B": ["London", "Tokyo"]
})

For your real use case, replace these with pd.read_excel("your_first_file.xlsx") and pd.read_excel("your_second_file.xlsx").

Step 3: Build the Interactive GUI #

Here's the full code to create a clickable table. Click any cell in the first table, and it'll show the matching value from the second table below:

import tkinter as tk
from tkinter import ttk

def on_cell_click(event):
    # Get the selected cell's details
    selected_item = tree.focus()
    if not selected_item:
        return
    
    # Convert click position to row/column indexes
    row_idx = tree.index(selected_item)
    col_id = tree.identify_column(event.x)
    col_idx = int(col_id.replace("#", "")) - 1  # switch to 0-based indexing
    
    # Fetch the corresponding value from the second table
    try:
        value = df_table2.iloc[row_idx, col_idx]
        # Handle NaN values clearly
        if pd.isna(value):
            result_label.config(text=f"Corresponding value: NaN")
        else:
            result_label.config(text=f"Corresponding value: {value}")
    except IndexError:
        result_label.config(text="No matching value found")

# Initialize the window
root = tk.Tk()
root.title("Excel Table Linker")

# Create a table widget for the first data frame
tree = ttk.Treeview(root, columns=list(df_table1.columns), show="headings")

# Set up column headings
for col in df_table1.columns:
    tree.heading(col, text=col)
    tree.column(col, width=180)

# Populate the table with data
for idx, row in df_table1.iterrows():
    tree.insert("", tk.END, values=list(row))

# Bind click events to the table
tree.bind("<Button-1>", on_cell_click)

# Add a label to display results
result_label = tk.Label(root, text="Click a cell to see its match", padx=10, pady=10)

# Layout the widgets
tree.pack(padx=10, pady=10)
result_label.pack(padx=10, pady=5)

# Start the GUI loop
root.mainloop()

How It Works #

• Data Handling: pandas loads and manages both tables—swap out the simulated frames with your actual Excel files using pd.read_excel().
• Click Logic: The on_cell_click function captures your mouse click, maps it to the correct row/column in the second table, and displays the value.
• NaN Support: We explicitly check for NaN values so they show up as "NaN" instead of a blank or generic string.

Quick Customizations #

• Popup Instead of Label: Replace the result_label.config() lines with tk.messagebox.showinfo("Match Found", f"Value: {value}") (import tkinter.messagebox first).
• Styling: Use ttk.Style() to adjust colors, fonts, or column widths to match your preferences.
• Multi-Sheet Excel: Add sheet_name="SheetName" to pd.read_excel() if your data is in a specific sheet.

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