如何高亮DataFrame的After列差异词并导出至Excel/Word表格？

问题描述 #

现有如下Pandas DataFrame：

import pandas as pd
df = pd.DataFrame({'Before':['one three four', 'apple banana milk'], 
                   'After': ['one two three four five', 'soda banana apple juice milk'], 
                   'Difference': ['two, five', 'soda, juice']})

需求：高亮df['After']列中属于对应行df['Difference']列的词汇，并将结果保存至Excel或docx表格（预期效果为差异词汇以特殊样式显示，比如黄色背景）。

方案1：导出到Excel（基于openpyxl） #

通过openpyxl操作Excel单元格的富文本样式，实现差异词汇高亮：

1. 安装依赖库

pip install pandas openpyxl

2. 实现代码

import pandas as pd
from openpyxl import Workbook
from openpyxl.cell.rich_text import RichText, TextBlock
from openpyxl.cell.text import InlineFont
from openpyxl.utils.dataframe import dataframe_to_rows

# 初始化DataFrame
df = pd.DataFrame({'Before':['one three four', 'apple banana milk'], 
                   'After': ['one two three four five', 'soda banana apple juice milk'], 
                   'Difference': ['two, five', 'soda, juice']})

# 创建Excel工作簿与工作表
wb = Workbook()
ws = wb.active

# 写入表头与基础数据
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 1):
    for c_idx, val in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=val)

# 定义高亮样式（红色加粗字体）
highlight_font = InlineFont(b=True, color='FF0000')

# 逐行处理After列的高亮
for row_num in range(2, len(df)+2):
    after_text = df.iloc[row_num-2]['After']
    diff_words = [w.strip() for w in df.iloc[row_num-2]['Difference'].split(',')]
    words = after_text.split()
    
    # 构建富文本内容
    rich_text = RichText()
    for word in words:
        if word in diff_words:
            rich_text.append(TextBlock(highlight_font, word + ' '))
        else:
            rich_text.append(word + ' ')
    
    # 替换单元格内容为富文本
    ws.cell(row=row_num, column=3).value = rich_text

# 保存文件
wb.save('highlighted_excel_result.xlsx')

方案2：导出到docx（基于python-docx） #

通过python-docx创建表格，并为差异词汇设置高亮背景：

1. 安装依赖库

pip install pandas python-docx

2. 实现代码

import pandas as pd
from docx import Document
from docx.enum.text import WD_COLOR_INDEX

# 初始化DataFrame
df = pd.DataFrame({'Before':['one three four', 'apple banana milk'], 
                   'After': ['one two three four five', 'soda banana apple juice milk'], 
                   'Difference': ['two, five', 'soda, juice']})

# 创建Word文档与表格
doc = Document()
table = doc.add_table(rows=len(df)+1, cols=3)
table.style = 'Table Grid'

# 设置表头
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Before'
hdr_cells[1].text = 'After'
hdr_cells[2].text = 'Difference'

# 逐行填充数据并设置高亮
for idx, row in df.iterrows():
    row_cells = table.rows[idx+1].cells
    # 填充普通列
    row_cells[0].text = row['Before']
    row_cells[2].text = row['Difference']
    
    # 处理After列的高亮
    after_words = row['After'].split()
    diff_words = [w.strip() for w in row['Difference'].split(',')]
    para = row_cells[1].paragraphs[0]
    
    for word in after_words:
        run = para.add_run(word + ' ')
        if word in diff_words:
            # 设置黄色背景高亮
            run.font.highlight_color = WD_COLOR_INDEX.YELLOW
            run.font.bold = True

# 保存文件
doc.save('highlighted_docx_result.docx')

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