需求
有班主任需要給每個學生生成成績報告，班主任有成績數據了，需要根據成績生成雷達圖并插入到word生成成績報告，有的還需要把每個學生的插入到ppt家長會的時候用，這種工作機械重復，但是很多班主任不得不做，給班主任帶來大量的負擔。
同時集團也要求上報學生成績對比分析，所以批量生成就很重要了。
解決辦法
python批量生成
用python寫腳本批量生成
編寫成績查詢網站
釘釘已經有校園寶可以發(fā)送單次成績并可以查看雷達圖，多次成績對比不能做。且不能導出和生成每個學生的成績報告，這個屬于收費功能。
針對小學部的需求，采用了PHP+MySQL搭建了網站，用PHP的phpoffice生成word，不過PHP的圖標模塊JpGraph用起來比較繁瑣。其實網頁端用echarts比較方便。
市面上成績分析系統(tǒng)已經非常成熟，但是不是所有的學校都有采購，問為什么不用系統(tǒng)就有點何不食肉糜的感覺了。
代碼
網站不是很方便，加上我對python的熟悉程度超過PHP，用python寫的代碼后期簡單修改結合flask之類的數據庫很容易生成。
用到的庫
- 讀取excel文件用到了
xlrd庫，當然處理excel最好是用numpy庫，但是我還不熟悉，用起來不如xlrd直觀 - 利用python生成word用到了
docxtpl庫，這個根據word模板，只需要填充數據就可以了，一定程度上可以減輕工作量，前提是熟悉模板的語法，不然還是直接生成更加方便 - 利用pptx庫生成ppt，也是基于模板生成，不過模板語法跟docxtpl不同，是jinja的語法。
- 生成圖表用
matplotlib庫
第一版代碼
import xlrd
import xlrd
from docxtpl import DocxTemplate, InlineImage
from docx.shared import Mm
import pyecharts
import numpy as np
from random import random
import matplotlib.pyplot as plt
import matplotlib
from docxtpl import DocxTemplate, InlineImage
from docx.shared import Mm
workbook12 = xlrd.open_workbook('mon12.xlsx')
sht12 = workbook12.sheet_by_name('Sheet3')
wbqimo = xlrd.open_workbook('qimo.xlsx')
shtqimo = wbqimo.sheet_by_name('Sheet3')
print(sht12.row_values(1, 2, 10))
print(sht12.nrows)
rank12data = {}
mon12data = {}
for i in range(sht12.nrows):
if i == 0:
continue
scores = sht12.row_values(i, 0, 17)[1:15:][::2]
# mon12data.append({sht12.cell_value(i,0):scores})
# mon12data.append(sht12.row_values(i, 0, 17))
mon12data[sht12.cell_value(i,0)] = scores
# mon12data['rank'] = sht12.cell_value(i, 16)
# print(sht12.cell_value(i,16))
rank12data[sht12.cell_value(i,0)] = sht12.cell_value(i, 16)
qimorank = {}
qimodata = {}
for i in range(shtqimo.nrows):
if i == 0:
continue
scores = shtqimo.row_values(i, 0, 17)[1:15:][::2]
# qimodata.append({shtqimo.cell_value(i,0):scores})
qimodata[shtqimo.cell_value(i,0)] = scores
# qimodata['rank'] = shtqimo.cell_value(i, 16)
# print(shtqimo.cell_value(i,16))
qimorank[shtqimo.cell_value(i,0)] = shtqimo.cell_value(i, 16)
labels = shtqimo.row_values(0,1,15)[::2]
# print(labels)
# print('!!!@@',labels)
# print(qimodata)
font = {
'family' : 'SimHei'
}
matplotlib.rc('font', **font)
for stuName, scores in mon12data.items():
# plt.cla()
# plt.title('hello')
# 數據找不到的情況
# 某次考試沒成績
data1 = scores
data2 = qimodata[stuName]
# print('#',len(d1),len(d2))
# print(stuName, d1, d2)
labels = np.array(labels) # 標簽
dataLenth = len(labels) # 數據長度
angles = np.linspace(0, 2*np.pi, dataLenth, endpoint=False) # 分割圓周長
d1 = np.concatenate((data1, [data1[0]])) # 閉合
d2 = np.concatenate((data2, [data2[0]])) # 閉合
angles = np.concatenate((angles, [angles[0]])) # 閉合
# plt.title('期末考試')
p1, = plt.polar(angles, d1, 'o-', linewidth=1) #做極坐標系
plt.fill(angles, d1)# 填充
p2, = plt.polar(angles, d2, 'o-', linewidth=1) #做極坐標系
plt.fill(angles, d2)# 填充
plt.legend([p1, p2], ['期中', '期末'],prop={'family':'SimHei'})
plt.thetagrids(angles * 180/np.pi, labels, fontproperties='SimHei') # 設置網格、標簽
plt.ylim(0,100) # polar的極值設置為ylim
plt.savefig('radar/{}radar.png'.format(stuName))
# plt.close()
plt.clf()
A = labels
B = data1
print(stuName)
fig, ax1 = plt.subplots(figsize=(12,9))
ax1.plot(A,B,label="期中")
ax1.plot(A,data2,label="期末")
plt.title("{}期中期末成績折線圖".format(stuName),fontproperties='SimHei')
ax1.legend()
ax1.grid(axis="y",color="grey",linestyle="--",alpha=0.5)
ax1.tick_params(axis="x",labelsize=30)
ax1.tick_params(axis="y",labelsize=20)
ax1.set_ylabel("Y",fontsize = 18)
ax1.set_xlabel("X",fontsize = 20)
ax1.set_ylim(0,100)
# ax1.set_yticks(np.linspace(0,15,16))
for tl in ax1.get_yticklabels():
tl.set_color('r')
ax1.spines['top'].set_visible(False)
# fig.text(0.1,0.02,"Author:MingYan",fontproperties='SimHei')
plt.savefig('line/{}line.png'.format(stuName))
plt.close()
doc = DocxTemplate(r"./temp.docx")
context = { 'stuName' : stuName,
'chinese': data1[0],
'maths': data1[1],
'english': data1[2],
'politic': data1[3],
'history': data1[4],
'physical': data1[5],
'hx': data1[6],
'avg':sum(data1),
'chinese1': data2[0],
'maths1': data2[1],
'english1': data2[2],
'politic1': data2[3],
'history1': data2[4],
'physical1': data2[5],
'hx1': data2[6],
'avg1': sum(data2),
'rank': rank12data[stuName],
'rank1':qimorank[stuName],
'linechart':InlineImage(doc, 'line/{}line.png'.format(stuName), width=Mm(100)),
'radarchart':InlineImage(doc, 'radar/{}radar.png'.format(stuName), width=Mm(100)),
}
doc.render(context)
doc.save(r"./doc/{}.docx".format(stuName))
# save_docx([r3,result1[0]])
這個是根據學生的成績，生成學生4次成績對比折線圖，和4次考試成績雷達圖的對比。
image.png
表頭如下
表頭
word模板
word模板
上面的代碼處于試驗階段很原始，后來重構了一下，可以配置文件名，學科字段，并生成成績雷達圖，歷次考試成績對比圖，等等
import xlrd
import xlrd
from docxtpl import DocxTemplate, InlineImage
from docx.shared import Mm
import pyecharts
import numpy as np
from random import random
import matplotlib.pyplot as plt
import matplotlib
import os
from docxtpl import DocxTemplate, InlineImage
from docx.shared import Mm
from pptx import Presentation
from pptx.util import Pt, Cm
from pptx.util import Inches
#===================開始設置中文字體=================
font = {
'family' : 'SimHei',
'size' : 12
}
matplotlib.rc('font', **font)
#===================結束設置中文字體=================
class GenerateReport:
def __init__(self, files, stuClass='1.1'):
if not files:
return None
else:
self.files = files
self.radarIndex = 0
# TODO:這種字符串可以用字符串分割方法生成數組
# 反之用數字拼接字符串可以降低拼寫的效率
# 怪不得校園寶要設置學科
self.subjects = ['語文','數學','英語','歷史','道法','地理','生物','物理']
self.subjects_en = ['chinese','maths','english','history','tao','geography','bio','physical']
self.ranks = []
self.root = stuClass
def mkdir(path):
if not os.path.isdir(path):
mkdir(os.path.split(path)[0],'/')
else:
return
os.mkdir(path)
if not os.path.exists(self.root):
os.mkdir(self.root)
os.mkdir("/{}/doc".format(self.root))
os.mkdir("/{}/line".format(self.root))
os.mkdir("/{}/radar".format(self.root))
self.dataset = self.processFiles(files)
def setTemplate(self, file):
self.template = file
def drawTotalLine(stuName):
pass
def paraXls(self, filename):
# TODO:要添加異常處理
# {
# 'stuName': 'langxm',
# 'scores': [
# {
# 'chinese': 80,
# 'math' : 90
# },
# ],
# 'grades': [
# {
# 'chinese': A,
# 'math' : B
# },
# ],
# 'sum': 20,
# 'rank': 20,
# 'total': 20
# }
dataset = {}
wb = xlrd.open_workbook(filename)
sht = wb.sheet_by_index(0)
rows = sht.nrows
cols = sht.ncols
header = sht.row_values(0, 0, cols)
self.students = sht.col_values(0, 1, rows)
# 查詢index的代碼有待優(yōu)化
def getIndex(value, valuelist):
# TODO：重名的情況
return valuelist.index(value)
subjectIndexes = [getIndex(subject, header) for subject in self.subjects]
# TODO:numpy的數組加載xls處理效率更高，可以用numpy的np數組改寫
for i in range(1, rows):
line = sht.row_values(i, 0, cols)
stuName = line[0]
scores = [line[index] for index in subjectIndexes]
grades = [line[index + 1] for index in subjectIndexes]
examData = {}
item = {}
item['scores'] = scores
item['grades'] = grades # 這些項目都是可以做成可以配置的
# 可以用工廠模式改寫
item['total'] = sum(scores)
item['avg'] = sum(scores) / len(subjectIndexes)
examData[stuName] = item
dataset[stuName] = examData
return dataset
def processFiles(self, files):
d = {}
def setValue(key, value):
d[key] = value
[setValue(file, self.paraXls(file)) for file in files]
return d
def drawRadar(self, stuName, index=0):
labels = np.array(self.subjects) # 標簽
dataLenth = len(labels) # 數據長度
angles = np.linspace(0, 2*np.pi, dataLenth, endpoint=False) # 分割圓周長
angles = np.concatenate((angles, [angles[0]])) # 閉合
dataset = []
# stuName = '徐文清'
for file in self.files[index:]:
dataset.append(self.dataset[file][stuName][stuName]['scores'])
polars = []
for ds in dataset:
ds = np.concatenate((ds, [ds[0]]))
p1, = plt.polar(angles, ds, 'o-', linewidth=1) #做極坐標系
polars.append(p1)
plt.legend(polars,[file[0:-5] for file in self.files] ,prop={'family':'SimHei'})
plt.thetagrids(angles * 180/np.pi, labels, fontproperties='SimHei') # 設置網格、標簽
plt.ylim(0,100) # polar的極值設置為ylim
plt.savefig(self.root + '/radar/{}radar.png'.format(stuName))
plt.close()
def drawLevelRadar(self, stuName, index=0):
labels = np.array(self.subjects) # 標簽
dataLenth = len(labels) # 數據長度
angles = np.linspace(0, 2*np.pi, dataLenth, endpoint=False) # 分割圓周長
angles = np.concatenate((angles, [angles[0]])) # 閉合
dataset = []
# stuName = '徐文清'
levelscore = {'A':1,'B':2,'C':3,'D':4,'E':5}
for file in self.files[index:]:
levels = self.dataset[file][stuName][stuName]['grades']
levels = [levelscore[level] for level in levels]
dataset.append(levels)
polars = []
for ds in dataset:
ds = np.concatenate((ds, [ds[0]]))
p1, = plt.polar(angles, ds, 'o-', linewidth=1) #做極坐標系
polars.append(p1)
plt.legend(polars,[file[0:-4] for file in self.files] ,prop={'family':'SimHei'})
plt.thetagrids(angles * 180/np.pi, labels, fontproperties='SimHei') # 設置網格、標簽
plt.ylim(0,5) # polar的極值設置為ylim最大值可配置
plt.savefig(self.root + '/radar/{}radar.png'.format(stuName))
plt.close()
def getRankData(self):
pass
wb = xlrd.open_workbook('812fuck.xlsx')
sht = wb.sheet_by_name('Sheet1')
# print(sht.cell_value(1,2))
rows = sht.nrows
cols = sht.ncols
ddd = {}
for i in range(1, rows):
# fuck[stuName] = [sht.cell_value(i,1),sht.cell_value(1,2),sht.cell_value(1,3),sht.cell_value(1,4)]
# print(sht.row_values(i,0,cols))
fuck[sht.cell_value(i,0)]=sht.row_values(i,1,cols)
self.ddd = ddd
self.randtitles=sht.row_values(0,1,cols)
# print(fuck)
def drawRankLine(self, stuName):
pass
labels = np.array(self.randtitles) # 標簽
fig, ax1 = plt.subplots(figsize=(12,9))
# stuName = '徐文清'
ax1.plot(labels, self.ddd[stuName],label='成績變化圖')
plt.title("{}成績編號圖".format(stuName),fontproperties='SimHei',fontsize=30)
ax1.legend()
ax1.grid(axis="y",color="grey",linestyle="--",alpha=0.5)
ax1.tick_params(axis="x",labelsize=16)
ax1.tick_params(axis="y",labelsize=16)
ax1.set_ylabel("成績",fontsize = 16)
ax1.set_xlabel("學科",fontsize = 16)
ax1.set_ylim(0,400)
for tl in ax1.get_yticklabels():
tl.set_color('r')
ax1.spines['top'].set_visible(True)
plt.savefig(self.root + '/line/{}rank.png'.format(stuName))
plt.close()
def generatePPT(self, stuName, rankpath, radarpath):
pass
prs = Presentation('./812ppt.pptx')
# blank_slide_layout = prs.slide_layouts[6]
# slide = prs.slides.add_slide(blank_slide_layout)
# textbox = slide.shapes.add_textbox(Cm(0.8), Cm(0.8), Cm(4), Cm(1.03)) # left，top為相對位置，width，height為文本框大小
# textbox.text = '水溫，PH'.center(10) # 文本框中文字
# prs = Presentation()
title_slide_layout = prs.slide_layouts[4]
# print(len(prs.slide_layouts))
# for x in prs.slide_layouts:
# print(dir(x.placeholders),)
slide = prs.slides.add_slide(title_slide_layout)
title = slide.shapes.title
# linechart = slide.placeholders[0]
# radarchat = slide.placeholders[1]
title.text = "{}同學成績分析".format(stuName)
# rankpath = 'foo.png'
left = top = Inches(2)
left = Inches(1)
width = height = Inches(6)
linecart = slide.shapes.add_picture(rankpath, left, top, width=width)
# rankpath = 'foo.png'
left = Inches(7)
radar = slide.shapes.add_picture(radarpath, left, top, width=width)
# subtitle.text = "python-pptx was here!"
prs.save('./812ppt.pptx')
def drawLine(self, stuName):
labels = np.array(self.subjects) # 標簽
fig, ax1 = plt.subplots(figsize=(12,9))
# stuName = '徐文清'
for file in self.files:
ax1.plot(labels, self.dataset[file][stuName][stuName]['scores'], label=file[0:-5])
plt.title("{}期中期末成績折線圖".format(stuName),fontproperties='SimHei',fontsize=30)
ax1.legend()
ax1.grid(axis="y",color="grey",linestyle="--",alpha=0.5)
ax1.tick_params(axis="x",labelsize=16)
ax1.tick_params(axis="y",labelsize=16)
ax1.set_ylabel("成績",fontsize = 16)
ax1.set_xlabel("學科",fontsize = 16)
ax1.set_ylim(0,100)
for tl in ax1.get_yticklabels():
tl.set_color('r')
ax1.spines['top'].set_visible(True)
plt.savefig(self.root + '/line/{}line.png'.format(stuName))
plt.close()
def saveDoc(self, stuName):
# plt.clear()
pass
# TODO:模板生成word雖然方便但是寫模板變量太煩了
# 后面改成直接生成
doc = DocxTemplate(r"./{}".format(self.template))
dataset = []
# stuName = '徐文清'
context = {}
for file in self.files:
dataset.append(self.dataset[file][stuName][stuName])
context['stuName'] = stuName
for key, ds in enumerate(dataset):
context['file'+str(key)] = self.files[key]
scores = ds["scores"]
for i in range(len(scores)):
context[self.subjects_en[i]+str(key)] = scores[i]
context['total' + str(key)] = sum(scores)
# context['linechart'] = InlineImage(doc, self.root + '/line/{}line.png'.format(stuName), width=Mm(100))
context['radarchart'] = InlineImage(doc, self.root + '/radar/{}radar.png'.format(stuName).format(stuName), width=Mm(100))
doc.render(context)
doc.save(r"./{}/doc/{}.docx".format(self.root, stuName))
def genDocs(self):
self.getRankData()
print(self.ddd)
print(self.ddd['付晶晶'])
for stuName in self.students:
self.drawRankLine(stuName)
self.drawLevelRadar(stuName,index=self.radarIndex)
self.drawRankLine(stuName)
# TODO:文件名應該是可以配置的包括路徑
self.saveDoc(stuName)
print('{}的文檔已經生成,位于./{}/doc/{}.docx'.format(stuName, self.root, stuName))
if __name__ == "__main__":
pass
gr = GenerateReport(['初二12班期末考試.xls'])
# gr = GenerateReport([])
# gr.radarIndex = 2
gr.setTemplate('86temp.docx')
# gr.drawLine('徐文清')
# print(len(gr.students))
# gr.genDocs()
# gr.generatePPT()
print(gr.students)
for stuName in gr.students:
gr.generatePPT(stuName,gr.root + '/line/{}rank.png'.format(stuName),gr.root + '/radar/{}radar.png'.format(stuName))
# print(gr.dataset)
需要的表格格式與上面相同
模板
