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!B1',
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!B2:B7',
})

# 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!B1',
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!B2:B7',
})

# Configure second series.
chart2.add_series({
    'name':       '=Sheet1!C1',
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!C2:C7',
})

# 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!B1',
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!B2:B7',
})

# Configure second series.
chart3.add_series({
    'name':       '=Sheet1!C1',
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!C2:C7',
})

# 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!B1:H1',    #将“星期一至星期日”作为图表数据标签(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

5 Responses

  1. 偃月 2016年5月17日 / 下午12:04

    这个设置x轴,我没有设置成功

  2. 耶耶耶 2016年5月4日 / 上午1:18

    好赞

  3. 大狗 2016年4月15日 / 上午8:13

    这个刘天斯的书上有吧

  4. 小王 2016年1月14日 / 下午4:27

    怎么把刻度显示成万,比如30000-》3万

  5. 尼玛 2014年12月14日 / 下午2:42

    这个有用

发表评论

邮箱地址不会被公开。 必填项已用*标注