如何在Python中实现类Excel VLOOKUP的下拉联动填充功能

Hey there! You're already halfway there with setting up the dropdown validation for column D. Let's build on that to add the auto-populating description functionality for column E—just like your VLOOKUP formula does in Excel.

First, let's fix a couple of small issues in your existing code, then add the formula logic you need:

Key Improvements & Fixes #

1. Avoid comma-related dropdown bugs: Your current method joins SOW values with commas, which breaks if any SOW has a comma in it. Instead, we'll import the SOW data directly into a hidden sheet in your target workbook and reference that range for validation.
2. Add auto-populating VLOOKUP formulas: We'll write the VLOOKUP formula to every cell in column E (matching column D's rows) so descriptions fill automatically when a SOW is selected.
3. Fix syntax issues: The " in your code is a HTML entity—replace those with regular quotes for valid Python syntax.

Complete Working Code #

from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
import pandas as pd
import webbrowser

def setup_sow_dropdown_and_autofill():
    # Load SOW data from the source Excel file
    sow_df = pd.read_excel('Billing Roster - SOW.xlsx')
    
    # Load the target resource allocation workbook
    wb = load_workbook('ACFC_Resource_Allocation.xlsx')
    allocationsheet = wb.active  # Use wb['YourSheetName'] if you need a specific sheet
    
    # Step 1: Create a hidden sheet to store SOW data (avoids user edits)
    if 'SOW_Data' not in wb.sheetnames:
        sow_sheet = wb.create_sheet('SOW_Data')
        # Write header and SOW data to the hidden sheet
        sow_sheet.append(['SOW', 'SOW Description'])
        for _, row in sow_df.iterrows():
            # Update column names here if they don't match your source file
            sow_sheet.append([row['SOW'], row['SOW Description (Match value for SOW)']])
        # Hide the sheet to keep the workbook clean
        sow_sheet.sheet_state = 'hidden'
    else:
        # Overwrite existing data to keep it updated
        sow_sheet = wb['SOW_Data']
        sow_sheet.delete_rows(1, sow_sheet.max_row)
        sow_sheet.append(['SOW', 'SOW Description'])
        for _, row in sow_df.iterrows():
            sow_sheet.append([row['SOW'], row['SOW Description (Match value for SOW)']])
    
    # Step 2: Set up data validation for column D (SOW dropdown)
    max_row = allocationsheet.max_row
    # Reference the SOW range in the hidden sheet
    sow_range = f"'SOW_Data'!$A$2:$A${sow_sheet.max_row}"
    validator_sow = DataValidation(
        type='list',
        formula1=sow_range,
        allow_blank=True,
        showDropDown=True
    )
    allocationsheet.add_data_validation(validator_sow)
    # Apply validation to all rows in column D starting from D2
    validator_sow.add(f'D2:D{max_row}')
    
    # Step 3: Add VLOOKUP formula to column E for auto-populating descriptions
    for row in range(2, max_row + 1):
        formula = f'=VLOOKUP(D{row}, \'SOW_Data\'!$A$2:$B${sow_sheet.max_row}, 2, FALSE)'
        allocationsheet[f'E{row}'] = formula
    
    # Save changes and close the workbook
    wb.save('ACFC_Resource_Allocation.xlsx')
    wb.close()

if __name__ == "__main__":
    setup_sow_dropdown_and_autofill()
    webbrowser.open('ACFC_Resource_Allocation.xlsx')

How This Works #

• Hidden SOW Sheet: We store the SOW data directly in your target workbook, so you don't have to rely on fragile external file references (which break if the source file is moved or renamed).
• Reliable Dropdown: The data validation references the hidden sheet's SOW column, so even if SOW values have commas or special characters, the dropdown works correctly.
• Auto-Populating Formula: The VLOOKUP formula is written to every cell in column E. When you select a SOW from column D, Excel automatically runs the formula to pull the matching description from the hidden sheet.

Quick Notes #

• If your SOW column in the source file is named SOW # instead of SOW, update row['SOW'] to row['SOW #'] in the code.
• If you prefer to keep the source file as an external reference (instead of importing data into the target workbook), modify the formula to use the external file path:

  formula = f'=VLOOKUP(D{row}, \'[Billing Roster - SOW.xlsx]SOW List\'!$A$1:$B$14, 2, FALSE)'
  

  Just ensure the source file stays in the same folder as the target workbook, or use a full absolute file path.

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