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()