美文网首页学习提升Python
利用python批量生成成绩折线图雷达图并生成每个学生的成绩报告

利用python批量生成成绩折线图雷达图并生成每个学生的成绩报告

作者: 少儿创客 | 来源:发表于2020-01-12 15:03 被阅读0次

    需求

    有班主任需要给每个学生生成成绩报告,班主任有成绩数据了,需要根据成绩生成雷达图并插入到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)
    
    

    需要的表格格式与上面相同


    模板

    相关文章

      网友评论

        本文标题:利用python批量生成成绩折线图雷达图并生成每个学生的成绩报告

        本文链接:https://www.haomeiwen.com/subject/rqoiactx.html