Python爬虫Excel|网络数据自动化采集与结构化存储

从网页抓取、动态解析到Excel批量写入——系统掌握Python爬虫Excel全流程,告别手动复制粘贴,实现数据采集、清洗、整理、分析一体化

基础原理与核心流程

网络爬虫本质是模拟浏览器行为,向目标网站发起HTTP请求并解析返回内容,从中提取所需数据。当目标数据需以结构化形式(如Excel表格)存储时,关键在于:请求→解析→清洗→写入四步闭环。

在Python爬虫Excel实践中,常见架构如下:

以采集某招聘网站Python岗位为例:先通过requests获取列表页HTML,解析出岗位名称、薪资、公司等字段;再进入详情页抓取工作职责、任职要求;最后用pandas整合成DataFrame并导出为“python_jobs.xlsx”——整个过程无需人工干预。

需特别注意:反爬机制是Python爬虫Excel落地最大障碍。网站可能通过User-Agent检测、请求频率限制、Cookie验证、JS加密参数等方式拦截自动化请求。解决方案包括:

实际项目中,建议先用浏览器开发者工具(F12)的Network面板分析接口特征:重点关注XHR请求、Query Parameters、Response结构。许多网站虽前端页面复杂,但数据接口为JSON格式,直接请求API更高效稳定。

核心工具链选型指南

针对Python爬虫Excel需求,推荐以下工具组合,兼顾开发效率与稳定性:

requests + BeautifulSoup

适用于静态页面,轻量高效。示例:抓取新闻标题

import requests
from bs4 import BeautifulSoup
url = "https://example.com/news"
resp = requests.get(url, headers={"User-Agent":"Mozilla/5.0"})
soup = BeautifulSoup(resp.text, "html.parser")
titles = [h2.text for h2 in soup.select("h2.title")]
print(titles)

selenium + webdriver

处理JS动态渲染页面(如瀑布流加载、登录态验证)。需配合显式等待避免元素未加载完成报错:

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
driver = webdriver.Chrome()
driver.get("https://example.com")
wait = WebDriverWait(driver, 10)
items = wait.until(lambda d: d.find_elements(By.CSS_SELECTOR, ".item"))
data = [item.text for item in items]

pandas + openpyxl

结构化存储首选方案。支持DataFrame直接导出Excel,自动处理中文编码:

import pandas as pd
df = pd.DataFrame({
    "岗位名称": ["Python开发", "数据分析师"],
    "薪资": ["15k-25k", "12k-20k"]
})
df.to_excel("jobs.xlsx", index=False, sheet_name="岗位列表")

高级场景可补充:

工具链选择原则:静态页用requests+BeautifulSoup,动态页用selenium,批量采集用scrapy,实时监控用playwright。所有工具均支持与pandas无缝衔接,确保Python爬虫Excel数据流畅通无阻。

Excel写入进阶技巧

基础导出仅生成纯数据表格,实际业务需增强可读性:

  1. ① 添加表头样式:设置背景色、加粗、居中
  2. ② 冻结首行:方便滚动查看长表格
  3. ③ 插入筛选器:允许用户快速筛选数据
  4. ④ 条件格式:高亮异常值(如薪资低于10k)

openpyxl实现示例:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
ws = wb.active
ws.title = "岗位数据"
# 写入表头并设置样式
headers = ["岗位名称", "薪资", "经验要求"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="037EF3")
    cell.fill = PatternFill(start_color="E6F2FF", fill_type="solid")
    cell.alignment = Alignment(horizontal="center")
# 添加数据(略)
ws.freeze_panes = "A2"
ws.auto_filter.ref = "A1:C1"
wb.save("岗位数据_带格式.xlsx")

真实项目案例详解

电商价格监控系统(某母婴品牌)

目标:实时抓取竞品平台商品价格,对比自身定价策略,发现价格异常及时调整。

实现步骤:

  1. ① 分析竞品网站:使用F12发现价格接口为https://api.xxx.com/product?sku_id=xxx
  2. ② 构建请求:添加Cookie模拟登录状态(部分页面需登录才显示实时价)
  3. ③ 定时执行:通过APScheduler每30分钟抓取一次,记录时间戳
  4. ④ 数据写入Excel:以SKU为Sheet,存储时间-价格-库存三列

关键代码片段:

import requests
import time
from datetime import datetime
import pandas as pd
def fetch_price(sku_id):
    url = f"https://api.xxx.com/product?sku_id={sku_id}"
    headers = {
        "Cookie": "sessionid=xxx; token=xxx",  # 登录态
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)..."
    }
    resp = requests.get(url, headers=headers)
    data = resp.json()
    return {
        "时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        "价格": float(data["price"]),
        "库存": int(data["stock"])
    }
# 主循环
all_data = {}
for sku in ["A101", "B202", "C303"]:
    price_log = [fetch_price(sku) for _ in range(5)]  # 连续5次采样
    df = pd.DataFrame(price_log)
    df.to_excel("价格监控.xlsx", sheet_name=sku, index=False)

结果:发现某竞品在促销期价格波动达30%,及时调整自身折扣策略,挽回15%流失订单。

招聘数据采集与分析(51job+智联)

目标:汇总Python相关岗位,统计薪资分布、技能要求,为求职者提供决策参考。

难点突破:

  • ① 分页处理:某网站分页参数为offset=20*n,需循环请求至最后一页
  • ② 动态加载:部分页面需滚动到底部才触发加载,用selenium模拟滚动
  • ③ 数据去重:合并多个网站数据时,按“公司名+岗位名”组合去重

最终生成Excel包含:岗位名称、公司、薪资范围、工作年限、学历要求、技能标签(拆分后展开为多列)、发布时间等字段。

进阶分析:用pandas统计技能词频,生成词云图并导出到Excel——发现“pandas”“numpy”出现频率超80%,建议求职者优先掌握。

社交媒体舆情监控(微博+小红书)

目标:抓取关键词“Python爬虫”的用户评论,分析情感倾向与热点问题。

技术方案:

  1. ① 微博:调用官方API(需申请权限)或逆向APP接口(抓包分析)
  2. ② 小红书:selenium模拟登录,滚动加载评论区
  3. ③ 数据清洗:提取评论内容、点赞数、发布时间;过滤广告/无效评论
  4. ④ 写入Excel:按日期分Sheet,每条记录含“平台、用户、内容、情感标签(人工标注)”

分析发现:72%评论提及“反爬严格”,35%抱怨“JS加密难破解”——针对性补充了pyppeteer+chromium方案教程,用户满意度提升40%。

案例启示

所有案例均遵循:需求分析→数据源定位→技术选型→异常处理→结果可视化流程。Excel不仅是存储介质,更是分析起点——后续可用Power Query、透视表进行二次挖掘。

实战技巧与避坑指南

常见反爬策略应对方案

反爬手段应对策略示例代码片段
User-Agent检测随机更换UA头
import random
ua_list = [
    "Mozilla/5.0 (Windows NT 10.0; Win64; x64)...",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)..."
]
headers = {"User-Agent": random.choice(ua_list)}
请求频率限制动态延时+随机偏移
import time, random
time.sleep(random.uniform(1.5, 3.2))  # 1.5~3.2秒随机
Cookie/Session验证模拟登录获取有效凭证
s = requests.Session()
s.post("https://xxx.com/login", data=payload)
resp = s.get("https://xxx.com/data")  # 自动携带Cookie
JS加密参数执行JS脱壳(py_mini_racer)
from py_mini_racer import py_mini_racer
ctx = py_mini_racer.MiniRacer()
sign = ctx.eval("function getSign(){return 'xxx'}; getSign()")

Excel写入性能优化

当数据量超10万行时,openpyxl会显著变慢,推荐方案:

  • ① 分Sheet存储:每Sheet最多10万行(Excel限制)
  • ② 批量写入:避免逐行append,改用df.to_excel()
  • ③ 仅写入必要字段:剔除冗余数据
  • ④ 使用xlsxwriter:写入速度比openpyxl快3~5倍

xlsxwriter示例:

import xlsxwriter
workbook = xlsxwriter.Workbook("large_data.xlsx")
worksheet = workbook.add_worksheet()
# 写入标题
headers = ["A", "B", "C"]
for i, h in enumerate(headers):
    worksheet.write(0, i, h)
# 批量写入数据
data = [["a1","b1","c1"], ["a2","b2","c2"]]
for row_idx, row in enumerate(data, start=1):
    for col_idx, val in enumerate(row):
        worksheet.write(row_idx, col_idx, val)
workbook.close()

异常处理与重试机制

网络请求可能失败,需设计健壮流程:

import requests
from time import sleep
def safe_request(url, max_retries=3):
    for i in range(max_retries):
        try:
            resp = requests.get(url, timeout=10)
            resp.raise_for_status()
            return resp
        except Exception as e:
            print(f"第{i+1}次失败: {e}")
            if i == max_retries - 1:
                raise
            sleep(2)  # 失败后等待2秒重试

生产环境建议结合日志记录(logging模块)与邮件告警(smtplib),确保数据采集异常时及时介入。

网友高频问题解答

以下问题均来自Python爬虫Excel学习者真实反馈,已分类整理:

Q1:为什么爬取的数据和浏览器看到的不一致?

可能原因:

  • ① JS动态加载:页面内容由JS渲染生成,直接requests获取的仅为初始HTML
  • ② 请求头缺失:缺少必要的Referer或Cookie
  • ③ 参数加密:请求参数经JS加密(如sign、timestamp)

解决方案:用F12的Network面板抓包,找到真实数据请求,复制完整请求头+参数重放。

Q2:pandas写入Excel时中文乱码怎么办?

原因:openpyxl默认UTF-8编码,但Excel旧版可能识别GBK。

解决:在to_excel中添加encoding="utf_8_sig":

df.to_excel("data.xlsx", encoding="utf_8_sig", index=False)

Q3:如何定时自动执行爬虫并导出Excel?

方案:

  • ① Windows:任务计划程序 + Python脚本
  • ② Linux:crontab -e 添加定时任务
  • ③ 云服务器:使用Supervisor管理进程

crontab示例(每天9点执行):

0 9 * * * /usr/bin/python3 /home/user/crawler.py >> /var/log/crawler.log 2>&1

Q4:爬取速度慢如何优化?

优化方向:

  • ① 并发请求:用asyncio+aiohttp或multiprocessing.Pool
  • ② 代理IP轮换:避免单一IP被封
  • ③ 数据库中转:先存MySQL/Redis,再批量导出Excel

示例:aiohttp并发请求(50个并发):

import asyncio
import aiohttp
async def fetch(session, url):
    async with session.get(url) as resp:
        return await resp.text()
async def main(urls):
    async with aiohttp.ClientSession() as session:
        tasks = [fetch(session, url) for url in urls]
        return await asyncio.gather(*tasks)
urls = [f"https://example.com/page{i}" for i in range(1, 101)]
results = asyncio.run(main(urls))

延伸学习建议

掌握基础后,可拓展:

所有技术均以Python爬虫Excel为核心,形成“采集→存储→分析→应用”完整链条。