# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
import pandas as pd
import xlwings
import os
import sys
import argparse

def compare():
          
     print('Enter the path of the source excel file')
     path_s = input('Path 1 : ')
     
     print('Enter the path of the dest excel file')
     path_d = input('Path 2 : ')
     
     print('Enter the source sheet name')
     sheet_s = input('Source Sheet : ')
     
     print('Enter the dest sheet name')
     sheet_d = input('Dest Sheet : ')
     
     print('Enter the First and last cell for source')
     e_source=input('first cell : ')
     s_source=input('last cell : ')
     
     print('Enter the First and last cell for dest')
     e_dest=input('first cell : ')
     s_dest=input('last cell : ')
     
     wb_dest = xlwings.Book(path_d)
     wb_source = xlwings.Book(path_s)
     
     
     sheet_dest = wb_dest.sheets[sheet_d]
     sheet_source = wb_source.sheets[sheet_s]
     
     df_source = sheet_source[e_source+':'+s_source].options(pd.DataFrame,index = False, header=True).value
     df_dest = sheet_dest[e_dest+':' +s_dest].options(pd.DataFrame, index=False, header=True).value
     
     
     list_headers = list(df_source)
     #print(list_headers)
     n_l =[]
     for col in list_headers:
          col=col.replace('\n',"")
          n_l.append(col)
     #print(list_headers)
     print(n_l)
     df_source = df_source.rename(columns = dict(zip(df_source.columns,n_l)))
     #print(list(df_source))
     df_dest = df_dest.rename(columns = dict(zip(df_dest.columns,n_l)))
     #print(list(df_dest)) 
     print('\nRefer the string above\n')
     print('\nEnter the coloumn name of key as it is shown above\n')
     c_add = input('col name : ')
     print('\nEnter the coloumn names of the dest separeted by commas\n')
     var = input('col names : ').split(",")
     
     d={}
     for i in range(1,len(df_source)): 
         s = df_source.loc[i,c_add]
         l=[]
         for x in range(0,len(var)) :
             l.append(df_source.loc[i,var[x]])
         d[s] = l
         
         if(i00==0):
              print(str(100*i/(len(df_source)))+"% Data copied")
     print("100 % Data copied")   
     for j in range(1,len(df_dest)) :
         s = df_dest.loc[j,c_add]
         if s in d:
             temp=d[s]
             for x in range(0,len(temp)) :
                 df_dest.loc[df_dest[c_add] == s,var[x]] = temp[x]
         else :
             df_dest.loc[df_dest[c_add] == s,var[x]] = 'NAN'
             
         if(j00==0):
              print(str(100*j/(len(df_dest)))+"% Data pasted")
              
     print("100 % Data pasted") 
     df_dest.rename(columns = dict(zip(df_dest.columns,list_headers)))        
     sheet_dest[e_dest+':' +s_dest].options(pd.DataFrame, index=False, header=True).value = df_dest 
     
     print(" Data verified and copied.")

def main(cmd_args=sys.argv[1:]):
    if cmd_args[0]=="-h" :
         print("-r  :   To run the script.\n" 
               "-a  :   To see parameters description")
    if cmd_args[0]=="-r" :
         compare()
    if cmd_args[0]=="-a" :
         print("Path        :   Give full path of the source file in the described format,\n"
                               "your\\path\\to\\input\\file")
         print("Sheet_Name  :   Give exact sheet name. Name is case sensitive.")
         print("First Cell  :   Address of the cell from which the data in the sheet  begins.\n"
                                " Address is alphanumeric with row number appended after column name\n"
                                "Example : F10, E1221")

if __name__ == '__main__':
    main()