写入excel

from datetime import datetime
import xlwt

style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
    num_format_str='#,##0.00')
#style1 = xlwt.easyxf(num_format_str='D-MMM-YY')

wb = xlwt.Workbook()
ws = wb.add_sheet('Server')

ws.write(0, 0, URL, style0)
ws.write(0, 1, Server, style0)


wb.save('example.xls')

查询excel并统计

import numpy as np  
import matplotlib.pyplot as plt  
import xlrd  

#打开一个workbook  
workbook = xlrd.open_workbook('example.xls')  

#抓取所有sheet页的名称  
worksheets = workbook.sheet_names()  
print('worksheets is %s' %worksheets)  

#定位到mySheet  
mySheet = workbook.sheet_by_name(u'Server')  

#get datas  
pressure = mySheet.col_values(0)  
#time = mySheet.col(1)  
#time = [x.value for x in time]  


#drop the 1st line of the data, which is the name of the data.  
pressure.pop(0)  
#time.pop(0)  

#declare a figure object to plot  
fig = plt.figure(1)  

#plot pressure  
plt.plot(pressure)  

plt.title('Barometer')   
plt.ylabel('Pa')   
#plt.xticks(range(len(time)),time)   
plt.show()  

读取行和列

import xlrd
worksheet = xlrd.open_workbook(u'example.xls')
#sheet_names= worksheet.sheet_names()
sheet = worksheet.sheet_by_name('Server')
for sheet_name in sheet_names:
    sheet2 = worksheet.sheet_by_name(sheet_name)
    print sheet_name 
    ##rows = sheet2.row_values(3) 
    #cols = sheet.col_values(1) # 获取第二列内容
    ##print rows
    #print cols

#import requests

#s = requests.get('http://sdfy.shunde.gov.cn/')
#print s.status_code
#print s.content

import pymongo
import urllib2
import random
def get_user_agent():
    user_agents = [
        "Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)",
        "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; AcooBrowser; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
        "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Acoo Browser; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)",
        "Mozilla/4.0 (compatible; MSIE 7.0; AOL 9.5; AOLBuild 4337.35; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
        "Mozilla/5.0 (Windows; U; MSIE 9.0; Windows NT 9.0; en-US)",
        "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 2.0.50727; Media Center PC 6.0)",
        "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322)",
        "Mozilla/4.0 (compatible; MSIE 7.0b; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN) AppleWebKit/523.15 (KHTML, like Gecko, Safari/419.3) Arora/0.3 (Change: 287 c9dfb30)",
        "Mozilla/5.0 (X11; U; Linux; en-US) AppleWebKit/527+ (KHTML, like Gecko, Safari/419.3) Arora/0.6",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2pre) Gecko/20070215 K-Ninja/2.1.1",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9) Gecko/20080705 Firefox/3.0 Kapiko/3.0",
        "Mozilla/5.0 (X11; Linux i686; U;) Gecko/20070322 Kazehakase/0.4.5",
        "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.8) Gecko Fedora/1.9.0.8-1.fc10 Kazehakase/0.5.6",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.20 (KHTML, like Gecko) Chrome/19.0.1036.7 Safari/535.20",
        "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; fr) Presto/2.9.168 Version/11.52",]
    return random.choice(user_agents)

def get_header():
    header={
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8',
        'Accept-Encoding': 'gzip, deflate, sdch',
        'Accept-Language': 'zh-CN,zh;q=0.8',
        'Connection': 'keep-alive',
        'User-Agent': get_user_agent()
        } 
    return header

import requests
import time
import urllib2
import threading
import re

import xlwt

add_time = time.strftime('%Y-%m-%d %H-%M-%S',time.localtime(time.time()))

import pymongo  

connection=pymongo.MongoClient('127.0.0.1',27017) 
db=connection.wangzuxian
collection=db.Server
#----------------------------------------------------------------------
def error_write(url, error):
    """"""
    f = open('error_log.txt', 'a')
    log_info = add_time + ':' + url + '---' + str(error)
    f.writelines('%s \n' %log_info)
    f.close()

#----------------------------------------------------------------------
def url_headers(url):
    """"""
    try:
        resp = requests.get(url, headers = get_header(), timeout = 10)
        #requests.adapters.DEFAULT_RETRIES = 10
        for k in resp.headers.keys():
            if k.upper() == 'SERVER':
                header_server = resp.headers[k].upper()
                if re.search('iis/6.0'.upper(), header_server):
                    short_server = 'IIS/6.0'
                elif re.search('iis/7.0'.upper(), header_server):
                    short_server = 'IIS/7.0'  
                elif re.search('iis/7.5'.upper(), header_server):
                    short_server = 'IIS/7.5'                  
                elif re.search('iis/8.0'.upper(), header_server):
                    short_server = 'IIS/8.0'   
                elif re.search('iis/8.5'.upper(), header_server):
                    short_server = 'IIS/8.5'   
                elif re.search('iis'.upper(), header_server):
                    short_server = 'IIS'                
                elif re.search('apache'.upper(), header_server):
                    short_server = 'Apache' 
                elif re.search('nginx'.upper(), header_server):
                    short_server = 'Nginx' 
                elif re.search('vWebServer'.upper(), header_server):
                    short_server = 'vWebServer' 
                elif re.search('openresty'.upper(), header_server):
                    short_server = 'OpebResty' 
                elif re.search('tengine'.upper(), header_server):
                    short_server = 'Tengine'  
                elif re.search('apusic'.upper(), header_server):
                    short_server = 'APUSIC'                  
                elif re.search('marco'.upper(), header_server):
                    short_server = 'Marco'  
                elif re.search('twebap'.upper(), header_server):
                    short_server = 'TWebAP'                
                else:
                    short_server = 'Others'
                print k + ':' + resp.headers[k] + '--' + short_server + '--' + url
                collection.update({"URL":url},
                                  {"$set":{'Server' : resp.headers[k], 'short_server' : short_server}},
                                  upsert = True)                
                #return url, resp.headers[k]
        

    except Exception,e:
        error_write(url, str(e))
        #print str(e)
        pass
 
 
#----------------------------------------------------------------------
def test1():
    """"""
    with open('kehu_ok.txt') as f:
        urls = f.readlines()
        for url in urls:
            try:
                url_headers(url.strip('\n').strip('\r'))
            except Exception,e:
                print url
for i in range(100):
    t = threading.Thread(target= test1())
    t.setDaemon(True)
    t.start()
                
    
#----------------------------------------------------------------------
def Start():
    """"""
    wb = xlwt.Workbook()
    ws = wb.add_sheet('Server', cell_overwrite_ok= True) 
    
    with open('10.txt') as f:
        urls = f.readlines()
        for url in urls:
            try:
            
                target_url, target_Server = url_headers(url.strip('\n').strip('\r'))
            except Exception,e:
                pass
            style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
                num_format_str='#,##0.00')
            
            ws.write(0, 0, 'URL' )
            ws.write(0, 1, 'Server')
            
            ws.write(urls.index(url) + 1, 0, target_url)
            ws.write(urls.index(url) + 1, 1, target_Server)
            
    wb.save('example.xls')
            
            
#Start()

#import urllib2

#resp = urllib2.Request('http://changning.sh.cn',headers= get_header())
#r = urllib2.urlopen(resp)
#print r.headers



import xlrd
worksheet = xlrd.open_workbook(u'example.xls')

sheet = worksheet.sheet_by_name('Server')
cols = sheet.col_values(1)
print cols
print len(cols)

import re
import numpy as np
import matplotlib.pyplot as plt

nginx_num = 0
iis_num = 0
apache_num = 0
VWebserver_num = 0
openresty_num = 0
unknow_num = 0


for i in cols:
    if re.search('nginx'.upper(), i.upper()):
        nginx_num  += 1
    elif re.search('iis'.upper(), i.upper()):
        iis_num += 1
    elif re.search('apache'.upper(), i.upper()):
        apache_num += 1
    elif re.search('vwebserver'.upper(), i.upper()):
        VWebserver_num += 1
    elif re.search('openresty'.upper(), i.upper()):
        openresty_num += 1
    else :
        unknow_num += 1
        
        
X=['nginx','iis','apache','VWebserver','openresty','unknow'] 
#X = [1,2,3,4,5,6]
Y=[nginx_num, iis_num, apache_num, VWebserver_num, openresty_num, unknow_num]  
#try:
    #fig = plt.figure()  
    #plt.bar(X,Y,0.4,color="green")  
    #plt.xlabel("X-axis")  
    #plt.ylabel("Y-axis")  
    #plt.title("bar chart")
#except Exception,e:
    #print str(e)
        
def draw_bar(labels,quants):  
    width = 0.4  
    ind = np.linspace(0.5,9.5,6)  
    # make a square figure  
    fig = plt.figure(1)  
    ax  = fig.add_subplot(111)  
    # Bar Plot  
    ax.bar(ind-width/2,quants,width,color='green')  
    # Set the ticks on x-axis  
    ax.set_xticks(ind)  
    ax.set_xticklabels(labels)  
    # labels  
    ax.set_xlabel('Server')  
    ax.set_ylabel('Num')  
    # title  
    ax.set_title('Server percent', bbox={'facecolor':'0.8', 'pad':5})  
    plt.grid(True)  
    plt.show()  
    plt.savefig("bar.jpg")  
    plt.close()    
#draw_bar(X,Y)  


#plt.show()    
#plt.savefig("barChart.jpg") 
        
        '''