[52]spider与OpenPyXL的结合

@[TOC]

OpenPyXL的API文档

1.OpenPyXL基础操作

• 引入Workbook这个类,然后调用

from openpyxl import Workbook
wb = Workbook()

• 通过openpyxl.workbook.Workbook.active()调用得到正在运行的工作表

ws = wb.active

该函数调用工作表的索引(_active_sheet_index)，默认是0。除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。

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

ws1 = wb.create_sheet() #默认插在工作簿末尾
# or
ws2 = wb.create_sheet(0) # 插入在工作簿的第一个位置

• 在创建工作表的时候系统自动命名。他们按照序列依次命名 (Sheet, Sheet1, Sheet2, ...)。你可以通过调用下面的属性修改工作表的名称：

ws.title = "New Title"

• 标签栏的背景色默认为白色。你可以通过提供一个RRGGBB颜色码改变标签栏的字体颜色

ws.sheet_properties.tabColor = "1072BA"

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

ws3 = wb["New Title"]
ws4 = wb.get_sheet_by_name("New Title")
ws is ws3 is ws4
True

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

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

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

for sheet in wb:
       print(sheet.title)

2.操作数据

• 使用一个单元格

我们开始修改工作表中单元格的内容 单元格可以直接根据他们的索引直接获得

>>> c = ws['A4']

通过上述的语句，将返回在A4处的单元格，如果不存在将在A4新建一个。 单元格的值也可以直接赋值

>>> ws['A4'] = 4

还提供 openpyxl.worksheet.Worksheet.cell() 方法获取单元格

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

也可以根据行列值获取单元格

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

注意：当一个工作表被创建时，其中不包含单元格。只有当单元格被获取时才被创建。这种方式我们不会创建我们从不会使用的单元格，从而减少了内存消耗。 警告：由于上述特性，你如果遍历了单元格而非想要使用它们也将会在内存当中创建。比如下面：

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

上述代码将会在内存中创建100*100个单元格。 当然，这里也有方法来清理这些不想要的单元格，在后续我们将会介绍。

3.使用多个单元格

使用切片获取多个单元格

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

使用openpyxl.worksheet.Worksheet.iter_rows() 方法获得多个单元格

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

如果你需要迭代文件中所有的行或者列，你可以使用 openpyxl.worksheet.Worksheet.rows()

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

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

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

• 数据存储

一旦我们有一个openpyxl.cell.Cell，我们可以直接为该单元格赋值

>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'
>>> d.value = 3.14
>>> print(d.value)
3.14
你也可以使用Python中的其他类型和格式
>>> wb = Workbook(guess_types=True)
>>> c.value = '12%'
>>> print(c.value)
0.12
>>> import datetime
>>> d.value = datetime.datetime.now()
>>> print d.value
datetime.datetime(2010, 9, 10, 22, 25, 18)
>>> c.value = '31.50'
>>> print(c.value)
31.5

4.保存到文件

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

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

！特别警告：这个操作将会在没有认识提示的情况下用现在写的内容，覆盖掉原文件中的所有内容 你也可以 as_template=True，将文件保存称为一个模板

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

如果as_template=False(默认)，则将文件或模板保存为文件

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

警告：在保存文件到文件模板中的时候你应该监控数据的属性和文件扩展名，反之亦然；否则，你得到的工作簿可能无法打开。 比如下面的：

>>> wb = load_workbook('document.xlsx')
>>> # Need to save with the extension *.xlsx
>>> wb.save('new_document.xlsm')
>>> # MS Excel can't open the document
>>>
>>> # or
>>>
>>> # Need specify attribute keep_vba=True
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>> # MS Excel can't open the document
>>>
>>> # or
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> # If us need template document, then we need specify extension as *.xltm.
>>> # If us need document, then we need specify attribute as_template=False.
>>> wb.save('new_document.xlsm', as_template=True)
>>> # MS Excel can't open the document

• 从文件中导入

和写入文件的方式相同，你可以引入openpyxl.load_workbook()来打开一个已经存在的工作簿

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

5.爬虫与OpenPyXL的结合（爬取前程无忧网站招聘数据，存储Excel表格中）

这里采用的python的bs4库，代码基于python2.7

分析

• 代码如下：

#-*- coding:utf-8 -*-
import requests,re

#引入解码器
import codecs
from bs4 import BeautifulSoup

#从OpenPyXl引入Workbook这个类
from openpyxl import Workbook

#调用
Excel = Workbook()
fileName = '51job.xlsx'

#调用得到正在运行的工作表
excel = Excel.active

#工作表的名字
excel.title = '51job'

#定义爬虫的方法
def spider(job,page):

    my_headers = {
        '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.36'
    }
    workName = []
    company = []
    address = []
    pay = []
    print page,type(page)
    page = int(page)+1


    try:
        for i in range(1,page):
            url = 'http://search.51job.com/list/020000,000000,0000,00,9,99,'+job+',2,' + str(i) + '.html'
            #获取url地址内容，伪造请求头

            data = requests.get(url,headers=my_headers).content
            print('得到网页html数据')

            #设置html解析器
            soup = BeautifulSoup(data,'html.parser')
            soup1 = soup.find('div',class_='dw_table')
            content = soup1.find_all('div','el')

            for i in content[1:]:
                # print(i)
                workna = i.find('p', attrs={'class': 't1'})
                work_name = workna.find('span').get_text().strip().replace('/n/r', '')  # 职位名字
                compa = i.find('span', attrs={'class': 't2'}).get_text()  # 公司名
                addre = i.find('span', attrs={'class': 't3'}).get_text()  # 工作地点
                payy = i.find('span', attrs={'class': 't4'}).get_text()  # 薪资
                print(work_name, compa, addre, payy)

                if payy:
                    pay.append(payy)
                else:
                    pay.append('面议')

                workName.append(work_name)
                company.append(compa)
                address.append(addre)

        for (w,c,a,p) in zip(workName,company,address,pay):
            col_A = 'A%s'%(workName.index(w) + 1)
            col_B = 'B%s'%(workName.index(w) + 1)
            col_C = 'C%s'%(workName.index(w) + 1)
            col_D = 'D%s'%(workName.index(w) + 1)
            excel[col_A] = w
            excel[col_B] = c
            excel[col_C] = a
            excel[col_D] = p
            #保存到excel文件
            print('开始保存数据')
            Excel.save(filename=fileName)
    except Exception as e:
        print(e)
if __name__ == '__main__':
    job = raw_input('请输入你要爬取的岗位名称：')
    page = raw_input('请输入你要爬取的页码数量：')
    spider(job,page)

• python3版本

import codecs
import requests
from bs4 import BeautifulSoup
from openpyxl import Workbook
import re
wb = Workbook()
filename = '上海python职位.xlsx'
ws1 = wb.active
ws1.title = '51job上海python岗位'
def main():
    job = input('请输入您要搜索的岗位：\n')
    page = int(input('请输入您要的页数\n'))
    las = '.html?'
    post1 = []
    company1 = []
    address1 = []
    salary1 = []
    time1 = []
    headers = {
        '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.36'

    }
    for i in range(1,page+1):
        url = 'http://search.51job.com/list/020000,000000,0000,00,9,99,' + job + ',2,' + str(i) + las
        req = requests.get(url,headers=headers)
        req.encoding = 'gb18030'
        soup = BeautifulSoup(req.text,'html.parser')
        page = soup.find('div',class_="dw_table")
        # post1 = []
        # company1 = []
        # address1 = []
        # salary1 = []
        # time1 = []

        for i in page.find_all('div',class_='el')[1:] :
            post = i.find('p',class_="t1").get_text().strip().replace('/n/r','')
            # post = post.encode("UTF-8")
            company = i.find('span',class_='t2').get_text()
            address = i.find('span',class_='t3').get_text()
            salary = i.find('span',class_='t4').get_text()
            time = i.find('span',class_='t5').get_text()
            post1.append(post)
            company1.append(company)
            address1.append(address)
            if salary:
                salary1.append(salary)
            else:
                salary1.append('面议')
            time1.append(time)

    for (p,c,a,s,t) in zip(post1,company1,address1,salary1,time1):
        col_A = 'A%s'%(post1.index(p)+1)
        col_B = 'B%s' % (post1.index(p) + 1)
        col_C = 'C%s' % (post1.index(p) + 1)
        col_D = 'D%s' % (post1.index(p) + 1)
        col_E = 'E%s' % (post1.index(p) + 1)
        ws1[col_A]=p
        ws1[col_B]=c
        ws1[col_C]=a
        ws1[col_D]=s
        ws1[col_E]=t
        wb.save(filename=filename)
if __name__ == '__main__':
    main()