LiuShen / 从xlsx获取加权成绩

import openpyxl # 定义数据范围 data_range = "C5:R39" # 定义源文件名称数组 source_files = ["./自评总表.xlsx", "./互评总表.xlsx", "./辅导员评分.xlsx", "./最终总表.xlsx"] # 打开所有源文件 workbooks = [openpyxl.load_workbook(file) for file in source_files] worksheets = [wb.active for wb in workbooks] # 解析数据范围 start_cell, end_cell = data_range.split(':') start_row, start_col = openpyxl.utils.coordinate_to_tuple(start_cell) end_row, end_col = openpyxl.utils.coordinate_to_tuple(end_cell) print(f"Data range: ({start_row}, {start_col}) to ({end_row}, {end_col})") # 遍历数据范围内的每个单元格，计算并存储结果 for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1): # 获取每个源文件中的对应值 value1 = worksheets[0].cell(row=row, column=col).value or 0 value2 = worksheets[1].cell(row=row, column=col).value or 0 value3 = worksheets[2].cell(row=row, column=col).value or 0 # 按照权重进行加权计算 result = value1 * 0.1 + value2 * 0.6 + value3 * 0.3 # 输出计算过程 print(f"Processing cell ({row}, {col}): {value1}*0.1 + {value2}*0.6 + {value3}*0.3 = {result}") # 将结果存储到第四个文件的对应单元格 worksheets[3].cell(row=row, column=col, value=result) # 保存结果到表4中 workbooks[3].save(source_files[3]) # 关闭所有工作簿 for wb in workbooks: wb.close()