class Excel(object): """A utility class to make it easier to get at Excel and manage a single (1) running instance from one class.""" def __init__(self, **kwargs): """ xlApp: instacne of the excel application xlBook: the active excel workbook """ visible = kwargs.get("visible", True) # The state of the current xl window self.number_sheets = 1 # By default excel will open with one sheet AppName = 'Excel.Application' self.xlApp = None # The window frame self.xlBook = None # The name of te file open inside the window (there can be many) self.filename = None if kwargs.get('runninginstance'): # We have to attach to an existing application instance try: filename = kwargs.get('filename') # GetObject(Class='Excel.Application') will get the first Excel instance over and over again self.xlBook = GetObject(filename) #will return a running instance of the file/program (if exists), else starts a new instance #self.xlApp = GetObject(Class=AppName) self.xlApp = kwargs.get('instance', self.xlBook.Application) # The only way to access the parent instnace is to get it from the proccess that started it except TypeError: print "Could not attach to any %s" % AppName return except: # Produces a com_error if the file is not avliable self.xlApp = GetObject(Class=AppName) self.xlBook = self.xlApp.ActiveWorkbook else: try: if kwargs.get('newinstance'): # Create a new instance of the application if kwargs.get('earlybinding') or kwargs.get('staticproxy'): self.xlApp = EnsureDispatch(AppName) else: self.xlApp = DispatchEx(AppName) # Will open a read only copy, if the file is already open if kwargs.get('filename'): try: self.filename = kwargs.get('filename') self.xlBook = self.xlApp.Workbooks.Open(self.filename) except: # The file does not exist pass #self.addWorkbook() # Binds a new xl workbook #self.xlBook = self.xlApp.ActiveWorkbook else: pass #Handled by line: "if kwargs.get('filename'):" for more details #self.xlApp = Dispatch(AppName) except TypeError: print "Could not dispatch %s" % AppName return if kwargs.get('filename') and self.xlBook is None: self.filename = kwargs.get('filename') try: #self.xlBook = self.xlApp.Workbooks.Open(self.filename) self.xlBook = GetObject(self.filename) # This looks like it is opening new files in an existing instance regardless of the newinstance variable being set self.xlApp = self.xlBook.Application #self.xlApp.Windows(self.xlBook.Name).Visible = kwargs.get("BookVisible", True) # The visibility of the workbook in the instace of excel except com_error: # The file does not exist... if self.xlApp: # A new instance of excel was launched, but it has not loaded any workbooks, so ActiveWorkbook is None self.xlApp.Workbooks.Add() self.xlBook = self.xlApp.ActiveWorkbook else: # A new instance was not started, and the file doesn't exist, so start a new workbook in the running instace self.xlApp = Dispatch(AppName) self.xlApp.Workbooks.Add() # Binds a new xl workbook self.xlBook = self.xlApp.ActiveWorkbook elif self.xlApp is None: self.xlApp = Dispatch(AppName) self.xlApp.Workbooks.Add() # Binds a new xl workbook self.xlBook = self.xlApp.ActiveWorkbook # Launch the iHistorian Excel plug-in separately (no plug-ins are launched with the Excel COM object) try: #self.launchiHistorian() <-- there are problems loading the plugin for the latest V7 Proficy Hist pass except: pass self.xlApp.Windows(self.xlBook.Name).Visible = kwargs.get("BookVisible", True) # The visibility of the workbook in the instace of excel self.xlApp.Visible = visible self.FinalTests() # (win32com.client) # Dispatch # Starts a new program process, but doesn't neccessarialy create a instance of the application # DispatchEx # Starts a new instance of the program (allows you to specify if you want the process on this machine or another one, in/out of proccess etc.) # Historian add-in not available # gencache.EnsureDispatch # Ensures a static proxy exists by either creating it or returning an existing one # Use in place of Dispatch() if you always want early binding # GetObject(filename, Class="") # Attaching to an existing application # GetObject attaches to the last opened instance (using GetActiveObject), else starts a new instance (if no other instances of the program e.g. excel are open, else it binds to an existing excel instance) def __repr__(self): filename = self.filename if not self.filename: filename = "Excel: " + str(self.xlApp) return filename def launchiHistorian(self): """This is for loading the GE Proficy Historian Excel Add-in""" iHistorianPath = r"C:\Program Files (x86)\Microsoft Office\Office14\Library\iHistorian.xla" self.xlApp.DisplayAlerts = False assert os.path.exists(iHistorianPath) == True, "Error: There was a problem locating the iHistorian.xla Excel plug-in." self.xlApp.Workbooks.Open(iHistorianPath) self.xlApp.RegisterXLL(iHistorianPath) self.xlApp.Workbooks(iHistorianPath.split("\\")[-1]).RunAutoMacros = True self.xlApp.DisplayAlerts = True def FinalTests(self): """Ensure a static proxy exists""" try: c.xlDown except: from win32com.client import makepy sys.argv = ["makepy", r"C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe"] with Capturing() as printoutput: makepy.main() if len(printoutput): if printoutput[0].startswith("Could not locate a type library matching"): sys.argv = ["makepy", r"C:\Program Files (x86)\Microsoft Office\Office16\Excel.exe"] with Capturing() as printoutput: makepy.main() if len(printoutput): if printoutput[0].startswith("Could not locate a type library matching"): sys.argv= [""] print "Choose: Microsoft Excel 14.0 Object Library (1.7), or Excel 16.0 Object Library (1.9)" with Capturing() as printoutput: makepy.main() if len(printoutput): if printoutput[0].startswith("Could not locate a type library matching"): print "Could not create a win32 proxy for Excel. Stopping now." time.sleep(5) sys.exit() def save(self, newfilepath=None): IntialAlertState = self.xlApp.DisplayAlerts self.xlApp.DisplayAlerts = False if newfilepath: self.filename = newfilepath directory = os.path.dirname(newfilepath) if not os.path.exists(directory): os.makedirs(directory) self.xlBook.SaveAs(newfilepath) else: if os.path.exists(self.filename): #os.remove(self.filename) self.xlBook.Save() else: directory = os.path.dirname(self.filename) if not os.path.exists(directory): os.makedirs(directory) self.xlBook.SaveAs(self.filename) self.xlApp.DisplayAlerts = IntialAlertState # Probably be set back to true, but maybe not def closeWorkbook(self, SaveChanges=0): self.xlApp.Windows(self.xlBook.Name).Visible = True self.xlBook.Close(SaveChanges) # Avoids a prompt when closing out #self.xlApp.Quit() #self.xlApp.Windows(self.xlBook.Name).Visible = 0 self.xlBook = None del self.xlBook # Free-up memory def closeApplication(self): """Closes the entire window/running instance, including any open workbooks.""" numberOfBooks = 0 for book in self.xlApp.Windows: numberOfBooks += 1 self.xlApp.Windows(self.xlBook.Name).Visible = True book.Close(SaveChanges=0) #self.xlBook.Close(SaveChanges=0) # Avoids a prompt when closing out self.xlApp.Visible = 0 # Must do this, else the excel.exe process does not quit self.xlApp.Quit() self.xlApp = None del self.xlApp # Free-up memory def closeInstance(self): """Closes the currently attached instanace, but leaves the window running""" self.xlApp = None del self.xlApp def SaveAsWebPage(self, sheetname, filepath): """Saves a single Excel sheet as a web page. Usefull for extracting charts as images""" if not os.path.exists(filepath): os.mkdir(filepath) Publisher = self.xlBook.PublishObjects.Add(c.xlSourceSheet, os.path.join(filepath, sheetname+".htm"), sheetname, "", c.xlHtmlStatic, "Main name", "") Publisher.Publish(True) # Setting True overwrites any existing published web page
class Usc: """ Survival mini-wrapper for a Unisim case """ def __init__(self, fname): self.case = GetObject(os.path.abspath(fname)) self.fname = self.case.FullName self.integrator = self.case.solver.Integrator self.solver = self.case.solver self.current_time = self.case.solver.Integrator.currenttimevalue self.path = self.case.path self.flsh = self.case.flowsheet self.stream_names = [self.flsh.Streams[i]() for i in self.flsh.Streams] self.streams = self.flsh.Streams self.ops = [self.flsh.Operations[i]() for i in self.flsh.Operations] self.stripcharts = {} self.profiles = {} self.pipes = {} def extract_stripchart(self, stripchart='overall', expose_data=True): """ Extract a specific stripchard and exposes the data in the namespace """ csv_fname = self.fname.split(os.sep)[-1].replace(".usc", ".csv") scp_fname = self.fname.split(os.sep)[-1].replace(".usc", ".SCP") case_details = { 'case': self.fname.__repr__()[1:-1], 'stripchart': stripchart, 'target': self.path.__repr__()[1:-1] + csv_fname } script = STRIPCHART_EXTRACTION_TEMPLATE.substitute(case_details) with open(self.path + scp_fname, 'w') as fobj: fobj.write(script) self.case.visible = True self.case.application.playscript(self.path + scp_fname) self.case.visible = False os.remove(self.path + scp_fname) if expose_data is True: self.stripcharts[stripchart] = unisim_csv_formatting( self.path, csv_fname) if os.path.isdir(self.path + 'trends') is not True: os.mkdir(self.path + 'trends') shutil.copy(self.path + csv_fname, self.path + 'trends\\{}.csv'.format(stripchart)) os.remove(self.path + csv_fname) def extract_profiles(self, pipeline_name, expose_data=True): """ Extract all the profiles of a specific pipeline and exposes the data in the namespace """ compas_pipe = self.__profile_definition(pipeline_name) get_variable = compas_pipe.GEtUserVariable if os.path.isdir(self.path + 'profiles') is not True: os.mkdir(self.path + 'profiles') target_dir = self.path + 'profiles' if expose_data is True: self.profiles[pipeline_name] = {} for key in PROFILE_KEYS: pipe = self.pipes[pipeline_name] pipe['data'][key] = get_variable(PROFILE_KEYS[key]).Variable() temp = {key: val for (key, val) in enumerate(pipe['data'][key])} try: data = pd.DataFrame(temp, index=pipe['grid']) except ValueError: data = pd.DataFrame(temp, index=pipe['non_st_grid']) data.columns = self.pipes[pipeline_name]['timesteps'] data.to_csv('{}/{}-{}.csv'.format(target_dir, pipeline_name, key)) if expose_data is True: self.profiles[pipeline_name][key] = data def __profile_definition(self, pipeline_name): """ Prepare the profiles extraction from a specific profile """ pipe = self.flsh.Operations[pipeline_name] x_st = pipe.GetUserVariable(PROFILE_LENGTH_ST).Variable() x_non_st = pipe.GetUserVariable(PROFILE_LENGTH_NON_ST).Variable() timesteps = pipe.GetUserVariable(PROFILE_TIME).Variable() self.pipes[pipeline_name] = { 'grid': x_st, 'non_st_grid': x_non_st, 'timesteps': timesteps, 'data': {} } return pipe def run_until(self, endtime, timeunit='minutes', save=True): """ Run a case untile the specifiend endtime """ integrator = self.case.solver.Integrator integrator.rununtil(endtime, timeunit) if save is True: self.case.save() def save(self, fname=''): """ Save the current case """ if fname is '': self.case.save() else: self.case.SaveAs(self.path + os.sep + fname) def close(self): """ Close the current case """ self.case.close()