使用Pandas和Openpyxl读取Excel时遇数值/通配符格式错误

该错误由原Excel文件中的无效筛选规则导致：openpyxl在解析工作表的筛选器时，遇到了既非数值也不含通配符的筛选条件，触发filters.py中的值校验错误。同时你的代码存在重复读取文件的问题，会覆盖之前对列的处理操作。

1. 修复文件读取问题（移除无效筛选器） #

有两种可选方式：

方式A：手动预处理Excel文件 #

打开原Excel文件，选中所有带筛选器的列，点击「数据」→「清除筛选」，保存后再用代码读取。

方式B：代码自动移除筛选器 #

用openpyxl先加载文件并清除所有工作表的筛选器，保存为临时文件后再用pandas读取：

from openpyxl import load_workbook
import tempfile

file_path = r'C:\Users\ivatu\Downloads\iglistaff.xlsx'
# 创建临时文件存储移除筛选器后的内容
temp_file = tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False).name

wb = load_workbook(file_path)
for ws in wb.worksheets:
    if ws.auto_filter:
        ws.auto_filter.ref = None  # 清除筛选器区域
wb.save(temp_file)

# 读取处理后的临时文件
df = pd.read_excel(temp_file, engine='openpyxl')
os.unlink(temp_file)  # 删除临时文件

2. 修正代码中的无效操作 #

删除代码中重复读取文件的行：

# 删掉此行：df = pd.read_excel(file_path)

这行代码会覆盖之前对Phone country code列的处理，导致所有修改丢失。

3. 优化列处理与过滤逻辑 #

用更稳健的方式处理非数值值，同时简化过滤条件：

# 处理Phone country code列的非数值值，转为0并转为字符串类型
df['Phone country code'] = pd.to_numeric(df['Phone country code'], errors='coerce').fillna(0).astype(str)

# 用isin替代apply，过滤条件更简洁高效
filtered_data = df[(df['Followers count'] > 1000) &
                   (df['Phone country code'].isin(['1', '44', '0'])) &
                   (df['Is private'] == 'NO')]

import pandas as pd
import openai
import os
from openpyxl import load_workbook
import tempfile

# Set your OpenAI GPT-3 API key
openai.api_key = 'key'

# 加载Excel文件并移除筛选器
file_path = r'C:\Users\ivatu\Downloads\iglistaff.xlsx'
temp_file = tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False).name

wb = load_workbook(file_path)
for ws in wb.worksheets:
    if ws.auto_filter:
        ws.auto_filter.ref = None
wb.save(temp_file)

df = pd.read_excel(temp_file, engine='openpyxl')
os.unlink(temp_file)

# Function to identify names using GPT-3
def identify_names(full_name):
    response = openai.Completion.create(
        engine="text-davinci-002",
        prompt=f"Identify names in {full_name}.",
        max_tokens=50
    )
    return response.choices[0].text.strip()

# Handle non-numeric values in the "Phone country code" column
df['Phone country code'] = pd.to_numeric(df['Phone country code'], errors='coerce').fillna(0).astype(str)

# Apply GPT-3 to identify names and replace non-obvious names with "Coach"
df['Full name'] = df['Full name'].apply(lambda name: identify_names(name) if not name.replace(" ", "").isalpha() else name)
df['Full name'] = df['Full name'].apply(lambda name: "Coach" if "Coach" in name else name)

# Filter the data based on the condition
filtered_data = df[(df['Followers count'] > 1000) &
                   (df['Phone country code'].isin(['1', '44', '0'])) &
                   (df['Is private'] == 'NO')]

# Extract the required columns
extracted_data = filtered_data[['Username', 'Full name', 'Followers count']]

output_folder = os.path.join(os.path.expanduser("~"), "Downloads", "output_path_folder")
os.makedirs(output_folder, exist_ok=True)

# Save the extracted data to a new Excel file in the output folder
output_path = os.path.join(output_folder, 'output_file.xlsx')
extracted_data.to_excel(output_path, index=False)

print("Extraction and filtering completed. Results saved to:", output_path)

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