告别手动复制网页数据!Python自动抓取+写入Excel,报表永远最新

1pip install pandas openpyxl requests beautifulsoup4 sqlalchemy pymysql

 1import pandas as pd
 2
 3
 4# 目标网页URL（这里用示例，实际替换成你要抓的网页）
 5
 6url = "https://quote.eastmoney.com/center/gridlist.html#hs_a_board"
 7
 8
 9# read_html 会返回页面中所有表格的列表
10
11tables = pd.read_html(url)
12
13
14# 通常第一个表格就是我们要的数据
15
16df = tables[0]
17
18
19# 看一眼数据
20
21print(df.head())
22
23
24# 保存到Excel
25
26df.to_excel("股票数据.xlsx", index=False)
27
28print("网页表格已保存到Excel")

💡 read_html 是最简单的方法，但有些网页的表格是JavaScript动态加载的，这种方法抓不到。遇到动态网页需要用Selenium（见场景4）。

进阶：抓取多页表格（翻页）

 1import pandas as pd
 2
 3import time
 4
 5
 6all_data = []
 7
 8for page in range(1, 6):  # 抓取前5页
 9
10    url = f"https://example.com/table?page={page}"
11
12    tables = pd.read_html(url)
13
14if tables:
15
16        df = tables[0]
17
18        all_data.append(df)
19
20        print(f"第{page}页抓取完成，共{len(df)}行")
21
22    time.sleep(1)  # 礼貌性等待，避免被封IP
23
24
25final_df = pd.concat(all_data, ignore_index=True)
26
27final_df.to_excel("多页表格汇总.xlsx", index=False)
28
29print(f"总计{len(final_df)}行数据已保存")

场景2：从API接口获取数据（最常用）

很多现代网站和服务提供API接口，返回JSON格式数据。比如天气、汇率、企业信息等。

示例：从公开API获取比特币实时价格

 1import requests
 2
 3import pandas as pd
 4
 5
 6url = "https://api.coindesk.com/v1/bpi/currentprice.json"
 7
 8response = requests.get(url)
 9
10data = response.json()
11
12
13# 解析JSON，提取我们需要的信息
14
15bpi = data["bpi"]
16
17records = []
18
19for currency, info in bpi.items():
20
21    records.append({
22
23"货币": currency,
24
25"最新价格": info["rate_float"],
26
27"描述": info["description"],
28
29"更新时间": data["time"]["updated"]
30
31    })
32
33
34df = pd.DataFrame(records)
35
36df.to_excel("比特币价格.xlsx", index=False)
37
38print(df)

示例：从企业公开API获取公司信息（假设有KEY）

 1import requests
 2
 3import pandas as pd
 4
 5
 6api_key = "your_api_key_here"
 7
 8company_name = "腾讯"
 9
10
11url = f"https://api.qichacha.com/company/search?key={api_key}&keyword={company_name}"
12
13headers = {"User-Agent": "Mozilla/5.0"}
14
15response = requests.get(url, headers=headers)
16
17
18if response.status_code == 200:
19
20    data = response.json()
21
22# 假设返回的列表在data["result"]["list"]中
23
24    companies = data["result"]["list"]
25
26    df = pd.DataFrame(companies)
27
28    df.to_excel("企业查询结果.xlsx", index=False)
29
30    print("保存成功")
31
32else:
33
34    print(f"请求失败，状态码：{response.status_code}")

场景3：从数据库读取数据写入Excel

公司内部数据通常存在MySQL、PostgreSQL、SQL Server等数据库中，用Python可以轻松导出到Excel。

示例：连接MySQL数据库，读取销售表

 1from sqlalchemy import create_engine
 2
 3import pandas as pd
 4
 5
 6# 数据库连接信息（请替换成你自己的）
 7
 8db_user = "your_username"
 9
10db_password = "your_password"
11
12db_host = "192.168.1.100"
13
14db_port = 3306
15
16db_name = "sales_db"
17
18
19# 创建数据库引擎
20
21engine = create_engine(f"mysql+pymysql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}")
22
23
24# 执行SQL查询
25
26sql = """
27
28SELECT 
29
30    order_date,
31
32    region,
33
34    product_name,
35
36    sales_amount
37
38FROM orders
39
40WHERE order_date >= '2025-01-01'
41
42"""
43
44
45df = pd.read_sql(sql, engine)
46
47
48# 写入Excel
49
50df.to_excel("数据库导出_销售数据.xlsx", index=False)
51
52print(f"导出成功，共{len(df)}行")
53
54
55# 关闭连接
56
57engine.dispose()

进阶：支持参数化查询（防止SQL注入 + 灵活过滤）

 1start_date = "2025-03-01"
 2
 3end_date = "2025-03-31"
 4
 5
 6sql = """
 7
 8SELECT * FROM orders 
 9
10WHERE order_date BETWEEN :start AND :end
11
12"""
13
14df = pd.read_sql(sql, engine, params={"start": start_date, "end": end_date})
15
16df.to_excel(f"订单_{start_date}_至_{end_date}.xlsx", index=False)

连接常见数据库的连接字符串格式：

数据库 连接字符串示例

MySQL mysql+pymysql://user:pass@host:3306/dbname

PostgreSQL postgresql://user:pass@host:5432/dbname

SQL Server mssql+pyodbc://user:pass@host:1433/dbname?driver=ODBC+Driver+17+for+SQL+Server

SQLite sqlite:///data.db（本地文件）

场景4：动态网页抓取（Selenium方案）

有些网页的数据是通过JavaScript动态加载的，read_html和requests都拿不到。这时候需要用Selenium模拟浏览器。

安装Selenium和浏览器驱动：

1pip install selenium

还需要下载ChromeDriver（https://chromedriver.chromium.org/）并放到系统PATH中。

示例：抓取动态加载的表格

 1from selenium import webdriver
 2
 3from selenium.webdriver.chrome.options import Options
 4
 5from selenium.webdriver.common.by import By
 6
 7import pandas as pd
 8
 9import time
10
11
12# 设置无头模式（不显示浏览器窗口）
13
14chrome_options = Options()
15
16chrome_options.add_argument("--headless")
17
18
19driver = webdriver.Chrome(options=chrome_options)
20
21driver.get("https://example.com/dynamic-table")
22
23
24# 等待表格加载（根据实际情况调整等待时间）
25
26time.sleep(3)
27
28
29# 找到表格元素
30
31table = driver.find_element(By.ID, "data-table")
32
33rows = table.find_elements(By.TAG_NAME, "tr")
34
35
36data = []
37
38for row in rows:
39
40    cells = row.find_elements(By.TAG_NAME, "td")
41
42if cells:
43
44        data.append([cell.text for cell in cells])
45
46
47driver.quit()
48
49
50# 转换为DataFrame
51
52df = pd.DataFrame(data)
53
54df.to_excel("动态网页表格.xlsx", index=False)
55
56print("动态网页数据抓取完成")

场景5：整合——从多个数据源生成一份综合Excel报表

假设你要做一份“每日销售运营报表”，数据来自：

· 数据库中的订单表

· 一个汇率API（用于转换外币金额）

· 一个网页上的竞品价格信息

你可以把它们整合到一个Excel文件的不同Sheet中：

 1import pandas as pd
 2
 3import requests
 4
 5from sqlalchemy import create_engine
 6
 7
 8# 1. 从数据库读取销售数据
 9
10engine = create_engine("mysql+pymysql://user:pass@host/sales")
11
12df_sales = pd.read_sql("SELECT * FROM orders WHERE date = CURDATE()", engine)
13
14engine.dispose()
15
16
17# 2. 从API获取汇率
18
19url = "https://api.exchangerate-api.com/v4/latest/USD"
20
21resp = requests.get(url).json()
22
23usd_to_cny = resp["rates"]["CNY"]
24
25# 增加一列人民币金额
26
27df_sales["amount_cny"] = df_sales["amount_usd"] * usd_to_cny
28
29
30# 3. 从网页抓取竞品价格
31
32tables = pd.read_html("https://competitor.com/prices")
33
34df_competitor = tables[0]
35
36
37# 4. 写入Excel，不同数据放不同sheet
38
39with pd.ExcelWriter("每日运营报表.xlsx") as writer:
40
41    df_sales.to_excel(writer, sheet_name="销售明细", index=False)
42
43    df_competitor.to_excel(writer, sheet_name="竞品价格", index=False)
44
45# 额外加一个汇总sheet
46
47    summary = pd.DataFrame({
48
49"指标": ["今日销售额(CNY)", "汇率USD/CNY", "竞品最低价"],
50
51"数值": [df_sales["amount_cny"].sum(), usd_to_cny, df_competitor["price"].min()]
52
53    })
54
55    summary.to_excel(writer, sheet_name="关键指标", index=False)
56
57
58print("综合报表生成完成")

场景6：定时刷新外部数据（结合第四篇）

你可以把上面的任意一个脚本放到定时任务里，让报表每天自动更新。

示例：每天9点自动从API抓取天气数据并写入Excel

 1import schedule
 2
 3import time
 4
 5import requests
 6
 7import pandas as pd
 8
 9from datetime import date
10
11
12deffetch_weather():
13
14    city = "Beijing"
15
16    api_key = "your_api_key"
17
18    url = f"http://api.openweathermap.org/data/2.5/weather?q={city}&appid={api_key}&units=metric"
19
20    resp = requests.get(url).json()
21
22
23
24    data = {
25
26"日期": [date.today()],
27
28"城市": [city],
29
30"温度": [resp["main"]["temp"]],
31
32"湿度": [resp["main"]["humidity"]],
33
34"天气": [resp["weather"][0]["description"]]
35
36    }
37
38    df = pd.DataFrame(data)
39
40
41
42# 追加到已有文件（如果文件存在）
43
44try:
45
46        existing = pd.read_excel("天气记录.xlsx")
47
48        df_new = pd.concat([existing, df], ignore_index=True)
49
50except FileNotFoundError:
51
52        df_new = df
53
54
55
56    df_new.to_excel("天气记录.xlsx", index=False)
57
58    print(f"{date.today()} 天气数据已保存")
59
60
61# 每天早上9点执行
62
63schedule.every().day.at("09:00").do(fetch_weather)
64
65
66whileTrue:
67
68    schedule.run_pending()
69
70    time.sleep(60)

常见问题与避坑指南

问题 原因 解决方法

read_html 抓不到数据 网页表格由JS动态渲染 改用Selenium或分析网络请求找到真实API

API请求返回401 缺少认证信息 检查是否需要API Key、Token或Bearer认证，在请求头中添加

数据库连接超时 网络问题或防火墙 检查IP白名单，使用engine.connect()后及时关闭

抓取速度太慢 单线程逐页请求 可用concurrent.futures多线程加速，但注意礼貌性等待

数据量太大导致Excel卡死 Excel最大行数约104万 改用CSV格式，或分多个Sheet存储，或使用数据库

网页反爬虫 频繁请求被封IP 设置time.sleep(random.uniform(1,3))，使用代理IP，更换User-Agent

写在最后

五篇文章，五个角度，一套完整的Python自动化Excel技能体系：

1. 基础入门：读写、样式、简单分析

2. 数据清洗：脏数据处理，让数据变干净

3. 自动化图表：让Excel自己画图

4. 批量与定时：让脚本替你跑腿

5. 外部数据源：打通网页、API、数据库

从今天起，任何重复的Excel工作，你都可以用Python来解决。

你可能会问：学这些要多久？

答案：从跑通第一个脚本开始，只需要10分钟。剩下的，在实践中不断积累。

如果你对某个场景有特定需求（比如抓取某类网站、连接某种数据库），欢迎在评论区留言，我会尽量补充。

感谢你一路跟完这个系列。希望你的工作效率，从此起飞。