版权声明:本文为作者原创文章，可以随意转载，但必须在明确位置标明出处！！！
最近知乎上有一篇文章《月入五万的西二旗人教你如何活得像月薪五千》说的是北京西二旗程序员月入五万却过着月薪五千一样的生活，相比较其它行业，程序员的工资相对来说算是比较高的，程序员给人的形象永远都是眼镜、没刮过的胡须、乱糟糟的头发、还有个必备的电脑包（毕竟这可是吃饭的家伙），就是这种形象的人拿着月薪五万却过着月薪五千的生活，不过这是有原因的，996的工作时间（朝九晚九+周六），公司发的工装和冲锋衣可以一年四季穿到头，全年都不带买衣服的，唯一的爱好可能就是打打游戏，买买电子产品了，显示器一定要大，而且不止一个。所以如果你是妹子，那么找个程序员也是不错的，安全、放心。当然本章的重点不是讲怎么月入五万，本章的重点是讲怎么将数据写入到Excel表格中，方便我们做简单的统计。
上一章讲到了将如何将数据写入到文本文件里，但文本文件操作比较简单，如果我们要对数据做一些统计之类的操作那么文本文件就提供不了这些支持了，所以本章我们将数据写到Excel表格里做一些简单的统计。
openpyxl库
读写Excel需要用到第三方库，这里我们选择openpyxl库，这个库可以操作新版本的Excel，xlrt、xlwt两个库是用来读写老版本的Execl的，也就是扩展名是*.xls
安装openpyxl库
在ubuntu命令行终端输入以下命令就可以安装openpydl库了，如果没有报错则证明你安装好了。
sudo pip install openpyxl
校验openpyxl是否安装
在终端执行python--》回车--》import openpyxl--》回车。如果没有抛出异常证明该库已经安装好了。
workbook、worksheet
要读写Excel我们首先要弄清楚两个概念，
- workbook: 工作簿，它的意思是我们打开一个Excel文档后，整个Excel文档被称作为一个工作簿。
- worksheet: 工作表，如果我们新键一个Excel文件，然后打开它，我们可以看到底部有一个Sheet的选项，也就是我们的当前表格，也被称之为活动表格。
所以我们操作Excel表格的时候一定是要先有workbook后才能去操作worksheet，这点概念大家要弄清楚。
在内存中操作Excel
有了上面工作簿和工作表的概念，那么我们在内存中的操作步骤也要遵循上面的先有工作簿，再有工作表的规定。所以我们第一步是先创建一个工作簿，其次是获取一个活动的工作表，最后才是去操作工作表中的元素，代码如下
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
#给A1单元赋值
ws['A1'] = 'test'
#保存Excel
wb.save('sample.xlsx')
执行结果
从结果可以看到A1单元确实被赋值为test了。Sheet及时我们的活动表格。
如果你想修改Sheet的名称，或者想创建其它Sheet，下面这么做就可以了。
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
#给A1单元赋值
ws['A1'] = 'test'
ws.title = 'test'
ws1 = wb.create_sheet("test1")
ws2 = wb.create_sheet("test2")
#保存Excel
wb.save('sample.xlsx')
执行结果
从结果中可以看到我们将Sheet该成了test，并且新创建了test1,test2表格页签。
访问元素
这里需要注意的是，当我们在内存中创建一个worksheet的时候，它是不包含cells的，也就是不包含创建了单元格，只有当我们第一次去访问它的时候它才回去创建单元格，就像上面的代码ws['A1'] = 'test'，当给A1单元格赋值的时候才回去创建单元格A1，这样做的目的相信大家都已经看出来了，节约资源，提高效率。cells操作还可以通过行、列来操作，如ws.cell(row=4, column=2, value=10)，就是给第4行第二列赋值为10，各位可是执行运行试试，看看结果是不是这样的。
多行多列范围访问
- 切片式范围访问： 切片是python里经常用到的操作，openpyxl库对范围访问也可以切片，语法如下
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
cell_range = ws['A1' : 'C4']
print(cell_range)
访问A1到C4范围的所有cell，它的输出结果如下：
((<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>))
结果返回的是一个元组，元组里每一项又包含了一个元组，该元组表示一行，一共4行。
- 按列访问： 按列访问的语法如下
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
col_range = ws['C:D']
print(col_range)
访问第C列到第D列的cell，执行结果如下
((<Cell 'Sheet'.C1>,), (<Cell 'Sheet'.D1>,))
从这里我们可以看出在内存中创建一个工作表时，只有访问cell的时候cell才回被创建，当我们的程序做出一下改变是，它的结果会是什么呢。
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
ws['A4'] = 'SFS'
col_range = ws['C:D']
print(col_range)
执行程序后的结果变成了：
((<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.C4>),
(<Cell 'Sheet'.D1>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.D4>))
- 按行访问：按行访问跟按列访问是一样的，具体的执行结果是怎么杨的，大家可以自己去尝试一下，语法如下：
row5= ws[5]
row_range = ws[5:10]
迭代器访问行列区域
除了切片方式，openpyxl也提供了迭代器的方式来访问行列，如下按行优先访问2 * 3列范围的元素
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
执行结果如下：
<Cell 'Sheet'.A1>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.C2>
按列优先访问如下：
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
for row in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
执行结果如下：
<Cell 'Sheet'.A1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.C2>
如果你需要迭代所有的行列那你可以像这样做：
from openpyxl import Workbook
#创建一个工作簿
wb = Workbook()
#活取一个活动的工作表
ws = wb.active
#按行优先迭代
ws['C9'] = 'test'
print(tuple(ws.rows))
#按列优先迭代
print(tuple(ws.columns))
执行结果到底怎样，各位自行探索一下。
加载一个已存在的Excel文档
加载一个已存在的Excel文档非常简单，加载后返回一个workbook对象，那么我们就可以像上面介绍的操作去处理加载后的文档了。
from openpyxl import load_workbook
wb2 = load_workbook('sample.xlsx')
print wb2.get_sheet_names()
图表
图表有2D和3D图表，下面以一个2D的图表作为例子，这个例子是openpyxl手册上的例子
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
wb.save("area.xlsx")
执行后的结果：
openpyxl提供了非常丰富的图表支持，像条形图、柱状图、饼图、曲线图等等，有兴趣的同学可以到https://openpyxl.readthedocs.io/en/default/查看。当然想合并单元格、公式、高亮、文本格式等等用法都可以到上面提供的网址查看。
用图表来表示哪个用户获得的好笑数最高
下面我们把爬到的数据写入到Excel表格中，并且用图表来先生哪个用户的好笑数最高，代码如下，源码放在https://github.com/Gavinxyj/Python/tree/master/python_study/Scrapy/modules欢迎大家fork、Stars
# Scheduler.py
from urllib import request
from urllib import error
import re
import os
from Excel import ExcelOper
class Scheduler(object):
def __init__(self, url, user_agent):
self.url = url
self.headers = {'User-Agent': user_agent}
self.excel_obj = ExcelOper()
def read_html(self, codec):
'''[read_html]
[读取html页面内容]
Arguments:
url {[string]} -- [url地址]
headers {[dict]} -- [用户代理，这里是一个字典类型]
codec {[string]} -- [编码方式]
Returns:
[string] -- [页面内容]
'''
# 构建一个请求对象
try:
req = request.Request(self.url, headers=self.headers)
# 打开一个请求
response = request.urlopen(req)
# 读取服务器返回的页面数据内容
content = response.read().decode(codec)
return content
except error.URLError as e:
print(e.reason)
return None
def match_element(self, content, pattern):
'''[match_element]
[匹配元素]
Arguments:
content {[string]} -- [文本内容]
pattern {[object]} -- [匹配模式]
Returns:
[list] -- [匹配到的元素]
'''
# 匹配所有用户信息
userinfos = re.findall(pattern, content)
return userinfos
def write_file(self, content):
with open('./qiubai.txt', 'a+') as fp:
fp.write(content + '\n')
def get_content(self):
content = self.read_html('utf-8')
pattern = re.compile(r'<div class="article block untagged mb15[\s\S]*?class="stats-vote".*?</div>', re.S)
if content:
userinfos = self.match_element(content, pattern)
infos = []
if userinfos:
pattern = re.compile(r'<a href="(.*?)".*?<h2>(.*?)</h2>.*?<div class="content">(.*?)</div>.*?<i class="number">(.*?)</i>', re.S)
picture = re.compile(r'<div class="thumb">.*?src="(.*?)"', re.S)
for userinfo in userinfos:
item = self.match_element(userinfo, pattern)
pictures = self.match_element(userinfo, picture)
try:
if item:
userid, name, content, num = item[0]
# 去掉换行符，<span></span>，<br/>符号
userid = re.sub(r'\n|<span>|</span>|<br/>', '', userid)
name = re.sub(r'\n|<span>|</span>|<br/>', '', name)
content = re.sub(r'\n|<span>|</span>|<br/>|\x01', '', content)
if pictures:
path = './users/'
if not os.path.exists(path):
os.makedirs(path)
request.urlretrieve('http:' + pictures[0], path + os.path.basename(pictures[0]))
infos.append((userid, name, int(num), content, pictures[0]))
#print((userid, name, num, content, pictures[0]))
#self.write_file(userid + '\t' + name + '\t' + content + '\t' + num + '\t' + pictures[0])
else:
#print((userid, name, content, num))
infos.append((userid, name, int(num), content))
#self.write_file(userid + '\t' + name + '\t' + content + '\t' + num)
except Exception as e:
print(e)
self.excel_obj.write_excel(infos)
if __name__ == '__main__':
url = 'https://www.qiushibaike.com'
user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36'
handle = Scheduler(url, user_agent)
handle.get_content()
# Excel.py
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart,
Reference,
Series,
)
class ExcelOper(object):
def __init__(self):
self.wb = Workbook()
def write_excel(self, infos):
try:
ws = self.wb.active
# 增加表头
ws.append(['id', 'username', 'funny_num', 'context', 'url'])
for row_index,row_value in enumerate(infos, 2):
for col_index, col_value in enumerate(row_value, 1):
ws.cell(row=row_index, column=col_index, value=col_value)
self.draw_chart(ws)
self.wb.save('qiubai.xlsx')
except Exception as e:
raise e
def draw_chart(self, ws):
chart = AreaChart()
chart.title = "Joker Chart"
chart.style = 13
chart.x_axis.title = 'User'
chart.y_axis.title = 'Funny Num'
cats = Reference(ws, min_col=2, min_row=1, max_row=25)
data = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=25)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A30")
def read_excel(self):
pass
整个程序增加了一个Excel.py文件，主要用来主要用来操作excel表格，里面使用到的方法在本章都已经讲过，更多的图表制作有兴趣的同学可以自己去研究研究，最后奉上执行结果。
PS: 如果你是“表”姐，需要经常在网上收集内容，那么找个程序员男朋友吧，他会帮你弄得妥妥的，再也不用加班，不用熬夜了，哈哈哈...
