【Day 17】- 手动更新汇率太麻烦了! 汇率爬虫搭配 OpenPyXL 做到自动读取&更新汇率!

前情提要

前一篇介绍了 openpyxl 这项可以操作 excel 的工具。

开始之前

本篇实战【Day 15】- 汇率什麽的。爬! (实战汇率爬虫 on chrome) 所撰写的汇率爬虫使用openpyxl 将汇率更新在 excel。

预期结果

可以简单地将想要爬取的汇率写在 excel ，执行程序後自动将爬取到的汇率更新在 excel 上。

实作

首先，先从 excel 下手，先将目标 excel 读入，并读取目前 row 的最大值。

import openpyxl

workbook = openpyxl.load_workbook('汇率及时更新.xlsx')
sheet = workbook['及时汇率']
mxR = sheet.max_row
print(mxR)
#Output : 4

接下来，读取 row = 2~mxR, column = 1 的单元格，并将其加入一串列。

import openpyxl

workbook = openpyxl.load_workbook('汇率及时更新.xlsx')
sheet = workbook['及时汇率']
mxR = sheet.max_row
cointypes = []
for r in range(2, mxR+1):
    cointype = sheet.cell(row=r, column=1).value
    cointypes.append(cointype)

print(cointypes)
#Output: ['美金', '日币', '欧元']

读取表单部分完工後，我们将汇率爬虫与此结合，并将汇率爬虫包为一个函式，以币种作为输入、汇率作为回传值。

import openpyxl
import requests
from bs4 import BeautifulSoup

def crawler(cointype):
    url = f'https://www.google.com/search?q={cointype}'
    user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.61 Safari/537.36'
    resp = requests.get(url, headers={
        'user-agent': user_agent
    })
    soup = BeautifulSoup(resp.text, 'html5lib')
    ele = soup.find('span', class_='DFlfde SwHCTb')

    if ele:
        return ele.text
    else:
        return None

workbook = openpyxl.load_workbook('汇率及时更新.xlsx')
sheet = workbook['及时汇率']
mxR = sheet.max_row
cointypes = []
for r in range(2, mxR+1):
    cointype = sheet.cell(row=r, column=1).value
    cointypes.append(cointype)

接下来，我们简单写个 for-loop 将 cointypes 内的每个元素都呼叫一次汇率爬虫，并将结果用 openpyxl 存於 excel，最後储存 excel 即可。

记得执行该程序的过程，需先将目标 excel 档案先关闭，否则会出现错误。

import openpyxl
import requests
from bs4 import BeautifulSoup

def crawler(cointype):
    url = f'https://www.google.com/search?q={cointype}'
    user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.61 Safari/537.36'
    resp = requests.get(url, headers={
        'user-agent': user_agent
    })
    soup = BeautifulSoup(resp.text, 'html5lib')
    ele = soup.find('span', class_='DFlfde SwHCTb')

    if ele:
        return ele.text
    else:
        return None

workbook = openpyxl.load_workbook('汇率及时更新.xlsx')
sheet = workbook['及时汇率']
mxR = sheet.max_row
cointypes = []
for r in range(2, mxR+1):
    cointype = sheet.cell(row=r, column=1).value
    cointypes.append(cointype)

nowrow = 2
for cointype in cointypes:
    result = crawler(cointype)
    sheet.cell(row=nowrow, column=2).value = result
    nowrow += 1

workbook.save('汇率及时更新.xlsx')