python使用xlsxwriter打造excel周报日周的统计图表

最近在做关于分布式爬虫和数据抽取计算的两个方面的日志分析，统计的结果也比较简单，把收集到的日志存入到hadoop，然后用python streaming实现mapreduce，把结果存入到mongodb里面。只是python这家伙实在慢的可以，直接改用 pig 来实现日志的解析，速度明显要好点。说正题，现在数据的展现用的是 highcharts来前端的现实，实时的数据是放在Influxdb里面，Influxdb是个含有时间序的数据库。然而我们还是有个需求是，可以做一定程度的日报和周报。把最大的几个网上抓取数目给抽取出来。原本我想的有些复杂，打算制作一个干净的图表页面，然后底层调用phantomjs进行截图，然后发邮件。

原文地址是，http://xiaorui.cc

但是问题来了…. … … 人家说，这高端是高端，但是你截图是好，各种的html的样式随你的变。但是你tmd不能让人复制数据呀。这是多大的仇多大的怨呀… … 顾问说，我原本想把数据复制出来，然后放到其他的数据平台上做运算，好家伙，你这一下子来了个图片，你逗比呐。让哥哥们一个个的复制呀。这尼玛，这被喷的… …

我和你多大的仇，多大的怨 … … … 好吧，改用xlsxwriter重新制作图表。 xlsxwriter 的图表和数据表还是很简单的，这个模块帮你做了很好用的封装。

import xlsxwriter

workbook = xlsxwriter.Workbook('chart_column.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})

# 这是个数据table的列
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
    [2, 3, 4, 5, 6, 7],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
]

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

############################################
#创建一个图表，类型是column
chart1 = workbook.add_chart({'type': 'column'})

# 配置series,这个和前面wordsheet是有关系的。 
chart1.add_series({
    'name':       '=Sheet1! $B$ 1',
    'categories': '=Sheet1! $A$ 2: $A$ 7',
    'values':     '=Sheet1! $B$ 2: $B$ 7',
})

# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
    'name':       ['Sheet1', 0, 2],
    'categories': ['Sheet1', 1, 0, 6, 0],
    'values':     ['Sheet1', 1, 2, 6, 2],
})

# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})

# Set an Excel chart style.
chart1.set_style(11)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})

#######################################################################
#
# Create a stacked chart sub-type.
#
chart2 = workbook.add_chart({'type': 'column', 'subtype': 'stacked'})

# Configure the first series.
chart2.add_series({
    'name':       '=Sheet1! $B$ 1',
    'categories': '=Sheet1! $A$ 2: $A$ 7',
    'values':     '=Sheet1! $B$ 2: $B$ 7',
})

# Configure second series.
chart2.add_series({
    'name':       '=Sheet1! $C$ 1',
    'categories': '=Sheet1! $A$ 2: $A$ 7',
    'values':     '=Sheet1! $C$ 2: $C$ 7',
})

# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Stacked Chart'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})

# Set an Excel chart style.
chart2.set_style(12)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})

#######################################################################
#
# Create a percentage stacked chart sub-type.
#
chart3 = workbook.add_chart({'type': 'column', 'subtype': 'percent_stacked'})

# Configure the first series.
chart3.add_series({
    'name':       '=Sheet1! $B$ 1',
    'categories': '=Sheet1! $A$ 2: $A$ 7',
    'values':     '=Sheet1! $B$ 2: $B$ 7',
})

# Configure second series.
chart3.add_series({
    'name':       '=Sheet1! $C$ 1',
    'categories': '=Sheet1! $A$ 2: $A$ 7',
    'values':     '=Sheet1! $C$ 2: $C$ 7',
})

# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Percent Stacked Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})

# Set an Excel chart style.
chart3.set_style(13)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})

workbook.close()

有些数据是需要做平均值计算的，看了下官方实例对于AVERAGE的处理函数，还算可以。然后又从某个论坛那边，搜到了刘天斯发表的一段代码，然后改了改就直接用了。自己这是需要关心series值。 excel是有很多计算的函数计算的，其实我是懒得在接口端做计算，就直接调用xlsxwriter计算了。

嗯，还有一个中文的问题… …. 居然还犯这么初级的问题… 编码没有指明的问题，需要指明sys.setdefaultencoding，或则会是自己申明decode

    self._xml_si_element(string, attributes)
  File "/Library/Python/2.7/site-packages/xlsxwriter/xmlwriter.py", line 122, in _xml_si_element
    self.fh.write("""<si><t%s>%s</t></si>""" % (attr, string))
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/codecs.py", line 691, in write
    return self.writer.write(data)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/codecs.py", line 351, in write
    data, consumed = self.encode(object, self.errors)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe5 in position 7: ordinal not in range(128)

大家一定要把xlsxwriter对象给关闭了，不然会报错….

Exception Exception: Exception('Exception caught in workbook destructor. Explicit close() may be required for workbook.',) in <bound method Workbook.__del__ of <xlsxwriter.workbook.Workbook object at 0x10053d4d0>> ignored

下面是个干净的例子，我把从tornado 接口获取数据，到发邮件的逻辑给去掉了。。。

#coding: utf-8
import xlsxwriter
import random

def get_num():
    return random.randrange(0, 201, 2)

workbook = xlsxwriter.Workbook('analyse_spider.xlsx')    #创建一个Excel文件
worksheet = workbook.add_worksheet()    #创建一个工作表对象
chart = workbook.add_chart({'type': 'column'})    #创建一个图表对象
#定义数据表头列表
title = [u'业务名称',u'星期一',u'星期二',u'星期三',u'星期四',u'星期五',u'星期六',u'星期日',u'平均流量']
buname= [u'时光网',u'汽车之家',u'weixin.com',u'163.com',u'baidu.com']    #定义频道名称
#定义5频道一周7天流量数据列表
data = []
for i in range(5):
    tmp = []
    for j in range(7):
        tmp.append(get_num())
    data.append(tmp)
    
format=workbook.add_format()    #定义format格式对象
format.set_border(1)    #定义format对象单元格边框加粗(1像素)的格式
 
format_title=workbook.add_format()    #定义format_title格式对象
format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
format_title.set_bg_color('#cccccc')   #定义format_title对象单元格背景颜色为
                                       #'#cccccc'的格式
format_title.set_align('center')    #定义format_title对象单元格居中对齐的格式
format_title.set_bold()    #定义format_title对象单元格内容加粗的格式
 
format_ave=workbook.add_format()    #定义format_ave格式对象
format_ave.set_border(1)    #定义format_ave对象单元格边框加粗(1像素)的格式
format_ave.set_num_format('0.00')   #定义format_ave对象单元格数字类别显示格式
 
#下面分别以行或列写入方式将标题、业务名称、流量数据写入起初单元格，同时引用不同格式对象
worksheet.write_row('A1',title,format_title)  
worksheet.write_column('A2', buname,format)
worksheet.write_row('B2', data[0],format)
worksheet.write_row('B3', data[1],format)
worksheet.write_row('B4', data[2],format)
worksheet.write_row('B5', data[3],format)
worksheet.write_row('B6', data[4],format)
 
#定义图表数据系列函数
def chart_series(cur_row):
    worksheet.write_formula('I'+cur_row, \
     '=AVERAGE(B'+cur_row+':H'+cur_row+')',format_ave)    #计算（AVERAGE函数）频
                                                          #道周平均流量
    chart.add_series({
        'categories': '=Sheet1! $B$ 1: $H$ 1',    #将“星期一至星期日”作为图表数据标签(X轴)
        'values':     '=Sheet1! $B$ '+cur_row+': $H$ '+cur_row,    #频道一周所有数据作
                                                               #为数据区域
        'line':       {'color': 'black'},    #线条颜色定义为black(黑色)
        'name': '=Sheet1! $A$ '+cur_row,    #引用业务名称为图例项
    })
 
for row in range(2, 7):    #数据域以第2～6行进行图表数据系列函数调用
    chart_series(str(row))
 
chart.set_size({'width': 577, 'height': 287})    #设置图表大小
chart.set_title ({'name': u'爬虫分析'})    #设置图表（上方）大标题
chart.set_y_axis({'name': 'count'})    #设置y轴（左侧）小标题
 
worksheet.insert_chart('A8', chart)    #在A8单元格插入图表
workbook.close()    #关闭Excel文档

大家觉得文章对你有些作用！如果想赏钱，可以用微信扫描下面的二维码，感谢!
另外再次标注博客原地址 xiaorui.cc

python使用xlsxwriter打造excel周报日周的统计图表

5 Responses

发表评论取消回复

5 Responses

发表评论 取消回复

发表评论取消回复