import openpyxl
import openmatrix as omx

#Caricamento delle matrici O/D dal file OMX
OMXfile = omx.open_file('IMQ2013_zonecampionarie.omx',"r")
matriciOMX = OMXfile.list_matrices()

#Caricamento dei vettori delle zone
zone_exceldoc = openpyxl.load_workbook('ZoneCampionarie2013.xlsx')
zone_nsheet = zone_exceldoc.sheetnames 
zone_sheet = zone_exceldoc[zone_nsheet[0]]  
zoneA = list()   
for j in range(0,209):
    zoneA.append(zone_sheet.cell(row=j+2,column=2).value)

#Apertura del file Excel contenente le tabelle pivot
PIV_exceldoc = openpyxl.load_workbook('IMQ2013_TabellePivot.xlsx')
PIV_nsheet = PIV_exceldoc.sheetnames

#Calcolo del RMSE
print('- VERIFICA DEL CODICE DI SCRITTURA DEL FILE OMX PER ZONE CAMPIONARIE-')
print('\nTabella Pivot - Matrice OMX:\tRMSE')
for mat in range(0,12):
    PIV = PIV_exceldoc[PIV_nsheet[mat]]
    somma = 0
    for i in range(0,209):
        for j in range(0,209):
            fPIV = PIV.cell(row=i+2,column=j+2).value
            try:
                float(fPIV)
            except:
                try:
                    int(fPIV)
                except:
                    fPIV = 0.0
            OR = PIV.cell(row=i+2,column=1).value
            DE = PIV.cell(row=1,column=j+2).value
            fOMX = OMXfile[matriciOMX[mat]][zoneA.index(OR)][zoneA.index(DE)]
            somma = somma+(fPIV-fOMX)**2
    RMSE = (somma/(209*209))**(0.5)
    print(PIV_nsheet[mat],'-',matriciOMX[mat],'\t',RMSE)
            
#Chiusura del file aperto
OMXfile.close()  