一个数据转换的实例
操作要求
从excel中导入表格 对信息进行筛选 分类后 统计保存到新的表格
# 东海县教育局 教育考试中心 # 作 者: 刘 磊 # QQ: 247483085 # 编写时间:2022-03-09 --14:24 # coding=utf-8 import xlwt import xlrd from xlutils.copy import copy file_name = input('输入学合考试成绩execl文件名称(要求XLS格式不用输入 .xls,文件与本程序在同一目录):') # file_name='0722' file_name = file_name + '.xls' # print(file_name) xls = xlrd.open_workbook(file_name) table = xls.sheet_by_index(0) rownum = table.nrows # 获取行数 # 循环处理数据 tongji = { '32072201': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072202': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072203': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072204': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072205': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072206': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072216': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072219': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } }, '32072200': { '2': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 }, '3': { 'wuli': 0, 'zwuli': 0, 'huaxue': 0, 'zhuaxue': 0, 'lishi': 0, 'zlishi': 0, 'dili': 0, 'zdili': 0, 'zhengzhi': 0, 'zzhengzhi': 0, 'shengwu': 0, 'zshengwu': 0, 'xxjs': 0, 'zxxjs': 0, 'yuwen': 0, 'zyuwen': 0, 'shuxue': 0, 'zshuxue': 0, 'waiyu': 0, 'zwaiyu': 0 } } } bhgnum=0 # print(tongji) for i in range(0, rownum): wuli = table.cell(i, 3).value.strip() huaxue = table.cell(i, 4).value.strip() lishi = table.cell(i, 5).value.strip() dili = table.cell(i, 6).value.strip() zhengzhi = table.cell(i, 7).value.strip() shengwu = table.cell(i, 8).value.strip() bmddm = table.cell(i, 9).value.strip() xxjs = table.cell(i, 10).value.strip() yuwen = table.cell(i, 13).value.strip() shuxue = table.cell(i, 14).value.strip() waiyu = table.cell(i, 15).value.strip() nj = table.cell(i, 12).value.strip() #向字典写入数据 if wuli == '合格': tongji[str(bmddm)][nj]['wuli'] = int(tongji[str(bmddm)][nj]['wuli']) + 1 tongji[str(bmddm)][nj]['zwuli'] = int(tongji[str(bmddm)][nj]['zwuli']) + 1 elif wuli == '不合格': tongji[str(bmddm)][nj]['zwuli'] = int(tongji[str(bmddm)][nj]['zwuli']) + 1 if huaxue == '合格': tongji[bmddm][nj]['huaxue'] = int(tongji[bmddm][nj]['huaxue']) + 1 tongji[bmddm][nj]['zhuaxue'] = int(tongji[bmddm][nj]['zhuaxue']) + 1 elif huaxue == '不合格': tongji[bmddm][nj]['zhuaxue'] = int(tongji[bmddm][nj]['zhuaxue']) + 1 if lishi == '合格': tongji[bmddm][nj]['lishi'] = int(tongji[bmddm][nj]['lishi']) + 1 tongji[bmddm][nj]['zlishi'] = int(tongji[bmddm][nj]['zlishi']) + 1 elif lishi == '不合格': tongji[bmddm][nj]['zlishi'] = int(tongji[bmddm][nj]['zlishi']) + 1 if dili == '合格': tongji[bmddm][nj]['dili'] = int(tongji[bmddm][nj]['dili']) + 1 tongji[bmddm][nj]['zdili'] = int(tongji[bmddm][nj]['zdili']) + 1 elif dili == '不合格': tongji[bmddm][nj]['zdili'] = int(tongji[bmddm][nj]['zdili']) + 1 if zhengzhi == '合格': tongji[bmddm][nj]['zhengzhi'] = int(tongji[bmddm][nj]['zhengzhi']) + 1 tongji[bmddm][nj]['zzhengzhi'] = int(tongji[bmddm][nj]['zzhengzhi']) + 1 elif zhengzhi == '不合格': tongji[bmddm][nj]['zzhengzhi'] = int(tongji[bmddm][nj]['zzhengzhi']) + 1 if shengwu == '合格': tongji[bmddm][nj]['shengwu'] = int(tongji[bmddm][nj]['shengwu']) + 1 tongji[bmddm][nj]['zshengwu'] = int(tongji[bmddm][nj]['zshengwu']) + 1 elif shengwu == '不合格': tongji[bmddm][nj]['zshengwu'] = int(tongji[bmddm][nj]['zshengwu']) + 1 if xxjs == '合格': tongji[bmddm][nj]['xxjs'] = int(tongji[bmddm][nj]['xxjs']) + 1 tongji[bmddm][nj]['zxxjs'] = int(tongji[bmddm][nj]['zxxjs']) + 1 elif xxjs == '不合格': tongji[bmddm][nj]['zxxjs'] = int(tongji[bmddm][nj]['zxxjs']) + 1 if yuwen == '合格': tongji[bmddm][nj]['yuwen'] = int(tongji[bmddm][nj]['yuwen']) + 1 tongji[bmddm][nj]['zyuwen'] = int(tongji[bmddm][nj]['zyuwen']) + 1 elif yuwen == '不合格': tongji[bmddm][nj]['zyuwen'] = int(tongji[bmddm][nj]['zyuwen']) + 1 if shuxue == '合格': tongji[bmddm][nj]['shuxue'] = int(tongji[bmddm][nj]['shuxue']) + 1 tongji[bmddm][nj]['zshuxue'] = int(tongji[bmddm][nj]['zshuxue']) + 1 elif shuxue == '不合格': tongji[bmddm][nj]['zshuxue'] = int(tongji[bmddm][nj]['zshuxue']) + 1 if waiyu == '合格': tongji[bmddm][nj]['waiyu'] = int(tongji[bmddm][nj]['waiyu']) + 1 tongji[bmddm][nj]['zwaiyu'] = int(tongji[bmddm][nj]['zwaiyu']) + 1 elif waiyu == '不合格': tongji[bmddm][nj]['zwaiyu'] = int(tongji[bmddm][nj]['zwaiyu']) + 1 if nj=='2': if '不合格' in [wuli,huaxue,shengwu,zhengzhi,lishi, dili]: bhgnum+=1 print(tongji) data=xlwt.Workbook() table=data.add_sheet('统计结果') style=xlwt.XFStyle() style1=xlwt.XFStyle() borders= xlwt.Borders() borders.left= 1 borders.right= 1 borders.top= 1 borders.bottom= 1 #为样式创建字体 font=xlwt.Font() #指定字体名字 font.name='Times New Roman' #字体加粗 font.bold=False #将该font设定为style的字体 style.font=font style.borders=borders style1.borders=borders tabletitle=['学校','年级','物理合格','物理总数','化学合格','化学总数','历史合格','历史总数','地理合格','地理总数','政治合格','政治总数','生物合格','生物总数','信息合格','信息总数','语文合格','语文总数','数学合格','数学总数','外语合格','外语总数'] for i in range(0,len(tabletitle)): table.write(0,i,tabletitle[i],style) q = 1 for key, value in tongji.items(): table.write(q, 0, key,style1) table.write(q + 1, 0, key, style1) table.write(q, 1, '高二', style1) table.write(q + 1, 1, '高三', style1) table.write(q, 2, value['2']['wuli'], style1) table.write(q, 3, value['2']['zwuli'], style1) table.write(q + 1, 2, value['3']['wuli'], style1) table.write(q + 1, 3, value['3']['zwuli'], style1) table.write(q, 4, value['2']['huaxue'], style1) table.write(q, 5, value['2']['zhuaxue'], style1) table.write(q + 1, 4, value['3']['huaxue'], style1) table.write(q + 1, 5, value['3']['zhuaxue'], style1) table.write(q, 6, value['2']['lishi'], style1) table.write(q, 7, value['2']['zlishi'], style1) table.write(q + 1, 6, value['3']['lishi'], style1) table.write(q + 1, 7, value['3']['zlishi'], style1) table.write(q, 8, value['2']['dili'], style1) table.write(q, 9, value['2']['zdili'], style1) table.write(q + 1, 8, value['3']['dili'], style1) table.write(q + 1, 9, value['3']['zdili'], style1) table.write(q, 10, value['2']['zhengzhi'], style1) table.write(q, 11, value['2']['zzhengzhi'], style1) table.write(q + 1, 10, value['3']['zhengzhi'], style1) table.write(q + 1,11, value['3']['zzhengzhi'], style1) table.write(q, 12, value['2']['shengwu'], style1) table.write(q, 13, value['2']['zshengwu'], style1) table.write(q + 1, 12, value['3']['shengwu'], style1) table.write(q + 1, 13, value['3']['zshengwu'], style1) table.write(q, 14, value['2']['xxjs'], style1) table.write(q, 15, value['2']['zxxjs'], style1) table.write(q + 1, 14, value['3']['xxjs'], style1) table.write(q + 1, 15, value['3']['zxxjs'], style1) table.write(q, 16, value['2']['yuwen'], style1) table.write(q, 17, value['2']['zyuwen'], style1) table.write(q + 1, 16, value['3']['yuwen'], style1) table.write(q + 1, 17, value['3']['zyuwen'], style1) table.write(q, 18, value['2']['shuxue'], style1) table.write(q, 19, value['2']['zshuxue'], style1) table.write(q + 1, 18, value['3']['shuxue'], style1) table.write(q + 1, 19, value['3']['zshuxue'], style1) table.write(q, 20, value['2']['waiyu'], style1) table.write(q, 21, value['2']['zwaiyu'], style1) table.write(q + 1, 20, value['3']['waiyu'], style1) table.write(q + 1, 21, value['3']['zwaiyu'], style1) q+=2 table.write(21,0,'有不合格学生数:',style1) table.write(21,1,bhgnum,style1) #注意:如果对同一个单元格重复操作,会引发overwrite Exception,想要取消该功能,需要在添加工作表时指定为可覆盖,像下面这样 #table=data.add_sheet('name',cell_overwrite_ok=True) data.save('学考成绩统计.xls')
非特殊说明,本文版权归原作者所有,转载请注明出处
评论列表
发表评论