需求
有班主任需要給每個學生生成成績報告趁舀，班主任有成績數(shù)據(jù)了巡蘸，需要根據(jù)成績生成雷達圖并插入到word生成成績報告唯欣，有的還需要把每個學生的插入到ppt家長會的時候用，這種工作機械重復(fù)萍聊，但是很多班主任不得不做，給班主任帶來大量的負擔亭螟。
同時集團也要求上報學生成績對比分析，所以批量生成就很重要了。
解決辦法
python批量生成
用python寫腳本批量生成
編寫成績查詢網(wǎng)站
釘釘已經(jīng)有校園寶可以發(fā)送單次成績并可以查看雷達圖炼蹦，多次成績對比不能做。且不能導(dǎo)出和生成每個學生的成績報告，這個屬于收費功能。
針對小學部的需求伪节，采用了PHP+MySQL搭建了網(wǎng)站，用PHP的phpoffice生成word，不過PHP的圖標模塊JpGraph用起來比較繁瑣蓖康。其實網(wǎng)頁端用echarts比較方便科贬。
市面上成績分析系統(tǒng)已經(jīng)非常成熟鸭丛，但是不是所有的學校都有采購瘾带，問為什么不用系統(tǒng)就有點何不食肉糜的感覺了。
代碼
網(wǎng)站不是很方便，加上我對python的熟悉程度超過PHP嚷兔，用python寫的代碼后期簡單修改結(jié)合flask之類的數(shù)據(jù)庫很容易生成竟块。
用到的庫
- 讀取excel文件用到了
xlrd庫蒋情，當然處理excel最好是用numpy庫，但是我還不熟悉金蜀，用起來不如xlrd直觀 - 利用python生成word用到了
docxtpl庫尝胆，這個根據(jù)word模板，只需要填充數(shù)據(jù)就可以了缓呛，一定程度上可以減輕工作量，前提是熟悉模板的語法，不然還是直接生成更加方便 - 利用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')
# 數(shù)據(jù)找不到的情況
# 某次考試沒成績
data1 = scores
data2 = qimodata[stuName]
# print('#',len(d1),len(d2))
# print(stuName, d1, d2)
labels = np.array(labels) # 標簽
dataLenth = len(labels) # 數(shù)據(jù)長度
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') # 設(shè)置網(wǎng)格、標簽
plt.ylim(0,100) # polar的極值設(shè)置為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]])
這個是根據(jù)學生的成績鳄乏，生成學生4次成績對比折線圖跷车，和4次考試成績雷達圖的對比。
image.png
表頭如下
表頭
word模板
word模板
上面的代碼處于試驗階段很原始橱野，后來重構(gòu)了一下朽缴，可以配置文件名，學科字段水援，并生成成績雷達圖密强，歷次考試成績對比圖，等等
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
#===================開始設(shè)置中文字體=================
font = {
'family' : 'SimHei',
'size' : 12
}
matplotlib.rc('font', **font)
#===================結(jié)束設(shè)置中文字體=================
class GenerateReport:
def __init__(self, files, stuClass='1.1'):
if not files:
return None
else:
self.files = files
self.radarIndex = 0
# TODO:這種字符串可以用字符串分割方法生成數(shù)組
# 反之用數(shù)字拼接字符串可以降低拼寫的效率
# 怪不得校園寶要設(shè)置學科
self.subjects = ['語文','數(shù)學','英語','歷史','道法','地理','生物','物理']
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的數(shù)組加載xls處理效率更高蜗元，可以用numpy的np數(shù)組改寫
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) # 數(shù)據(jù)長度
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') # 設(shè)置網(wǎng)格或渤、標簽
plt.ylim(0,100) # polar的極值設(shè)置為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) # 數(shù)據(jù)長度
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') # 設(shè)置網(wǎng)格、標簽
plt.ylim(0,5) # polar的極值設(shè)置為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:文件名應(yīng)該是可以配置的包括路徑
self.saveDoc(stuName)
print('{}的文檔已經(jīng)生成,位于./{}/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)
需要的表格格式與上面相同
模板
