使用python统计提交文件并生成报表

本文程序的功能是使用python统计文件名，并生成excel报表。

因为近期学校要求使用ftp上交毕业设计论文，因此统计学生的上交情况十分麻烦。故写了一个小程序来自动统计。
他的原理是提取文件名里的学号，与excel表名单进行比对，最后将结果输出到excel表中。

# --coding:utf-8--
import os
import xlrd
import xlwt
import time

def get_file_list(dir):
    filelist = []
    rootdir = os.path.abspath(dir)
    for parent,dirnames,filenames in os.walk(rootdir):
        for filename in filenames:
            filelist.append(filename)
    return filelist


def get_excel_value(filename,sheetname):
    table = xlrd.open_workbook(filename).sheet_by_name(sheetname)
    valuelist = []
    for rownum in range(1, table.nrows):
        row = table.row_values(rownum)
        valuelist.append(row)
    return valuelist


def total_file(file_name,student_info):
    total_file_list = []
    for student in range(len(student_info)):
        count=0
        temp = student_info[student]
        for file in file_name:
            if file.find(student_info[student][1])!=-1:
                count=count+1
        if count!=0:
            temp.append(count)
        else:
            temp.append("未提交")
        total_file_list.append(temp)
    return total_file_list

def style_title():
    style = xlwt.XFStyle()
    #字体样式
    fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    fnt.name = u'华文仿宋'  # 设置其字体为微软雅黑
    fnt.colour_index = 0  # 设置其字体颜色
    fnt.bold = True
    fnt.height = 0x015A
    style.font = fnt  # 将赋值好的模式参数导入Style
    #单元格对齐方式
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # May be: HORZ_GENERAL,HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED,HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: VERT_TOP,VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    style.alignment = alignment  # Add Alignment to Style
    #边框
    borders = xlwt.Borders()  # Create Borders
    borders.left = xlwt.Borders.NO_LINE  # May be: NO_LINE, THIN, MEDIUM,DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED,THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through0x0D.
    borders.right = xlwt.Borders.NO_LINE
    borders.top = xlwt.Borders.NO_LINE
    borders.bottom = xlwt.Borders.NO_LINE
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style.borders=borders
    return style


def style_time():
    style = xlwt.XFStyle()
    #字体样式
    fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    fnt.name = u'宋体'  # 设置其字体
    fnt.colour_index = 0  # 设置其字体颜色
    fnt.bold = False
    fnt.height = 0x00CA
    style.font = fnt  # 将赋值好的模式参数导入Style
    #单元格对齐方式
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_LEFT  # May be: HORZ_GENERAL,HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED,HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: VERT_TOP,VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    style.alignment = alignment  # Add Alignment to Style
    #边框
    borders = xlwt.Borders()  # Create Borders
    borders.left = xlwt.Borders.THIN  # May be: NO_LINE, THIN, MEDIUM,DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED,THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through0x0D.
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style.borders=borders
    return style


def style_body():
    style = xlwt.XFStyle()
    #字体样式
    #fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    #fnt.name = u'宋体'  # 设置其字体
    #fnt.colour_index = 0  # 设置其字体颜色
    #fnt.bold = False
    #fnt.height = 0x00DA
    #style.font = fnt  # 将赋值好的模式参数导入Style
    #单元格对齐方式
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # May be: HORZ_GENERAL,HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED,HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: VERT_TOP,VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    style.alignment = alignment  # Add Alignment to Style
    #边框
    borders = xlwt.Borders()  # Create Borders
    borders.left = xlwt.Borders.THIN  # May be: NO_LINE, THIN, MEDIUM,DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED,THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through0x0D.
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style.borders=borders
    return style

def write_excel(filename,sheetname,list,style):
    if os.path.exists(filename):
        os.remove(filename)
    file = xlwt.Workbook(encoding='utf-8')
    table = file.add_sheet(sheetname,cell_overwrite_ok=True)


    col_number = len(list[0])
    table.write_merge(0,0,0,col_number-1,"毕业论文上交统计表",style_title())   #第一行：表头
    nowtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
    table.write_merge(1,1,0,col_number - 1,"输出时间："+nowtime,style_time())


    for row, list_data in enumerate(list):
        for col, data in enumerate(list_data):
            table.write(row + 2, col, data,style) #
            table.col(col).width = 4444     # 3333 = 1 inch
        file.save(filename)

print('正在统计文件...')
file_name = get_file_list('E:\\BYSJ-2018')
print('正在统计提交情况...')
student_info = get_excel_value('E:\\BYSJ-2018\\stud_info.xls','1')
list=total_file(file_name,student_info)
print('正在生成报表...')
list.insert(0,['序号','学号','专业班级','姓名','指导教师姓名','提交份数'])
write_excel('E:\\BYSJ-2018\\result.xls','统计结果',list,style_body())