PDF与Excel数据对比代码优化：拆分删除项、动态添文件及导出

需求说明 #

• 使每一条差异（包括Deleted状态）均单独成行，避免冗长拼接
• 将差异结果导出为带有对应状态高亮的Excel文件和Word文件
• 实现动态输入文件路径，替换硬编码的路径配置

问题分析 #

原代码中find_differences函数对Deleted状态的行做了拼接累加处理，导致多条删除内容被合并成一条冗长字符串，无法清晰展示每条差异。同时文件路径为硬编码，缺乏灵活性；Word导出环节存在冗余的CSV转XML步骤，且未实现状态高亮。

修复后的完整代码 #

import pandas as pd
from PyPDF2 import PdfReader
import difflib
import re
from docx import Document
from docx.shared import RGBColor
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT

# 从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:
            text += page.extract_text()
    return text

# 读取Excel文件并返回行列表
def read_excel(excel_path):
    try:
        df = pd.read_excel(excel_path)
        return [row.tolist() for _, row in df.iterrows()]
    except FileNotFoundError:
        print("Excel文件未找到，请检查路径。")
        return []

# 清洗文本数据
def clean_text(text):
    # 移除不可打印ASCII字符和Excel非法字符
    cleaned_text = ''.join(filter(lambda x: x.isprintable() or x.isspace() or ord(x) > 31, text))
    cleaned_text = re.sub(r'[^\x20-\x7E]', '', cleaned_text)
    return cleaned_text

# 查找文本差异（修复Deleted行拼接问题）
def find_differences(text1, text2):
    diff = difflib.ndiff(text1.splitlines(), text2.splitlines())
    diff_data = []
    for line in diff:
        if line.startswith('+'):
            diff_data.append({"Text": line[2:].strip(), "Status": "Added"})
        elif line.startswith('-'):
            # 每条Deleted行单独添加，不再拼接
            diff_data.append({"Text": line[2:].strip(), "Status": "Deleted"})
        elif line.startswith(' '):
            diff_data.append({"Text": line[2:].strip(), "Status": "Matched"})
    return diff_data

# 生成带高亮的Word文档
def generate_highlighted_word(diff_df, output_path):
    doc = Document()
    # 添加标题
    doc.add_heading('PDF与Excel数据差异对比结果', level=1)
    
    # 添加表格
    table = doc.add_table(rows=1, cols=2)
    table.style = 'Table Grid'
    # 设置表头
    hdr_cells = table.rows[0].cells
    hdr_cells[0].text = '内容'
    hdr_cells[1].text = '状态'
    # 设置表头样式
    for cell in hdr_cells:
        cell.paragraphs[0].runs[0].bold = True
        cell.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
    
    # 添加差异行并设置高亮
    for _, row in diff_df.iterrows():
        row_cells = table.add_row().cells
        # 设置内容单元格
        content_paragraph = row_cells[0].paragraphs[0]
        content_run = content_paragraph.add_run(row['Text'])
        # 设置状态单元格
        status_paragraph = row_cells[1].paragraphs[0]
        status_run = status_paragraph.add_run(row['Status'])
        
        # 根据状态设置颜色
        if row['Status'] == 'Added':
            content_run.font.color.rgb = RGBColor(0, 128, 0)  # 绿色
            status_run.font.color.rgb = RGBColor(0, 128, 0)
            status_run.bold = True
        elif row['Status'] == 'Deleted':
            content_run.font.color.rgb = RGBColor(255, 0, 0)  # 红色
            status_run.font.color.rgb = RGBColor(255, 0, 0)
            status_run.bold = True
        else:
            content_run.font.color.rgb = RGBColor(0, 0, 0)  # 黑色
    
    doc.save(output_path)
    print(f"Word文件已生成：{output_path}")

# 动态获取文件路径
pdf_path = input("请输入PDF文件路径：").strip()
excel_path = input("请输入Excel文件路径：").strip()

# 提取并处理数据
excel_rows = read_excel(excel_path)
if not excel_rows:
    exit()

pdf_text = extract_text_from_pdf(pdf_path)
pdf_text_cleaned = clean_text(pdf_text)

# 查找差异
diff_data = find_differences(pdf_text_cleaned, '\n'.join([' '.join(map(str, row)) for row in excel_rows]))
diff_df = pd.DataFrame(diff_data, columns=["Text", "Status"])

# 导出带高亮的Excel文件
highlight_colors = {
    'Added': '#90EE90',    # 浅绿色
    'Deleted': '#FFCCCB',  # 浅红色
    'Matched': '#FFFFFF'   # 白色
}

excel_writer = pd.ExcelWriter("差异对比结果.xlsx", engine='xlsxwriter')
diff_df.to_excel(excel_writer, index=False)

workbook = excel_writer.book
worksheet = excel_writer.sheets['Sheet1']

# 设置列宽
worksheet.set_column('A:A', 80)
worksheet.set_column('B:B', 15)

# 应用高亮格式
for status, color in highlight_colors.items():
    cell_format = workbook.add_format({'bg_color': color})
    for row_idx, row in diff_df.iterrows():
        if row['Status'] == status:
            worksheet.set_row(row_idx + 1, None, cell_format)

excel_writer.close()
print("Excel文件已生成：差异对比结果.xlsx")

# 导出带高亮的Word文件
generate_highlighted_word(diff_df, "差异对比结果.docx")

关键修改说明 #

1. 修复Deleted行拼接问题：重写find_differences函数，遇到-开头的删除行直接单独添加到差异列表，不再累加拼接
2. 动态路径输入：使用input()函数让用户手动输入PDF和Excel文件路径，替代硬编码配置
3. Excel高亮优化：调整高亮颜色为更柔和的浅绿/浅红，同时设置列宽提升可读性
4. Word高亮实现：直接基于差异DataFrame生成Word表格，给不同状态的内容设置对应颜色和加粗效果，移除冗余的CSV/XML转换步骤
5. 代码精简：删除无用的CSV导出和XML转换逻辑，直接用DataFlow处理生成最终文件

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