使用Pandas检测xlsx含@符号时遇空列报错的解决方法

Got it, let's tackle your problem step by step. You're trying to check if an .xlsx file contains the @ symbol using Pandas, but it throws an xlrd error when the first column is empty. Here's how to fix both the error and handle empty columns properly:

1. Resolve the File Read Error #

That XLRDError about unsupported format/corrupt file is almost certainly because xlrd v2.0+ dropped support for .xlsx files—it only handles .xls now. Plus, when the first column is fully empty, xlrd can struggle to parse the sheet structure. We'll switch to openpyxl (a dedicated .xlsx engine) and adjust read parameters to avoid header misidentification:

import pandas as pd

def check_for_at_symbol(file_path):
    # Use openpyxl for .xlsx files, skip header auto-detection with header=None
    df = pd.read_excel(file_path, engine='openpyxl', header=None)
    
    # Drop columns that are completely empty to avoid unnecessary checks
    df_filtered = df.dropna(axis=1, how='all')
    
    has_at = False
    for col in df_filtered.columns:
        # Only check string-type columns to avoid errors on numeric/datetime columns
        if df_filtered[col].dtype == object:
            # na=False ignores NaN values so they don't break the check
            if df_filtered[col].str.contains('@', na=False).any():
                has_at = True
                break
    return 'True' if has_at else 'False'

2. Key Improvements Explained #

• Engine Switch: engine='openpyxl' is designed for .xlsx files, eliminating the xlrd compatibility issue
• Empty Column Removal: dropna(axis=1, how='all') gets rid of fully empty columns right away, so we don't waste time checking them
• Type Safety Check: We only run str.contains on object-type columns (which hold strings), preventing errors on non-text columns like numbers or dates
• Handle NaN Values: The na=False flag tells Pandas to treat empty cells as not containing @, instead of returning NaN which breaks the .any() check

How to Test #

Just call the function with your file path:

result = check_for_at_symbol("your_file.xlsx")
print(result)  # Outputs 'True' if @ exists, 'False' otherwise

This will work even if the first column (or any other columns) are fully empty, and won't throw that annoying xlrd error anymore.

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