def __init__(self, debug=False): self.debug = debug self.xcel = ExcelWrapper() # row indices into 'General' worksheet self.i_price = 5 self.i_nturb = 6 self.i_lftim = 7 if (self.debug): sys.stdout.write("Created ecnomXLS object\n")
def __init__(self,debug=False): self.debug = debug self.xcel = ExcelWrapper() # row indices into 'General' worksheet self.i_price = 5 self.i_nturb = 6 self.i_lftim = 7 if (self.debug): sys.stdout.write ("Created ecnomXLS object\n")
class ecnomXLS(object): ''' class ecnomXLS: a Python interface to the ECN O&M Excel spreadsheet Note: for setCell() and getCell(), the user MUST know the exact cell row and column indicies in 'General' and 'OverviewResults' respectively. Results are unpredictable otherwise. methods execute(), failsweep() and salsweep() are provide only as examples. ''' #----------------------- def __init__(self,debug=False): self.debug = debug self.xcel = ExcelWrapper() # row indices into 'General' worksheet self.i_price = 5 self.i_nturb = 6 self.i_lftim = 7 if (self.debug): sys.stdout.write ("Created ecnomXLS object\n") #----------------------- def ssopen(self,ssfile=None): """ open the ECN O&M spreadsheet """ if (ssfile is not None): if (not os.path.isfile(ssfile)): print "xlsfile not found: {}".format(ssfile) raise ValueError('No such file: {}'.format(ssfile)) #return 0 self.xlsfile = ssfile else: self.xlsfile = " " if (not os.path.isfile(self.xlsfile)): print "xlsfile not valid: {}".format(self.xlsfile) '''raise ValueError('No such file: {}'.format(self.xlsfile))''' else: # Start Excel res = self.xcel.openWorkbook(self.xlsfile) if res != 0: return res # if (self.debug): # print "Found %d sheets" % self.ss.Sheets.Count # for i in range(1,self.ss.Sheets.Count+1): # print "%2d %s" % (i, self.ss.Worksheets(i).Name) # print #----------------------- def ssclose(self): """ close the ECN O&M spreadsheet """ # Close spreadsheet without saving self.xcel.closeWorkbook() # self.ss.Close(False) # If user doesn't have any other workbooks open in Excel, quit Excel if self.xcel.countWorkbooks() == 0: self.xcel.closeExcel() #----------------------- def setCell(self,irow,icol,value): """ set an input cell in the ECN O&M spreadsheet, 'General' worksheet """ res = self.xcel.setCell(irow, icol, value, "General") # self.ss.Activate() # self.xl.Worksheets("General").Activate() # sh = self.ss.ActiveSheet # sh.Cells(irow,icol).Value = value # sh.Calculate() #----------------------- def getCell(self,irow,icol): """ get an output cell in the ECN O&M spreadsheet, 'OverviewResults' worksheet """ #self.xl.Worksheets("OverviewResults").Activate() #sh = self.ss.ActiveSheet #value = sh.Cells(irow,icol).Value # if value.__class__ is 'unicode' or 'NoneType', # we have an error #return (value) # return (self.ss.Worksheets(shnums['OverviewResults']).Cells(irow,icol).Value) cval = self.xcel.getCell(irow, icol, "OverviewResults") return cval #----------------------- def getInputCell(self,irow,icol): """ get an input cell in the ECN O&M spreadsheet, 'Input' worksheet """ # return self.ss.Worksheets(shnums['General']).Cells(irow,icol).Value cval = self.xcel.getCell(irow, icol, "General") return cval #----------------------- def getCost(self): """ return the value of cents/kWH from the ECN O&M spreadsheet, 'OverviewResults' worksheet """ # return (self.ss.Worksheets(shnums['OverviewResults']).Cells(3,6).Value) cval = self.xcel.getCell(irow, icol, "OverviewResults") return cval #----------------------- def printCosts(self): """ print calculated costs from the ECN O&M spreadsheet returns revenue loss, repair costs and totals """ # self.ss.Activate() # self.xl.Worksheets("OverviewResults").Activate() # sh = self.ss.ActiveSheet iRowRL = 24 # revenue losses iRowRC = 56 # repair costs rl = [] rc = [] ttl = [] head = ['Winter', 'Spring', 'Summer', 'Fall', 'Year', 'Total'] print ' ' * 12, for h in head: if h != None: print "{0:>6} ".format(h), print print '{0:12}'.format('Revenue Loss'), for i in range(4,10): val = self.xcel.getCell(iRowRL,i,"OverviewResults") # val = sh.Cells(iRowRL,i).Value rl.append(val) print "{0:6.0f} ".format(val), print print '{0:12}'.format("Repair Cost"), for i in range(4,10): val = self.xcel.getCell(iRowRC,i,"OverviewResults") # val = sh.Cells(iRowRC,i).Value rc.append(val) print "{0:6.0f} ".format(val), print print '{0:12}'.format("Total"), for i in range(4,10): val = self.xcel.getCell(iRowRL,i,"OverviewResults") + self.xcel.getCell(iRowRC,i,"OverviewResults") # val = sh.Cells(iRowRL,i).Value+sh.Cells(iRowRC,i).Value ttl.append(val) print "{0:6.0f} ".format(val), print return (rl, rc, ttl) #----------------------- #----------------------- def execute(self): """ EXAMPLES: ways to access the ECN O&M spreadsheet """ #c61 = currency #i61 = cost isht = shnums['OverviewResults'] print "Cost %.3f %s" % ( self.ss.Worksheets(isht).Cells(61,9).Value, self.ss.Worksheets(isht).Cells(61,3).Text) # Modify the input data sheet self.xl.Worksheets("General").Activate() sh = self.ss.ActiveSheet inpCol = 3 # Set x and y in spreadsheet, calculate and return f(x,y) price = 0.15 # Euros/kWh nturb = 100 lftim = 18 sh.Cells(self.i_price, inpCol).Value = price sh.Cells(self.i_nturb, inpCol).Value = nturb sh.Cells(self.i_lftim, inpCol).Value = lftim sh.Calculate() self.printCosts() #----------------------- def salsweep(self): """ EXAMPLE: Sweep over a salary range """ for i in range(8): salary = 150000 + i*10000. self.ss.Worksheets(shnums['General']).Cells(5,15).Value = salary self.ss.Worksheets(shnums['General']).Calculate() print "Salary {:.1f} Cost {:.1f}".format(salary,self.ss.Worksheets(shnums['OverviewResults']).Cells(61,9).Value) self.ss.Worksheets(shnums['General']).Cells(5,15).Value = 150000 self.ss.Worksheets(shnums['General']).Calculate() #----------------------- def failsweep(self): """ EXAMPLE: Sweep over a failure rate range """ isht = shnums['FaultTypesWT'] fail_rate = self.ss.Worksheets(isht).Cells(wt_failfreq_row['MDK10'],wt_failfreq_col).Value fr_mult = 1.3 for i in range(5): self.ss.Worksheets(isht).Cells(wt_failfreq_row['MDK10'],wt_failfreq_col).Value = fail_rate self.ss.Worksheets(isht).Calculate() print "FailRate {:.2f} Cost {:.1f}".format(fail_rate,self.ss.Worksheets(shnums['OverviewResults']).Cells(61,9).Value) fail_rate *= fr_mult
class ecnomXLS(object): ''' class ecnomXLS: a Python interface to the ECN O&M Excel spreadsheet Note: for setCell() and getCell(), the user MUST know the exact cell row and column indicies in 'General' and 'OverviewResults' respectively. Results are unpredictable otherwise. methods execute(), failsweep() and salsweep() are provide only as examples. ''' #----------------------- def __init__(self, debug=False): self.debug = debug self.xcel = ExcelWrapper() # row indices into 'General' worksheet self.i_price = 5 self.i_nturb = 6 self.i_lftim = 7 if (self.debug): sys.stdout.write("Created ecnomXLS object\n") #----------------------- def ssopen(self, ssfile=None): """ open the ECN O&M spreadsheet """ if (ssfile is not None): if (not os.path.isfile(ssfile)): print "xlsfile not found: {}".format(ssfile) raise ValueError('No such file: {}'.format(ssfile)) #return 0 self.xlsfile = ssfile else: self.xlsfile = " " if (not os.path.isfile(self.xlsfile)): print "xlsfile not valid: {}".format(self.xlsfile) '''raise ValueError('No such file: {}'.format(self.xlsfile))''' else: # Start Excel res = self.xcel.openWorkbook(self.xlsfile) if res != 0: return res # if (self.debug): # print "Found %d sheets" % self.ss.Sheets.Count # for i in range(1,self.ss.Sheets.Count+1): # print "%2d %s" % (i, self.ss.Worksheets(i).Name) # print #----------------------- def ssclose(self): """ close the ECN O&M spreadsheet """ # Close spreadsheet without saving self.xcel.closeWorkbook() # self.ss.Close(False) # If user doesn't have any other workbooks open in Excel, quit Excel if self.xcel.countWorkbooks() == 0: self.xcel.closeExcel() #----------------------- def setCell(self, irow, icol, value): """ set an input cell in the ECN O&M spreadsheet, 'General' worksheet """ res = self.xcel.setCell(irow, icol, value, "General") # self.ss.Activate() # self.xl.Worksheets("General").Activate() # sh = self.ss.ActiveSheet # sh.Cells(irow,icol).Value = value # sh.Calculate() #----------------------- def getCell(self, irow, icol): """ get an output cell in the ECN O&M spreadsheet, 'OverviewResults' worksheet """ #self.xl.Worksheets("OverviewResults").Activate() #sh = self.ss.ActiveSheet #value = sh.Cells(irow,icol).Value # if value.__class__ is 'unicode' or 'NoneType', # we have an error #return (value) # return (self.ss.Worksheets(shnums['OverviewResults']).Cells(irow,icol).Value) cval = self.xcel.getCell(irow, icol, "OverviewResults") return cval #----------------------- def getInputCell(self, irow, icol): """ get an input cell in the ECN O&M spreadsheet, 'Input' worksheet """ # return self.ss.Worksheets(shnums['General']).Cells(irow,icol).Value cval = self.xcel.getCell(irow, icol, "General") return cval #----------------------- def getCost(self): """ return the value of cents/kWH from the ECN O&M spreadsheet, 'OverviewResults' worksheet """ # return (self.ss.Worksheets(shnums['OverviewResults']).Cells(3,6).Value) cval = self.xcel.getCell(irow, icol, "OverviewResults") return cval #----------------------- def printCosts(self): """ print calculated costs from the ECN O&M spreadsheet returns revenue loss, repair costs and totals """ # self.ss.Activate() # self.xl.Worksheets("OverviewResults").Activate() # sh = self.ss.ActiveSheet iRowRL = 24 # revenue losses iRowRC = 56 # repair costs rl = [] rc = [] ttl = [] head = ['Winter', 'Spring', 'Summer', 'Fall', 'Year', 'Total'] print ' ' * 12, for h in head: if h != None: print "{0:>6} ".format(h), print print '{0:12}'.format('Revenue Loss'), for i in range(4, 10): val = self.xcel.getCell(iRowRL, i, "OverviewResults") # val = sh.Cells(iRowRL,i).Value rl.append(val) print "{0:6.0f} ".format(val), print print '{0:12}'.format("Repair Cost"), for i in range(4, 10): val = self.xcel.getCell(iRowRC, i, "OverviewResults") # val = sh.Cells(iRowRC,i).Value rc.append(val) print "{0:6.0f} ".format(val), print print '{0:12}'.format("Total"), for i in range(4, 10): val = self.xcel.getCell(iRowRL, i, "OverviewResults") + self.xcel.getCell( iRowRC, i, "OverviewResults") # val = sh.Cells(iRowRL,i).Value+sh.Cells(iRowRC,i).Value ttl.append(val) print "{0:6.0f} ".format(val), print return (rl, rc, ttl) #----------------------- #----------------------- def execute(self): """ EXAMPLES: ways to access the ECN O&M spreadsheet """ #c61 = currency #i61 = cost isht = shnums['OverviewResults'] print "Cost %.3f %s" % (self.ss.Worksheets(isht).Cells( 61, 9).Value, self.ss.Worksheets(isht).Cells(61, 3).Text) # Modify the input data sheet self.xl.Worksheets("General").Activate() sh = self.ss.ActiveSheet inpCol = 3 # Set x and y in spreadsheet, calculate and return f(x,y) price = 0.15 # Euros/kWh nturb = 100 lftim = 18 sh.Cells(self.i_price, inpCol).Value = price sh.Cells(self.i_nturb, inpCol).Value = nturb sh.Cells(self.i_lftim, inpCol).Value = lftim sh.Calculate() self.printCosts() #----------------------- def salsweep(self): """ EXAMPLE: Sweep over a salary range """ for i in range(8): salary = 150000 + i * 10000. self.ss.Worksheets(shnums['General']).Cells(5, 15).Value = salary self.ss.Worksheets(shnums['General']).Calculate() print "Salary {:.1f} Cost {:.1f}".format( salary, self.ss.Worksheets(shnums['OverviewResults']).Cells(61, 9).Value) self.ss.Worksheets(shnums['General']).Cells(5, 15).Value = 150000 self.ss.Worksheets(shnums['General']).Calculate() #----------------------- def failsweep(self): """ EXAMPLE: Sweep over a failure rate range """ isht = shnums['FaultTypesWT'] fail_rate = self.ss.Worksheets(isht).Cells(wt_failfreq_row['MDK10'], wt_failfreq_col).Value fr_mult = 1.3 for i in range(5): self.ss.Worksheets(isht).Cells(wt_failfreq_row['MDK10'], wt_failfreq_col).Value = fail_rate self.ss.Worksheets(isht).Calculate() print "FailRate {:.2f} Cost {:.1f}".format( fail_rate, self.ss.Worksheets(shnums['OverviewResults']).Cells(61, 9).Value) fail_rate *= fr_mult