import xlrd
import xlwt
from xlutils.copy import copy
 
def info_row_col_execl(file_name):
  myworkbook = xlrd.open_workbook(file_name)
  mySheetlist = myworkbook.sheets()
  mysheet = mySheetlist[0]
  nrows = mysheet.nrows
  ncols = mysheet.ncols
  return nrows,ncols
 
def read_execl(filename):
  myworkbook = xlrd.open_workbook(filename)
  mySheetlist = myworkbook.sheets()
  mysheet = mySheetlist[0]
  return mysheet
 
def com_exel(file_name_dai,file_name_all):
  dai_rows, dai_cols = info_row_col_execl(file_name_dai)
  all_rows,all_cols = info_row_col_execl(file_name_all)
  print(dai_rows, dai_cols)
  print(all_rows,all_cols)
 
def read_execl_values(file_name):
  list_execl_values = []
  mysheet = read_execl(file_name)
  execl_rows, execl_cols = info_row_col_execl(file_name)
  myRowValues_name= mysheet.row_values(0)
  for nrow in range(1,execl_rows):
    myRowValues= mysheet.row_values(nrow)
    #print(myRowValues)
    myRow_name_value=dict(zip(myRowValues_name,myRowValues))
    list_execl_values.append(myRow_name_value)
  return list_execl_values
 
def com_execl_values(file_name_dai,file_name_all,key_list):
  dai_list_execl_values = read_execl_values(file_name_dai)
  all_list_execl_values = read_execl_values(file_name_all)
  row_num = 1
  for i in dai_list_execl_values:
    dai_list_execl_value = i
    dai_len_row = len(dai_list_execl_value)
    for k in all_list_execl_values:
      all_list_execl_value = k
      result = compare_two_dict(dai_list_execl_value,all_list_execl_value,key_list)
      if result == "Pass":
        #print(dai_list_execl_value,all_list_execl_value,result)
        print(dai_list_execl_value['ID'],dai_list_execl_value['名字'],)
        print(all_list_execl_value['班級'],all_list_execl_value['教師'])
        write_execl(file_name_dai,all_list_execl_value['班級'],all_list_execl_value['教師'],row_num,dai_len_row)
        break;
    row_num = row_num + 1   
   
def write_execl(file_name_dai,all_list_execl_value_1,all_list_execl_value_2,row_num,dai_len_row):
  open_execl = xlrd.open_workbook(file_name_dai)
  open_execl_copy = copy(open_execl)
  open_execl_copy_sheet = open_execl_copy.get_sheet(0)
  open_execl_copy_sheet.write(row_num,dai_len_row-3,all_list_execl_value_1)
  open_execl_copy_sheet.write(row_num,dai_len_row-2,all_list_execl_value_2)
  open_execl_copy.save(file_name_dai)
   
 
def compare_two_dict(dai_dict,all_dict,key_list):
  flag = True
  dai_dict_key = dai_dict.keys()
  all_dict_key = all_dict.keys()
  if len(key_list) != 0:
    for key in key_list:
      if key in dai_dict_key and key in all_dict_key:
        if dai_dict[key] == all_dict[key]:
          #print(dai_dict[key],all_dict[key])
          flag = flag & True
        else:
          flag = flag & False
      else:
        raise Exception('key_list contains error key')
  else:
    raise Exception('key_list is null')
 
  if flag:
    result = "Pass"
  else:
    result = "Failed"
  #print(result)
  return result
 
if __name__ == '__main__':
  file_name_dai = 'name_1.xlsx'
  file_name_all = 'name.xlsx'
  key_list = ['ID', '名字']
  com_execl_values(file_name_dai,file_name_all,key_list)