程序简述:用python对excel作业评分文件按班级学号进行统计
开发环境:PyCharm Community Edition
使用模块:
import os
import time
import xlrd
import xlwt
from xlutils.copy import copy
读取文件夹内所有excel表的内容(学号、姓名、分数):
代码如下:
for root, dirs, files in os.walk("{}\\excel\\".format(path)):
for i in range(len(files)):
open_file = xlrd.open_workbook("{}\\excel\\".format(path) + files[i])
sheet = open_file.sheet_by_name("Sheet1")
rows = sheet.nrows
student_score = []
for j in range(rows):
num = sheet.cell_value(j, 0)
student_id = student_id + [num]
name = sheet.cell_value(j, 1)
student_name = student_name + [name]
if sheet.cell_value(j, 5) == "NULL":
score = 0
else:
score = sheet.cell_value(j, 5)
student_score = student_score + [score]
写入excel表中的内容(学号、姓名、分数、总分):
代码如下:
write_file = xlrd.open_workbook("{}\\result\\score.xls".format(path))
write_file_1 = copy(write_file)
write_sheet_1 = write_file_1.get_sheet(0)
for k in range(len(student_score)):
write_sheet_1.write(k, 0, student_id[k])
write_sheet_1.write(k, 1, student_name[k])
write_sheet_1.write(k, i + 2, student_score[k])
write_sheet_1.write(0, 8, "总分")
write_sheet_1.write(k, 8, xlwt.Formula("SUM(C" + str(k + 1) + ":H" + str(k + 1) + ")"))
write_file_1.save("{}\\result\\score.xls".format(path))
程序运行结果:
完整代码如下:
#!/usr/bin/env python
# encoding: utf-8
import os
import time
import xlrd
import xlwt
from xlutils.copy import copy
student_id = []
student_name = []
student_score = []
def read_write():
global student_id
global student_name
global student_score
for root, dirs, files in os.walk("{}\\excel\\".format(path)):
for i in range(len(files)):
open_file = xlrd.open_workbook("{}\\excel\\".format(path) + files[i])
sheet = open_file.sheet_by_name("Sheet1")
rows = sheet.nrows
student_score = []
for j in range(rows):
num = sheet.cell_value(j, 0)
student_id = student_id + [num]
name = sheet.cell_value(j, 1)
student_name = student_name + [name]
if sheet.cell_value(j, 5) == "NULL":
score = 0
else:
score = sheet.cell_value(j, 5)
student_score = student_score + [score]
write_file = xlrd.open_workbook("{}\\result\\score.xls".format(path))
write_file_1 = copy(write_file)
write_sheet_1 = write_file_1.get_sheet(0)
for k in range(len(student_score)):
write_sheet_1.write(k, 0, student_id[k])
write_sheet_1.write(k, 1, student_name[k])
write_sheet_1.write(k, i + 2, student_score[k])
write_sheet_1.write(0, 8, "总分")
write_sheet_1.write(k, 8, xlwt.Formula("SUM(C" + str(k + 1) + ":H" + str(k + 1) + ")"))
write_file_1.save("{}\\result\\score.xls".format(path))
if __name__ == "__main__":
path = os.getcwd()
start_time = time.time()
read_write()
end_time = time.time()
print('Running time : {} seconds!'.format(str(end_time - start_time)))