如何提取网页数据并导出为Excel表格？

Question

在使用Python爬取网页数据并导出为Excel时，常遇到“中文乱码或数据格式丢失”的问题。例如，通过`requests`获取页面内容后，使用`pandas.DataFrame`保存为Excel文件，但导出后发现中文显示为问号或乱码字符，或数字、日期格式被错误识别。该问题通常源于编码设置不当或未正确指定`to_excel()`中的编码参数（如encoding='utf-8'）。此外，若使用`openpyxl`引擎处理复杂HTML表格，还可能出现样式错乱或多级表头解析失败的情况。如何确保数据完整提取并准确导出为结构清晰、编码正确的Excel文件？

Answer 1

一、问题背景与常见现象

在使用Python进行网页数据爬取并导出为Excel文件的过程中，许多开发者频繁遭遇中文乱码或数据格式丢失的问题。典型场景如下：

• 通过requests.get()获取HTML内容后，未正确设置响应编码（如UTF-8），导致中文字符被错误解析为乱码。
• 使用pandas.read_html()提取表格时，多级表头未能正确映射，造成列名结构混乱。
• 调用df.to_excel()导出数据时，未指定合适的引擎或编码参数，导致Excel中出现问号（???）或数字/日期格式异常。
• 当使用openpyxl作为写入引擎处理复杂样式表格时，原有HTML中的合并单元格、字体颜色等信息无法保留。

这些问题不仅影响数据可读性，更可能引发后续数据分析的逻辑错误。

二、底层机制分析：从HTTP到Excel的编码流转

要解决乱码问题，需理解整个数据流转过程中的编码转换环节：

阶段	涉及组件	默认编码	常见风险点
HTTP响应接收	requests.Response	基于headers或chardet推测	response.encoding未手动设置
HTML解析	lxml / BeautifulSoup	Unicode内部表示	非标准meta标签导致误判
DataFrame构建	pandas.DataFrame	Python str (Unicode)	类型推断错误（如日期转字符串）
Excel导出	pandas.ExcelWriter	xlsx无编码概念，但CSV有	to_excel()中encoding参数无效于xlsx

三、解决方案层级递进

1. 第一层：确保HTTP响应正确解码

   import requests
   response = requests.get("https://example.com/table-page")
   response.encoding = 'utf-8'  # 强制指定编码，避免requests自动猜测失败
   html_content = response.text

   若目标网站使用GB2312或GBK编码（常见于国内政府网站），应设为response.encoding = 'gbk'。
2. 第二层：精准提取HTML表格结构 使用pandas.read_html()时，可通过match和flavor参数提高解析准确性：

   dfs = pd.read_html(html_content, match='业绩报表', flavor='lxml', header=[0,1])

   其中header=[0,1]用于识别双层表头，避免扁平化列名。
3. 第三层：控制DataFrame内部数据类型 在导出前显式定义字段类型，防止pandas自动推断出错：

   df['营收'] = pd.to_numeric(df['营收'], errors='coerce')
   df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d', errors='coerce')

4. 第四层：合理选择Excel导出配置 注意：to_excel()对.xlsx文件不支持encoding参数（因为OOXML基于Unicode）。若需生成CSV，则必须指定：

   # 导出为CSV（需编码）
   df.to_csv('output.csv', encoding='utf-8-sig', index=False)
   
   # 导出为XLSX（推荐方式）
   with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
       df.to_excel(writer, sheet_name='数据表', index=False)

四、高级技巧：处理复杂HTML表格与样式还原

对于包含合并单元格、嵌套表头的复杂表格，pandas.read_html()往往力不从心。此时可结合BeautifulSoup进行预处理：

from bs4 import BeautifulSoup
import pandas as pd

soup = BeautifulSoup(html_content, 'lxml')
table = soup.find('table', {'class': 'complex-table'})

# 手动重建行列结构，处理rowspan/colspan
rows = table.find_all('tr')
data = []
for row in rows:
    cols = row.find_all(['td', 'th'])
    data.append([col.get_text(strip=True) for col in cols])

df = pd.DataFrame(data[1:], columns=data[0])

此外，在导出时若需保留样式，可直接操作openpyxl工作簿对象：

from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

with pd.ExcelWriter('styled_output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='Styled Data')
    ws = writer.sheets['Styled Data']
    
    # 设置标题行加粗居中
    for col_num in range(1, len(df.columns) + 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')

五、完整流程图示：从爬取到导出的标准化路径

graph TD A[发起HTTP请求] --> B{检查Content-Type与charset} B -- charset缺失或错误 --> C[手动设置response.encoding] B -- 正确UTF-8/GKB --> D[获取text内容] C --> D D --> E[使用read_html或BeautifulSoup解析] E --> F{是否含多级表头或合并单元格?} F -- 是 --> G[自定义解析逻辑重建DataFrame] F -- 否 --> H[直接生成DataFrame] G --> I[清洗与类型转换] H --> I I --> J[使用ExcelWriter导出] J --> K[选择openpyxl引擎] K --> L[可选：添加样式与格式化] L --> M[保存为.xlsx文件]

六、最佳实践建议

• 始终验证response.apparent_encoding并与实际页面<meta />标签比对。
• 优先使用.xlsx而非.csv存储结构化数据，规避编码争议。
• 在CI/CD环境中部署爬虫时，统一设置Python环境变量PYTHONIOENCODING=utf-8。
• 对金融、政务类网站，注意其常采用IE兼容模式且编码为GBK，需特殊处理。
• 利用chardet.detect(response.content)辅助判断未知源编码。
• 定期更新pandas和openpyxl至最新稳定版，修复已知bug。
• 对于动态渲染页面，考虑结合Selenium或Playwright获取最终DOM。
• 导出前加入数据质量校验步骤，如空值统计、唯一性检查。
• 使用logging模块记录关键节点的编码状态与字段类型。
• 建立模板化的导出函数，封装常用样式与格式规则。