import openpyxl
import numpy as np
import matplotlib.pyplot as plt


def excel_range(filename, r):
    wb = openpyxl.load_workbook(filename)
    sheet = wb[wb.sheetnames[0]]
    return [[cell.value for cell in sheet[r][i]] for i in range(len(sheet[r]))]


led_sp = np.float32(excel_range("Spectrum.xlsx", "B2:AF2")[0])
cmf_sp = np.float32(excel_range("Spectrum.xlsx", "B3:AF5"))
filter_sp = np.float32(excel_range("Spectrum.xlsx", "B6:AF8"))
tcc_sp = np.float32(excel_range("handy_tcc.xlsx", "F2:AJ25")) / 100
# print("led=", led_sp)

sum_y = led_sp.dot(cmf_sp[1])
k = 100.0 / sum_y

tcc_xyz = np.zeros((24, 3), np.float32)
for patch_no in range(24):
    tcc_xyz[patch_no, 0] = (led_sp * tcc_sp[patch_no]).dot(cmf_sp[0]) * k
    tcc_xyz[patch_no, 1] = (led_sp * tcc_sp[patch_no]).dot(cmf_sp[1]) * k
    tcc_xyz[patch_no, 2] = (led_sp * tcc_sp[patch_no]).dot(cmf_sp[2]) * k
    # print(patch_no, tri_x, tri_y, tri_z, sep="\t")
print("tcc_xyz=")
print(tcc_xyz)

white_patch = 0
white_balance = np.float32([240, 240, 240])
raw_rgb = np.float32(
    [(led_sp * tcc_sp[white_patch]).dot(filter_sp[i]) for i in range(3)]
)
print("raw_rgb=", raw_rgb)
rgb_gain = white_balance / raw_rgb
print("rgb_gain=", rgb_gain)
tcc_rgb = np.zeros((24, 3), np.float32)
for patch_no in range(24):
    tcc_rgb[patch_no, 0] = (led_sp * tcc_sp[patch_no]).dot(filter_sp[0]) * rgb_gain[0]
    tcc_rgb[patch_no, 1] = (led_sp * tcc_sp[patch_no]).dot(filter_sp[1]) * rgb_gain[1]
    tcc_rgb[patch_no, 2] = (led_sp * tcc_sp[patch_no]).dot(filter_sp[2]) * rgb_gain[2]
    # print(patch_no, tri_x, tri_y, tri_z, sep="\t")
print("tcc_rgb=")
print(tcc_rgb)