##############################################################################
# imports required for the script to work
import openpyxl
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill
import sys
import time
import os
import glob
###############################################################################

os.chdir(".")       # bound path to the current directory






wb = openpyxl.load_workbook("test.xlsx")
userSheet = wb.get_sheet_by_name("user")
bankSheet = wb.get_sheet_by_name("bank")

C = userSheet["C"]
B = userSheet["B"]
IDS = []
LEAVES= []
for e in C:
    LEAVES.append(e.value)
print("\n")
for e in B:
    IDS.append(e.value)


IDS_LEAVES = dict(zip(IDS, LEAVES))  # create a dict() with keys = IDS and values = LEAVES

IDS_LEAVES.pop("ID")  # get rid of the first key-pair since it contains "headings" of the sheets
print(IDS_LEAVES)       # make sure we got the correct key-pairs in our dict()

bankID = bankSheet["B"]     # gives tuples of everything in column B of our bank sheet
list_bankID = list(bankID) # convert  to list so we can pop the first element which contains header
list_bankID.pop(0)  #pop header

bankLV = bankSheet["C"]         #get tuples of everything in column C
list_bankLV = list(bankLV)  # convert to list so we can pop header aka first element
list_bankLV.pop(0)          # pop the header

for i in range(1,len(bankLV)):   #for every item in the leaves column of sheet bank
    if bankID[i].value in IDS_LEAVES.keys():   #check if the item exist in the keys of our dict()
            try:
                bankLV[i].value = IDS_LEAVES[bankID[i].value]   # populate with the correct key
            except:
                None

wb.save("ready.xlsx")             # all ready
print("Exiting...")