Python写入Excel时Name字段显示异常问题求助

我编写了一段Python脚本，用于从网站抓取数据并写入Excel。脚本可正常运行，但提取的Name写入Excel后显示字号极小，点击单元格后字号恢复正常。Parcel Number显示正常，只有Name出现这个问题，以下是当前代码：

from openpyxl import load_workbook 
from bs4 import BeautifulSoup
import requests

# Fetch the HTML page
url = 'https://esearch.mobilecopropertytax.com/Property/View/466089'
response = requests.get(url)
html = response.text

# Parse the HTML page
soup = BeautifulSoup(html, 'lxml')

# Find the element containing the Parcel Number
element = soup.find('th', text='Parcel Number:')
parcel_number = element.find_next_sibling().text

# Find the element containing the Name
element = soup.find('th', text='Name:')
name = element.find_next_sibling().text

# Load the workbook
wb = load_workbook(r'C:\Users\user\EJW Test\EJWtest.xlsx')
ws = wb['Justification Worksheet']

# Select the cells
cell1 = ws['C7'] # Parcel Number
cell2 = ws['C10'] # Name

# Set the values of the cells
cell1.value = parcel_number
cell2.value = name

# Save the workbook
wb.save('completetest.xlsx')

原因分析 #

这种情况几乎都是因为从网页提取的Name文本中包含隐藏的HTML格式残留或特殊控制字符（比如网页里的<small>标签格式、多余的换行/缩进、非标准空格等），openpyxl写入时会携带这些隐性格式，导致Excel显示异常；而点击单元格时，Excel会重新解析渲染文本，格式就自动恢复了。Parcel Number没有这个问题，是因为它的网页文本本身没有这类隐藏格式。

解决方法 #

1. 清理提取的文本 #

先去除文本中的所有多余空白字符和潜在控制字符，确保写入的是纯文本：

# 提取Name后立即清理文本
name = element.find_next_sibling().text.strip()
# 把所有换行、制表符、连续空格替换成单个空格
name = ' '.join(name.split())

2. 强制统一单元格字体格式 #

如果清理文本后问题仍存在，直接将Name单元格的字体设置为和Parcel Number单元格一致，覆盖隐性格式：

# 设置单元格值后，复制Parcel Number单元格的字体格式
cell2.value = name
cell2.font = cell1.font.copy()

修改后的完整代码 #

from openpyxl import load_workbook 
from bs4 import BeautifulSoup
import requests

# Fetch the HTML page
url = 'https://esearch.mobilecopropertytax.com/Property/View/466089'
response = requests.get(url)
html = response.text

# Parse the HTML page
soup = BeautifulSoup(html, 'lxml')

# Find the element containing the Parcel Number
element = soup.find('th', text='Parcel Number:')
parcel_number = element.find_next_sibling().text

# Find the element containing the Name
element = soup.find('th', text='Name:')
# 清理Name文本
name = element.find_next_sibling().text.strip()
name = ' '.join(name.split())

# Load the workbook
wb = load_workbook(r'C:\Users\user\EJW Test\EJWtest.xlsx')
ws = wb['Justification Worksheet']

# Select the cells
cell1 = ws['C7'] # Parcel Number
cell2 = ws['C10'] # Name

# Set the values of the cells
cell1.value = parcel_number
cell2.value = name
# 统一字体格式
cell2.font = cell1.font.copy()

# Save the workbook
wb.save('completetest.xlsx')

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