大PDF与Excel数据对比报错求助：解决IllegalCharacterError问题

问题根源 #

1. 错误的Excel读取方式：xlsx是基于ZIP的压缩格式，直接用文本模式读取会得到二进制乱码（如PK开头的内容），这是触发非法字符错误的核心原因。
2. 字符清理逻辑不足：原清理规则未覆盖openpyxl明确禁止的控制字符，导致残留非法内容无法写入Excel。

修正方案及完整代码 #

import pandas as pd
from PyPDF2 import PdfReader
import difflib
import re
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
from openpyxl import Workbook

# 定义文件路径
pdf_path = "./pdf_name.pdf"
excel_path = 'excel_name.xlsx'

# 从PDF提取文本（跳过空页内容）
def extract_text_from_pdf(pdf_path):
    with open(pdf_path, "rb") as file:
        pdf_reader = PdfReader(file)
        text = ""
        for page in pdf_reader.pages:
            extracted = page.extract_text()
            if extracted:
                text += extracted + "\n"
    return text

# 读取Excel所有工作表并转换为结构化文本
def read_excel_as_structured_text(excel_path):
    try:
        xls = pd.ExcelFile(excel_path)
        excel_text = ""
        for sheet_name in xls.sheet_names:
            df = pd.read_excel(xls, sheet_name=sheet_name)
            # 保留工作表标识和行列结构，方便对比定位
            excel_text += f"--- 工作表: {sheet_name} ---\n"
            excel_text += df.to_string(index=True, na_rep="") + "\n\n"
        return excel_text
    except FileNotFoundError:
        print("Excel文件未找到")
        return ""

# 清理文本，移除openpyxl禁止的非法字符
def clean_text(text):
    # 匹配openpyxl明确禁止的控制字符（排除换行、制表符）
    illegal_chars = re.compile(r'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]')
    # 替换非法字符为空格
    cleaned_text = illegal_chars.sub(' ', text)
    # 合并多余空格，保留换行结构
    cleaned_text = '\n'.join([line.strip() for line in cleaned_text.split('\n') if line.strip()])
    return cleaned_text

# 高效对比文本差异，区分匹配/删除/新增/替换状态
def find_differences(text1, text2):
    lines1 = text1.split('\n')
    lines2 = text2.split('\n')
    matcher = difflib.SequenceMatcher(None, lines1, lines2)
    diff_data = []
    for tag, i1, i2, j1, j2 in matcher.get_opcodes():
        if tag == 'equal':
            for line in lines1[i1:i2]:
                diff_data.append([line, line, "匹配"])
        elif tag == 'delete':
            for line in lines1[i1:i2]:
                diff_data.append([line, "", "删除"])
        elif tag == 'insert':
            for line in lines2[j1:j2]:
                diff_data.append(["", line, "新增"])
        elif tag == 'replace':
            for line in lines1[i1:i2]:
                diff_data.append([line, "", "替换"])
            for line in lines2[j1:j2]:
                diff_data.append(["", line, "替换"])
    return diff_data

# 生成带高亮的差异Excel文件
def write_diff_to_excel(diff_data, output_path):
    wb = Workbook()
    ws = wb.active
    ws.title = "差异对比"
    # 设置表头
    headers = ["PDF内容", "Excel内容", "状态"]
    for col, header in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=header)
    # 定义差异高亮样式
    delete_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
    insert_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
    replace_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
    # 写入数据并设置高亮
    for row_idx, (pdf_line, excel_line, status) in enumerate(diff_data, 2):
        cell_pdf = ws.cell(row=row_idx, column=1, value=pdf_line)
        cell_excel = ws.cell(row=row_idx, column=2, value=excel_line)
        ws.cell(row=row_idx, column=3, value=status)
        
        if status == "删除":
            cell_pdf.fill = delete_fill
        elif status == "新增":
            cell_excel.fill = insert_fill
        elif status == "替换":
            cell_pdf.fill = replace_fill
            cell_excel.fill = replace_fill
    # 调整列宽适配内容
    for col in range(1, 4):
        ws.column_dimensions[get_column_letter(col)].width = 50
    wb.save(output_path)

# 主执行流程
if __name__ == "__main__":
    pdf_text = extract_text_from_pdf(pdf_path)
    excel_text = read_excel_as_structured_text(excel_path)
    
    pdf_cleaned = clean_text(pdf_text)
    excel_cleaned = clean_text(excel_text)
    
    diff_results = find_differences(pdf_cleaned, excel_cleaned)
    
    write_diff_to_excel(diff_results, "./differences.xlsx")
    print("差异对比完成，结果已保存到differences.xlsx")

关键改进说明 #

• Excel读取修正：用pandas读取Excel的所有工作表，转换为带结构标识的文本，彻底避免直接读取二进制文件导致的乱码。
• 字符清理增强：精准过滤openpyxl禁止的控制字符，从根源消除非法字符错误。
• 差异对比优化：使用SequenceMatcher替代ndiff，更高效处理大型文本，同时细分差异状态。
• 可视化输出：生成的Excel文件用不同颜色高亮差异内容，便于快速定位差异点。

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