import openpyxl
import numpy as np

# 定义数据范围
data_range = "C5:R39"

# 定义源文件名称数组
source_files = ["./1.xlsx", "./2.xlsx", "./3.xlsx", "./4.xlsx", "./5.xlsx", "./6.xlsx", "./7.xlsx", "./8.xlsx", "./9.xlsx"]

# 定义目标文件名称
target_file = "./total.xlsx"

# 打开目标文件，如果不存在则创建一个新的工作簿
try:
    target_wb = openpyxl.load_workbook(target_file)
    target_ws = target_wb.active
except FileNotFoundError:
    target_wb = openpyxl.Workbook()
    target_ws = target_wb.active

# 解析数据范围
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):
        cell_values = []
        
        # 从每个源文件中读取对应位置的数据
        for file in source_files:
            wb = openpyxl.load_workbook(file)
            ws = wb.active
            cell_value = ws.cell(row=row, column=col).value
            if cell_value is not None:
                cell_values.append(cell_value)
        
        if len(cell_values) > 0:
            print(f"Processing cell ({row}, {col}): Data = {cell_values}")
        
        # 去掉最大值和最小值后求平均值
        if len(cell_values) > 2:  # 确保有足够的数据进行操作
            max_value = max(cell_values)
            min_value = min(cell_values)
            cell_values.remove(max_value)
            cell_values.remove(min_value)
            average_value = np.mean(cell_values)
            
            print(f"Max value: {max_value}, Min value: {min_value}, Average after removal: {average_value}")
        elif len(cell_values) == 2:  # 如果只有两个值，则直接取平均值
            max_value = max(cell_values)
            min_value = min(cell_values)
            average_value = np.mean(cell_values)
            print(f"Only two values: Max = {max_value}, Min = {min_value}, Average: {average_value}")
        elif len(cell_values) == 1:  # 如果只有一个值，则直接使用该值
            average_value = cell_values[0]
            print(f"Only one value: {average_value}")
        else:
            average_value = None  # 如果没有数据，设为None
        
        # 将计算结果写入目标文件的对应位置
        target_ws.cell(row=row, column=col, value=average_value)

# 保存目标文件
target_wb.save(target_file)