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