Spider與OpenPyXL的結合

• OpenPyXL的API文檔

1.OpenPyXL基礎操作

• 引入Workbook這個類,然後調用

from openpyxl import Workbooknwb = Workbook()n

• 通過openpyxl.workbook.Workbook.active()調用得到正在運行的工作表

ws = wb.activen

該函數調用工作表的索引(_active_sheet_index)，默認是0。除非你修改了這個值，否則你使用該函數一直是在對第一張工作表進行操作。

• 使用openpyxl.workbook.Workbook.create_sheet()新建一張表

ws1 = wb.create_sheet() #默認插在工作簿末尾n# ornws2 = wb.create_sheet(0) # 插入在工作簿的第一個位置n

• 在創建工作表的時候系統自動命名。他們按照序列依次命名 (Sheet, Sheet1, Sheet2, ...)。你可以通過調用下面的屬性修改工作表的名稱：

ws.title = "New Title"n

• 標籤欄的背景色默認為白色。你可以通過提供一個RRGGBB顏色碼改變標籤欄的字體顏色

ws.sheet_properties.tabColor = "1072BA"n

• 一旦你獲取工作表的名字，你可以通過workbook的key或者 openpyxl.workbook.Workbook.get_sheet_by_name() 方法得到該工作表

ws3 = wb["New Title"]nws4 = wb.get_sheet_by_name("New Title")nws is ws3 is ws4nTruen

• 你可以通過openpyxl.workbook.Workbook.get_sheet_names() 方法得到工作簿的所有工作表。

print(wb.get_sheet_names())n[Sheet2, New Title, Sheet1]n

• 你也可以循環得到所有的工作表

for sheet in wb:n print(sheet.title)n

操作數據

• 使用一個單元格

我們開始修改工作表中單元格的內容

單元格可以直接根據他們的索引直接獲得

>>> c = ws[A4]n

通過上述的語句，將返回在A4處的單元格，如果不存在將在A4新建一個。 單元格的值也可以直接賦值

>>> ws[A4] = 4n

還提供 openpyxl.worksheet.Worksheet.cell() 方法獲取單元格

>>> c = ws.cell(A4)n

也可以根據行列值獲取單元格

>>> d = ws.cell(row = 4, column = 2)n

注意：當一個工作表被創建是，其中不包含單元格。只有當單元格被獲取是才被創建。這種方式我們不會創建我們從不會使用的單元格，從而減少了內存消耗。

>>> for i in range(1,101):n for j in range(1,101):n ws.cell(row = i, column = j)n

上述代碼將會在內存中創建100*100個單元格。

使用多個單元格

使用切片獲取多個單元格

>>> cell_range = ws[A1:C2]n

使用openpyxl.worksheet.Worksheet.iter_rows() 方法獲得多個單元格

>>> tuple(ws.iter_rows(A1:C2))n((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),n(<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))n>>> for row in ws.iter_rows(A1:C2):n for cell in row:n print celln<Cell Sheet1.A1>n<Cell Sheet1.B1>n<Cell Sheet1.C1>n<Cell Sheet1.A2>n<Cell Sheet1.B2>n<Cell Sheet1.C2>n

如果你需要迭代文件中所有的行或者列，你可以使用

openpyxl.worksheet.Worksheet.rows()

>>> ws = wb.activen>>> ws[C9] = hello worldn>>> ws.rowsn((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),n(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),n(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),n(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),n(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),n(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),n(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),n(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),n(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))n

或者使用openpyxl.worksheet.Worksheet.columns()方法

>>> ws.columnsn((<Cell Sheet.A1>,n<Cell Sheet.A2>,n<Cell Sheet.A3>,n<Cell Sheet.A4>,n<Cell Sheet.A5>,n<Cell Sheet.A6>,n...n<Cell Sheet.B7>,n<Cell Sheet.B8>,n<Cell Sheet.B9>),n(<Cell Sheet.C1>,n<Cell Sheet.C2>,n<Cell Sheet.C3>,n<Cell Sheet.C4>,n<Cell Sheet.C5>,n<Cell Sheet.C6>,n<Cell Sheet.C7>,n<Cell Sheet.C8>,n<Cell Sheet.C9>))n

• 數據存儲

一旦我們有一個openpyxl.cell.Cell，我們可以直接為該單元格賦值

>>> c.value = hello, worldn>>> print(c.value)nhello, worldn>>> d.value = 3.14n>>> print(d.value)n3.14n你也可以使用Python中的其他類型和格式n>>> wb = Workbook(guess_types=True)n>>> c.value = 12%n>>> print(c.value)n0.12n>>> import datetimen>>> d.value = datetime.datetime.now()n>>> print d.valuendatetime.datetime(2010, 9, 10, 22, 25, 18)n>>> c.value = 31.50n>>> print(c.value)n31.5n

保存到文件

保存工作簿最簡單最安全的方式是使用openpyxl.workbook.Workbook的openpyxl.workbook.Workbook.save() 方法

>>> wb = Workbook()n>>> wb.save(balances.xlsx)n

！特別警告：這個操作將會在沒有認識提示的情況下用現在寫的內容，覆蓋掉原文件中的所有內容

你也可以 as_template=True，將文件保存稱為一個模板

>>> wb = load_workbook(document.xlsx)n>>> wb.save(document_template.xltx, as_template=True)n

如果as_template=False(默認)，則將文件或模板保存為文件

>>> wb = load_workbook(document_template.xltx)n>>> wb.save(document.xlsx, as_template=False)n>>> wb = load_workbook(document.xlsx)n>>> wb.save(new_document.xlsx, as_template=False)n

警告：在保存文件到文件模板中的時候你應該監控數據的屬性和文件擴展名，反之亦然；否則，你得到的工作簿可能無法打開。

>>> wb = load_workbook(document.xlsx)n>>> # Need to save with the extension *.xlsxn>>> wb.save(new_document.xlsm)n>>> # MS Excel cant open the documentn>>>n>>> # orn>>>n>>> # Need specify attribute keep_vba=Truen>>> wb = load_workbook(document.xlsm)n>>> wb.save(new_document.xlsm)n>>> # MS Excel cant open the documentn>>>n>>> # orn>>>n>>> wb = load_workbook(document.xltm, keep_vba=True)n>>> # If us need template document, then we need specify extension as *.xltm.n>>> # If us need document, then we need specify attribute as_template=False.n>>> wb.save(new_document.xlsm, as_template=True)n>>> # MS Excel cant open the documentn

• 從文件中導入

和寫入文件的方式相同，你可以引入openpyxl.load_workbook()來打開一個已經存在的工作簿

>>> from openpyxl import load_workbookn>>> wb2 = load_workbook(test.xlsx)n>>> print wb2.get_sheet_names()n[Sheet2, New Title, Sheet1]n

2.爬蟲與OpenPyXL的結合（爬取前程無憂網站招聘數據，存儲Excel表格中）

這裡採用的python的bs4庫，代碼基於python2.7

分析

第一步，分析網頁結構，找到我們需要數據的塊標籤

從我們找到的塊中篩選我們想要的數據

分析網頁url，通過拼接url，得到更多的數據

代碼如下：

#-*- coding:utf-8 -*-nimport requests,renn#引入解碼器nimport codecsnfrom bs4 import BeautifulSoupnn#從OpenPyXl引入Workbook這個類nfrom openpyxl import Workbooknn#調用nExcel = Workbook()nfileName = 51job.xlsxnn#調用得到正在運行的工作表nexcel = Excel.activenn#工作表的名字nexcel.title = 51jobnn#定義爬蟲的方法ndef spider(job,page):nn my_headers = {n user-agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 UBrowser/6.1.3397.16 Safari/537.36n }n workName = []n company = []n address = []n pay = []n print page,type(page)n page = int(page)+1nnn try:n for i in range(1,page):n url = http://search.51job.com/list/020000,000000,0000,00,9,99,+job+,2, + str(i) + .htmln #獲取url地址內容，偽造請求頭nn data = requests.get(url,headers=my_headers).contentn print(得到網頁html數據)nn #設置html解析器n soup = BeautifulSoup(data,html.parser)n soup1 = soup.find(div,class_=dw_table)n content = soup1.find_all(div,el)nn for i in content[1:]:n # print(i)n workna = i.find(p, attrs={class: t1})n work_name = workna.find(span).get_text().strip().replace(/n/r, ) # 職位名字n compa = i.find(span, attrs={class: t2}).get_text() # 公司名n addre = i.find(span, attrs={class: t3}).get_text() # 工作地點n payy = i.find(span, attrs={class: t4}).get_text() # 薪資n print(work_name, compa, addre, payy)nn if payy:n pay.append(payy)n else:n pay.append(面議)nn workName.append(work_name)n company.append(compa)n address.append(addre)nn for (w,c,a,p) in zip(workName,company,address,pay):n col_A = A%s%(workName.index(w) + 1)n col_B = B%s%(workName.index(w) + 1)n col_C = C%s%(workName.index(w) + 1)n col_D = D%s%(workName.index(w) + 1)n excel[col_A] = wn excel[col_B] = cn excel[col_C] = an excel[col_D] = pn #保存到excel文件n print(開始保存數據)n Excel.save(filename=fileName)n except Exception as e:n print(e)nif __name__ == __main__:n job = raw_input(請輸入你要爬取的崗位名稱：)n page = raw_input(請輸入你要爬取的頁碼數量：)n spider(job,page)n

運行程序，拿到數據

可以看到數據已經成功保存到Excel表格中

使用同樣的分析方法爬取智聯招聘崗位信息

代碼如下：

#-*- coding:utf-8 -*-nimport requests,renimport codecs #引入解碼器nimport randomnnfrom bs4 import BeautifulSoupnfrom openpyxl import Workbook #從openpyxl引入Workbooknfrom fake_useragent import UserAgent #引入userAgentnnExcel = Workbook() #調用nfileName = zl.xlsxnnexcel = Excel.active #調用運行的工作表nexcel.title = (zj)nnn#定義爬蟲的方法ndef spider(job,page,add):n ua = UserAgent()n my_headers = {n user-agent:ua.randomn }nn name = [] #定義崗位名稱n percent = [] #定義反饋率n company = [] #定義公司名稱n salary = [] #定義職位月薪n position = [] #定義工作地點n page = int(page)+1n try:n for i in range(1,page):n url = http://sou.zhaopin.com/jobs/searchresult.ashx?jl=+ add +&kw= + job + &sm=0&p= + str(i)n # data = {n # jl:上海,n # kw:job,n # p:in # }n data = requests.get(url,headers=my_headers).contentnn #設置htmL解析器n soup = BeautifulSoup(data,html.parser)n soup1 = soup.find(div,class_=newlist_list_content)n content = soup1.find_all(table,class_="newlist")nn for i in content[1:]:n print in na = i.find(td,attrs={class:zwmc}).find(a).get_text().strip().replace(/n/r, )#崗位名n perc = i.find(td,attrs={class:fk_lv}).find(span).get_text() #反饋率n comp = i.find(td,attrs={class:gsmc}).find(a).get_text() #公司名n sala = i.find(td,attrs={class:zwyx}).get_text() #職位月薪n positi = i.find(td,attrs={class:gzdd}).get_text() #工作地點nn print na,perc,comp,sala,positinn if perc:n percent.append(perc)n else:n percent.append(空)nn name.append(na)n company.append(comp)n salary.append(sala)n position.append(positi)n for (n, p, c, s, p) in zip(name, percent, company, salary, position):n col_A = A%s % (name.index(n) + 1)n col_B = B%s % (name.index(n) + 1)n col_C = C%s % (name.index(n) + 1)n col_D = D%s % (name.index(n) + 1)n col_E = E%s % (name.index(n) + 1)n excel[col_A] = nn excel[col_B] = pn excel[col_C] = cn excel[col_D] = sn excel[col_E] = pnn # 保存到excel文件n print(開始保存數據)n Excel.save(filename=fileName)n except Exception as e:n print(e)nnif __name__ == __main__:n print ◆我是一隻小爬蟲◆n job = raw_input(請輸入您想要的到的崗位名稱：)n page = raw_input(請輸入您想要得到的頁碼數量：)n add = raw_input(請輸入您想要哪個城市的招聘信息：)n spider(job,page,add)n

Excel數據展示

作者：_知幾 Python愛好者社區專欄作者，請勿轉載，謝謝。

簡書主頁：http://www.jianshu.com/u/9dad6621d2a0

博客專欄：_知幾的博客專欄

配套視頻教程：Python3爬蟲三大案例實戰分享：貓眼電影、今日頭條街拍美圖、淘寶美食 Python3爬蟲三大案例實戰分享

公眾號：Python愛好者社區（微信ID：python_shequ），關注，查看更多連載內容。

推薦閱讀：