Issues with in try except and excel spreadsheet

padawanwebdev at gmail.com padawanwebdev at gmail.com
Mon Mar 13 16:37:26 EDT 2017


On Monday, March 13, 2017 at 11:10:36 AM UTC-7, Rhodri James wrote:
> On 13/03/17 17:40, padawanwebdev at gmail.com wrote:
> > Hello, I'm having a problem with a try except inside a while loop. The problem I see occuring has to do with an excel file the script tries to write to while the excel file is open. The exception captures and gives an error:
> >
> > OError: [Errno 13] Permission denied:'C:\\Users\\Administrator\\Desktop\\Version5.0\\DeviceTrackerReport45.xlsx'
> >
> > This is expected and is not the problem. However, the issue occurs when I close the excel file. As an example, if I run the script and the excel file is open thru 3 iterations, meaning that the script can't write to the excel file until the excel file is closed, then after the excel file is closed the script prints to the excel file 3 times. I don't need it to write to the excel file 3 times. I only need it to write just once. If the excel file is closed before I run the script than it works like its suppose too.
> > I hope I made my case clear. I look forward to some feedback. Any would be greatly appreciated!
> 
> When you say "...the excel file is open thru 3 iterations", what do you 
> mean?  Three iterations of what?  You haven't shown us a loop, so it's 
> not obvious.
> 
> How do you know it prints (writes?) to the excel file three times? 
> Again, there's nothing in the code snippet that would tell you.
> 
> > here is part of the code:
> >
> >         connectionResults = None
> >         returnResults = InternetQualityTest.connectionTest(connectionResults)
> >         if returnResults == True:
> >             try:
> >                 execfile('assetMapping.py')
> >                 time.sleep(4)
> >                 sys.exit()
> >             except Exception as e:
> >                 print "error",e
> >                 time.sleep(20)
> 
> This looks plausible, though honestly the execfile makes me feel icky 
> for unrelated reasons.  I repeat, though; this isn't a loop, and my 
> crystal ball isn't up to telling me how it gets invoked.
> 
> > FYI: The assetMapping.py runs another module from inside, and it's this module running from assetMapping that writes to the excel file.
> >
> 
> 
> -- 
> Rhodri James *-* Kynesim Ltd

I apologize if I short-changed you with the code. It's a bigger piece of code and I thought maybe with my explanation and the piece I showed it would be enough. I was wrong. Here is the rest. I apologize in advance if there are any more confusions. 

I do appreciate your help! Thanks in advance :-)


THIS IS: Main.py

import InternetQualityTest
import assetMapping
import time
import sys



if __name__ == '__main__':
    
    while True:
        
        connectionResults = None
        returnResults = InternetQualityTest.connectionTest(connectionResults)
        if returnResults == True:
            try:
                execfile('assetMapping.py')
                time.sleep(4)
                sys.exit()
            except Exception as e:
                print "error",e
                time.sleep(20)

THIS IS: constants.py

import urllib
import socket
from openpyxl.reader.excel import load_workbook




hostname = socket.gethostname()
getip = urllib.urlopen('https://api.ipify.org').read()

errorReportWB = load_workbook(r'C:\Users\Administrator\Desktop\Version5.0\DeviceTrackerReport45.xlsx')
masterListWB = load_workbook(r'C:\Users\Administrator\Desktop\Version5.0\masterListWB.xlsx')
yardWB = load_workbook(r'C:\Users\Administrator\Desktop\Version5.0\yardWB.xlsx')

errorReportSheet = errorReportWB.get_sheet_names()
masterListSheet = masterListWB.get_sheet_names()
yardSheet = yardWB.get_sheet_names()


import constants
import DataRedundancyCheck
import assetMappingFunctions


ipCoordinate = []
deviceCoordinate = [] 

#xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx# 
#************************************************************************************************************************************#


THIS IS: assetMapping.py


## Initital Test: checks if IP is in master list
initialCheck = assetMappingFunctions.functions()                                                          #Creat object Initial_Test of Asset_Mapping_Module Class from the Asset_Mapping.py file
masterInventorySheet = initialCheck.yard(constants.masterListSheet,0,constants.masterListWB)        #Get Master list sheet name 
ipMasterInventoryCount = initialCheck.inventoryCount(masterInventorySheet,"B")                      #Get number count of IP addresses in the Master list 
ipMasterInventory = initialCheck.ipDeviceList(ipMasterInventoryCount,"B",masterInventorySheet)      #Get the list of IPs in the Masterlist 
ipMasterValidation = initialCheck.ipValidationCheck(ipMasterInventory,masterInventorySheet)         #Test if external IP address is in the Master List

if ipMasterValidation==False:
    print "IP INVALID"
    
elif ipMasterValidation == True:                                                #if ipMasterValidation from initial test returns something than program continues
##        print "IP VALIDATED"
    matchTrack = assetMappingFunctions.functions()                                    #Create object Track_Map for the class Asset_Mapping_Module found in the Asset_Mapping.py file

    for x in range(0,len(constants.yardSheet)):                                 #loop range dependent on number of sheets in yardWB.xlsx
        yardNameUnfil = constants.yardSheet[x]                                  #Get from yardWB.xlsx list of all sheet names... 
                                                                                #....Formated to print to excel spreadsheet
##            print "unfiltered yard",yardNameUnfil
        yardName = matchTrack.yard(constants.yardSheet,x,constants.yardWB)      #Get from yardWB.xlsx list of all sheet names...
                                                                                #....Unformated and used  for testing.
##            print yardName,yardNameUnfil

        ipListCount = matchTrack.inventoryCount(yardName,"B")                   #Get the number of IP addresses in each yards list                
        deviceListCount = matchTrack.inventoryCount(yardName,"C")               #Get the number Devices in each yards list
##        print ipListCount
##            print deviceListCount
##            print "\n"

#xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx# 
#************************************************************************************************************************************#    

        ipList = matchTrack.ipDeviceList(ipListCount,"B",yardName)              #Get list of IP Addresses from each yard
        deviceList = matchTrack.ipDeviceList(deviceListCount,"C",yardName)      #Get list of Devices from each yard
##            print "IP list ",ipList
##            print "Device list ",deviceList
##            print "\n"
     
#xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx# 
#************************************************************************************************************************************#    
  
        ipValidation = matchTrack.ipValidationCheck(ipList,yardName)                    #IP COORDINATE: Track IP address to yard                                   
        deviceValidation = matchTrack.deviceValidationCheck(deviceList,yardName)        #DEVICE COORDINATE: Track Device hostname to yard    
##            print "Test IP match to yard: ", ipValidation,yardName
##            print "Test Device match to yard: ",deviceValidation, yardName
##            print "\n"

#### When ipValidation makes a match of IP to a yard, the yard gets appended into a list called ipCoordinate            
        if ipValidation == True:                                                        #Test condition to isolate and append yard into a list
            ipConfirmed = ipValidation
            ipCoordinate.append(yardNameUnfil)
##                print "Filter list of yards that contains the external IP address: ",ipCoordinate,ipValidation

#### When deviceValidation makes a match of Device to a yard, the yard gets appended into a list called deviceCoordinate
        if deviceValidation == True:                                                    #Test condition to isolate and append yard into a list
            deviceConfirmed = deviceValidation
            deviceCoordinate.append(yardNameUnfil)
##                print "Filter list of yards that contains the Device Host Name: ",deviceCoordinate,deviceConfirmed
##                print "02. device validation =",constants.device_yard_coordinate             


#xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx# 
#************************************************************************************************************************************#
            
##    print "testing",deviceConfirmed,deviceCoordinate
##    if ipConfirmed and deviceConfirmed == True:
##        print None 
##        print "03. Device validation =",deviceConfirmed,"; IP traced to yard: ",deviceCoordinate

if ipCoordinate == deviceCoordinate:                                                                                    #Test if yards ipCoordinate = deviceCoordinate 
      print None 
else:
    errorReport = matchTrack.yard(constants.errorReportSheet,0,constants.errorReportWB)
    inventoryCount = matchTrack.inventoryCount(errorReport,"B")

    temp = DataRedundancyCheck.redundancy()
    ipRedundancy = (", ".join(ipCoordinate))
    deviceRedundancy = (", ".join(deviceCoordinate))
    redundancyTest = temp.dataRedundancyCheck(ipRedundancy,deviceRedundancy)
    print "Test =",redundancyTest
    if redundancyTest == True:
        print ""
##        print str(((", ".join(ipCoordinate)),(", ".join(deviceCoordinate)),errorReport,inventoryCount))
        matchTrack.entryReport((", ".join(ipCoordinate)),(", ".join(deviceCoordinate)),errorReport,inventoryCount)      #


THIS IS: DataRedundancyCheck.py


import datetime
import constants                                 
import assetMappingFunctions
from openpyxl import load_workbook



##errorReportWB = load_workbook(r'C:\Users\Administrator\Desktop\Version5.0\DeviceTrackerReport.xlsx', data_only=True)
temp = assetMappingFunctions.functions()


errorReportWB = constants.errorReportWB
cellData = errorReportWB["IP_TrackMap_Report"]
wb = temp.yard(constants.errorReportSheet,0,constants.errorReportWB)
count = temp.inventoryCount(wb,"B")

class redundancy():
    def dataRedundancyCheck(self,ipAssigned,deviceFound):
        celllist=[]
        cmpValue = 0
       
        for i in range (0,4):
            cellAdd = chr(ord("C")+i)
            cell = cellAdd + str((count+1))
            celllist.append(str(cellData[cell].value))
            data =[constants.getip,ipAssigned,deviceFound,'{:%m-%d-%Y}'.format(datetime.date.today())]
            
##            print celllist[i],"<=>",data[i]

        cmpValue = cmp (celllist,data)

        if cmpValue == 0:
            print ""
        else:
            print cmpValue
            return True


THIS IS: assetMappingFunctions.py


import constants
import datetime

class functions():


    def yard(self,sheet,sheetNumber,workBook):

##        sheetNumber = len(sheet)-1
          return workBook[sheet[sheetNumber]]


    def inventoryCount(self,yard,column):
        count = 0
        while True:
            count += 1
            append = column + str(count)
            yard[append].value       
            if yard[append].value  == None:
                break 
        return count-2

               
    def ipDeviceList(self, count, column, yard):
        x=2
        list=[]
        #temp = ""
        for a in range (0,count):
            append1 = column + str(x)
            x +=1
            list.append(str(yard[append1].value))
##            print str(yard[append1].value)
##            #temp= ", ".join(list1)
           
        return list

               
    def ipValidationCheck(self, inventory_list,yard):

        temp = constants.getip          

        if temp in inventory_list:
##            print temp
            return True
        else:
            return False

        
    def deviceValidationCheck(self, inventory_list,yard):
   
        temp = constants.hostname
          
        if temp in inventory_list:            
            return True
        else:
            return False


    def ipReport(self, yard):
        
        if get_field == 1:
            set_field = constants.wb.get_sheet_by_name('IP_Report')
        return set_field

                      
    def entryReport(self,ipCoordinate,deviceCoordinate,yard,row_count):
        
        dataInput = [str(constants.hostname),str(constants.getip),str(ipCoordinate),str(deviceCoordinate),'{:%m-%d-%Y}'.format(datetime.date.today()),'{:%H:%M:%S}'.format(datetime.datetime.now())]

        for i in range(0,6):
            
            temp= chr(ord("B")+i)
##            print "temp",temp
            column = temp + str((row_count+2))
##            print "column",column,dataInput[i]
            yard[column]=dataInput[i]
##            print dataInput[i]
            
        constants.errorReportWB.save(r'C:\Users\Administrator\Desktop\Version5.0\DeviceTrackerReport45.xlsx')
        print "Report Updated"




More information about the Python-list mailing list