某大数据_学生在宿舍的进出时间记录

#coding:utf-8import sysimport xlrdimport timeimport threadingfrom Queue import Queuefrom datetime import datetimefrom operator import itemgetterfrom xlrd import xldate_as_tupledef dealExcle(filename):    myqueue = Queue()    X_thousand = []    all_content = []    # deal with excel    data = xlrd.open_workbook(filename)    table = data.sheets()[0]    rows = table.nrows    cols = table.ncols    for i in range(rows):        # 去除 excle 第一行              if i == 0:            continue        # 每 10000 个数据 取一个 列表        if i%10000 == 0:            X_thousand = sorted(X_thousand,key=itemgetter(0))            all_content.append(X_thousand)            X_thousand = []        # 读取 excel 每一行 因为时间直接读取, 有问题 会用浮点型显示        row_content = []        for j in range(cols):            ctype = table.cell(i, j).ctype            cell = table.cell_value(i, j)            if ctype == 3:                date = datetime(*xldate_as_tuple(cell, 0))                cell = date.strftime('%Y/%d/%m %H:%M:%S')            row_content.append(cell)        X_thousand.append(row_content)    # 将所有数据导入 队列    for x in all_content:        myqueue.put(x)    return myqueuedef subTime(d2,d1):    temp = time.strptime(d1, "%Y/%d/%m %H:%M:%S")    timestampd1 = time.mktime(temp)    temp = time.strptime(d2, "%Y/%d/%m %H:%M:%S")    timestampd2 = time.mktime(temp)    if timestampd2 > timestampd1:        return timestampd2 - timestampd1    else :        return timestampd1 - timestampd2# okdef addTime(X_thounsand,Startnum,Endnum):    num = 0     for x in xrange(Startnum+1,Endnum+1):        if X_thounsand[x-1][2] == 1 and X_thounsand[x][2] == 0:            num = num + subTime(X_thounsand[x][1],X_thounsand[x-1][1])    return num# 处理 X_thousands 的数据 并输出def run(myqueue):    while myqueue.qsize() > 0:        X_thounsand = myqueue.get()        flag = 1        for x in xrange(1,len(X_thounsand)):            if X_thounsand[x-1][0] == X_thounsand[x][0]:                flag = flag + 1            else :                Endnum = x-1                    Startnum = Endnum - flag + 1                # 如果 Startnum == Endnum 则说明只有一个数据                if Startnum != Endnum :                    num = addTime(X_thounsand,Startnum,Endnum)                    print "-"*20                    print "学号 : %d | %5s 分钟" %  ( int(X_thounsand[Endnum][0]) ,str(num/60))                    flag = 1if __name__ == '__main__':    if len(sys.argv) != 2:        print "Usage : python run.py xxx.xlsx"        exit()    filename = sys.argv[1]    myqueue = dealExcle(filename)    threads = []    for i in range(30):        t = threading.Thread(target=run,args=(myqueue,))        t.start()        threads.append(t)    for t in threads:        t.join()# dt = "2016-05-05 20:28:54"# timeArray = time.strptime(dt, "%Y-%m-%d %H:%M:%S")# timestamp = time.mktime(timeArray)# from operator import itemgetter# sorted(a,key=itemgetter(0))# >>> from datetime import datetime# >>> d1 = datetime(2017,11,14,11,30,20)# >>> d2 = datetime(2017,11,14,17,20,11)# >>> (d2-d1).seconds# 20991# mysql> create table student(#     -> id char(10),#     -> time char(25),#     -> turn char(5));