import sys
import System
import clr
import _commands
clr.AddReference("AppCommandContext"), clr.AddReference("QuantUIScriptIF")
clr.AddReference("QuantUIScriptIFImpls"), clr.AddReference("System.Windows.forms")
sys.path.append('C:\Program Files (x86)\IronPython 2.7')
sys.path.append('C:\Program Files (x86)\IronPython 2.7\Lib')
from Agilent.MassSpectrometry.DataAnalysis.LibraryEdit.Commands import CompoundProperty
import csv
import System.Windows.Forms as WinForms

with open('C:\Users\cse\Desktop\RIKey2.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    CNsN=[]
    CASsN=[]
    RIsN=[]
    for row in reader:
        CNsN.append(row['CompoundName'])
        CASsN.append(row['CASNumber'])
        RIsN.append(row['RetentionIndex'])
        
countO=LibraryAccess.CompoundCount
countMatch=0
NotUpdated=[]
for i in range(countO):
    CidO = LibraryAccess.GetCompoundId(i)
    CASO = LibraryAccess.GetCompoundProperty(CidO, "CASNumber")
    CNO = LibraryAccess.GetCompoundProperty(CidO, "CompoundName")
    if CASO in CASsN:
        countMatch+=1
        j=CASsN.index(CASO)
        RIvalue=RIsN[j]
        _commands.SetCompoundProperty(CidO, "RetentionIndex", RIvalue)
    else:
    
        NotUpdated.append(CNO)
        
WinForms.MessageBox.Show(
"Number of Compounds in Library: " + str(countO) + "\n" +
"Number of RI's updated: " + str(countMatch) + "\n" +
"Compounds not updated: " + str(NotUpdated) , "Summary" )