成绩整合脚本——emerge.py

by AquarHEAD

某班任终于把成绩单给我了，不过气人的是人已经把前三次的成绩整合做完了，还理直气壮地说一个一个复制粘贴的啊，就用了一节课。。。

这篇日志先把代码贴出来，刚刚在我的台式、爸的VAIO和我以前的那个破台式上玩三角进攻，终于是把发布成exe文件搞定了——py2exe神器一枚——折腾死我了。。。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

# coding=utf-8
# Originally developed by AquarHEAD, licensed under Creative Commons BY-NC-SA 3.0, visit http://creativecommons.org/licenses/by-nc-sa/3.0/
# any questions, ideas, please contact me via AquarHEAD (At) gmail.com
# Thanks for using
 
from xlrd import *
from xlwt import *
from tempfile import *
import glob
 
names = []
cols = [u'姓名']
infiles = glob.glob('*.xls')
outfile = Workbook()
 
def find_all_rows():
    for num_inf in range(len(infiles)):
        now_inf = open_workbook(infiles[num_inf])
        now_ins = now_inf.sheet_by_index(0)
        now_row = 1
        for now_col in range(now_ins.ncols):
            rowname = now_ins.cell_value(now_row, now_col)
            if (cols.count(rowname) == 0):
                cols.append(rowname)
 
def find_all_names():
    for num_inf in range(len(infiles)):
        now_inf = open_workbook(infiles[num_inf])
        now_ins = now_inf.sheet_by_index(0)
        for now_col in range(now_ins.ncols):
            if (now_ins.cell_value(1, now_col) == cols[0]):
                break
        for now_row in range(2,now_ins.nrows):
            now_name = now_ins.cell_value(now_row, now_col)
            if (names.count(now_name) == 0):
                names.append(now_name)
 
def add_data():
    now_outrow = 0
    for num_name in range(len(names)):
        now_name = names[num_name]
        outsheet.write(now_outrow, 0, now_name)
        for each in range(1, len(cols)):
            outsheet.write(now_outrow, each, cols[each])
        for num_inf in range(len(infiles)):
            now_inf = open_workbook(infiles[num_inf])
            now_ins = now_inf.sheet_by_index(0)
            now_outrow += 1
            outsheet.write(now_outrow, 0, now_ins.cell_value(0, 0))
            now_inrow = 1
            for name_col in range(now_ins.ncols):
                if (now_ins.cell_value(now_inrow, name_col) == cols[0]):
                    break
            now_incol = name_col
            for now_inrow in range(2, now_ins.nrows):
                if (now_ins.cell_value(now_inrow, now_incol) == now_name):
                    break
            if (now_ins.cell_value(now_inrow, now_incol) == now_name):
                for now_incol in range(now_ins.ncols):
                    colname = now_ins.cell_value(1, now_incol)
                    cellvalue = now_ins.cell_value(now_inrow, now_incol)
                    if (colname != cols[0]):
                        outsheet.write(now_outrow, cols.index(colname), cellvalue)
        now_outrow += 2
 
 
find_all_rows()
find_all_names()        
 
outfile.add_sheet('Analysis')
outsheet = outfile.get_sheet(0)
 
add_data()
 
outfile.save(u'个人成绩分析.xls')
outfile.save(TemporaryFile())

P.S.还是不很习惯行尾没有分号的脚本语言。
另，第一个函数的名字写错了，应该改成find_all_cols，呵呵。。。后面的调用也要改过来，写的时候2了一下，行和列分不清了，后来改的不彻底。。。