上一篇博客当中我预留了很多问题没有处理，写出的代码给人感觉烂糟的，自己都不忍心看，经过同事的帮忙和自己一阵捣腾（各种优化），终于实现了所有需求。而且更重要的是我又换回了xlsxwriter模块，上次我说到这个模块不能修改数据，只适合一次性写入，这个说法没错，现在我们的逻辑思维改变一下，首先我们将产生的数据保存在一个文本中，然后创建一个新的execl文件和图表，接着读这个文本文件，再把文本中的所有数据再写入到excel表中，最后再创建图表。以前总是想着从文本里读一行然后在execl中写一行，现在是读所有行然后一次性写入。具体代码如下：
点击(此处)折叠或打开
- #!/usr/bin/python
- #coding=utf-8
- import os,sys
importtime- import pycurl
- import xlsxwriter
- URL="http://www.baidu.com"
- c = pycurl.Curl()
- c.setopt(pycurl.URL, URL)
- c.setopt(pycurl.CONNECTTIMEOUT, 10)
- c.setopt(pycurl.TIMEOUT, 10)
- c.setopt(pycurl.NOPROGRESS, 1)
- c.setopt(pycurl.FORBID_REUSE, 1)
- c.setopt(pycurl.MAXREDIRS, 1)
- c.setopt(pycurl.DNS_CACHE_TIMEOUT, 30)
- indexfile = open(os.path.dirname(os.path.realpath(__file__))+"/content.txt","wb")
- try:
- c.perform()
- except Exception,e:
- print "connection error:"+str(e)
- indexfile.close()
- NAMELOOKUP_TIME = c.getinfo(c.NAMELOOKUP_TIME)
- CONNECT_TIME = c.getinfo(c.CONNECT_TIME)
- TOTAL_TIME = c.getinfo(c.TOTAL_TIME)
- HTTP_CODE = c.getinfo(c.HTTP_CODE)
- SIZE_DOWNLOAD = c.getinfo(c.SIZE_DOWNLOAD)
- HEADER_SIZE = c.getinfo(c.HEADER_SIZE)
- SPEED_DOWNLOAD=c.getinfo(c.SPEED_DOWNLOAD)
- print "HTTP状态码: %s" %(HTTP_CODE)
- print "DNS解析时间: %.2f ms" %(NAMELOOKUP_TIME*1000)
- print "建立连接时间: %.2f ms" %(CONNECT_TIME*1000)
- print "传输结束总时间: %.2f ms" %(TOTAL_TIME*1000)
- print "下载数据包大小: %d bytes/s" %(SIZE_DOWNLOAD)
- print "HTTP头部大小: %d byte" %(HEADER_SIZE)
- print "平均下载速度: %d bytes/s" %(SPEED_DOWNLOAD)
- indexfile.close()
- c.close()
ifnotos.path.exists('chart1.txt'):file('chart1.txt','w')print'Successfully created directory','chart1.txt'- f = file('chart1.txt','a')
- f.close()
- format=workbook.add_format()
- format.set_border(1)
- format_title=workbook.add_format()
- format_title.set_border(1)
- format_title.set_bg_color('#cccccc')
- format_title.set_align('center')
- format_title.set_bold()
- worksheet.write_row(0, 0,title,format_title)
- f = open('chart1.txt','r')
- line = 1
- for i in f:
- head = [line]
- lineList = i.split(',')
- lineList = map(lambda i2:int(float(i2.replace("\n", ''))), lineList)
- lineList = head + lineList
- worksheet.write_row(line, 0, lineList, format)
- line += 1
- average = [u'平均值', '=AVERAGE(B2:B' + str((line - 1)) +')', '=AVERAGE(C2:C' + str((line - 1)) +')', '=AVERAGE(D2:D' + str((line - 1)) +')', '=AVERAGE(E2:E' + str((line - 1)) +')', '=AVERAGE(F2:F' + str((line - 1)) +')', '=AVERAGE(G2:G' + str((line - 1)) +')', '=AVERAGE(H2:H' + str((line - 1)) +')']
- worksheet.write_row(line, 0, average, format)
- f.close()
- def chart_series(cur_row, line):
- chart.add_series({
- 'categories': '=Sheet1!$B$1:$H$1',
- 'values': '=Sheet1!$B$'+cur_row+':$H$'+cur_row,
- 'line': {'color': 'black'},
- 'name': '=Sheet1!$B',
- })
- for row in range(2, line + 1):
- chart_series(str(row), line)
- chart.set_size({'width':876,'height':287})
- worksheet.insert_chart(line + 2, 0, chart)
- workbook.close()
实现的效果图如下，我们只要将此脚本放到cron计划任务中去执行就行啦！平均值永远在脚本最后一次运行数据的下面，图表永远在平均值下面的一格处，也就是最后一次运行数据的下面两格处。
