利用python批量生成成绩折线图雷达图并生成每个学生的成绩报告 |
您所在的位置：网站首页 › python输出折线图 › 利用python批量生成成绩折线图雷达图并生成每个学生的成绩报告 |
|
需求
有班主任需要给每个学生生成成绩报告，班主任有成绩数据了，需要根据成绩生成雷达图并插入到word生成成绩报告，有的还需要把每个学生的插入到ppt家长会的时候用，这种工作机械重复，但是很多班主任不得不做，给班主任带来大量的负担。 同时集团也要求上报学生成绩对比分析，所以批量生成就很重要了。 解决办法 python批量生成用python写脚本批量生成 编写成绩查询网站钉钉已经有校园宝可以发送单次成绩并可以查看雷达图，多次成绩对比不能做。且不能导出和生成每个学生的成绩报告，这个属于收费功能。 针对小学部的需求，采用了PHP+MySQL搭建了网站，用PHP的phpoffice生成word，不过PHP的图标模块JpGraph用起来比较繁琐。其实网页端用echarts比较方便。 市面上成绩分析系统已经非常成熟，但是不是所有的学校都有采购，问为什么不用系统就有点何不食肉糜的感觉了。 代码网站不是很方便，加上我对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的代码有待优化 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)需要的表格格式与上面相同 模板 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |