如何使用Excel和Python从互联网上获取数据
一、构建测试用网站数据通过PythonFlaskWeb框架分别构建一个Web网站和一个WebAPI服务。1.构建Web网站新建一个名为“5-5-WebTable.py”的Python脚本，创建一个包含表格的简单网页。如果读者对构建方法不感兴趣，可跳过以下代码，直接执行脚本“5-5-WebTable.py”打开网站。（1）安装flask包。pipinstallflask（2）构建包含表格的网页。fromflaskimportFlaskapp=Flask(__name__)#创建FalskWeb应用实例#将路
一、构建测试用网站数据
通过Python Flask Web框架分别构建一个Web网站和一个Web API服务。
1.构建Web网站
新建一个名为“5-5-WebTable.py”的Python脚本，创建一个包含表格的简单网页。如果读者对构建方法不感兴趣，可跳过以下代码，直接执行脚本“5-5-WebTable.py”打开网站。
（1）安装flask包。
pip install flask
（2）构建包含表格的网页。
from flask import Flask
app = Flask(__name__)# 创建Falsk Web应用实例
# 将路由“/”映射到table_info函数，函数返回HTML代码
@app.route('/')
def table_info():
return """HTML表格实例，用于提供给Excel和Python读取用户信息表姓名性别年龄小米女22………."""
if __name__ == '__main__':
app.debug = True# 启用调试模式
app.run()# 运行，网站端口默认为5000通过命令“python ./5-5-WebTable.py”启动网站，然后在浏览器中输入http://127.0.0.1:5000/，出现如图1所示的网页内容。
图1 使用Flask构建的测试网站
2.构建Web API服务
新建一个名为“5-5-WebAPI.py”的Python脚本，使用flask_restplus包构建Web API服务。如果读者对构建方法不感兴趣，可跳过以下代码，直接执行脚本“5-5-WebAPI.py”打开Web API服务。
（1）安装flask_restplus包。
pip install flask-restplus
（2）导入必要的库与初始化应用对象。
from flask import Flask
# Api类是Web API应用的入口，需要用Flask应用程序初始化
from flask_restplus import Api
# Resource类是HTTP请求的资源的基类
from flask_restplus import Resource
# fields类用于定义数据的类型和格式
from flask_restplus import fields
app = Flask(__name__)# 创建Falsk Web应用实例
# 在flask应用的基础上构建flask_restplusApi对象
api = Api(app, version='1.0',
title='Excel集成Python数据分析-测试用WebAPI',
description='测试用WebAPI', )
# 使用namespace函数生成命名空间，用于为资源分组
ns = api.namespace('ExcelPythonTest', description='Excel与Python Web API测试')
# 使用api.model函数生成模型对象
todo = api.model('task_model', {
'id': fields.Integer(readonly=True,
description='ETL任务唯一标识'),
'task': fields.String(required=True,
description='ETL任务详情')
})（3）Web API数据操作类，包含增、删、改、查等方法。
class TodoDAO(object):
def __init__(self):
self.counter = 0
self.todos = []
def get(self, id):
for todo in self.todos:
if todo['id'] == id:
return todo
api.abort(404, "ETL任务 {} 不存在".format(id))
def create(self, data):
todo = data
todo['id'] = self.counter = self.counter + 1
self.todos.append(todo)
return todo
# 实例化数据操作，创建3条测试数据
DAO = TodoDAO()
DAO.create({'task': 'ETL-抽取数据操作'})
DAO.create({'task': 'ETL-数据清洗转换'})
DAO.create({'task': 'ETL-数据加载操作'})（4）构建Web API的路由映射。
HTTP资源请求类从Resource类继承，然后映射到不同的路由，同时指定可使用HTTP方法。
@ns.route('/')# 路由“/”对应的资源类为TodoList，可使用get方法和post方法进行请求
class TodoList(Resource):
@ns.doc('list_todos')# @doc装饰器对应API文档的信息
@ns.marshal_list_with(todo)# @marshal_xxx装饰器对模型数据进行格式转换与输出
def get(self):# 定义get方法获取所有的任务信息
return DAO.todos
@ns.doc('create_todo')
@ns.expect(todo)
@ns.marshal_with(todo, code=201)
def post(self):# 定义post方法获取所有的任务信息
return DAO.create(api.payload), 201
# 路由/对应的资源类为Todo，可使用get、delete、put方法进行请求
@ns.route('/')
@ns.response(404, '未发现相关ETL任务')
@ns.param('id', 'ETL任务ID号')
class Todo(Resource):
@ns.doc('get_todo')
@ns.marshal_with(todo)
def get(self, id):
return DAO.get(id)
@ns.doc('delete_todo')
@ns.response(204, 'ETL任务已经删除')
def delete(self, id):
DAO.delete(id)
return '', 204
@ns.expect(todo)
@ns.marshal_with(todo)
def put(self, id):
return DAO.update(id, api.payload)
if __name__ == '__main__':
app.run(debug=True, port=8000)# 启动Web API服务，端口为8000（4）开启Web API服务。
通过命令“python ./5-5-WebAPI.py”启动Web API服务，在浏览器中输入“http://127.0.0.1:8000/”将出现如图5-23所示的Web API服务请求方法列表。
图2 WebAPI服务请求方法列表
2、抓取用网页数据
Excel可以通过“数据”选项卡下的“自网站”功能抓取网页数据。Python可以使用 requests 库、Beautiful Soup包、Scrapy框架抓取网页数据。
1.通过Excel抓取
单击“数据”→“自其他源”→“自网站”功能。Excel可读取的网页数据有局限：动态网页数据无法自动识别，非表格数据无法自动识别。
（1）单击“数据”→“自其他源”→“自网站”功能。
（2）确保在5.5.1节中编写的Web网站已经开启。
（3）输入网站URL地址“http://127.0.0.1:5000/”
单击“高级”按钮可配置更详细的HTTP请求信息，然后单击“确定”按钮，如图3所示。
图3 配置要读取网站的URL
（4）在“导航器”窗口中选择导入数据。
如图4所示，Excel自动识别网页中的表格数据，选择表名后单击“加载”按钮即可。
图4 Excel自动识别网页中的表格数据
2.使用Python抓取
下面演示使用requests库抓取整个网页中的数据，然后使用Beautiful Soup解析网页。读者可参考本书代码素材文件“5-5-web.ipynb”进行学习。
（1）通过requests读取网页数据。
import requests #导入requests包 url ='http://127.0.0.1:5000/' strhtml= requests.get(url) #使用get方法请求网页数据
（2）通过Beautiful Soup解析网页。
from bs4 import BeautifulSoup
soup = BeautifulSoup(strhtml.text)# 将网页内容作为参数，创建soup对象
table = soup.find('table')# 查找网页中的table元素
table_body = table.find('tbody')# 查找table元素中的tbody元素
data = []
rows = table_body.find_all('tr')# 查找表中的所有tr元素
for row in rows:# 遍历数据
cols = row.find_all('td')
cols = [ele.text.strip() for ele in cols]
data.append([ele for ele in cols if ele])
# 结果输出：[[],
['小米', '女', '22'],['小明','男','23'],……3、调用Web API服务
Excel可以通过“数据”选项卡下的“自网站”功能调用Web API服务。Python可以使用 requests 库、Beautiful Soup包、Scrapy框架调用Web API获取数据。
1.使用Excel调用
（1）确保5.5.1节中编写的Web API服务已经开启。
（2）输入Web API方法对应的URL：http://127.0.0.1:8000/ExcelPythonTest/。
（3）处理返回的数据。
调用Web API服务后数据以JSON格式返回，按照5.4.3小节中介绍的方法处理JSON数据。
2.使用Python调用
使用requests库调用Web API方法，然后对返回的JSON数据进行处理，读者可参考本书代码素材文件“5-5-api.ipynb”进行学习。
import requests#导入requests包 url ='http://127.0.0.1:8000/ExcelPythonTest/' strhtml= requests.get(url)#使用get方法获取网页数据 import pandas as pd frame= pd.read_json(strhtml.text)#使用Pandas包中的read_json函数 print(frame) #结果输出： id task 0 1 ETL-抽取数据操作 1 2 ETL-数据清洗转换 2 3 ETL-数据加载操作
4、两种方法对比
表1所示为Excel和Python抓取互联网数据方法的对比。需要注意Excel从互联网抓取数据的功能并不完善。
表1 Excel和Python抓取互联网数据方法对比
Excel跨表引用数据，可以在目标单元格输入等号，再点击源工作表标签和需要引用的单元格，按Enter完成。公式通常是“工作表名!单元格地址”；需要向下复制时，还要检查相对引用与绝对引用是否符合需求。
Excel把文本转换为日期，可以选中日期列，进入“数据—分列”，保持每条日期不被拆开，在最后一步选择“日期”并指定YMD、DMY或MDY顺序。完成解析后，再设置日期显示格式，并抽查年月日，不能只改显示格式就认为转换成功。
Excel批量填充序号，可以先输入1和2，选中这两格，再拖动右下角填充柄向下延续。需要一次生成较多序号时，进入“开始—填充—序列”，选择列、等差序列、步长1，并设置终止值即可。
Excel生成数据透视表，先选中带有字段标题的明细区域，点击“插入—数据透视表”，确认数据源并选择放置位置。创建后把分类字段拖到“行”或“列”，把金额、数量拖到“值”，再检查求和方式与汇总结果。
Excel合并多个结构相同的表格，可以在Windows桌面版进入“数据—获取数据—从文件—从文件夹”，选择源文件夹，再用Power Query合并并转换数据。若已经把各张表建立为查询，则使用“追加查询”把记录纵向汇总，检查字段与类型后加载结果。
Excel保护工作表，可以先解除需要填写区域的锁定，再点击“审阅—保护工作表”，设置取消保护密码和允许操作并确认。这样可以保留录入区域，同时限制公式、表头等单元格被随意修改；需要调整设置时，先取消工作表保护。
Excel每页打印表头，可以进入“页面布局—打印标题”，在“工作表”页签的“顶端标题行”中选择表头所在的整行，例如第一行填写$1:$1。保存后打开打印预览，并翻到第二页确认表头已重复出现。
Excel删除重复项，先选中完整数据区域，点击“数据—删除重复项”，确认是否包含标题，再勾选用于判断重复的列并确定。操作会删除所选区域内的重复记录，建议先备份，并用业务编号、日期等字段确定真正的重复标准。
Excel设置条件格式，先选中数据区域，再点击“开始—条件格式—突出显示单元格规则”，选择大于、小于或重复值等条件，填写判断值并设置样式。以后需要修改范围或取消规则，可进入“管理规则”检查条件与“应用于”区域。
Excel拆分单元格内容，可以选中需要处理的一列，进入“数据—分列”，选择“分隔符号”，再勾选逗号、空格或自定义分隔符。确认预览、各列数据格式和目标位置后完成，操作前要给右侧结果预留空列。
火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力，依托字节自研豆包多模态大模型，凭借火山方舟一站式平台，提供模型精调、推理部署、智能应用搭建等全链路服务，结合高性能AI算力，助力各行业快速落地大模型数字化与智能化应用。
