def readfile(): if request.method == "POST": msg = "成功" table_title = [] f = request.files["file"] filename = secure_filename("".join(lazy_pinyin(f.filename))) print(filename) if filename.endswith(".xlsx"): upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) print("总行数:" + str(table.nrows)) print("总列数:" + str(table.ncols)) table_title = table.row_values(0) # print(table_title) # print(len(table_title)) os.remove(upload_path) else: msg = "请导入xlsx格式的文件" return jsonify({"msg": msg, "table_title": table_title})
def importData(): if request.method == "POST": # 获取年份列表 db = get_lijing_db() year_data = db.execute("select year from year_list").fetchall() year_list = [] for year in year_data: year_list.insert(0, year["year"]) # 获取要导入的年份,判断是否在已有年份中 year_select = request.form.get("year_select") if year_select not in year_list: # 插入新的年份 db.execute( "insert into year_list (year, basicinfo, workinfo, honorinfo) values (?,?,?,?)", ( year_select, 0, 0, 0, ), ) # 新年份创建新的表 create_table(["person", "education", "workinfo", "skill"], year_select) # 获取选择的表格文件 f = request.files["file"] # 获取文件名,判断是否是excel文件 filename = secure_filename("".join(lazy_pinyin(f.filename))) if filename.endswith(".xlsx"): # 保存文件到服务器uploads文件夹 upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) # 打开保存后的文件 data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) # print("总行数:" + str(table.nrows)) # print("总列数:" + str(table.ncols)) # 找到标题 dict_title = { "姓名": "person_name", "性别": "gender", "身份证号": "id_number", "联系电话": "phone", "政治面貌": "political_status", "入党时间": "time_Party", "参加工作时间": "time_work", "家庭住址": "address", "工作简历": "resume", "第一学历": "edu_start", "第一学历毕业时间": "time_edu_start", "第一学历毕业学校": "school_edu_start", "第一学历专业": "major_edu_start", "最高学历": "edu_end", "最高学历毕业时间": "time_edu_end", "最高学历毕业学校": "school_edu_end", "最高学历专业": "major_edu_end", "专业技术职称": "skill_title", "取得时间": "time_skill", "发证单位": "skill_unit", "发证文件批号": "skill_number", "调入大集中学时间": "time_school", "用工性质": "work_kind", "工作岗位": "job_post", "退休时间": "time_retire", } row_title = table.row_values(0) title_id = {} for i in dict_title: # 初始化-1 title_id[dict_title[i]] = -1 for i in range(0, len(row_title)): title_name = row_title[i] if title_name in dict_title: # 保存标题序号 title_id[dict_title[title_name]] = i # 导入数据 item = get_item_list(["person", "education", "skill", "workinfo"]) for i in range(1, table.nrows): # 获取每一行的值 row_value = table.row_values(i) insert_dict = {} for j in item: if title_id[j] == -1: # 没有找到标题 insert_dict[j] = "暂无" else: # 找到标题 insert_dict[j] = float_int_string(row_value[title_id[j]]) if len(insert_dict[j]) == 0: # 找到标题,但没有值 insert_dict[j] = "暂无" # 插入数据到person表 item_person = get_item_list("person") insert_table("person", year_select, item_person, insert_dict) # 获取自动生成的person_id condition = {"person_name": " = '" + insert_dict["person_name"] + "'"} person_id = select_table( "person", year_select, {"person_id": "person"}, condition )["person_id"] insert_dict["person_id"] = float_int_string(person_id) # 插入其他表 item_education = get_item_list("education") item_education.append("person_id") insert_table("education", year_select, item_education, insert_dict) item_skill = get_item_list("skill") item_skill.append("person_id") insert_table("skill", year_select, item_skill, insert_dict) item_workinfo = get_item_list("workinfo") item_workinfo.append("person_id") insert_table("workinfo", year_select, item_workinfo, insert_dict) # 删除保存的表格文件 os.remove(upload_path) return redirect(url_for("lijing_basicinfo.hello")) else: # 不是execl文件 error = "请导入xlsx格式的文件" flash(error) return redirect(url_for("lijing_basicinfo.hello"))
def exportData(): # 获取要导出的项 export_item = {} table = ["person", "education", "skill", "workinfo"] item = get_item_list(table) for table_name in table: item_table = get_item_list(table_name) for i in item_table: if request.args.get(i) == "true": export_item[i] = table_name # 判断是否导出全部数据 year = session["year_current"] flag_search = request.args.get("flag_search") id_list = [] if flag_search == "true": # 导出部分数据 id_list = request.args.getlist("id_list[]") else: # 导出全部数据 person_data = select_table("person", year, {"person_id": "person"}) for i in person_data: id_list.append(str(i["person_id"])) # 获取要导出的数据 table_data = [] for i in id_list: result = select_table( table, year, export_item, {"person_" + year + ".person_id": " = '" + i + "'"}, ) table_data.append(result) # 写入数据到表格 item_name_dict = { "person_name": "姓名", "gender": "性别", "id_number": "身份证号", "phone": "联系电话", "political_status": "政治面貌", "time_Party": "入党时间", "time_work": "参加工作时间", "address": "家庭住址", "resume": "个人简历", "edu_start": "第一学历", "time_edu_start": "第一学历毕业时间", "school_edu_start": "第一学历毕业学校", "major_edu_start": "第一学历专业", "edu_end": "最高学历", "time_edu_end": "最高学历毕业时间", "school_edu_end": "最高学历毕业学校", "major_edu_end": "最高学历专业", "skill_title": "专业技术职称", "time_skill": "职称取得时间", "skill_unit": "职称发证单位", "skill_number": "发证文件批号", "time_school": "调入大集中学时间", "work_kind": "用工性质", "job_post": "工作岗位", "time_retire": "退休时间", } save_path = list_to_path(["flaskr", "static", "downloads", "exportData.xlsx"]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") col = 0 for i in export_item: worksheet.write(0, col, item_name_dict[i]) col = col + 1 for i in range(len(id_list)): col = 0 for j in export_item: worksheet.write(i + 1, col, table_data[i][j]) col = col + 1 workbook.close() # 完成导出 msg = "成功导出" + str(len(id_list)) + "条信息" return jsonify({"msg": msg, "filename": "exportData.xlsx"})
def readfile(): if request.method == "POST": msg = "成功" f = request.files["file"] filename = current_time() + secure_filename("".join( lazy_pinyin(f.filename))) if not filename.endswith(".xlsx"): msg = "请导入xlsx格式的文件" return jsonify({"msg": msg}) # 保存文件 upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) # 打开文件 data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) # 获取合并单元格(起始行,结束行,起始列,结束列) merged_cells = table.merged_cells # 获取数据行索引 row_data = 1 for cell in merged_cells: if cell[1] > row_data: row_data = cell[1] pass table_col = [] items = { "姓名": -1, "考号": -1, "班级": -1, "总分": -1, "语文": -1, "数学": -1, "英语": -1, "物理": -1, "化学": -1, "道法": -1, "历史": -1, "地理": -1, "生物": -1, } # items = ['总分', '语文', '数学', '英语', '物理', '化学', '生物', '历史', '地理', '道法'] for row_title in range(0, row_data): title = table.row_values(row_title) for i in range(0, len(title)): # 返回每列的标题数据 if row_title == 0: table_col.append("第" + str(i + 1) + "列:[ " + title[i] + " ] ") else: table_col[i] = table_col[i] + "[ " + title[i] + " ] " # 获取分数列索引 if title[i] in items: items[title[i]] = i pass pass # 分数列索引 col_index = [] for item in items: col_index.append(items[item]) # 前9行数据 table_row = [] for i in range(0, 9): data_row = str(table.row_values(i)) table_row.append("第" + str(i + 1) + "行:" + data_row) pass os.remove(upload_path) pass return jsonify({ "msg": msg, "table_col": table_col, "col_index": col_index, "table_row": table_row, "row_data": row_data, })
def resultStatistics(): if request.method == "POST": msg = "成功" f = request.files["file"] f2 = request.files["file2"] filename = secure_filename("".join(lazy_pinyin(f.filename))) filename2 = secure_filename("".join(lazy_pinyin(f2.filename))) if not filename2.endswith(".xlsx"): msg = "请导入xlsx格式的文件" return jsonify({"msg": msg}) # 防止文件重名 if filename == filename2: filename2 = "1" + filename2 # 获取request数据 class1 = int(request.form.get("class1")) class2 = int(request.form.get("class2")) delete_id_list = request.form.get("delete_id").split("\n") change_examid_list = request.form.get("change_examid").split("\n") change_class_list = request.form.get("change_class").split("\n") if len(change_examid_list) != len(change_class_list): msg = "调整班级数据有错误" return jsonify({"msg": msg}) # 保存文件 upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) upload_path2 = list_to_path(["flaskr", "static", "uploads", filename2]) f2.save(upload_path2) # 打开文件 data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) data2 = xlrd.open_workbook(upload_path2) table2 = data2.sheet_by_index(0) # 获取成绩表格列索引 items = { "姓名": -1, "考号": -1, "班级": -1, "总分": -1, "语文": -1, "数学": -1, "英语": -1, "物理": -1, "化学": -1, "道法": -1, "历史": -1, "地理": -1, "生物": -1, } item_title = [ "姓名", "考号", "班级", "语文", "数学", "英语", "物理", "化学", "生物", "历史", "地理", "道法", "总分", ] item_id_list = request.form.get("item_id_list").split(",") it = 0 for item in items: items[item] = int(item_id_list[it]) - 1 it = it + 1 if items[item] == -1: item_title.remove(item) pass # 获取各科老师、人数表格索引 items_table2 = item_title[2:] items_table2[-1] = "人数" data_title_teacher = table2.row_values(0) item_table2_id_list = [] for item in items_table2: flag = False for i in range(len(data_title_teacher)): if item in data_title_teacher[i]: flag = True item_table2_id_list.append(i) pass pass if not flag: if item == "班级": msg = "教师、有效人数表格:缺少班级" elif item == "人数": msg = "教师、有效人数表格:缺少班级有效人数" else: msg = "教师、有效人数表格:缺少" + item + "教师姓名" return jsonify({"msg": msg}) pass # 获取成绩数据、班级数据 data_table = [] class_list = [] index_data = int(request.form.get("index_data")) - 1 for i in range(index_data, table.nrows): row_value = table.row_values(i) class_name = float_int_string(row_value[items["班级"]]) if class_name not in class_list: class_list.append(class_name) data_row = [] for item in item_title: if row_value[items[item]] == "": data_row.append(0.0) else: if item == "班级": data_row.append( int(float_int_string(row_value[items[item]]))) elif item == "考号": data_row.append( float_int_string(row_value[items[item]])) else: data_row.append(row_value[items[item]]) pass pass data_table.append(data_row) pass # 获取老师、有效人数数据 data_table2 = [] for i in range(1, table2.nrows): row = [] data_row = table2.row_values(i) for teacher_id in item_table2_id_list: row.append(float_int_string(data_row[teacher_id])) data_table2.append(row) os.remove(upload_path) os.remove(upload_path2) # 处理数据 # 调整部分学生的班级 for i in range(len(change_examid_list)): change_exam_id = change_examid_list[i] flag = False for j in range(len(data_table)): if change_exam_id == data_table[j][1]: flag = True data_table[j][2] = int(change_class_list[i]) pass pass if not flag: if change_exam_id == "": pass else: msg = "要调整班级的学生学号找不到:" + change_exam_id return jsonify({"msg": msg}) pass pass # 判断调整的班级是否存在 for class_name in change_class_list: if class_name not in class_list: if class_name == "": pass else: msg = "要调整班级的班级不存在:" + class_name return jsonify({"msg": msg}) pass pass # 通过学号删除不计入统计的学生 index_delele = [] delete_id_dict = {} for i in delete_id_list: if i == "": continue i = i.replace("\r", "") delete_id_dict[i] = True pass for i in range(len(data_table)): exam_id = data_table[i][1] if exam_id in delete_id_dict: delete_id_dict[exam_id] = False index_delele.append(i) pass for i in delete_id_dict: if delete_id_dict[i]: msg = "要删除的学生学号找不到:" + i return jsonify({"msg": msg}) pass index_delele = sorted(index_delele, reverse=True) for i in index_delele: del data_table[i] ############################################################# export_title = [] export_data = [] item_subject = item_title[3:] # 找到总分索引 index_total = -1 for i in range(len(item_title)): if "总分" == item_title[i]: index_total = i break pass # 按总分排序 data_table.sort(key=itemgetter(index_total), reverse=True) # 找到尖子生边界分数 class1_total_result = data_table[class1 - 1][-1] class2_total_result = data_table[class1 + class2 - 1][-1] # 找到班级索引 index_class = -1 for i in range(len(item_title)): if "班级" == item_title[i]: index_class = i break pass # 按班级排序、按班级分组 data_table.sort(key=itemgetter(index_class)) for class_name, class_students in groupby(data_table, itemgetter(index_class)): # 一个班的所有学生数据 class_students_result = [] for student in class_students: class_students_result.append(student) # 判断教师、有效人数表格中是否有对应的班级 index_teacher_number = -1 for i in range(len(data_table2)): if str(class_name) == data_table2[i][0]: index_teacher_number = i break if index_teacher_number == -1: msg = "教师、有效人数表格:找不到班级 %s" % str(class_name) return jsonify({"msg": msg}) # # 有效人数 average_num = int(data_table2[index_teacher_number][-1]) # if average_num > len(class_students_result): # msg = "教师、有效人数表格:%s班有效人数错误" % str(class_name) # return jsonify({"msg": msg}) # 每个班级的数据 data_class = [] data_class.append(class_name) # 分科目计算平均分 for item in item_subject: # 科目老师的名字 index_teacher_name = -1 for i in range(len(items_table2)): if item == items_table2[i]: index_teacher_name = i break if index_teacher_name != -1: data_class.append( data_table2[index_teacher_number][index_teacher_name]) # 找到分数索引 index_item = -1 for i in range(len(item_title)): if item == item_title[i]: index_item = i break # 计算平均分 sum_result = 0 if average_num <= len(class_students_result): for i in range(0, average_num): sum_result = sum_result + class_students_result[i][ index_item] pass else: for i in range(0, len(class_students_result)): sum_result = sum_result + class_students_result[i][ index_item] pass average_result = sum_result / average_num data_class.append(average_result) # 预留排名 data_class.append(0) # 统计尖子生个数 number_class1 = 0 number_class2 = 0 for student in class_students_result: if student[-1] >= class1_total_result: number_class1 = number_class1 + 1 elif student[-1] >= class2_total_result: number_class2 = number_class2 + 1 else: pass data_class.append(number_class1) data_class.append(0) data_class.append(number_class2) data_class.append(0) export_data.append(data_class) # 标题 export_title.append("班级") it = 0 sort_id_list = [] for item in item_subject: if item != "总分": export_title.append(item + "教师") it = it + 1 export_title.append(item + "人平") export_title.append(item + "排名") it = it + 2 sort_id_list.append(it) pass export_title.append("一类人数") export_title.append("排名") it = it + 2 sort_id_list.append(it) export_title.append("二类人数") export_title.append("排名") it = it + 2 sort_id_list.append(it) # 统计排名 for i in sort_id_list: export_data.sort(key=itemgetter(i - 1), reverse=True) it = 1 old_result = export_data[0][i - 1] old_paiming = 1 for row in export_data: if old_result == row[i - 1]: row[i] = old_paiming else: row[i] = it old_result = row[i - 1] old_paiming = row[i] it = it + 1 pass pass # 按班级排序 export_data.sort(key=itemgetter(0)) # 统计全校人平 total_school_row = [] for i in range(len(export_title)): if i == 0: total_school_row.append("全校人平") else: total_school_row.append("") pass for i in sort_id_list: sum_result = 0 for row in export_data: sum_result = sum_result + row[i - 1] total_school_row[i - 1] = sum_result / len(class_list) pass total_school_row[-2] = total_school_row[-2] * len(class_list) total_school_row[-4] = total_school_row[-4] * len(class_list) export_data.append(total_school_row) ############################################################## # 导出数据 out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path( ["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") for it in range(len(export_title)): worksheet.write(0, it, export_title[it]) for i in range(len(export_data)): data_row = export_data[i] for j in range(len(data_row)): worksheet.write(i + 1, j, data_row[j]) pass workbook.close() return jsonify({"msg": msg, "filename": out_file_name})
def exportData(): if request.method == "POST": msg = "成功" f = request.files["file"] filename = current_time() + secure_filename("".join( lazy_pinyin(f.filename))) # 保存文件 upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) # 打开文件 data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) # 获取要导出的列 items = { "姓名": -1, "考号": -1, "班级": -1, "总分": -1, "语文": -1, "数学": -1, "英语": -1, "物理": -1, "化学": -1, "道法": -1, "历史": -1, "地理": -1, "生物": -1, } item_title = [ "考号", "班级", "姓名", "语文", "数学", "英语", "物理", "化学", "生物", "历史", "地理", "道法", "总分", ] item_id_list = request.form.get("item_id_list").split(",") it = 0 for item in items: items[item] = int(item_id_list[it]) - 1 it = it + 1 if items[item] == -1: item_title.remove(item) pass # 获取数据 data_table = [] index_data = int(request.form.get("index_data")) - 1 for i in range(index_data, table.nrows): row_value = table.row_values(i) data_row = [] for item in item_title: if row_value[items[item]] == "": data_row.append(0.0) else: data_row.append(row_value[items[item]]) pass data_table.append(data_row) pass os.remove(upload_path) # 计算排名 # 找到总分 index_total = -1 for i in range(len(item_title)): if "总分" == item_title[i]: index_total = i pass item_title.append("学校排名") # 按总分排序 data_table.sort(key=itemgetter(index_total), reverse=True) # data_table = sorted(data_table, key=( # lambda x: x[index_total]), reverse=True) for i in range(len(data_table)): if i == 0: data_table[i].append(i + 1) else: if data_table[i][index_total] == data_table[i - 1][index_total]: data_table[i].append(data_table[i - 1][-1]) else: data_table[i].append(i + 1) pass pass # 找到班级 index_class = -1 for i in range(len(item_title)): if "班级" == item_title[i]: index_class = i pass item_title.append("班级排名") # 按班级排序 data_table.sort(key=itemgetter(index_class)) for elt, items in groupby(data_table, itemgetter(index_class)): it = 1 totol_old = 0 for i in items: if it == 1: i.append(it) totol_old = i[index_total] else: if totol_old == i[index_total]: i.append(i[-1]) else: i.append(it) pass it = it + 1 pass pass # 导出数据 out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path( ["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") it = 0 for it in range(len(item_title)): worksheet.write(0, it, item_title[it]) for i in range(len(data_table)): data_row = data_table[i] for j in range(len(data_row)): worksheet.write(i + 1, j, data_row[j]) pass workbook.close() return jsonify({"msg": msg, "filename": out_file_name})
def search_data(): # 获取要查询的表 table_list = request.args.getlist("select_table_list[]") # 获取要查询的年份和分校 year = request.args.get("year", type=str) school = request.args.get("school", type=str) # 获取要查询的项 select_item = {} select_item_list = request.args.getlist("select_item_list[]") for item in select_item_list: select_item[item] = request.args.get(item, type=str) table_list.append(select_item[item]) # 获取要查询的表 table_list = list(set(table_list)) print(table_list) # 获得要查询的字段和值 search_item_list = request.args.getlist("condition_item_list[]") search_string_list = request.args.getlist("search_string_list[]") # 拼接condition_dict字典 condition_dict = {} for i in range(0, len(search_item_list)): condition_dict[ search_item_list[i]] = " LIKE '%" + search_string_list[i] + "%'" # 查询结果 data = [] if school == "全部数据": # 查所有分校 data = select_table(table_list, year, select_item, condition_dict) else: # 查特定分校 person_id_data = select_table( ["school", "job"], year, {"person_id": "job"}, {"school_" + year + ".school_name": " = '" + school + "'"}, ) select_item["person_id"] = "person" search_data = select_table(table_list, year, select_item, condition_dict) person_id_list = [] for i in person_id_data: person_id_list.append(i["person_id"]) for i in search_data: if i["person_id"] in person_id_list: del i["person_id"] data.append(i) # 未查询到结果 if len(data) == 0: total = 0 msg = "没有查询到相关信息" return jsonify({"msg": msg, "total": total}) # 写入表格数据 item_name_dict = { "person_id": "编号", "person_name": "姓名", "gender": "性别", "id_number": "身份证号", "phone": "联系电话", "political_status": "政治面貌", "time_Party": "入党时间", "time_work": "参加工作时间", "address": "家庭住址", "resume": "个人简历", "edu_start": "第一学历", "time_edu_start": "第一学历毕业时间", "school_edu_start": "第一学历毕业学校", "major_edu_start": "第一学历专业", "edu_end": "最高学历", "time_edu_end": "最高学历毕业时间", "school_edu_end": "最高学历毕业学校", "major_edu_end": "最高学历专业", "skill_title": "专业技术职称", "time_skill": "职称取得时间", "skill_unit": "职称发证单位", "skill_number": "发证文件批号", "time_school": "调入大集中学时间", "work_kind": "用工性质", "job_post": "工作岗位", "time_retire": "退休时间", "job_name": "行政职务", "class_name": "班级名称", "lesson_number": "总课时数", "year_result": "年度考核", "school_name": "所在分校", "honor_time": "发证时间", "honor_unit": "发证单位", "honor_name": "获奖名称", "honor_grade": "证书级别", "honor_number": "证书编号", "honor_remark": "备注", "get_time": "获得时间", } out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path(["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") # 写标题 col = 0 for i in data[0]: worksheet.write(0, col, item_name_dict[i]) col = col + 1 # 写数据 for i in range(len(data)): col = 0 row = data[i] for j in row: worksheet.write(i + 1, col, row[j]) col = col + 1 workbook.close() total = len(data) msg = "查询到" + str(len(data)) + "条信息" return jsonify({"msg": msg, "total": total, "filename": out_file_name})
def exportData(): db = get_lijing_db() year = session["year_current"] person = "person_" + year clas = "class_" + year school = "school_" + year job = "job_" + year workload = "workload_" + year rank = "rank_" + year sql_search = ( "select p.person_name, class.class_name, school.school_name, job.job_name, work.lesson_number, work.year_result from " + person + " as p left join " + clas + " as class on p.person_id = class.person_id left join " + job + " as job on p.person_id = job.person_id left join " + workload + " as work on p.person_id = work.person_id left join " + school + " as school on job.school_id = school.school_id where p.person_id = ?" ) sql_rank = ( "select subject, class_name, rank_up_school, rank_up_country, rank_down_school, rank_down_country from " + rank + " as r join " + clas + " as c on r.class_id = c.class_id where r.person_id = ?" ) print(sql_rank) flag_search = request.args.get("flag_search") id_list = [] if flag_search == "true": id_list = request.args.getlist("id_list[]") else: person_data = db.execute("select person_id from " + person).fetchall() for i in person_data: id_list.append(i["person_id"]) export_item = [ "person_name", "class_name", "school_name", "job_name", "lesson_number", "year_result", ] rank_item = [ "subject", "class_name", "rank_up_school", "rank_up_country", "rank_down_school", "rank_down_country", ] table_data = [] rank_number = 0 for i in id_list: result = db.execute(sql_search, (i,)).fetchone() rank = db.execute(sql_rank, (i,)).fetchall() if len(rank) >= rank_number: rank_number = len(rank) row = [] for item in export_item: row.append(result[item]) for r in rank: for item in rank_item: row.append(r[item]) table_data.append(row) item_name_dict = { "person_name": "姓名", "class_name": "是否班主任", "school_name": "所在分校", "job_name": "行政职务", "lesson_number": "总课时数", "year_result": "年度考核", } rank_name_list = ["任教班级", "任教学科", "上学期全校排名", "上学期全县排名", "下学期全校排名", "下学期全县排名"] out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path(["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") for i in range(len(export_item)): worksheet.write(0, i, item_name_dict[export_item[i]]) num = 6 for i in range(0, rank_number): for j in range(len(rank_name_list)): worksheet.write(0, num + j, rank_name_list[j] + str(i + 1)) num = num + 6 for i in range(len(id_list)): for j in range(len(table_data[i])): worksheet.write(i + 1, j, table_data[i][j]) workbook.close() msg = str(len(id_list)) return jsonify({"msg": msg, "filename": out_file_name})
def importData(): if request.method == "POST": db = get_lijing_db() # year_select = session['year_current'] # print(session['year_current']) f = request.files["file"] year_select = request.form.get("year") item_id_list = request.form.get("item_id_list").split(",") filename = secure_filename("".join(lazy_pinyin(f.filename))) upload_path = list_to_path(["flaskr", "static", "uploads", filename]) f.save(upload_path) data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) item = [ "person_name", "school_name", "job_name", "lesson_number", "year_result", "class_id", "subject", "rank_up_school", "rank_up_country", "rank_down_school", "rank_down_country", ] update_item = {} for i in range(len(item)): if item_id_list[i] != "0": update_item[item[i]] = int(item_id_list[i]) - 1 print(update_item) person_data = db.execute( "select person_id, person_name from person_" + year_select ).fetchall() person_id_dict = {} person_name_list = [] for i in person_data: person_id_dict[i["person_name"]] = i["person_id"] person_name_list.append(i["person_name"]) for i in range(1, table.nrows): row = table.row_values(i) person_name = float_int_string(row[update_item["person_name"]]) if person_name not in person_name_list: return jsonify({"msg": "教师姓名:“" + person_name + "” 不存在"}) # 表中有不存在基本信息的教师 person_id = person_id_dict[person_name] if "lesson_number" in update_item: lesson_number = float_int_string(row[update_item["lesson_number"]]) if ( db.execute( "select workload_id from workload_" + year_select + " where person_id = ?", (person_id,), ).fetchone() is None ): db.execute( "insert into workload_" + year_select + " (lesson_number, year_result, person_id) values (?,?,?)", ( lesson_number, "", person_id, ), ) else: db.execute( "update workload_" + year_select + " set lesson_number = ? where person_id = ?", ( lesson_number, person_id, ), ) if "year_result" in update_item: year_result = float_int_string(row[update_item["year_result"]]) if ( db.execute( "select workload_id from workload_" + year_select + " where person_id = ?", (person_id,), ).fetchone() is None ): db.execute( "insert into workload_" + year_select + " (lesson_number, year_result, person_id) values (?,?,?)", ( "", year_result, person_id, ), ) else: db.execute( "update workload_" + year_select + " set year_result = ? where person_id = ?", ( year_result, person_id, ), ) if "school_name" in update_item: school_name = float_int_string(row[update_item["school_name"]]) school_id_data = db.execute( "select school_id from school_" + year_select + " where school_name = ?", (school_name,), ).fetchone() if school_id_data is None: return jsonify({"msg": "分校:“" + school_name + "” 不存在"}) school_id = school_id_data["school_id"] db.execute( "update job_" + year_select + " set school_id = ? where person_id = ?", ( school_id, person_id, ), ) if "job_name" in update_item: job_name = float_int_string(row[update_item["job_name"]]) if job_name == "": job_name = "无" db.execute( "update job_" + year_select + " set job_name = ? where person_id = ?", ( job_name, person_id, ), ) if "class_id" in update_item: school_name = row[update_item["school_name"]] school_id = db.execute( "select school_id from school_" + year_select + " where school_name = ?", (school_name,), ).fetchone()["school_id"] class_name = float_int_string(row[update_item["class_id"]]) class_id_data = db.execute( "select class_id from class_" + year_select + " where school_id = ? and class_name = ? ", ( school_id, class_name, ), ).fetchone() if class_id_data is None: return jsonify( {"msg": "分校“" + school_name + "”中不存在班级:“" + class_name + "”"} ) class_id = class_id_data["class_id"] subject = row[update_item["subject"]] rank_data = db.execute( "select rank_up_school from rank_" + year_select + " where person_id = ? and subject = ? and class_id = ?", ( person_id, subject, class_id, ), ).fetchone() if rank_data is None: db.execute( "insert into rank_" + year_select + " (subject, class_id, person_id, rank_up_school, rank_up_country, rank_down_school, rank_down_country) values (?,?,?,?,?,?,?)", ( subject, class_id, person_id, "暂无", "暂无", "暂无", "暂无", ), ) if "rank_up_school" in update_item: rank_up_school = float_int_string(row[update_item["rank_up_school"]]) db.execute( "update rank_" + year_select + " set rank_up_school = ? where subject = ? and person_id = ? and class_id = ?", ( rank_up_school, subject, person_id, class_id, ), ) if "rank_up_country" in update_item: rank_up_country = float_int_string(row[update_item["rank_up_country"]]) db.execute( "update rank_" + year_select + " set rank_up_country = ? where subject = ? and person_id = ? and class_id = ?", ( rank_up_country, subject, person_id, class_id, ), ) if "rank_down_school" in update_item: rank_down_school = float_int_string( row[update_item["rank_down_school"]] ) db.execute( "update rank_" + year_select + " set rank_down_school = ? where subject = ? and person_id = ? and class_id = ?", ( rank_down_school, subject, person_id, class_id, ), ) if "rank_down_country" in update_item: rank_down_country = float_int_string( row[update_item["rank_down_country"]] ) db.execute( "update rank_" + year_select + " set rank_down_country = ? where subject = ? and person_id = ? and class_id = ?", ( rank_down_country, subject, person_id, class_id, ), ) db.commit() os.remove(upload_path) return jsonify({"msg": "成功导入"})
def exportData(): db = get_lijing_db() year = session["year_current"] sql = ( "select p.person_name, school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark from person_" + year + " as p left join honor_" + year + " as h on p.person_id = h.person_id where p.person_id = ?") flag_search = request.args.get("flag_search") id_list = [] if flag_search == "true": id_list = request.args.getlist("id_list[]") else: person_data = db.execute("select person_id from person_" + year).fetchall() for i in person_data: id_list.append(i["person_id"]) export_item = [ "person_name", "honor_name", "honor_grade", "honor_time", "honor_number", "honor_unit", "get_time", "school_name", "honor_remark", ] table_data = [] for i in id_list: result = db.execute(sql, (i, )).fetchall() row = [] for r in result: for item in export_item: row.append(r[item]) table_data.append(row) item_name_dict = { "person_name": "姓名", "honor_name": "获奖名称", "honor_grade": "证书级别", "honor_time": "发证时间", "honor_number": "证书编号", "honor_unit": "发证单位", "get_time": "获奖时间", "school_name": "获奖时所在分校", "honor_remark": "备注", } out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path(["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") for i in range(len(export_item)): worksheet.write(0, i, item_name_dict[export_item[i]]) for i in range(len(table_data)): for j in range(len(export_item)): worksheet.write(i + 1, j, table_data[i][j]) workbook.close() msg = str(len(table_data)) return jsonify({"msg": msg, "filename": out_file_name})
def importData(): if request.method == "POST": db = get_lijing_db() # year_select = request.form.get('year_select') year_select = session["year_current"] f = request.files["file"] filename = secure_filename("".join(lazy_pinyin(f.filename))) if filename.endswith(".xlsx"): upload_path = list_to_path( ["flaskr", "static", "uploads", filename]) f.save(upload_path) data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) print("总行数:" + str(table.nrows)) print("总列数:" + str(table.ncols)) # 找到标题 dict_title = { "姓名": 0, "所在分校": 1, "发证时间": 2, "获得时间": 3, "发证单位": 4, "获奖名称": 5, "证书级别": 6, "证书编号": 7, "备注": 8, } row_title = table.row_values(0) title_id = [-1 for i in range(9)] for i in range(0, len(row_title)): title_name = row_title[i] if title_name in dict_title: title_id[dict_title[title_name]] = i # 教师基本信息 person_data = db.execute( "select person_id, person_name from person_" + year_select).fetchall() person_id_dict = {} # person_name_list = [] for i in person_data: person_id_dict[i["person_name"]] = i["person_id"] # person_name_list.append(i['person_name']) # 导入数据 for i in range(1, table.nrows): row_value = table.row_values(i) person_name = float_int_string(row_value[title_id[0]]) if person_name not in person_id_dict: error = "教师姓名:“" + person_name + "” 不存在" flash(error) return redirect(url_for("lijing_honorinfo.hello")) # 表中有不存在基本信息的教师 person_id = person_id_dict[person_name] print(person_id, person_name) insert_data = [0 for i in range(9)] for j in range(0, len(title_id)): if title_id[j] == -1: insert_data[j] = "待添加" else: insert_data[j] = float_int_string( row_value[title_id[j]]) if len(insert_data[j]) == 0: insert_data[j] = "待添加" print(insert_data) db.execute( "insert into honor_" + year_select + " (person_id, school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark) values (?,?,?,?,?,?,?,?,?)", ( person_id, insert_data[1], insert_data[2], insert_data[3], insert_data[4], insert_data[5], insert_data[6], insert_data[7], insert_data[8], ), ) db.commit() os.remove(upload_path) return redirect(url_for("lijing_honorinfo.hello")) else: error = "请导入xlsx格式的文件" flash(error) return redirect(url_for("lijing_honorinfo.hello"))
def calculator_bonus(): if request.method == "POST": msg = "成功" # 接收文件 f = request.files["file"] filename = secure_filename("".join(lazy_pinyin(f.filename))) # 接收参数 item_value = request.form.get("item_value").split(",") item_subject = [ "chinese", "math", "english", "wl", "hx", "zz", "ls", "dl", "sw", ] item = [ "chinese", "math", "english", "wl", "hx", "zz", "ls", "dl", "sw", "class1", "class2", "bonus1", "bonus2", "ebonus1", "ebonus2", "bonus_average", "bonus3", ] # 参数字典 value_dict = {} for i in range(len(item)): value_dict[item[i]] = float(item_value[i]) # 科目系数 item_coefficient = [] for i in item_subject: item_coefficient.append(value_dict[i]) # 判断文件格式 if filename.endswith(".xlsx"): # 保存文件 upload_path = list_to_path( ["flaskr", "static", "uploads", filename]) f.save(upload_path) # 打开文件 data = xlrd.open_workbook(upload_path) table = data.sheet_by_index(0) # 标题栏 table_title = table.row_values(0) item_average = [ "语文人平", "数学人平", "英语人平", "物理人平", "化学人平", "道法人平", "历史人平", "地理人平", "生物人平", ] item_teacher = [ "语文教师", "数学教师", "英语教师", "物理教师", "化学教师", "道法教师", "历史教师", "地理教师", "生物教师", ] # 日志数据 log = {} # 奖金数据 bonus_dict = {} # 标题序号 id_dict = {} for i in range(len(table_title)): if table_title[i] == "班级": id_dict["班级"] = i if table_title[i] in item_average: id_dict[table_title[i]] = i if table_title[i] in item_teacher: id_dict[table_title[i]] = i if "一类" in table_title[i]: id_dict["一类"] = i if "二类" in table_title[i]: id_dict["二类"] = i # 检查是否有班级数据 if "班级" not in id_dict: msg = "找不到班级数据,读取失败\n" return jsonify({"msg": msg}) # 获取班级数据 class_list = [] for i in range(1, table.nrows): table_row = table.row_values(i) class_list.append(float_int_string(table_row[id_dict["班级"]])) class_list.remove("全校人平") # 获取老师列表 teacher_list = [] for item in item_teacher: for i in range(1, table.nrows): table_row = table.row_values(i) if item in id_dict: if table_row[id_dict[item]] != "": teacher_list.append(table_row[id_dict[item]]) teacher_list = list(set(teacher_list)) print(teacher_list) teacher_list = sorted(teacher_list) # 平均分奖金计算 # 按九个科目依次计算 for i in range(9): # 没有某科目老师 if item_teacher[i] in id_dict: teacher_id = id_dict[item_teacher[i]] else: continue # 没有某科目平均分 if item_average[i] in id_dict: average_id = id_dict[item_average[i]] else: continue # 拿到某科目全校平均分 average_total = table.row_values(table.nrows - 1)[average_id] # 计算每行数据 for row in range(1, table.nrows - 1): table_row = table.row_values(row) class_name = table_row[id_dict["班级"]] teacher = table_row[teacher_id] average = table_row[average_id] diff = round((average - average_total), 1) # 日志 log_string = [] log_string.append(float_int_string(class_name) + "班") log_string.append(item_average[i]) log_string.append(str(average)) log_string.append(str(average_total)) log_string.append("%.2f-%.2f=%.2f" % (average, average_total, diff)) # 判断是否有老师奖金的信息 if teacher in bonus_dict: sum = bonus_dict[teacher] else: # 没有某老师信息则初始化 log[teacher] = teacher + "\n" sum = 0 # 计算基本奖金:100*系数 basic = value_dict["bonus_average"] * item_coefficient[i] log_string.append("%.2f*%.2f=%.2f" % (value_dict["bonus_average"], item_coefficient[i], basic)) # 计算额外奖金:多一分8元 addition = value_dict["bonus3"] * diff log_string.append("%.2f*%.2f=%.2f" % (value_dict["bonus3"], diff, addition)) # 奖金相加 bonus_dict[teacher] = sum + basic + addition log_string.append( "%.2f+%.2f=%.2f" % (sum, basic + addition, bonus_dict[teacher])) # 保存日志 log[teacher] = log[teacher] + "\t".join(log_string) + "\n" # 计算系数和 total_coefficient = 0 for i in range(len(item_teacher)): if item_teacher[i] in id_dict: total_coefficient = total_coefficient + item_coefficient[i] total_coefficient = round(total_coefficient, 2) # 计算尖子生奖金 # 按每行一个班级计算 for i in range(1, table.nrows - 1): # 日志 log_string = [] table_row = table.row_values(i) class_name = float_int_string(table_row[id_dict["班级"]]) + "班" log_string.append(class_name) # 尖子生个数 class1 = table_row[id_dict["一类"]] class2 = table_row[id_dict["二类"]] log_string.append("一类:%d个,二类:%d个" % (class1, class2)) # 尖子生差距 diff1 = class1 - value_dict["class1"] diff2 = class2 - value_dict["class2"] # 尖子生奖金 bonus1 = value_dict["bonus1"] + value_dict["ebonus1"] * diff1 bonus2 = value_dict["bonus2"] + value_dict["ebonus2"] * diff2 log_string.append("一类奖金:%d,二类奖金:%d" % (bonus1, bonus2)) # 总奖金 bonus_total = bonus1 + bonus2 log_string.append("总奖金:%d" % (bonus_total)) # 计算某个班级的老师奖金 for j in range(len(item_teacher)): # 没有某科目老师 if item_teacher[j] not in id_dict: continue # 老师名称 teacher_id = id_dict[item_teacher[j]] teacher = table_row[teacher_id] log_string.append("\n\t%s:%s" % (item_teacher[j], teacher)) # 奖金比例 ratio = item_coefficient[j] / total_coefficient log_string.append( "%.2f/%.2f=%.2f" % (item_coefficient[j], total_coefficient, ratio)) # 尖子生奖金 bonus_class = bonus_total * ratio log_string.append("%.2f*%.2f=%.2f" % (bonus_total, ratio, bonus_class)) # 汇入奖金数据 origin = bonus_dict[teacher] bonus_dict[teacher] = origin + bonus_class log[teacher] = log[teacher] + "%s %.2f+%.2f=%.2f\n" % ( class_name, origin, bonus_class, bonus_dict[teacher], ) # 日志保存 log[class_name] = "\t".join(log_string) + "\n" # 输出数据到表格 out_file_name = "exportData(%s).xlsx" % current_time() save_path = list_to_path( ["flaskr", "static", "downloads", out_file_name]) workbook = xlsxwriter.Workbook(save_path) worksheet = workbook.add_worksheet("Sheet1") worksheet.write(0, 0, "教师") worksheet.write(0, 1, "奖金") for i in range(len(teacher_list)): teacher = teacher_list[i] worksheet.write(i + 1, 0, teacher) # 四舍五入 bonus_dict[teacher] = int(bonus_dict[teacher] + 0.5) worksheet.write(i + 1, 1, bonus_dict[teacher]) workbook.close() os.remove(upload_path) else: msg = "请导入xlsx格式的文件\n" return jsonify({"msg": msg}) return jsonify({ "msg": msg, "class_list": class_list, "teacher_list": teacher_list, "log": log, "filename": out_file_name, })